Partitioning for query performance in Hadoop - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use partitioning in Hadoop queries?

Which of the following best explains why partitioning improves query performance in Hadoop?

A Partitioning encrypts data to secure it during query execution.

B Partitioning compresses data to reduce storage space and speed up queries.

C Partitioning reduces the amount of data scanned by filtering data at the storage level.

D Partitioning duplicates data across nodes to increase fault tolerance.

💡 Hint

Think about how partitioning helps avoid reading unnecessary data.

Partitioning organizes data into parts based on column values, so queries can skip irrelevant partitions and scan less data, improving speed.

❓ Predict Output

intermediate

Output of query with partition pruning

Given a Hive table partitioned by year, what will be the output count of this query?

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

Assume the table has 100,000 rows total, with 20,000 rows for year 2023.

A 100000

B 20000

C 0

D 50000

💡 Hint

The query filters on the partition column year.

The query only scans the partition for year 2023, which contains 20,000 rows, so the count is 20,000.

❓ data_output

advanced

Result of partitioned data filtering

Consider a Hadoop table partitioned by country with the following data counts:

• USA: 50,000 rows
• Canada: 30,000 rows
• Mexico: 20,000 rows

What is the number of rows returned by this query?

SELECT * FROM table WHERE country IN ('USA', 'Mexico');

A 70000

B 50000

C 30000

D 100000

💡 Hint

Sum the rows from the selected partitions.

The query scans partitions for USA and Mexico only, totaling 50,000 + 20,000 = 70,000 rows.

🔧 Debug

advanced

Identify the cause of slow query despite partitioning

A Hive query on a partitioned table is running very slowly. The query filters on a non-partitioned column. What is the most likely reason?

A The query optimizer is disabled.

B The table is not actually partitioned, causing full scan.

C The partition column has too many distinct values causing overhead.

D The query cannot use partition pruning because the filter is on a non-partitioned column.

💡 Hint

Partition pruning only works when filtering on partition columns.

Partition pruning skips irrelevant partitions only if the filter is on the partition column. Filtering on non-partitioned columns causes full table scan.

🚀 Application

expert

Choosing partition columns for best query performance

You have a large Hadoop dataset with columns: date, region, product, and sales. Most queries filter by date and region. Which partitioning strategy will likely give the best query performance?

A Partition by both <code>date</code> and <code>region</code>

B Partition by <code>region</code> only

C Partition by <code>date</code> only

D Do not partition the table

💡 Hint

Consider which columns are most commonly used in filters.

Partitioning by both date and region allows queries filtering on either or both columns to prune partitions effectively, improving performance.