If the Hive code is not written properly, you may face timing in hive query execution. And so hive performance tuning is very important.
When you do Hive query optimization, it helps the query to execute at least by 50%. If your query is not optimized, a simple select statement can take very long to execute.
In this tutorial, I will be talking about Hive performance tuning and how to optimize Hive queries for better performance and result.
There are many methods for Hive performance tuning and being a Hadoop developer; you should know these to do well with the queries in a production environment.
- 1 Hive Performance Tuning- 10 Best Tips to adopt
Hive Performance Tuning- 10 Best Tips to adopt
You may be knowing some of these hive query optimization techniques like using parallel lines, file formats, optimizing joins, etc. But I will also discuss some advanced hive performance tuning techniques so that you can master the optimization of hive queries.
So let’s start with Hive performance tuning techniques!
1. Use Tez to Fasten the execution
Apache TEZ is an execution engine used for faster query execution. It fastens the query execution time to around 1x-3x times.
To use TEZ execution engine, you need to enable it instead of default Map-Reduce execution engine. TEZ can be enabled using the below query-
If you are using Cloudera/Hortonworks, then you will find TEZ option in the Hive query editor as well.
2. Enable compression in Hive
Compression techniques reduce the amount of data being transferred and so reduces the data transfer between mappers and reducers.
For better result, you need to perform compression at both mapper and reducer side separately. Although gzip is considered as the best compression format but beware that it is not splittable and so should be applied with caution.
Other formats are snappy, lzo, bzip, etc. You can set compression at mapper and reducer side using codes below-
Also, the compressed file should not be more than few hundred MBs else it may impact the jobs.
3. Use ORC file format
ORC (optimized record columnar) is great when it comes to hive performance tuning. We can improve the query performance using ORC file format easily. You can check Hadoop file formats in detail here.
There is no barrier like in which table you can use ORC file and in response, you get faster computation and compressed file size.
It is very easy to create ORC table, and you just need to add STORED AS ORC command as shown below.
Now simply you can also insert the data like-
4. Optimize your joins
If you are using joins to fetch the results, it’s time to revise it. If you have large data in the tables, then it is not advisable to just use normal joins we use in SQL. There are many other joins like Map Join; bucket joins, etc. which can be used to improve Hive query performance.
You can do the following with joins to optimize hive queries-
- Use Map Join
Map join is highly beneficial when one table is small so that it can fit into the memory. Hive has a property which can do auto-map join when enabled. Set the below parameter to true to enable auto map join.
Set hive.auto.convert.join to true to enable the auto map join. You can either set this from the command line or from the hive-site.xml file.
- Use Skew Join
Skew join is also helpful when your table is skewed. Set the hive.optimize.skewjoin property to true to enable skew join.
- Bucketed Map Join
If tables are bucketed by a particular column, you can use bucketed map join to improve the hive query performance.
You can set the below two property to enable the bucketed map join in Hive.
5. Use partition
Partition is a useful concept in Hive. It is used to divide the large table based on certain column so that the whole data can be divided into small chunks. It allows you to store the data under sub-directory inside a table.
Selecting the partition table is always a critical decision, and you need to take care of future data as well as the volume of data as well. For example, if you have data of a particular location then partition based on state can be one of the ideal choices.
Here is the syntax to create partition table-
There are two types of partition in Hive-
- Static partition
- Dynamic partition
Static partition is the default one. To use dynamic partition in Hive, you need to set the following property-
6. Bucketing can also be used
If you have more number of columns on which you want the partitions, bucketing in the hive can be a better option. We use CLUSTERED BY command to divide the tables in the bucket.
Here is the syntax to create bucketed table-
To enable bucketing in Hive, you need to set the following property-
This should be set every time you are writing the data to the bucketed table.
7. Parallel execution
As we know, Hive converts the queries into different stages during execution. These stages are usually getting executed one after the other and thus increases the time of execution. Below are some of the normal steps involved-
• MapReduce stage
• Sampling stage
• Limit stage
• Merge stage etc.
But the good thing is, you can set some of this independent stage to process parallel. This is a parallel execution in Hive. For this, you need to set the below properties to true-
Vectorization improves the query performance of all the operation like scans, aggregations, filters and joins, by performing them in batches of 1024 rows at once instead of single row each time.
Again you will have to set some parameter to enable vectorization-
9. Cost based optimization
Cost based optimization (CBO) is the new feature to Hive. CBO offers better hive query performance regarding cost.
To use CBO, you need to set the following properties-
10. Avoid Global sorting
Global sorting in Hive is getting done by the help of the command ORDER BY in the hive. But the issue is, if you’re using ORDER BY command, then the number of reducers will be set to one which can be illogical when you have large Hadoop dataset.
So when you don’t need global sorting, use SORT BY command which sorts the result per reducer.
Even you can also use DISTRIBUTE BY command if you want to control which particular rows will go with which reducer.
These were some of the best Hive performance tuning techniques one can apply to Hive. Use these techniques and improve Hive query performance easily.
Do let me know if you have any other method to improve the hive query performance.