Hive vs Spark vs Presto: Which Query Engine to Choose?

Now that we have covered setting up our machines to learn big data, the next question that we need to answer should be around different choices we have. The best thing about the Apache toolkit for big data is the number of choices for query engines. In this post, I will compare the three most popular such engines, namely Hive, Presto and Spark. These choices are available either as open source options or as part of proprietary solutions like AWS EMR.

Hive

Hive is the one of the original query engines which shipped with Apache Hadoop. Over the course of time, hive has seen a lot of ups and downs in popularity levels. There are two major functions of hive in any big data setup.

Hive Metastore

One of the constants in any big data implementation now-a-days is the use of Hive Metastore. Hive ships with the metastore service (or the Hcatalog service). This service allows you to manage your metastore as any other database. You can host this service on any of the popular RDBMS (e.g. MySQL, PostgreSQL etc.). This allows you to query your metastore with simple SQL queries, along with provisions of backup and disaster recovery. If your metastore starts growing you can always scale up your DB instance, instead of touching your Hadoop setup.

Hive Engine

Hive query engine allows you to query your HDFS tables via almost SQL like syntax, i.e. HQL. This is a massive factor in the usage and popularity of Hive. 

When to use Hive?

Unless you have a strong reason to not use the Hive metastore, you should always use it. For the Hive engine, though its performance is really improving over the last few years, there are better options in terms of capabilities and performance if you go with Spark or Presto. Another use case where I have seen people using Hive is in the ELT process on their Hadoop setup. As Hive allows you to do DDL operations on HDFS, it is still a popular choice for building data processing pipelines.

Spark

Spark is the new poster boy of big data world. And it deserves the fame. It is way faster than Hive and offers a very robust library collection with Python support. It processes data in-memory and optimizations like lazy processing and DAG implementation for dependency management makes it a de-facto choice for a lot of people. It also offers ANSI SQL support via the SparkSQL shell.

When to use Spark?

Spark excels in almost all facets of a processing engine. Using Spark, you can build your pipelines using Spark, do DDL operations on HDFS, build batch or streaming applications and run SQL on HDFS. The only reason to not have a Spark setup is the lack of expertise in your team. A minor issue with SparkSQL is its deteriorating performance with increased concurrency.

Presto

Presto is a peculiar product. It does only one thing but it does that really well. It is built for supporting ANSI SQL on HDFS and it excels at that. It is also an in-memory compute engine and as a result it is blazing fast. 

When to use Presto?

Presto is no-doubt the best alternative for SQL support on HDFS. It scales well with growing data. It supports high concurrency on the cluster. Its workload management system has improved over time. Another great feature of Presto is its support for multiple data stores via its catalogs. That means that you can join data in a Hadoop cluster with another dataset in MySQL (or Redshift, Teradata etc.) in a single SQL query. Isn't that amazing? 

In the next post I will share the results of performance benchmarking between Hive, Spark and Presto. 

Comments

Post a Comment

Popular posts from this blog

Uber Data Model

Image
In this post I will try to come up with a data model which can serve the requirements of ride sharing companies like Uber, Lyft, Ola etc. We will approach the problem as an interview and see how we can come up with a feasible data model by answering important questions. Important Entities The first step towards building a data model is to identify important actors/ entities involved in the process. In our case, if we think about our interaction with taxi apps, we can identify important entities involved. The user (i.e. Rider) is one such entity, so is the Driver/ Partner . Once we open the app, we try to book a trip by finding a suitable taxi/ cab from a particular location to another . After the trip gets finished, the app collects the payment and we are done . Ideally, the flow continues to reviews/ ratings, helpcenter in case of issues etc. but for this post we will only consider scenarios till the ride gets finished. So, to summarize, we have the following key entities;
Read more

Data Engineer Interview Questions: SQL

Image
In this post, I will try to share some actual questions asked by top companies for Data Engineer positions. A lot of these companies will cover data modelling as one of the rounds and will use the data model for the next round based on SQL queries. Q1: Find the number of drivers available for rides in any area at any given point of time. Q2: Do you consider Driver and Rider as separate entities? Why or why not? Q3: Give me all passenger names who used the app for only airport rides. Q4: How will you decide where to apply surge pricing? Q5: How will you calculate wait times for rides? Q6: A driver can ride multiple cars, how will you find out who is driving which car at any moment? Q7: Find out Rank without using any function. Q8: How will you delete duplicates from a table? Q9: How will you find percentile? Q10:  You have 3 tables, user_dim (user_id, account_id), account_dim (account_id, paying_customer), and dload_facts (date, user_id, and downloads), find the ave
Read more

Cracking Data Engineering Interviews

Of late, a lot of people have asked me for tips on how to crack Data Engineering interviews at FAANG (Facebook, Amazon, Apple, Netflix, Google) or similar companies. I have not worked at all of these companies so I can't share tips which will necessarily apply for all of them but I will share tips which can be generalized for most of the big companies. First of all, the field of Data Engineering has expanded a lot in the last few years and has become one of the core functions of any big technology company. The obvious reason for this expansion is the amount of data being generated by devices and data-centric economy of the internet age. Each company is focussed on making the best use of data owned by them by making data driven decisions. If you compare this to the Data Engineering roles which used to exist a decade back, you will see a huge change. In the past, Data Engineering was invariably focussed on Databases and SQL. Even now, these two form some part of most Data Engin
Read more

Hive Challenges: Bucketing, Bloom Filters and More

In the second post of this series, we will learn about few more aspects of table design in Hive. Now that you know about partitioning challenges , you will be able to appreciate these features which will help you to further tune your Hive tables. Bucketing In addition to Partitioning the tables, you can enable another layer of bucketing of data based on some attribute value by using the Clustering method. Clustering can be used with partitioned or non-partitioned hive tables. One particular use case where Clustering becomes useful when your partitions might have unequal number of records (e.g. users logging in per country, US partition might be a lot bigger than New Zealand). In such cases, you can define the number of buckets and the clustered by field (like user Id), so that all the buckets have equal records. In partitioning each partition gets a directory while in Clustering, each bucket gets a file. Records with the same bucketed column will always be stored in the same bucke
Read more

UBER Data Architecture

Image
In this post, I will try to cover the data architecture Uber has built to support their big data applications. This should be applicable for other ride hailing apps as well. There are multiple modules at play here so I will try to give a brief overview along with a detailed discussion on the data architecture.  An aeroplane view of the problem tells us that effectively we are trying to solve a demand vs. supply problem. All the Drivers active at any point of time constitute the supply while all the cab requesters (Riders) form the demand. What Uber tries to do is to have the best matching between demand and supply "at any point at any moment". I have highlighted the location and temporal conditions as they are critical for the success of what Uber tries to do. Let's get started to see how Uber comes around these challenges by creating a scalable data architecture. Every Driver active on Uber keeps sending his location data to the server (e.g. every 5 seconds). T
Read more