The biggest gift for working with Hadoop system in the recent times which can make every RDBMS user smile is HIVE. Although working with Hive can be a frustrating experience if your used to traditional RDBM systems since the traditional statistics required for query optimization are not available .

The most important thing before you work with Hadoop System is to work with your data, If you know your data and what you want to access you can leverage whole set of Hadoop performance optimization parameters which will help in getting better results.

Important links for reference

Configuration Parameters:

Various Hive Parameters:

Hive Indexes:

Hive Windowing and Analytics Functions:

Analytical Queries in Hive:

Hive Server2 Clients:

Important Tips:

set -v Show all settings.

[toggle title=”Skew Merge Bucket Joins“]

  • set hive.optimize.skewjoin = true;
  • set hive.skewjoin.key = skew_key_threshold

Join bottlenecked on the reducer who gets the skewed key[/toggle]

[toggle title=”Sort Merge Bucket Map Join“]

  • set hive.optimize.bucketmapjoin = true;
  • set hive.optimize.bucketmapjoin.sortedmerge = true;
  • set;

Why: No limit on file/partition/table size.

  • 1. Work together with bucket map join
  • 2. Bucket columns == Join columns == sort columns[/toggle]

[toggle title=”Bucket Map Join“]

set hive.optimize.bucketmapjoin = true;

Why: Total table/partition size is big, not good for mapjoin.

  • 1. Work together with map join
  • 2. All join tables are bucketized, and each small table’s bucket number can be divided by big table’s bucket number.
  • 3. Bucket columns == Join columns[/toggle]

[toggle title=”Prevent MapJoins for Large Tables“]

  • set;
  • Star Join Optimization A simple schema for decision support systems or data warehouses is the star schema, where events are collected in large fact tables, while smaller supporting tables (dimensions) are used to describe the data.  [/toggle]

[toggle title=”Controlling the CombinedHiveInputFormat Size“]

  • set mapred.max.split.size=268435456;
  • set mapred.min.split.size=
  • set mapreduce.input.fileinputformat.split.maxsize=
  • set mapreduce.input.fileinputformat.split.minsize=
  • set


[toggle title=”Dynamic Partition Creation“]

  • set hive.exec.dynamic.partition.mode=nonstrict;
  • set hive.exec.max.dynamic.partitions=10000;
  • set hive.exec.max.dynamic.partitions.pernode=500;
  • set = true;
  • set = 10000;


[toggle title=”Control the Output Compression“]

  • set hive.exec.compress.output=true
  • set hive.exec.compress.intermediate=true
  • set io.sort.mb=400

The total amount of buffer memory to use while sorting files, in megabytes.

By default, gives each merge stream 1MB, which should minimize seeks.

  • set hive.limit.pushdown.memory.usage=0.1f

That is used in ORDER BY LIMIT queries for pushing down the LIMIT clause.

select * from table order by key1 limit 10; would use 10% of memory to store a Top-K, which would mean that the impl will keep an in-memory ordered list of 10 rows & replace/discard rows which fall outside the top-10. This means Top-K is worst case of O(log(K)*n), while the unoptimized sorter+LIMIT is O(log(n)*n), which is a few magnitudes of performance gains when K is something like 100 and n is ~1+ million.

  • set hive.optimize.correlation=true;


[toggle title=”Hive Stats“]

The items below will require the stats for each table. Set the stats and ‘analyze’ the table/partitions as directed.

  • set hive.stats.autogather=true;
  • set hive.stats.dbclass=fs;

Analyze table store_sales partition(ss_sold_date) compute statistics partialscan;

  1. While data is inserted   :   set hive.stats.autographer = [true, **false**]
  2. This optimizes “select count(1) from foo;” to run in ~1 second :  set hive.compute.query.using.stats=true;
  3. This optimizes “select x from foo limit 10;” to run <1 second. : set hive.fetch.task.conversion=more;
  4. This optimizes "select x from foo where y = 10;" on ORC tables. :  set hive.optimize.index.filter=true;[/toggle]

[toggle title=”Hive CBO“]

  • hive.compute.query.using.stats = [true, **false**];
  • hive.stats.fetch.column.stats = [true, **false**];
  • hive.stats.fetch.partition.stats = [true, **false**];
  • hive.cbo.enable = [true, **false**];


[toggle title=”Hive Tuning“]hive.optimize.sort.dynamic.partition = [ **true**, false ][/toggle]

[toggle title=”Hive Server 2“]

  • hive.execution.engine=tez/spark/mr

This setting determines whether Hive queries will be executed using Tez / Spark or MapReduce. hive.tez.container.size The memory (in MB) to be used for Tez/Spark tasks.

If this is not specified (-1), the memory settings from the MapReduce configurations ( be used by default for map tasks Java command line options for Tez. If this is not specified, the MapReduce java opts settings ( will be used by default for map tasks.

  • hive.server2.tez.default.queues : A comma-separated list of queues configured for the cluster.
  • hive.server2.tez.sessions.per.default.queue The number of sessions for each queue named in the hive.server2.tez.default.queues.
  • hive.server2.tez.initialize.default.sessions Enables a user to use HiveServer2 without enabling Tez for HiveServer2. Users may potentially may want to run queries with Tez without a pool of sessions.[/toggle]

[toggle title=”ORC File Tuning“]

  • hive.exec.orc.memory.pool

Maximum fraction of heap that can be used by ORC file writers. Can effect how “stripes” are written and effect the stripe size.

set hive.exec.orc.write.format=”0.11″

Set the specific ORC file version to write.


Similar Posts