Partitioning for query performance in Hadoop - Step-by-Step Execution

Concept Flow - Partitioning for query performance

Start Query

↓

Check Partition Key

↓

Filter Partitions

↓

Scan Only Needed Partitions

↓

Return Results

↓

End Query

When a query runs, it uses the partition key to filter data and scans only relevant partitions, speeding up the query.

Execution Sample

Hadoop

SELECT * FROM sales
WHERE year = 2023 AND region = 'US';

This query selects sales data only for year 2023 and region US, using partitioning to scan less data.

Execution Table

Step	Action	Partition Key Used	Partitions Scanned	Result
1	Start Query	N/A	N/A	Query begins
2	Check WHERE clause for partition keys	year, region	N/A	Identified partitions to filter
3	Filter partitions by year=2023	year=2023	Partitions with year=2023	Reduced partitions
4	Filter partitions by region='US'	region='US'	Partitions with year=2023 and region='US'	Further reduced partitions
5	Scan filtered partitions	year=2023, region='US'	Only relevant partitions	Data scanned efficiently
6	Return results	N/A	N/A	Query results returned
7	End Query	N/A	N/A	Query finished

💡 Query ends after scanning only partitions matching year=2023 and region='US', improving performance.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Partitions to scan	All partitions	Partitions with year=2023	Partitions with year=2023 and region='US'	Partitions with year=2023 and region='US'

Key Moments - 2 Insights

Why does the query scan fewer partitions after filtering by partition keys?

Because the query uses the WHERE clause on partition keys (year and region), it can skip partitions that don't match, as shown in steps 3 and 4 of the execution table.

What happens if the query does not filter by partition keys?

If no partition keys are used in the WHERE clause, the query scans all partitions, which is slower. This is implied by the 'Partitions to scan' variable starting as 'All partitions'.

Visual Quiz - 3 Questions

Look at the execution table, at which step does the query identify the partition keys used?

A Step 2

B Step 3

C Step 5

D Step 6

💡 Hint

Check the 'Partition Key Used' column in the execution table at each step.

According to the variable tracker, what is the state of 'Partitions to scan' after step 4?

A All partitions

B Partitions with year=2023

C Partitions with year=2023 and region='US'

D No partitions

💡 Hint

Look at the 'After Step 4' column for 'Partitions to scan' in the variable tracker.

If the query did not filter by region, how would the partitions scanned change at step 4?

A Partitions would be filtered by region only

B Partitions would remain filtered by year only

C Partitions would include all years and regions

D Partitions would be empty

💡 Hint

Refer to the filtering steps in the execution table and how partition keys affect partitions scanned.

Concept Snapshot

Partitioning splits data into parts by keys (like year, region).
Queries use WHERE on these keys to scan only needed parts.
This reduces data scanned and speeds up queries.
Without partition filters, all data is scanned, slowing queries.

Full Transcript

Partitioning helps queries run faster by dividing data into parts based on keys like year or region. When a query uses these keys in its WHERE clause, it scans only the matching partitions instead of all data. For example, a query filtering year=2023 and region='US' scans only partitions with those values. This reduces the amount of data read and speeds up the query. If no partition keys are used, the query scans all partitions, which is slower. The execution table shows each step: starting the query, identifying partition keys, filtering partitions step-by-step, scanning only relevant partitions, and returning results. The variable tracker shows how the set of partitions to scan shrinks after each filter. Understanding this flow helps write efficient queries on partitioned data.

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