Real time analytics at uber @ strata data 2019

Real Time Analytics
at Uber: Bring SQL
into Everything
Zhenxiao Luo

NYC
Uber’s mission is to
ignite opportunity by
setting the world in
motion.
15M
Trips/Day
600+
Cities
75M
Monthly Riders

Data informs every decision at the company

Overview of Uber’s Data Platform
DATA SOURCES
RAW DATA
MODELED TABLES
MINING BUSINESS
INSIGHTS
CONSUMING BUSINESS INSIGHTS
EXPERIMENTATION
DATA SCIENCE
MACHINE
LEARNING
CUSTOM DATA SETS
Dashboarding
Alerting
Monitoring
Data Exploration
Knowledge Bases
Storage
Infrastructure
ETL Frameworks
Data Integrity
Query Engines

Kafka
Uber Data Infrastructure
Schemaless
MySQL,
Postgres
Vertica
Streamio
Raw
Data
Raw
Tables
Sqoop
Reports
Hadoop
Hive Presto Spark
Notebook Ad Hoc Queries
Real Time
Applications
Machine
Learning Jobs
Business
Intelligence Jobs
Cluster
Management
All-Active
Observability
Security
Vertica
Samza
Pinot
Flink
AresDB
Modeled
Tables
Streaming
Warehouse
Real-time

Presto @ Uber-scale
5KWeekly Active Users
160KQueries/day
3Data Centers
2KNodes
700MHDFS ﬁles read/day
10PBHDFS ﬁles
processed/day

Presto use cases at Uber
Growth Marketing
Data Science
Marketplace
Pricing
Community
Operations
Data Quality
Ad-hoc Querying

The people who rely on us
Technical
Skills
Data Scientists
Software Engineers
ML/AI Researchers
Advanced SQL
Advanced Statistics
Scala/Spark, Python/R
Data Modeling
Inventor Ivan
Marketing Managers
Entry-level Analysts
General Managers
Product Managers
Limited SQL
Spreadsheets
Reliant Rebecca
City Operations
Regional Managers
Intermediate SQL
Spreadsheets
Dashboarding
Monitoring Matt
Operations Managers
Data Analysts
Product Analysts
Advanced SQL
Spreadsheets
Limited Statistics
Limited Python/R
Analyst Anna

Exploratory ML &
model-training
Data Scientists ML ResearchersEngineers
Using ML to ensure data
security and compliance

Advanced data
science &
complex analytics
Data Scientists Ops Analysts Support Agents
Surfacing hidden insights
to empower restaurants

Business process
automation
S&P AnalystsOps Managers Contractors
Using technology to make
transportation safer

What is Presto: Interactive SQL
Engine for Big Data
Interactive query speeds
Horizontally scalable
ANSI SQL
Battle-tested by Facebook, Uber, Linkedin, Twitter, Netﬂix, Airbnb, etc
Completely open source
Access to petabytes of data in the Hadoop, Elasticsearch, Pinot, etc.

Why Presto is Fast
● Data in memory during execution
● Pipelining and streaming
● Columnar storage & execution
● Bytecode generation

Resource Management
● Presto has its own resource manager
○ Not on YARN
○ Not on Mesos
● CPU Management
○ Priority queues
○ Short running queries higher priority
● Memory Management
○ Max memory per query per node
○ If query exceeds max memory limit, query fails
○ No OutOfMemory in Presto process

Presto Connectors:
No Need to Copy Data

Contributions
New Features
● Geospatial indexing and operations - 10x or more speedup
● Pinot connector enhancements (in-house)
Optimizations
● Elasticsearch connector
● New Parquet reader - 4x speedup
● Nested column pushdowns (project, predicate) - 10x speedup
Security
● Metastore authentication support for Kerberos deployments
● Dispatch Proxy using HTTP redirect for multi-cluster operation

Presto Connector Interface
● ConnectorMetadata
○ Schema, Table, Column
● ConnectorSplitManager
○ Divide data into splits
● ConnectorSplit
○ Split data range
○ Predicate/JsonFunction/Limit pushdown
● ConnectorRecordCursor
○ Transform underlying storage data into Presto internal
page/block

Presto Elasticsearch Connector

Data Model
● each Elasticsearch index is a table partition
● each ﬁeld of an index is a column
● all Elasticsearch indexes sharing the same preﬁx
consist a logical table
○ Es-vehicles-sjc1, es-vehicles-dca1, es-vehicles

