Hive query optimization in Hadoop - Step-by-Step Execution

Concept Flow - Hive query optimization

Write Hive Query

↓

Parse Query

↓

Generate Logical Plan

↓

Apply Optimizations

↓

Generate Physical Plan

↓

Execute Query on Hadoop

↓

Return Results

The Hive query goes through parsing, logical plan creation, optimization, physical plan generation, and then execution on Hadoop.

Execution Sample

Hadoop

SELECT dept, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY dept;

This query counts employees with salary over 50000 per department.

Execution Table

Step	Action	Details	Effect
1	Parse Query	Check syntax and build parse tree	Valid parse tree created
2	Generate Logical Plan	Create plan with filter and group by	Logical plan with filter salary>50000 and group by dept
3	Apply Optimizations	Push filter before group by	Filter applied early to reduce data
4	Generate Physical Plan	Create MapReduce or Tez jobs	Physical plan optimized for execution
5	Execute Query	Run jobs on Hadoop cluster	Data processed with less resource usage
6	Return Results	Aggregate counts per dept	Final counts returned

💡 Query execution completes after returning aggregated results.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Query Plan	Raw parse tree	Logical plan with filter and group by	Optimized logical plan with filter pushed down	Physical execution plan	Executed and results ready

Key Moments - 3 Insights

Why is pushing the filter before the group by important?

Because applying the filter early reduces the amount of data grouped, making the query faster as shown in step 3 of the execution_table.

What happens if the query is not optimized before execution?

The query will still run but may use more resources and take longer, as the physical plan won't be efficient (see step 4 and 5).

How does Hive decide which physical plan to use?

Hive chooses the plan based on cost and available execution engines like MapReduce or Tez, as indicated in step 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the filter condition applied early to reduce data?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Apply Optimizations' step in the execution_table where filter pushdown is mentioned.

According to variable_tracker, what is the state of the query plan after step 3?

A Optimized logical plan with filter pushed down

B Physical execution plan

C Raw parse tree

D Executed and results ready

💡 Hint

Look at the 'After Step 3' column in variable_tracker for 'Query Plan'.

If the filter was not pushed down, how would the execution_table change?

A Step 4 would be skipped

B Step 3 would show no optimization and more data processed

C Step 5 would return no results

D Step 1 would fail parsing

💡 Hint

Refer to step 3 in execution_table where optimization is applied to reduce data.

Concept Snapshot

Hive Query Optimization:
- Parse query to build logical plan
- Push filters early to reduce data
- Generate efficient physical plan
- Execute on Hadoop with less resource use
- Returns aggregated results faster

Full Transcript

Hive query optimization involves several steps: first, the query is parsed to check syntax and create a parse tree. Then, a logical plan is generated that includes operations like filtering and grouping. Optimizations are applied, such as pushing filters before grouping to reduce data early. Next, a physical plan is created to run the query efficiently on Hadoop using engines like MapReduce or Tez. Finally, the query executes and returns results. This process reduces resource use and speeds up query execution.

Practice

1. What is the main purpose of Hive query optimization?

easy

A. To increase the size of data stored in Hive

B. To make data queries faster and reduce resource usage

C. To make Hive queries more complex

D. To disable partitioning in Hive tables

Solution

1. Step 1: Understand Hive query optimization goal

   Hive query optimization aims to improve query speed and reduce resource consumption.

2. Step 2: Evaluate options based on this goal

   Only To make data queries faster and reduce resource usage matches the goal of faster queries and less resource use.

3. Final Answer:

   To make data queries faster and reduce resource usage -> Option B

4. Quick Check:

   Optimization = Faster queries [OK]

Hint: Optimization means faster queries and less cost [OK]

Common Mistakes:

• Thinking optimization increases data size
• Confusing optimization with query complexity
• Ignoring resource usage benefits

2. Which of the following is the correct syntax to enable cost-based optimization (CBO) in Hive?

easy

A. ACTIVATE hive.cbo = 1;

B. ENABLE hive.cbo = true;

C. SET hive.cbo.enable=true;

D. SET hive.cob.enable = yes;

Solution

1. Step 1: Recall correct Hive syntax for setting properties

   Hive uses 'SET property=value;' to configure settings.

2. Step 2: Identify correct property name and value

   The correct property is 'hive.cbo.enable' and value is 'true'. SET hive.cbo.enable=true; matches this exactly.

3. Final Answer:

   SET hive.cbo.enable=true; -> Option C

4. Quick Check:

   SET + hive.cbo.enable=true [OK]

Hint: Use SET with exact property name and true/false [OK]

Common Mistakes:

• Misspelling property name
• Using ENABLE or ACTIVATE instead of SET
• Using yes/no instead of true/false

3. Given the Hive query:
SELECT * FROM sales WHERE year = 2023 AND region = 'US';
Assuming the table is partitioned by year and bucketed by region, what is the main optimization benefit?

medium

A. Hive reads only data for year 2023 and region 'US' buckets

B. Hive scans the entire table ignoring partitions and buckets

C. Hive reads only the region 'US' partition

D. Hive reads only the year 2023 bucket

Solution

1. Step 1: Understand partitioning and bucketing roles

   Partitioning by year means data is divided by year folders; bucketing by region means data is split into files by region.

2. Step 2: Analyze query filter impact

   Filtering on year=2023 uses partition pruning; filtering on region='US' uses bucket pruning.

3. Final Answer:

   Hive reads only data for year 2023 and region 'US' buckets -> Option A

4. Quick Check:

   Partition + bucket pruning = less data read [OK]

Hint: Partition filters reduce folders; bucket filters reduce files [OK]

Common Mistakes:

• Confusing partition with bucket filtering
• Assuming full table scan despite filters
• Mixing partition and bucket roles

4. Identify the error in this Hive query optimization setting:
SET hive.cbo.enable = 'true';

medium

A. SET command cannot be used for this property

B. Property name is incorrect

C. Missing semicolon at end

D. Value should not be in quotes

Solution

1. Step 1: Check correct syntax for SET command

   Hive SET commands use format: SET property=value; without quotes around boolean values.

2. Step 2: Identify issue with quotes around 'true'

   Putting 'true' in quotes makes it a string, which Hive does not accept for boolean properties.

3. Final Answer:

   Value should not be in quotes -> Option D

4. Quick Check:

   Boolean values no quotes [OK]

Hint: Boolean values in SET must be unquoted true/false [OK]

Common Mistakes:

• Adding quotes around boolean values
• Confusing property name spelling
• Omitting semicolon (optional but recommended)

5. You have a large Hive table with sales data partitioned by year and month. You want to optimize a query filtering on year=2023 only. Which approach will improve query performance the most?

hard

A. Add bucketing on month and enable cost-based optimization

B. Drop the month partition and keep only year

C. Disable partition pruning to scan all data

D. Use SELECT * without WHERE clause

Solution

1. Step 1: Understand current partitioning and query filter

   The table is partitioned by year and month, but query filters only on year=2023.

2. Step 2: Evaluate optimization options

   Adding bucketing on month helps organize data within partitions. Enabling cost-based optimization lets Hive choose best plan.

3. Step 3: Assess other options

   Dropping month partition reduces granularity; disabling pruning or removing WHERE clause worsens performance.

4. Final Answer:

   Add bucketing on month and enable cost-based optimization -> Option A

5. Quick Check:

   Partition + bucketing + CBO = best performance [OK]

Hint: Combine partitioning, bucketing, and CBO for best speed [OK]

Common Mistakes:

• Removing useful partitions
• Disabling pruning increases data scanned
• Ignoring cost-based optimization benefits