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.