7 Best Hive Optimization Techniques – Hive Performance

Boost your career with Free Big Data Courses!!

There are several types of Hive Query Optimization techniques are available while running our hive queries to improve Hive performance with some Hive Performance tuning techniques.

So, in this Hive Optimization Techniques article, Hive Optimization Techniques for Hive Queries we will learn how to optimize hive queries to execute them faster on our cluster, types of Hive Optimization Techniques for Queries: Execution Engine, Usage of Suitable File Format, Hive Partitioning, Bucketing in Apache Hive, Vectorization in Hive, Cost-Based Optimization in Hive, and Hive Indexing. 

So, let’s start Hive Query Optimization Tutorial.

What are Hive Optimization Techniques?

However,  to run queries on petabytes of data we all know that hive is a query language which is similar to SQL built on Hadoop ecosystemSo, there are several Hive optimization techniques to improve its performance which we can implement when we run our hive queries.

Types of Query Optimization Techniques in Hive

Following are the Hive optimization techniques for Hive Performance Tuning, let’s discuss them one by one:

Types of Hive optimization Techniques

Types of Hive optimization Techniques

a. Tez-Execution Engine in Hive

Tez Execution Engine – Hive Optimization Techniques, to increase the Hive performance of our hive query by using our execution engine as Tez. On defining Tez, it is a new application framework built on Hadoop Yarn.

That executes complex-directed acyclic graphs of general data processing tasks. However, we can consider it to be a much more flexible and powerful successor to the map-reduce framework.

In addition, to write native YARN applications on Hadoop that bridges the spectrum of interactive and batch workloads Tez offers an API framework to developers. To be more specific,  to work with petabytes of data over thousands of nodes it allows those data access applications.

b. Usage of Suitable File Format in Hive

ORCFILE File Formate – Hive Optimization Techniques, if we use appropriate file format on the basis of data. It will drastically increase our query performance.

Basically, for increasing your query performance ORC file format is best suitable. Here, ORC refers to Optimized Row Columnar. That implies we can store data in an optimized way than the other file formats.

To be more specific, ORC reduces the size of the original data up to 75%. Hence,  data processing speed also increases. On comparing to Text, Sequence and RC file formats, ORC shows better performance.

Basically, it contains rows data in groups. Such as Stripes along with a file footer.  Therefore, we can say when Hive is processing the data ORC format improves the performance.

c. Hive Partitioning 

Hive Partition – Hive Optimization Techniques, Hive reads all the data in the directory Without partitioning. Further, it applies the query filters on it.  Since all data has to be read this is a slow as well as expensive.

Also, users need to filter the data on specific column values frequently. Although, users need to understand the domain of the data on which they are doing analysis, to apply the partitioning in the Hive.

Basically, by Partitioning all the entries for the various columns of the dataset are segregated and stored in their respective partition.

Hence, While we write the query to fetch the values from the table, only the required partitions of the table are queried. Thus it reduces the time taken by the query to yield the result.

d. Bucketing in Hive

Bucketing in Hive – Hive Optimization Techniques, let’s suppose a scenario. At times, there is a huge dataset available. However, after partitioning on a particular field or fields, the partitioned file size doesn’t match with the actual expectation and remains huge.

Still, we want to manage the partition results into different parts. Thus, to solve this issue of partitioning, Hive offers Bucketing concept. Basically,  that allows the user to divide table data sets into more manageable parts.

Hence, to maintain parts that are more manageable we can use Bucketing. Through it, the user can set the size of the manageable parts or Buckets too.

e. Vectorization In Hive

Vectorization In Hive – Hive Optimization Techniques, to improve the performance of operations we use Vectorized query execution. Here operations refer to scans, aggregations, filters, and joins. It happens by performing them in batches of 1024 rows at once instead of single row each time.

However, this feature is introduced in Hive 0.13. It significantly improves query execution time, and is easily enabled with two parameters settings:

set hive.vectorized.execution = true

set hive.vectorized.execution.enabled = true

f. Cost-Based Optimization in Hive (CBO)

Cost-Based Optimization in Hive – Hive Optimization Techniques, before submitting for final execution Hive optimizes each Query’s logical and physical execution plan. Although, until now these optimizations are not based on the cost of the query.

However, CBO, performs, further optimizations based on query cost in a recent addition to Hive. That results in potentially different decisions: how to order joins, which type of join to perform, the degree of parallelism and others.

To use CBO, set the following parameters at the beginning of your query:

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;
Then, prepare the data for CBO by running Hive’s “analyze” command to collect various statistics on the tables for which we want to use CBO.

g. Hive Indexing 

Hive Index – Hive Optimization Techniques, one of the best ways is Indexing. To increase your query performance indexing will definitely help. Basically, for the original table use of indexing will create a separate called index table which acts as a reference.

As we know, there are many numbers of rows and columns, in a Hive table. Basically, it will take a large amount of time if we want to perform queries only on some columns without indexing. Because queries will be executed on all the columns present in the table.

Moreover,  there is no need for the query to scan all the rows in the table while we perform a query on a table that has an index, it turned out as the major advantage of using indexing. Further, it checks the index first and then goes to the particular column and performs the operation.

Hence, maintaining indexes will be easier for Hive query to look into the indexes first and then perform the needed operations within less amount of time. Well, time is the only factor that everyone focuses on, eventually.

This was all about Hive Optimization Techniques Tutorial. Hope you like our explanation of Hive Performance Tuning.

So, this was all in Hive Query Optimization Techniques. Hope you like our explanation.

Conclusion – Hive Optimization Techniques

Hence, we hope this article ‘’Top 7 Hive Optimization techniques‘’ helped you in understanding how to optimize hive queries for faster execution, Hive Performance Tuning with these Best Hive Optimization techniques: Execution Engine, Usage of Suitable File Format, Hive Partitioning, Bucketing in Hive, Vectorization in Hive, Cost-Based Optimization in Hive, and Hive Indexing.

However, if any doubt occurs feel free to ask in the comment section. 

Your 15 seconds will encourage us to work even harder
Please share your happy experience on Google

follow dataflair on YouTube

4 Responses

  1. Prasanta Kumar says:

    Hi All ,

    I have gone through your sites and its content in different fields in hadoop, I was surprised to see the nice explanation. It will help me for my upcoming interviews and so on.


  2. RajLucky says:


    Thanks for providing good explanation for the BigData related stuff

  3. Ghansham Dass Vaswani says:

    That’s uphill task to put all optimization on same page, Good Job Bro keep it up.

  4. imran khatik says:

    good study contains than you dataflair team.

Leave a Reply

Your email address will not be published. Required fields are marked *