Big Data Things

In my previous post, we went over the qualitative comparisons between Hive, Spark and Presto . In this post, we will do a more detailed analysis, by virtue of a series of performance benchmarking tests on these three query engines.  Benchmarking Data Set For this benchmarking, we have two tables. Some of the key points of the setup are: - All the query engines are using the Hive metastore for table definitions as Presto and Spark both natively support Hive tables - All the tables are external Hive tables with data stored in S3 - All the tables are using  Parquet  and  ORC  as a storage format Tables : 1. product_sales: It has ~6 billion records 2. product_item: It has ~589k records Hardware Tests were done on the following EMR cluster configurations, EMR Version: 5.8 Spark: 2.2.0 Hive: 2.3.0 Presto: 0.170 Nodes: Master Node:   1x  r4.16xlarge Task nodes:  8 x r4.8xlarge Query Types There are thre...

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

In this series of articles, I will talk about the challenges while working with Hive tables. Like with any other Apache toolkit, there are a lot of configurations to play around with and you need to know a few of them. Luckily, for partitioning there are a lot of concepts which are similar to the RDBMS world. So, if you have some idea about table partitioning in databases, this should not be very complex for you. Q: How to decide number of partitions for a very large table? There are multiple aspects to look out for, Cardinality:  Find out the cardinality of important attributes to find out how many distinct values the attribute holds. If you are making any attribute as a partition, it should have as few distinct values as possible( though few hundred partitions for a table are also fine). Sorting:  If you can compromise on the write speeds, sort the data while writing. This allows formats like ORC to capture the starting and ending values in the metadata of fi...