Need advice about which tool to choose?Ask the StackShare community!
Google BigQuery vs Presto: What are the differences?
Introduction
Google BigQuery and Presto are both popular query engines used for analyzing large datasets. While they share some similarities, there are key differences between the two that set them apart in terms of functionality and performance. In this article, we will explore six major differences between Google BigQuery and Presto.
Underlying Architecture: Google BigQuery is a fully managed serverless data warehouse service provided by Google Cloud. It uses proprietary storage and query execution technology to handle large-scale data processing. On the other hand, Presto is an open-source distributed SQL query engine that can be deployed on-premises or in the cloud. It utilizes a distributed architecture and can process data from a variety of sources.
Data Storage: In BigQuery, data is stored in Google's proprietary columnar storage format, which is designed for efficient query performance. It offers advanced compression techniques and automatic data shuffling for parallel processing. In contrast, Presto doesn't have its own storage layer and can process data stored in various formats, including Hadoop Distributed File System (HDFS), Amazon S3, and more.
Cost Model: BigQuery operates on a fully managed pricing model, where users are billed based on the amount of data processed and the type of operations performed. It provides a predictable cost structure, with options for on-demand or flat-rate pricing. Presto, being an open-source project, is cost-free to use but requires manual setup and management. However, users still need to consider the infrastructure costs for running Presto on their own cluster.
Query Optimization: BigQuery's query optimizer is built into the service and automatically tunes the execution plans for better performance. It takes advantage of columnar storage, parallel processing, and other optimizations to speed up queries. On the other hand, Presto relies on cost-based optimization and joins reordering techniques to optimize query execution plans. It provides users with more control over query tuning but requires manual intervention.
Data Partitioning and Clustering: BigQuery supports partitioning and clustering of data, which helps to improve query performance by reducing the amount of data read during queries. Partitioning can be done based on a specific column or by ingestion time, while clustering helps to physically order the data within each partition. Presto doesn't have native support for these features, although it can leverage external tools or techniques to achieve similar optimizations.
Integration with Ecosystem: BigQuery integrates seamlessly with other Google Cloud services, such as Google Data Studio, Google Analytics, and Machine Learning APIs. It also has connectors for various data ingestion methods, such as Dataflow and Pub/Sub. Presto, being an open-source project, can be extended or customized to integrate with a wide range of tools and platforms. It has connectors for popular systems like Hive, Hadoop, and more.
In summary, Google BigQuery is a fully managed data warehouse with proprietary storage and query execution technology, optimized for scalability, ease of use, and integration with the Google Cloud ecosystem. Presto, on the other hand, is an open-source distributed SQL query engine that can be deployed in different environments, provides more flexibility in data sources, query optimization, and customization, but requires manual management and setup.
Cloud Data-warehouse is the centerpiece of modern Data platform. The choice of the most suitable solution is therefore fundamental.
Our benchmark was conducted over BigQuery and Snowflake. These solutions seem to match our goals but they have very different approaches.
BigQuery is notably the only 100% serverless cloud data-warehouse, which requires absolutely NO maintenance: no re-clustering, no compression, no index optimization, no storage management, no performance management. Snowflake requires to set up (paid) reclustering processes, to manage the performance allocated to each profile, etc. We can also mention Redshift, which we have eliminated because this technology requires even more ops operation.
BigQuery can therefore be set up with almost zero cost of human resources. Its on-demand pricing is particularly adapted to small workloads. 0 cost when the solution is not used, only pay for the query you're running. But quickly the use of slots (with monthly or per-minute commitment) will drastically reduce the cost of use. We've reduced by 10 the cost of our nightly batches by using flex slots.
Finally, a major advantage of BigQuery is its almost perfect integration with Google Cloud Platform services: Cloud functions, Dataflow, Data Studio, etc.
BigQuery is still evolving very quickly. The next milestone, BigQuery Omni, will allow to run queries over data stored in an external Cloud platform (Amazon S3 for example). It will be a major breakthrough in the history of cloud data-warehouses. Omni will compensate a weakness of BigQuery: transferring data in near real time from S3 to BQ is not easy today. It was even simpler to implement via Snowflake's Snowpipe solution.
We also plan to use the Machine Learning features built into BigQuery to accelerate our deployment of Data-Science-based projects. An opportunity only offered by the BigQuery solution
To provide employees with the critical need of interactive querying, we’ve worked with Presto, an open-source distributed SQL query engine, over the years. Operating Presto at Pinterest’s scale has involved resolving quite a few challenges like, supporting deeply nested and huge thrift schemas, slow/ bad worker detection and remediation, auto-scaling cluster, graceful cluster shutdown and impersonation support for ldap authenticator.
Our infrastructure is built on top of Amazon EC2 and we leverage Amazon S3 for storing our data. This separates compute and storage layers, and allows multiple compute clusters to share the S3 data.
We have hundreds of petabytes of data and tens of thousands of Apache Hive tables. Our Presto clusters are comprised of a fleet of 450 r4.8xl EC2 instances. Presto clusters together have over 100 TBs of memory and 14K vcpu cores. Within Pinterest, we have close to more than 1,000 monthly active users (out of total 1,600+ Pinterest employees) using Presto, who run about 400K queries on these clusters per month.
Each query submitted to Presto cluster is logged to a Kafka topic via Singer. Singer is a logging agent built at Pinterest and we talked about it in a previous post. Each query is logged when it is submitted and when it finishes. When a Presto cluster crashes, we will have query submitted events without corresponding query finished events. These events enable us to capture the effect of cluster crashes over time.
Each Presto cluster at Pinterest has workers on a mix of dedicated AWS EC2 instances and Kubernetes pods. Kubernetes platform provides us with the capability to add and remove workers from a Presto cluster very quickly. The best-case latency on bringing up a new worker on Kubernetes is less than a minute. However, when the Kubernetes cluster itself is out of resources and needs to scale up, it can take up to ten minutes. Some other advantages of deploying on Kubernetes platform is that our Presto deployment becomes agnostic of cloud vendor, instance types, OS, etc.
#BigData #AWS #DataScience #DataEngineering
The platform deals with time series data from sensors aggregated against things( event data that originates at periodic intervals). We use Cassandra as our distributed database to store time series data. Aggregated data insights from Cassandra is delivered as web API for consumption from other applications. Presto as a distributed sql querying engine, can provide a faster execution time provided the queries are tuned for proper distribution across the cluster. Another objective that we had was to combine Cassandra table data with other business data from RDBMS or other big data systems where presto through its connector architecture would have opened up a whole lot of options for us.
Pros of Google BigQuery
- High Performance28
- Easy to use25
- Fully managed service22
- Cheap Pricing19
- Process hundreds of GB in seconds16
- Big Data12
- Full table scans in seconds, no indexes needed11
- Always on, no per-hour costs8
- Good combination with fluentd6
- Machine learning4
- Easy to manage1
- Easy to learn0
Pros of Presto
- Works directly on files in s3 (no ETL)18
- Open-source13
- Join multiple databases12
- Scalable10
- Gets ready in minutes7
- MPP6
Sign up to add or upvote prosMake informed product decisions
Cons of Google BigQuery
- You can't unit test changes in BQ data1
- Sdas0
Cons of Presto
Sign up to add or upvote consMake informed product decisions
What is Google BigQuery?
What is Presto?
Need advice about which tool to choose?Ask the StackShare community!
What companies use Google BigQuery?
What companies use Presto?
Sign up to get full access to all the companiesMake informed product decisions
What tools integrate with Presto?
Sign up to get full access to all the tool integrationsMake informed product decisions
Blog Posts
+17
+11
+16
+25
+47
+31
+39+26