Harnessing the Power of Presto and Starburst for Big Data Analytics

ByJordan Brown

In the world of big data, the ability to rapidly query and analyze massive datasets is crucial. Traditionally, this has required moving data into specialized data warehouses or writing complex code to process data on Hadoop clusters.

But what if you could query big data in place, wherever it resides, using familiar SQL syntax? That‘s the promise of Presto, a fast and scalable distributed SQL query engine.

In this post, we‘ll dive into what makes Presto uniquely powerful, especially for machine learning and AI applications. We‘ll go under the hood of its intelligent query optimizer, benchmark its performance against other engines, and highlight how Starburst makes Presto enterprise-grade.

By the end of this guide, you‘ll be equipped to leverage Presto for your most demanding big data analytics and data science use cases. Let‘s get started!

What is Presto?

Presto is an open source, high-performance, distributed SQL query engine for big data. It was originally developed at Facebook to power fast, interactive analytics on their 300PB+ data warehouse.

Since being open sourced in 2013, Presto has seen massive adoption. Today it powers analytics for tech giants like Uber, Twitter, Airbnb, Netflix, Salesforce, LinkedIn, and more. It‘s also widely used in finance, retail, healthcare, and beyond.

So what makes Presto so powerful? Let‘s look at some of the key features and architectural choices:

Intelligent Query Optimizer

At the heart of Presto is a sophisticated query optimizer that leverages a variety of statistics and heuristics to produce highly efficient query plans. Some of the key optimizations include:

  • Cost-based optimization (CBO): Presto builds a graph model of the query and available table/partition statistics to identify the fastest plan.
  • Dynamic filtering: For queries with selective joins, Presto pushes the join predicates up to the data source as a filter, greatly reducing the amount of data scanned. Facebook has seen a 10-100x reduction in data scanned using this technique.
  • Connector predicate pushdown: Presto can push column- and partition-level predicates down to the data source for evaluation, minimizing data transfer.
  • Partial aggregation: For queries with aggregations, Presto performs the aggregation as early and locally as possible rather than centralizing all data.
  • Optimized joins: Presto supports hash, broadcast, and dynamic partition pruning-based joins that optimize for different table sizes and data distributions.

Presto also makes heavy use of machine learning in its optimizer. It collects detailed statistics on query performance which it uses to continuously learn and adapt its model of the underlying data and clusters.

Some examples of how Presto leverages AI/ML:

  • Using ML models like decision trees and neural nets to estimate cardinalities and IO costs during plan exploration
  • Adaptive techniques like join reordering, skew detection, and data flow rebalancing based on runtime statistics
  • Anomaly detection to identify and fail slow or hanging queries
  • Automated tuning and modeling of key configs like memory, concurrency, and worker sizing

Separate Compute & Storage

Another key aspect of Presto is that it decouples computation from storage. Presto is a pure compute engine that can be scaled up and down independently of the data volume.

This is especially important for AI/ML use cases that have bursty, unpredictable compute needs. With Presto you can elastically scale a cluster to handle a massive training workflow, then scale it back down.

Presto is also storage-agnostic and can query data from anywhere, including HDFS, cloud stores like S3, NoSQL DBs like Cassandra, and even federated across multiple systems. This "data lake" architecture avoids silos and allows for analytic flexibility.

Performance & Scale

Presto is designed for interactive, sub-second queries on data of any scale, from GBs to PBs. It achieves this with:

  • Distributed, in-memory processing with a "push" model
  • Pipelined execution that avoids unnecessary I/O and materialization
  • Custom data processing kernels and columnar caching
  • Dynamic partition pruning and late materialization
  • Efficient ORC & Parquet readers with lazy decode

Just how fast is Presto? Here are some benchmarks:

  • Facebook has seen Presto consistently outperform Hive by 10x in their production workloads
  • Airbnb found Presto 2.7x faster than Hive for their ad-hoc analytics
  • AtScale performed a TPC-DS benchmark across Presto, Spark, Hive, and Impala and found Presto had the fastest overall query times
  • Starburst shared benchmarks showing Starburst Enterprise performing SQL queries on a 1+ TB TPC-H dataset in 1-2 seconds

Interactive Queries & Data Science

One of Presto‘s greatest strengths is empowering data scientists to interactively explore big data using familiar tools. With Presto, you can use SQL to quickly filter, aggregate, and visualize data directly from your data lake.

This is a game changer compared to the slow, batch-oriented workflows of MapReduce or Spark. With Presto, you can iterate on your queries and models in seconds, not hours.

Presto also integrates with a variety of data science tools and notebooks, including:

  • Jupyter via the presto-jupyter SQL magics
  • RStudio via the RPresto package
  • Tableau, Looker, Microstrategy, and more via ODBC/JDBC

This allows data scientists to incorporate big data into their analysis and ML workflows, without needing to move or downsample data.

Enterprise Scale with Starburst

While open source Presto is powerful, deploying and optimizing Presto for production use cases does require some heavy lifting. Key enterprise needs like security, fine-grained access control, high availability, and performance at scale all need to be addressed.

That‘s where Starburst comes in. Starburst provides an enterprise-grade distribution of Presto along with 24/7 support and services.

