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.

Practice

1. What is the main benefit of partitioning data in Hadoop for query performance?

easy

A. It reduces the amount of data scanned by queries.

B. It compresses the data to save storage space.

C. It automatically indexes all columns for faster search.

D. It duplicates data to improve fault tolerance.

Solution

1. Step 1: Understand partitioning purpose

   Partitioning divides data into parts based on column values to limit data scanned.

2. Step 2: Connect partitioning to query speed

   Queries read only relevant partitions, reducing data scanned and speeding up queries.

3. Final Answer:

   It reduces the amount of data scanned by queries. -> Option A

4. Quick Check:

   Partitioning = less data scanned [OK]

Hint: Partitioning limits data scanned, speeding queries [OK]

Common Mistakes:

• Confusing partitioning with compression
• Thinking partitioning creates indexes
• Assuming partitioning duplicates data

2. Which of the following is the correct syntax to create a partitioned table in Hive by the column year?

easy

A. CREATE TABLE sales (id INT, amount FLOAT) PARTITIONED BY (year INT);

B. CREATE TABLE sales PARTITIONED BY (year INT) (id INT, amount FLOAT);

C. CREATE TABLE sales (id INT, amount FLOAT, year INT) PARTITIONED;

D. CREATE TABLE sales (id INT, amount FLOAT) WITH PARTITION year INT;

Solution

1. Step 1: Recall Hive partition syntax

   Hive syntax places partition columns after main columns using PARTITIONED BY clause.

2. Step 2: Match syntax to options

   CREATE TABLE sales (id INT, amount FLOAT) PARTITIONED BY (year INT); correctly uses PARTITIONED BY (year INT) after main columns.

3. Final Answer:

   CREATE TABLE sales (id INT, amount FLOAT) PARTITIONED BY (year INT); -> Option A

4. Quick Check:

   Partitioned BY after columns = correct syntax [OK]

Hint: Partition columns go after main columns with PARTITIONED BY [OK]

Common Mistakes:

• Placing PARTITIONED BY before columns
• Adding partition column inside main columns
• Using incorrect keywords like WITH PARTITION

3. Given a Hive table partitioned by country, what will be the effect of this query?

SELECT * FROM sales WHERE country = 'US';

medium

A. The query scans all data ignoring partitions.

B. The query scans only the partition where country='US'.

C. The query fails because partition filters are not allowed.

D. The query scans partitions for all countries except 'US'.

Solution

1. Step 1: Understand partition pruning

   When filtering on a partition column, Hive reads only matching partitions.

2. Step 2: Apply to given query

   Filtering WHERE country='US' reads only the 'US' partition, skipping others.

3. Final Answer:

   The query scans only the partition where country='US'. -> Option B

4. Quick Check:

   Partition filter = scan matching partition only [OK]

Hint: Filter on partition column scans only matching partitions [OK]

Common Mistakes:

• Thinking query scans all partitions
• Believing partition filters cause errors
• Assuming partitions except filter are scanned

4. You created a Hive table partitioned by date. Your query filtering on date still scans all data. What is the likely cause?

medium

A. You forgot to add the partition column in the WHERE clause.

B. Partition columns cannot be used in WHERE clauses.

C. You did not load data into partitions properly.

D. Hive does not support partition pruning.

Solution

1. Step 1: Check partition pruning requirements

   Partition pruning works only if data is loaded into correct partitions.

2. Step 2: Identify cause of full scan

   If data is not loaded into partitions properly, Hive scans all data ignoring partitions.

3. Final Answer:

   You did not load data into partitions properly. -> Option C

4. Quick Check:

   Missing partition data = no pruning, full scan [OK]

Hint: Load data into partitions correctly to enable pruning [OK]

Common Mistakes:

• Assuming partition columns can't be filtered
• Believing Hive lacks partition pruning
• Forgetting to filter on partition column

5. You have a large sales dataset with columns region, product, and sale_date. You often query sales by region and sale_date. Which partitioning strategy will best improve query performance?

hard

A. Partition by product only.

B. Do not partition; use full table scans.

C. Partition by sale_date only.

D. Partition by region and sale_date together.

Solution

1. Step 1: Analyze query patterns

   Queries filter by both region and sale_date, so partitioning should match these columns.

2. Step 2: Choose partition columns

   Partitioning by both region and sale_date allows pruning on both filters, speeding queries.

3. Step 3: Evaluate other options

   Partitioning only by product or sale_date misses one filter; no partitioning causes full scans.

4. Final Answer:

   Partition by region and sale_date together. -> Option D

5. Quick Check:

   Partition on query filter columns = best performance [OK]

Hint: Partition on columns used in filters together [OK]

Common Mistakes:

• Partitioning on unused columns
• Partitioning on only one filter column
• Skipping partitioning for large data