Getting Started with Presto & Hive on AWS

Today I will show you how to get started with Hive and Presto. I compare both tools by looking at the strength of each tool and their use cases.  At the end of this post, it explains a scenario in which we can leverage both tools. Let's dive right in!

Apache Hive is a Data Warehousing tool that sits on top of Apache Hadoop for performing data querying and analysis on large datasets. Hive uses a SQL like method to query data making it easy to explore and analyze big data.

Hives’ original release was primarily focused on leveraging data that existed within HDFS. However, data analytics storage and access patterns have evolved since its conception. Hive has become a valuable tool for building out external database structures for querying objects on storage platforms like AWS S3.

Presto is a distributed SQL query engine that is used for querying datasets from multiple sources including Hadoop, S3, MySQL, Teradata, and other relational and non-relational databases. 

Presto was developed by Facebook to run queries against multiple data stores with response times ranging from sub-second to minutes. Other companies using presto include Netflix, airbnb and dropbox.

One of the main advantages of using Presto is that it supports concurrent query workloads. Being able to run multiple queries at the same time makes it the go-to application for BI-users.

AWS Athena is basically a presto service managed by AWS.  Presto can also be deployed as a service on the Amazon EMR cluster.

Presto vs Hive

• Presto is preferably used for performing quick and fast data analysis that will not require very much memory.
• Presto is designed for low latency while on the other hand Hive is used for query throughput and queries that require very large amount of memory.
• We can also use both tools to explore data sitting on top of a Hadoop system.
• Apache Hive uses a language similar to SQL, but it still has some differences that BI-Users might need to learn how to write some queries.

Using Hive and Presto

Presto does not include built in support for the Hadoop file system and it will need to leverage other tools such as Hive connector (aka HCatalog).

One of the strengths of presto is that it's suitable for star schema models.

In the use case below it shows how to leverage Hive and Presto while working with NoSQL partition parquet tables and file-system data paths.

Note: If you plan on leveraging Presto as a reporting query engine, it can be used against both flattened and dimensional models. However, if Presto is querying dimensional report models or you’re planning on joining datasets together, you will want to consider the joining tables sizes and Presto configuration to ensure optimal performance. 

Large memory usage and bad performance are commonly seen if Presto is not configured correctly for your use case so it’s worth reviewing the documentation which can be found here:  https://teradata.github.io/presto/docs/141t/admin/tuning.html.

Creating the Fact Partitioned Table with Spark

data = [(1,1001,'SMALL8','','USD', 10,2020,9),(2,1002,'MED9','','CAD', 17,2019,10),(3,1003,'LARGE10','','CAD',90,2019,10)] columns = ['uid','productid','sizeid','description','currency','quantity','year','month'] df = spark.createDataFrame(data, columns)

Writing Parquet Data with Spark

df.write.format("parquet").mode("overwrite").option("key", "uid")\ .partitionBy('year','month').save(data_location)

*Note, when working with partitioned parquet tables the partition fields needs to be in lowercase before you can create an external table in Hive.