Some of the key features Starburst adds include:

  • Fine-grained, role-based access control and column/row-level security that integrates with existing postures like LDAP and Kerberos
  • Global security and policy enforcement including user auditing, data masking, and end-to-end data encryption
  • Built-in high-availability and redundancy with advanced failure handling and auto-scaling
  • Cluster monitoring, usage analytics, and alerting via a management console
  • Performance acceleration features like indexing, caching, materialized views, and optimized connectors
  • Expert support from the Presto founders and largest team of Presto contributors in the world

Starburst allows you to get the power of Presto, at scale, without needing to become Presto experts. It‘s the fastest path to generating insights from a secure, highly-performant Presto cluster.

Presto Use Cases & Case Studies

So what are some of the ways leading companies are using Presto? Here‘s a run through some common use cases and examples.

Petabyte Scale Analytics

Presto was built to enable interactive queries on massive datasets. Internet giants like Facebook, Netflix, and Uber all use Presto to power reporting and dashboarding at the petabyte scale.

  • Facebook uses Presto for interactive queries on their 300PB data warehouse. It powers 1000s of queries each day, scanning over 1PB of data.
  • Netflix uses Presto on their 100PB+ data lake for use cases ranging from ETL to recommendation algorithms. Presto handles millions of queries daily.
  • Uber uses Presto for everything from fraud detection, to A/B testing, to surge pricing. It‘s the query engine behind their 100PB data lake.

Self-Service BI on Data Lakes

A common challenge with data lakes is making them accessible to BI users beyond data engineers. Presto enables interactive, self-service analytics on data in HDFS or cloud storage without moving it.

  • FINRA (Financial Industry Regulatory Authority) built a Presto-powered data platform that enabled self-service access to a 20PB data lake for 1000s of analysts. It processes over 30B transactions daily.
  • Zalando, Europe‘s largest fashion ecommerce company, uses Presto to enable self-service analytics on their S3 data lake, powering 1600+ BI dashboards.

Data Science & Machine Learning

The ability to process large, diverse datasets is crucial for training machine learning models and doing predictive analytics. Presto makes it easy to extract insights from big data using SQL and feed those into ML workflows.

  • Airbnb uses Presto to power ML feature engineering on top of their Hive data warehouse. Data scientists crunch TBs of data and productionize model training using SQL.
  • Criteo, a global ad-tech leader, uses Presto to make multiple TBs of daily ad data available for interactive analysis and exploration to data science teams globally.

Customer 360 & Behavior Analytics

Building a holistic view of customers increasingly means stitching together disparate datasets, from mobile to web to CRM. Presto excels at joining and aggregating data across sources.

  • Comcast uses Presto to join customer data across 20+ data sources to create unified profiles enabling use cases like personalized recommendations and predicting churn.
  • Doordash uses Presto to combine event streams, user data, and order logs to understand customer flows and optimize key metrics like conversion.

Real-Time Analytics

While Presto began as a batch engine, it has evolved to support real-time streaming use cases. Its in-memory processing and ability to "push down" aggregations enable high performance on continually updating data.

  • Pinterest uses Presto to query real-time user events as a supplement to their batch pipelines. It powers applications like real-time personalization and anomaly detection.
  • Uber uses Presto on top of Apache Pinot to slice and dice TBs of real-time rider/driver data for use cases like supply positioning and ETA prediction.

Getting Started with Presto

Hopefully you‘re now well-versed in what Presto is and what it can do. So how do you get started? Here‘s a quick tutorial on deploying Presto and running your first queries.

Deployment

First, you‘ll need to choose your deployment model. You can either manually deploy open source PrestoDB, or use one of the managed offerings.

For open source, you‘ll need to:

  1. Provision hardware for a coordinator node and worker nodes. The coordinator needs 16+ GB RAM while the workers can be sized based on workload.
  2. Install Java 11+ and the Presto server binaries on each node
  3. Configure the coordinator and workers by editing the config.properties, jvm.config, node.properties files
  4. Setup connectors to your data sources by dropping connector config files into the catalog directory
  5. Start the coordinator and worker processes

For a managed experience, you can use Starburst Galaxy, which provides a fully hosted and managed Presto cluster. Simply connect your data sources and you‘re off to the races.

Running Queries

Once deployed, there are a variety of ways to interface with Presto to issue queries:

  • Presto CLI – interactive terminal for executing statements and scripts
  • JDBC/ODBC driver – connect from any compatible app or tool
  • REST API – submit queries and check status via HTTP
  • BI tools – explore data visually using SQL-generating tools like Tableau

Here‘s an example query that joins and filters across data in Hive and MySQL:

SELECT u.name, o.amount, o.status 
FROM mysql.db.users u
JOIN hive.db.orders o
  ON u.id = o.buyer
WHERE o.amount > 100 
  AND o.status = ‘PENDING‘

This query will be optimized by Presto‘s cost-based optimizer, pushed down as needed to the Hive and MySQL sources, and executed in memory across the worker nodes. The results will be streamed back as soon as ready.

From here, the possibilities are endless! You can use Presto to slice and dice your big data however you want, powering use cases from reporting to ML.

Conclusion

Presto is a powerful SQL engine for interactive analytics at any scale of data. Its memory-optimized, scale-out architecture and intelligent query optimizer make it perfect for ad-hoc exploration and iteration.

Data-driven organizations around the globe rely on Presto to query their data lakes, power BI reporting, and feed ML applications and AI workflows. And with Starburst, you can leverage Presto in production with enterprise-grade performance, security, and support.

No matter where you are on your big data journey, Presto can help you extract insights to power your business. To learn more, check out:

May the Presto be with you on your big data adventures!

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts