Presented By
O’Reilly + Cloudera
Make Data Work
March 25-28, 2019
San Francisco, CA

Adaptive ETL to optimize query performance at Lyft

James Taylor (Lyft)
2:40pm3:20pm Wednesday, March 27, 2019
Average rating: ***..
(3.56, 9 ratings)

Who is this presentation for?

  • Data infrastructure engineers and data architects

Level

Advanced

Prerequisite knowledge

  • Familiarity with big data ETL, querying, and optimization

What you'll learn

  • Learn how Lyft optimizes big data queries

Description

In today’s big data world, it’s difficult to manually adjust the structure of your data to achieve optimal query performance. In a company-wide ad hoc query environment, many different types of users access the same data in ever-changing ways, gleaning business insights and asking new questions—leading to new query patterns. With a typical skew of 20% of the queries accounting for 80% of the load, it quickly becomes infeasible to analyze and optimize this manually as a one-time task.

To help with this issue, Lyft developed an automated feedback loop to adapt its ETL based on monitoring the cost of queries run on the system by identifying the most common columns being filtered on in the most expensive queries against the highest cardinality values across all tables; feeding this information into a recommendation engine to determine the optimal column sorting to use at ingest time to achieve the maximum partition pruning by Presto’s Parquet reader; and driving column sorting and distribution during ingest using these recommendations.

James Taylor explores the system and the impact it has had on cluster load over time and discusses future work to enhance the system through the use of materialized views to reduce the number of ad hoc joins and sorting performed by the most expensive queries by transparently rewriting queries when possible.

Photo of James Taylor

James Taylor

Lyft

James Taylor is a software engineer in the Data Infrastructure Group at Lyft, where he works on big data systems. Previously, he was an architect at Salesforce, where he founded the Apache Phoenix project and led its development, and worked on federated query processing systems and event-driven programming platforms at BEA Systems.