Need advice about which tool to choose?Ask the StackShare community!

Google BigQuery

1.6K
1.5K
+ 1
152
Presto

393
1K
+ 1
66
Add tool

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

Decisions about Google BigQuery and Presto
Julien Lafont

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

See more
Ashish Singh
Tech Lead, Big Data Platform at Pinterest · | 38 upvotes · 2.9M views

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

See more
Karthik Raveendran
CPO at Attinad Software · | 3 upvotes · 208.9K views

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.

See more
Get Advice from developers at your company using StackShare Enterprise. Sign up for StackShare Enterprise.
Learn More
Pros of Google BigQuery
Pros of Presto
  • 28
    High Performance
  • 25
    Easy to use
  • 22
    Fully managed service
  • 19
    Cheap Pricing
  • 16
    Process hundreds of GB in seconds
  • 12
    Big Data
  • 11
    Full table scans in seconds, no indexes needed
  • 8
    Always on, no per-hour costs
  • 6
    Good combination with fluentd
  • 4
    Machine learning
  • 1
    Easy to manage
  • 0
    Easy to learn
  • 18
    Works directly on files in s3 (no ETL)
  • 13
    Open-source
  • 12
    Join multiple databases
  • 10
    Scalable
  • 7
    Gets ready in minutes
  • 6
    MPP

Sign up to add or upvote prosMake informed product decisions

Cons of Google BigQuery
Cons of Presto
  • 1
    You can't unit test changes in BQ data
    Be the first to leave a con

    Sign up to add or upvote consMake informed product decisions

    What is Google BigQuery?

    Run super-fast, SQL-like queries against terabytes of data in seconds, using the processing power of Google's infrastructure. Load data with ease. Bulk load your data using Google Cloud Storage or stream it in. Easy access. Access BigQuery by using a browser tool, a command-line tool, or by making calls to the BigQuery REST API with client libraries such as Java, PHP or Python.

    What is Presto?

    Distributed SQL Query Engine for Big Data

    Need advice about which tool to choose?Ask the StackShare community!

    Jobs that mention Google BigQuery and Presto as a desired skillset
    What companies use Google BigQuery?
    What companies use Presto?
    See which teams inside your own company are using Google BigQuery or Presto.
    Sign up for StackShare EnterpriseLearn More

    Sign up to get full access to all the companiesMake informed product decisions

    What tools integrate with Google BigQuery?
    What tools integrate with Presto?

    Sign up to get full access to all the tool integrationsMake informed product decisions

    Blog Posts

    Aug 28 2019 at 3:10AM

    Segment

    PythonJavaAmazon S3+16
    7
    2555
    Jul 2 2019 at 9:34PM

    Segment

    Google AnalyticsAmazon S3New Relic+25
    10
    6748
    GitHubPythonNode.js+47
    54
    72302
    What are some alternatives to Google BigQuery and Presto?
    Google Cloud Bigtable
    Google Cloud Bigtable offers you a fast, fully managed, massively scalable NoSQL database service that's ideal for web, mobile, and Internet of Things applications requiring terabytes to petabytes of data. Unlike comparable market offerings, Cloud Bigtable doesn't require you to sacrifice speed, scale, or cost efficiency when your applications grow. Cloud Bigtable has been battle-tested at Google for more than 10 years—it's the database driving major applications such as Google Analytics and Gmail.
    Amazon Redshift
    It is optimized for data sets ranging from a few hundred gigabytes to a petabyte or more and costs less than $1,000 per terabyte per year, a tenth the cost of most traditional data warehousing solutions.
    Hadoop
    The Apache Hadoop software library is a framework that allows for the distributed processing of large data sets across clusters of computers using simple programming models. It is designed to scale up from single servers to thousands of machines, each offering local computation and storage.
    Snowflake
    Snowflake eliminates the administration and management demands of traditional data warehouses and big data platforms. Snowflake is a true data warehouse as a service running on Amazon Web Services (AWS)—no infrastructure to manage and no knobs to turn.
    Google Analytics
    Google Analytics lets you measure your advertising ROI as well as track your Flash, video, and social networking sites and applications.
    See all alternatives