Hive query optimization in Hadoop - Mini Project: Build & Apply

Hive Query Optimization

📖 Scenario: You work as a data analyst in a company that stores large sales data in Hive tables. You want to write efficient Hive queries to get insights quickly without wasting resources.

🎯 Goal: Build a Hive query step-by-step that selects sales data for a specific year and calculates total sales per product, using optimization techniques like filtering early and selecting only needed columns.

📋 What You'll Learn

Create a Hive table with sales data

Add a variable for the target year to filter data

Write a Hive query that selects product and sales amount filtered by the target year

Print the total sales per product for the target year

💡 Why This Matters

🌍 Real World

Companies use Hive to store and analyze large datasets. Writing optimized queries saves time and computing resources.

💼 Career

Data analysts and engineers must write efficient Hive queries to handle big data and deliver insights quickly.

1

Create the sales data table

Create a Hive table called sales_data with columns product (string), year (int), and sales_amount (int). Insert these exact rows: ('Apple', 2022, 100), ('Banana', 2022, 150), ('Apple', 2023, 200), ('Banana', 2023, 100).

Hadoop

# Create the sales_data table and insert the rows
# Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO TABLE to add rows.

2

Set the target year variable

Create a Hive variable called target_year and set it to 2022 to filter sales data for that year.

Hadoop

CREATE TABLE sales_data (product STRING, year INT, sales_amount INT);
INSERT INTO TABLE sales_data VALUES
('Apple', 2022, 100),
('Banana', 2022, 150),
('Apple', 2023, 200),
('Banana', 2023, 100);

-- Set the target_year variable
# Your code here

Hint

Use SET variable_name=value; syntax to create a variable in Hive.

3

Write the optimized query to select filtered data

Write a Hive query that selects product and sales_amount from sales_data where year equals the variable ${target_year}. Use this exact query format: SELECT product, sales_amount FROM sales_data WHERE year = ${target_year};

Hadoop

CREATE TABLE sales_data (product STRING, year INT, sales_amount INT);
INSERT INTO TABLE sales_data VALUES
('Apple', 2022, 100),
('Banana', 2022, 150),
('Apple', 2023, 200),
('Banana', 2023, 100);

SET target_year=2022;

-- Write the query to select product and sales_amount filtered by target_year
# Your code here

Hint

Use the variable ${target_year} in the WHERE clause to filter data.

4

Calculate and display total sales per product

Write a Hive query that calculates the total sales per product for the year stored in ${target_year}. Use GROUP BY product and SUM(sales_amount). Print the results with columns product and total_sales.

Hadoop

CREATE TABLE sales_data (product STRING, year INT, sales_amount INT);
INSERT INTO TABLE sales_data VALUES
('Apple', 2022, 100),
('Banana', 2022, 150),
('Apple', 2023, 200),
('Banana', 2023, 100);

SET target_year=2022;

-- Write the query to calculate total sales per product for target_year
# Your code here

Hint

Use SUM(sales_amount) AS total_sales and GROUP BY product to get totals.

Practice

1. What is the main purpose of Hive query optimization?

easy

A. To increase the size of data stored in Hive

B. To make data queries faster and reduce resource usage

C. To make Hive queries more complex

D. To disable partitioning in Hive tables

Solution

1. Step 1: Understand Hive query optimization goal

   Hive query optimization aims to improve query speed and reduce resource consumption.

2. Step 2: Evaluate options based on this goal

   Only To make data queries faster and reduce resource usage matches the goal of faster queries and less resource use.

3. Final Answer:

   To make data queries faster and reduce resource usage -> Option B

4. Quick Check:

   Optimization = Faster queries [OK]

Hint: Optimization means faster queries and less cost [OK]

Common Mistakes:

• Thinking optimization increases data size
• Confusing optimization with query complexity
• Ignoring resource usage benefits

2. Which of the following is the correct syntax to enable cost-based optimization (CBO) in Hive?

easy

A. ACTIVATE hive.cbo = 1;

B. ENABLE hive.cbo = true;

C. SET hive.cbo.enable=true;

D. SET hive.cob.enable = yes;

Solution

1. Step 1: Recall correct Hive syntax for setting properties

   Hive uses 'SET property=value;' to configure settings.

2. Step 2: Identify correct property name and value

   The correct property is 'hive.cbo.enable' and value is 'true'. SET hive.cbo.enable=true; matches this exactly.

3. Final Answer:

   SET hive.cbo.enable=true; -> Option C

4. Quick Check:

   SET + hive.cbo.enable=true [OK]

Hint: Use SET with exact property name and true/false [OK]

Common Mistakes:

• Misspelling property name
• Using ENABLE or ACTIVATE instead of SET
• Using yes/no instead of true/false

3. Given the Hive query:
SELECT * FROM sales WHERE year = 2023 AND region = 'US';
Assuming the table is partitioned by year and bucketed by region, what is the main optimization benefit?

medium

A. Hive reads only data for year 2023 and region 'US' buckets

B. Hive scans the entire table ignoring partitions and buckets

C. Hive reads only the region 'US' partition

D. Hive reads only the year 2023 bucket

Solution

1. Step 1: Understand partitioning and bucketing roles

   Partitioning by year means data is divided by year folders; bucketing by region means data is split into files by region.

2. Step 2: Analyze query filter impact

   Filtering on year=2023 uses partition pruning; filtering on region='US' uses bucket pruning.

3. Final Answer:

   Hive reads only data for year 2023 and region 'US' buckets -> Option A

4. Quick Check:

   Partition + bucket pruning = less data read [OK]

Hint: Partition filters reduce folders; bucket filters reduce files [OK]

Common Mistakes:

• Confusing partition with bucket filtering
• Assuming full table scan despite filters
• Mixing partition and bucket roles

4. Identify the error in this Hive query optimization setting:
SET hive.cbo.enable = 'true';

medium

A. SET command cannot be used for this property

B. Property name is incorrect

C. Missing semicolon at end

D. Value should not be in quotes

Solution

1. Step 1: Check correct syntax for SET command

   Hive SET commands use format: SET property=value; without quotes around boolean values.

2. Step 2: Identify issue with quotes around 'true'

   Putting 'true' in quotes makes it a string, which Hive does not accept for boolean properties.

3. Final Answer:

   Value should not be in quotes -> Option D

4. Quick Check:

   Boolean values no quotes [OK]

Hint: Boolean values in SET must be unquoted true/false [OK]

Common Mistakes:

• Adding quotes around boolean values
• Confusing property name spelling
• Omitting semicolon (optional but recommended)

5. You have a large Hive table with sales data partitioned by year and month. You want to optimize a query filtering on year=2023 only. Which approach will improve query performance the most?

hard

A. Add bucketing on month and enable cost-based optimization

B. Drop the month partition and keep only year

C. Disable partition pruning to scan all data

D. Use SELECT * without WHERE clause

Solution

1. Step 1: Understand current partitioning and query filter

   The table is partitioned by year and month, but query filters only on year=2023.

2. Step 2: Evaluate optimization options

   Adding bucketing on month helps organize data within partitions. Enabling cost-based optimization lets Hive choose best plan.

3. Step 3: Assess other options

   Dropping month partition reduces granularity; disabling pruning or removing WHERE clause worsens performance.

4. Final Answer:

   Add bucketing on month and enable cost-based optimization -> Option A

5. Quick Check:

   Partition + bucketing + CBO = best performance [OK]

Hint: Combine partitioning, bucketing, and CBO for best speed [OK]

Common Mistakes:

• Removing useful partitions
• Disabling pruning increases data scanned
• Ignoring cost-based optimization benefits