Hive query optimization in Hadoop - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a Hive query with partition pruning

Consider a Hive table sales partitioned by year. What is the output count of this query?

SELECT COUNT(*) FROM sales WHERE year = 2023;

Hadoop

SELECT COUNT(*) FROM sales WHERE year = 2023;

A Returns count of all rows where year is 2023

B Returns count of all rows ignoring the year filter

C Returns count of all rows where year is 2022

D Raises a syntax error due to missing GROUP BY

💡 Hint

Partition pruning filters data before scanning, so only 2023 partitions are counted.

The query uses partition pruning on the 'year' column, so only data from 2023 partitions is scanned and counted.

🧠 Conceptual

intermediate

Effect of using OR vs UNION ALL in Hive queries

Which option best explains the performance difference between these two Hive queries?

Query 1: SELECT * FROM table WHERE col = 'A' OR col = 'B';
Query 2: SELECT * FROM table WHERE col = 'A' UNION ALL SELECT * FROM table WHERE col = 'B';

A Query 1 is faster because OR uses index efficiently

B Both queries have identical performance

C Query 2 is faster because UNION ALL executes two simple filters separately

D Query 1 causes a syntax error in Hive

💡 Hint

Splitting filters can help Hive optimize each scan separately.

Using UNION ALL splits the query into two simpler scans that can be optimized individually, often improving performance compared to OR conditions.

🔧 Debug

advanced

Identify the cause of slow Hive query with joins

This Hive query runs very slowly:

SELECT a.id, b.value FROM table_a a JOIN table_b b ON a.key = b.key WHERE a.date = '2023-01-01';

What is the most likely cause of the slow performance?

A Missing partition pruning on table_b causing full scan

B Using JOIN instead of UNION ALL causes slowness

C The WHERE clause filters table_b instead of table_a

D The query syntax is invalid due to missing GROUP BY

💡 Hint

Check if both tables are partitioned and filters applied correctly.

If table_b is large and not partitioned or filtered, the join causes a full scan on it, slowing the query.

❓ data_output

advanced

Result of using map-side join in Hive

Given table_small is small and table_large is very large, what is the output of this query?

SELECT /*+ MAPJOIN(table_small) */ l.id, s.value FROM table_large l JOIN table_small s ON l.key = s.key LIMIT 5;

A Returns 5 rows but performs a reduce-side join

B Returns 5 rows joining large and small tables using map-side join

C Raises an error because MAPJOIN hint is invalid

D Returns no rows because map-side join skips large table

💡 Hint

MAPJOIN loads the small table into memory to speed up join.

MAPJOIN hint forces Hive to load the small table into memory and join it with the large table on the map side, improving speed and returning correct rows.

🚀 Application

expert

Optimizing a Hive query with skewed data

You have a Hive table with skewed keys causing slow joins. Which option is the best approach to optimize the join performance?

A Increase the number of reducers to a very high number

B Remove the skewed keys from the dataset before join

C Use UNION ALL instead of JOIN to avoid skew

D Enable <code>hive.optimize.skewjoin</code> to handle skewed keys automatically

💡 Hint

Hive has built-in features to handle skewed joins efficiently.

Enabling hive.optimize.skewjoin lets Hive detect skewed keys and handle them separately to avoid reducer overload and improve join speed.

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