The article, Engineering SQL Support on Apache Pinot at Uber, was originally published by Uber on the Uber Engineering Blog on January 15, 2020. Check out eng.uber.com for more articles about Uber's engineering work and follow Uber Engineering at @UberEng and Uber Open Source at @UberOpenSouce on Twitter for updates from our teams.
Uber leverages real-time analytics on aggregate data to improve the user experience across our products, from fighting fraudulent behavior on Uber Eats to forecasting demand on our platform.
As Uber’s operations became more complex and we offered additional features and services through our platform, we needed a way to generate more timely analytics on our aggregated marketplace data to better understand how our products were being used. Specifically, we needed our Big Data stack to support cross-table queries as well as nested queries, both requirements that would enable us to write more flexible ad hoc queries to keep up with the growth of our business.
To resolve these issues, we built a solution that linked Presto, a query engine that supports full ANSI SQL, and Pinot, a real-time OLAP (online analytical processing) datastore. This married solution allows users to write ad-hoc SQL queries, empowering teams to unlock significant analysis capabilities.
By engineering full SQL support on Apache Pinot, users of our Big Data stack can now write complex SQL queries as well as join different tables in Pinot with those in other datastores at Uber. This new solution enables operations teams with basic SQL knowledge to build dashboards for quick analysis and reporting on aggregated data without having to spend extra time working with engineers on data modelling or building data pipelines, leading to efficiency gains and resource savings across the company.
Denormalized data with nested values (e.g. array/map) have become omnipresent in this Big Data era, as a lot of data naturally conforms to a nested representation [1, 2]. As a result it is important to provide an efficient and convenient way to query nested data. SQL traditionally does not include support for this.
The pioneering work of Dremel proposed an extension to SQL based on recursive relational algebra to allow querying nested records [1], and is now available in BigQuery and the SQL:2016 standard. The following example shows how to transform array elements with this (adapted from BigQuery Docs):
SELECT elements,
ARRAY(SELECT v * 2FROMUNNEST(elements) AS v) AS multiplied_elements
FROM (
VALUES
(ARRAY[1, 2]),
(ARRAY[1, 3, 9]),
(ARRAY[1, 4, 16, 64])
) AS t(elements)
elements | multiplied_elements
----------------+---------------------
[1, 2] | [2, 4]
[1, 3, 9] | [2, 6, 18]
[1, 4, 16, 64] | [2, 8, 32, 128]
(3rows)
While nested relational algebra provides an elegant and unified approach to query nested data, we found it could be challenging for users to track the “unnest stack” in mind when writing the query. In our experience, users are more comfortable to apply a given function (e.g lambda) to each element in the collection. This motivates us to introduce lambda expressions into Presto to help query nested data, as illustrated below:
SELECT elements,
transform(elements, v -> v * 2) as multiplied_elements
FROM (
VALUES
(ARRAY[1, 2]),
(ARRAY[1, 3, 9]),
(ARRAY[1, 4, 16, 64])
) AS t(elements)
In Presto, a lambda expression consists of an argument list and lambda body, separated by ->:
x -> x + 1
(x, y) -> x + y
x -> regexp_like(x, 'a+')
x -> x[1] / x[2]
x -> IF(x > 0, x, -x)
x -> COALESCE(x, 0)
x -> CAST(x AS JSON)
x -> x + TRY(1 / 0)
Note there is no type annotation in a lambda expression. The type of a lambda expression (e.g. function(integer, integer)) thus has to be inferred from the context of function call. As a result, standalone lambda expressions are not allowed since their types cannot be determined.
On March 24, 2020 in San Mateo, the Presto Foundation, in partnership with The Linux Foundation, will be hosting the organization’s first-ever PrestoCon. The event, one of the first Presto-focused full-day conferences ever held, will feature speakers from Uber, Facebook, and Twitter, as well as tech talks from other major Presto contributors and enthusiasts.
Presto, a high performance, distributed SQL query engine for Big Data, was originally developed at Facebook to power large-scale data analytics on Apache Hadoop. Open sourced in 2013, the PrestoDB project was contributed to the Linux Foundation in 2019 to facilitate an open and neutral governance model that has enabled the software to grow and diversify its community of contributors.
In addition to keynotes and tech talks, PrestoCon will bring together Big Data and analytics practitioners from across the world for a day of networking and collaboration to advance the use of Presto. We will close out the event with a special happy hour for all attendees, providing an opportunity for discussion and socializing.
For those interested in speaking about their Presto experiences or best practices, we encourage you to submit a proposal to speak.
Don’t forget to register for the event as spaces are limited. We look forward to seeing you there!
Facebook: James Sun, Maria Basmanova, Rongrong Zhong, Jiexi Lin, Saksham Sachdev
Pinterest: Yi He
University of Waterloo: Akshay Pall
Presto defines a connector API that allows Presto to query any data source that has a connector implementation. The existing connector API provides basic predicate pushdown functionality allowing connectors to perform filtering at the underlying data source.
However, there are certain limitations with the existing predicate pushdown functionality that limits what connectors can do. The expressiveness of what can be pushed down is limited and the connectors can't change the structure of the plan at all.
Ying Su, Masha Basmanova, Orri Erling, Tim Meehan, Sahar Massachi, Bhavani Hari
We like Presto. We like it a lot — so much we want to make it better in every way. Here's an example: we just optimized the PartitionedOutputOperator. It's now 2-3x more CPU efficient, which, when measured against Facebook's production workload, translates to 6% gains overall. That's huge.
The optimized repartitioning is in use on some production Presto clusters right now, and available for use as of release 0.229.
In this note, let's go over how we did it, what optimizations we unlocked specifically, and a case study of how we approached opportunity sizing whether this was worth doing at all.
The Presto Foundation (PF) was established in September 2019 as an openly governed and vendor-neutral body dedicated to scaling and diversifying the Presto community. Hosted by the Linux Foundation, PF and its Governing Board are in a unique position to make Presto the fastest and the most reliable SQL engine for massively distributed data processing.
After many discussions, the Governing Board agreed and ratified the PF’s guiding principles and 2020 strategic goals.
Presto Foundation Guiding Principles
Our guiding principles reflect our commitment to open governance, neutrality, and community building, listed below:
One open, neutral and united Presto community
One of the main reasons the founding members came together and created the Presto Foundation was to unite the Presto community. The Presto community and its contributors have been working on many great capabilities and connectors, and many of these haven’t made their way back into the repo, and we truly believe that these can benefit many other users. We invite all Presto community members to embrace PrestoDB and contribute back to move the project faster, reduce duplication of efforts and create wider impact for the entire community.
Open, transparent technical leadership and direction
At the end of the day, technology and technical direction is what keeps a community interested and the innovation going. We want the project to be inclusive and the technical roadmap to be fairly representative of the community’s view. In fact, the Technical Steering Committee has its own open charter and the meetings are open for everyone to attend. We invite you to join the Technical Steering Committee meetings. We believe under the open and transparent governance of the Technical Steering Committee, the PrestoDB project will continue to grow, gain contributors, and solve some hard technical challenges.
No one individual or company is greater than the project and its community
The Linux Foundation is dedicated to building sustainable ecosystems around open source projects to accelerate technology development and commercial adoption. It is the home of Linux creator Linus Torvalds, and most importantly provides a neutral home to Linux and other projects where their development can be protected and accelerated for years to come. This important principle of neutrality has been the core of Linux Foundation as well as Apache Software Foundation so that no one individual or company agenda becomes that of the projects. Neutrality will drive the approach to governance that we have adopted so that no single company or individual can take undue influence.
2020 Strategic Goals
With our guiding principles in mind, the PF Governing Board developed six strategic goals to guide our growth in 2020. The goals, listed in no particular order, below, are fluid and meant to evolve over time with input from the community:
Unify Presto under one neutral and openly governed community
By establishing a neutral and openly governed body to help incorporate new contributions to Presto, we can facilitate the open sourcing and maintenance of these updates to the software, and in turn, provide great benefit to Presto’s users. Additionally, unifying Presto under one community will enable greater adoption and collaboration with benefits beyond the foundation itself.
Cultivate adoption of Presto across industries
With a history of adopting and supporting the growth of ecosystems for such projects as Envoy, Kubernetes, LetsEncrypt, Prometheus, Node.js, we are confident that the Linux Foundation will provide an open, collaborative, and supportive environment for the growth of the PF and the broader Presto community. Through the foundation, we invite all Presto users to embrace PrestoDB and contribute back to the project faster, allowing us to reduce duplication and create true impact.
Create value for all PF developers and members
The foundation’s neutral and open governance body is core to the PF’s ability to ensure that all members benefit from the organization, and no single company or individual can wield undue influence. Additionally, the PF’s Technical Steering Committee (TSC) is responsible for ensuring that project development is on track, software contributions are high quality, and all developer opinions and perspectives are welcomed and considered as it relates to the future of the ecosystem. We invite you to join the TSC meetings and we believe under the leadership of our TSC Chairperson Nezih Yigitbasi (PrestoDB Lead at Facebook), the PrestoDB project will continue to grow, gain contributors, and solve hard technical challenges applicable to organizations across the industry.
Grow PF membership
The PF’s success is dependent on the growth of our community. In 2020, we intend to increase PF membership through our support from the Linux Foundation, our neutral and open Governing Board, the dedication of our Technical Steering Committee, and various other initiatives that will contribute to a robust and diverse community that represents the scope and scale of Presto usage by organizations worldwide.
Champion diversity and inclusion in the community
In addition to growing our membership, the foundation is committed to broadening the diversity of our community, and making sure that all members are supported. To this end, our Governing Board ensures that all members feel welcomed regardless of their background, and our Technical Steering Committee’s open charter is reflective of the diverse perspectives and opinions of our members, with TSC meetings open to everyone in the community.
Create community events to foster collaboration
In 2020, we intend to host neutral events that support discussion and open collaboration between all members of our community. Through these initiatives, we can bring about unprecedented growth of the Presto ecosystem that will open up new partnership opportunities for both members and end users alike.
Moving forward: PrestoCon 2020
The journey to make PrestoDB into a successful project is just beginning. As Chairperson of the Governing Board, it gives me great pleasure to announce a key milestone in this journey that brings together our core principles and strategic goals: in early 2020, the Presto Foundation will be hosting the first-ever PrestoCon. The details are still in the works and a date will be announced shortly. We look forward to new contributions, new memberships, and new milestones.
We invite everyone who has built upon Presto to join our community. We truly believe that having all users and contributors working together under one neutral organization will accelerate the growth of the Presto ecosystem.
Sincerely,
Brian Hsieh, Uber
Head of Open Source Program Office and Presto Foundation Governing Board Chairperson
In the previous article we saw what gains are possible when filtering early and in the right order. In this article we look at how we do this with nested and structured types.
We are excited to announce today, in partnership with Alibaba, Facebook, Twitter, and Uber, the launch of the Presto Foundation, a non-profit organization under the umbrella of the Linux Foundation.
Hosting by the Linux Foundation opens up the Presto community to a broader ecosystem of users and contributors. The Presto Foundation's open and neutral governance enables the community to influence Presto's future, which will also make it more attractive to developers. Together, we will raise Presto's performance, scalability, and reliability to new heights that could never have been reached alone.
This is a new chapter for the Presto open source project. We are very excited for what lies ahead!
In a multi-tenant system like Presto careful memory management is required to keep the system stable and prevent individual queries from taking over all the resources. However, tracking the memory usage of data structures in an application (Presto) running on the Java Virtual Machine (JVM) requires a significant amount of work. In addition, Presto is a distributed system, which makes the problem more complicated. This post provides an overview of how memory management works in Presto, and provides info about the various memory management related JMX counters/endpoints that can be used for monitoring production clusters.
Wenlei Xie, Andrii Rosa, Shixuan Fan, Rebecca Schlussel, Tim Meehan
Presto is an open source distributed SQL query engine for running analytic queries against data sources of all sizes ranging from gigabytes to petabytes.
Presto was originally designed for interactive use cases, however, after seeing the merit in having a single interface for both batch and interactive, it is now also used heavily for processing batch workloads [6]. As a concrete example, more than 80% of new warehouse batch workloads at Facebook are developed on Presto. Its flexible “connector” design makes it possible to run queries against heterogeneous data sources — such as joining together Hive and MySQL tables without preloading the data.
However, memory-intensive (many TBs) and long-running (multiple hours) queries have been major pain points for Presto users. It is difficult to reason how much memory queries will use and when it will hit memory limit, and failures in long-running queries cause retries which create landing time variance. To improve user experience and scale MPP Database to large ETL workloads, we started this Presto Unlimited project.
