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.