Hive Challenges: Partitioning, Performance and More

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 files. While reading it filters out all the files which do not have values you are looking for by just looking at the metadata indexes, thus reducing the amount of read needed. Sorting can be done either at the time of table creation as part of the Create Table command [e.g. clustered by(userid) sorted by (age)] or at the time of insertion of data using the order by or sort by in the insert/ select statement.

Skew: Another thing to watch out for is the skew. If there are attribute values which have very few corresponding records in the table, you might have some very small partitions along with other huge partitions. There are few ways to deal with Skew but each has its own use case. One of the way is to use Skewed Table. In this case you are expecting a lot of data for few values for Columns C1 and C2, so you use the following command to let Hive know that. Hive will create separate directories to store such values so that these values can be directly used in the where clause (just like partition values). The only problem with this approach is that you need to know which values (and for which column) will create the skew in your data beforehand.

CREATE TABLE <T> (SCHEMA) SKEWED BY (keys) ON ('c1', 'c2')
ON (('C1_Value1, C2_Value2),('C1_Value2','C2_Value2'))
[Stored as Directories];

Small file problem: Make sure that the partition scheme you are coming up with is not resulting into a lot of small files. The way hive metastore works, each new file gets a record in the metadata tables. If you are defining partitions which are resulting in a large number of files, your queries will start overloading the Namenode of your hadoop cluster. This happens because the namenode needs to load all the records from the metastore into memory. As the number of records increase (because of large number of files), time to query the metastore increases. Also, the memory needed to store these records on the Namenode increases. All these factors slow down the Namenode performance and results in slower query performance as a whole. Also, as the Namenode is the single point of failure, this presents a larger risk of an unhealthy cluster which can bring down the cluster.

Q: E.g. Facebook wants to track the number of active users every month based on the user_activity table which logs every successful login. How will you partition this data to support a decent read performance.

 A novel method will be to have a table which stores the (Cust_Id, Date, Current_status). Partition this table on day level. Now your query is just a count of cust_id with Current_status = True for any date.

 To capture changes in status of users (active->inactive or inactive->active), have another table which just stores incoming (Cust_id, Status_changes). From this table we can generate daily partitions for the first table at the end of every day and insert in a new partition.

 In the next post, I will cover some more aspects of designing Hive tables which impact query performance and how to avoid some common pitfalls while designing your tables.

 Next post: Bucketing, Bloom Filters & More

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