原文链接 https://www.hdfstutorial.com/blog/hive-performance-tuning/
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!
- 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-
Set hive.execution.engine=tez;
If you are using Cloudera/Hortonworks, then you will find TEZ option in the Hive query editor as well.
- 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-
set mapred.compress.map.output = true;
set mapred.output.compress= true;
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.
Syntax:
Create table orctbl (id int, name string, address string) stored as ORC tblproperties (“orc.compress”= “SNAPPY”);
Now simply you can also insert the data like-
Insert overwrite table orctbl select * from tbldetails;
- 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.
<property>
<name>hive.auto.convert.join</name>
<value>true</value>
<description>Whether Hive enables the optimization about converting common join into mapjoin based on the input file size</description>
</property>
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.
<property>
<name>hive.optimize.skewjoin</name>
<value>true</value>
<description>
Whether to enable skew join optimization. The algorithm is as follows: At runtime, detect the keys with a large skew. Instead of processing those keys, store them temporarily in an HDFS directory. In a follow-up map-reduce job, process those skewed keys. The same key need not be skewed for all the tables, and so, the follow-up map-reduce job (for the skewed keys) would be much faster, since it would be a map-join.
</description> </property>
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.
<property>
<name>hive.optimize.bucketmapjoin</name>
<value>true</value>
<description>Whether to try bucket mapjoin</description>
</property>
<property>
<name>hive.optimize.bucketmapjoin.sortedmerge</name>
<value>true</value>
<description>Whether to try sorted bucket merge map join</description>
</property>
- 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-
CREATE TABLE countrydata_partition
(Id int, countryname string, population int, description string)
PARTITIONED BY (country VARCHAR(64), state VARCHAR(64))
row format delimited
fields terminated by ‘\t’
stored AS textfile;
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-
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
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-
CREATE TABLE emp_bucketed_table(
ID int, name string, address string, salary string )
COMMENT ‘this is a bucketed table’
PARTITIONED BY (country VARCHAR(64))
CLUSTERED BY (state) INTO 10 BUCKETS
STORED AS TEXTFILE;
To enable bucketing in Hive, you need to set the following property-
SET hive.enforce.bucketing=true;
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-
Set hive.exec.parallel = true;
8. Vectorization
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-
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
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-
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
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.
