Partitioning for query performance in Hadoop - Mini Project: Build & Apply

Partitioning for query performance

📖 Scenario: You work with a large sales dataset stored in Hadoop. Queries on this dataset are slow because the data is not organized efficiently. Partitioning the data by a key column can speed up queries by reading only relevant parts.

🎯 Goal: You will create a partitioned table in Hadoop, configure the partition column, load data into partitions, and query the data to see improved performance.

📋 What You'll Learn

Create a Hive table with partitioning on the year column

Set a variable partition_column to 'year'

Load data into the table partitions using the partition column

Query the table filtering by the partition column and display results

💡 Why This Matters

🌍 Real World

Partitioning large datasets in Hadoop helps reduce query time by scanning only relevant data parts, saving computing resources and speeding up reports.

💼 Career

Data engineers and analysts use partitioning to optimize big data queries in Hadoop ecosystems, improving performance and cost efficiency.

1

Create a partitioned Hive table

Write a Hive query to create a table called sales_data with columns id INT, product STRING, amount FLOAT, and partitioned by year INT. Use STORED AS TEXTFILE format.

Hadoop

# Write the Hive CREATE TABLE statement for sales_data with partition on year
# Your code here

Need a hint?

Use CREATE TABLE with PARTITIONED BY clause to specify the partition column.

2

Set the partition column variable

Create a Hive variable called partition_column and set it to the string 'year'.

Hadoop

CREATE TABLE sales_data (
  id INT,
  product STRING,
  amount FLOAT
)
PARTITIONED BY (year INT)
STORED AS TEXTFILE;

-- Set the partition_column variable
-- Your code here

Need a hint?

Use SET to define a Hive variable.

3

Load data into partitions

Write a Hive query to load data from /user/hive/sales_data_2023.txt into the sales_data table partition where year=2023. Use LOAD DATA INPATH with INTO TABLE and PARTITION clauses.

Hadoop

CREATE TABLE sales_data (
  id INT,
  product STRING,
  amount FLOAT
)
PARTITIONED BY (year INT)
STORED AS TEXTFILE;

SET partition_column=year;

-- Load data into partition year=2023
-- Your code here

Need a hint?

Use LOAD DATA INPATH 'path' INTO TABLE table_name PARTITION (partition_column=value).

4

Query data filtering by partition

Write a Hive query to select all columns from sales_data where year=2023. Print the results.

Hadoop

CREATE TABLE sales_data (
  id INT,
  product STRING,
  amount FLOAT
)
PARTITIONED BY (year INT)
STORED AS TEXTFILE;

SET partition_column=year;

LOAD DATA INPATH '/user/hive/sales_data_2023.txt' INTO TABLE sales_data PARTITION (year=2023);

-- Query sales_data filtering by year=2023
-- Your code here

Need a hint?

Use SELECT * FROM sales_data WHERE year=2023; to query only the partitioned data.