Optimizations
● Parallel Reads
○ Get all indices and search nodes
○ For each search node, send request for one speciﬁc index
● Cap Max Hits
● Predicate Pushdown
● Json Function Pushdown
● Limit Pushdown
● Nested Fields

How many Uber trip
requests did we serve
in Chicago yesterday?

Fetch daily trip count in seconds
SELECT T.base.city_id AS cid,
Count(CASE WHEN T.base.status = 'completed' THEN 1 END) AS
completed_trips,
Count(CASE WHEN T.base.status = 'canceled' THEN 1 END) AS
rider_canceled_trips
FROM trips AS T
WHERE T.datestr = '2019-03-11'
GROUP BY 1

Column Chunk
base.client_uuid
Column Chunk
base.driver_uuid
Column Chunk
base.status
Column Chunk
base.vehicle_id
Column Chunk
base.city_idRow Group
Column Chunk
base.client_uuid
Column Chunk
base.driver_uuid
Column Chunk
base.city_id
Column Chunk
base.vehicle_id
Column Chunk
base.statusRow Group
Parquet
Parquet Footer: File Metadata, Row Group Metadata
Step 1: Read all Parquet nested ﬁelds from disk
base.driver_uuid base.client_uuid base.city_id …... base.vehicle_id base.status
base.driver_uuid
base.driver_uuid
base.driver_uuid
base.driver_uuid base.client_uuid base.city_id …... base.vehicle_id base.status
Presto Columnar Engine
Step 2: Transform Parquet rows into Presto columnar blocks
Step 3: Evaluate predicates on columnar blocks
base.client_uuid
base.client_uuid
base.client_uuid
base.city_id
base.city_id
base.city_id
base.vehicle_id
base.vehicle_id
base.vehicle_id
base.status
base.status
base.status
….
Default Apache Parquet Reader

Column Chunk
base.client_uuid
Column Chunk
base.driver_uuid
Column Chunk
base.status
Column Chunk
base.vehicle_id
Column Chunk
base.city_id
Row Group
Column Chunk
base.client_uuid
Column Chunk
base.driver_uuid
Column Chunk
base.city_id
Column Chunk
base.vehicle_id
Column Chunk
base.status
Row Group
Parquet Footer: File Metadata, Row Group Metadata
Step 1: Read ONLY Required nested fields from disk
Presto Columnar Engine
Apache Parquet Reader Optimization
base.driver_uuid
base.driver_uuid
base.driver_uuid
base.city_id
base.city_id
base.city_id
Step 1: Read ONLY Required nested fields from disk
Evaluate predicates on the fly:
Skip reading row group;
predicate: base.city_id = 12
dictionary: base.city_id: {3,
5, 9, 14, 21}
Build columnar blocks only
for predicate matches
Step 2. Build columnar blocks on the fly
base.driver_uuid
base.driver_uuid
base.driver_uuid
Step 3: Evaluate predicates on columnar blocks
Parquet

Federated SQL Layer
Vision
HDFS
VerticaElasticsearch
Apache
Pinot
MySQL
Machines
Reports
Users
Presto RealTime Presto
Proxy layer
Management
Universal
Metadata
Service

Focus areas
Connectors
● Apache Hive, Apache Pinot, Elasticsearch, Apache Cassandra, Vertica, MySQL, etc
● Aggregation / Join pushdown
● Cross-connector optimizations (hybrid connectors)
Real-time
● Real-time mode with low latency pass through
● Query plan / result / data cache
● Time-series joins and stitching
Universal Metadata Service (UMS)
● Logical deﬁnitions / physical schemas
● Column stitching and joins
● Table and partition caching

Thank you
Proprietary © 2018 Uber Technologies, Inc. All rights reserved. No part of this
document may be reproduced or utilized in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any
information storage or retrieval systems, without permission in writing from
Uber. This document is intended only for the use of the individual or entity to
whom it is addressed. All recipients of this document are notiﬁed that the
information contained herein includes proprietary information of Uber, and
recipient may not make use of, disseminate, or in any way disclose this
document or any of the enclosed information to any person other than
employees of addressee to the extent necessary for consultations with
authorized personnel of Uber.

Real time analytics at uber @ strata data 2019

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Real time analytics at uber @ strata data 2019

Similar to Real time analytics at uber @ strata data 2019 (20)

More from Zhenxiao Luo

More from Zhenxiao Luo (10)

Recently uploaded

Recently uploaded (20)

Real time analytics at uber @ strata data 2019