Bucketing for sampling in Hadoop - Mini Project: Build & Apply

Bucketing for Sampling in Hadoop

📖 Scenario: You work at a company that stores large amounts of user data in Hadoop. You want to practice sampling data efficiently using bucketing. Bucketing helps split data into fixed parts, making sampling faster and easier.

🎯 Goal: You will create a Hive table with bucketing on user IDs, set the number of buckets, insert sample data, and then write a query to sample data from a specific bucket.

📋 What You'll Learn

Create a Hive table called users with columns user_id (int) and user_name (string)

Bucket the table by user_id into 4 buckets

Insert 8 sample users with specific user_id and user_name

Write a query to select users from bucket number 2

💡 Why This Matters

🌍 Real World

Bucketing is used in big data systems like Hadoop to split large datasets into manageable parts. This helps in faster sampling and querying.

💼 Career

Data engineers and analysts use bucketing to optimize data storage and speed up queries in Hadoop ecosystems.

1

Create a bucketed Hive table

Write a Hive CREATE TABLE statement to create a table called users with columns user_id INT and user_name STRING. Bucket the table by user_id into 4 buckets using CLUSTERED BY (user_id) INTO 4 BUCKETS. Use STORED AS ORC format.

Hadoop

-- Write the CREATE TABLE statement for 'users' with bucketing
-- Your code here

Hint

Use CLUSTERED BY (user_id) INTO 4 BUCKETS to bucket by user_id.

2

Insert sample data into the bucketed table

Write an INSERT INTO TABLE users statement to add these 8 users exactly: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David'), (5, 'Eva'), (6, 'Frank'), (7, 'Grace'), (8, 'Hannah'). Use VALUES syntax.

Hadoop

CREATE TABLE users (
  user_id INT,
  user_name STRING
)
CLUSTERED BY (user_id) INTO 4 BUCKETS
STORED AS ORC;

-- Insert 8 users into 'users' table
-- Your code here

Hint

Use INSERT INTO TABLE users VALUES (...), (...), ...; to add all users.

3

Write a query to sample users from bucket 2

Write a Hive SELECT query to get all columns from users where the bucket number is 2. Use the function pmod(hash(user_id), 4) = 2 to filter the bucket.

Hadoop

CREATE TABLE users (
  user_id INT,
  user_name STRING
)
CLUSTERED BY (user_id) INTO 4 BUCKETS
STORED AS ORC;

INSERT INTO TABLE users VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'David'),
  (5, 'Eva'),
  (6, 'Frank'),
  (7, 'Grace'),
  (8, 'Hannah');

-- Select users from bucket number 2
-- Your code here

Hint

Use pmod(hash(user_id), 4) = 2 in the WHERE clause to filter bucket 2.

4

Display the sampled users from bucket 2

Write a Hive SELECT statement to display the user_id and user_name of users from bucket 2 using the same filter pmod(hash(user_id), 4) = 2. Use ORDER BY user_id to sort the output.

Hadoop

CREATE TABLE users (
  user_id INT,
  user_name STRING
)
CLUSTERED BY (user_id) INTO 4 BUCKETS
STORED AS ORC;

INSERT INTO TABLE users VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'David'),
  (5, 'Eva'),
  (6, 'Frank'),
  (7, 'Grace'),
  (8, 'Hannah');

-- Display user_id and user_name from bucket 2 ordered by user_id
-- Your code here

Hint

Use SELECT user_id, user_name FROM users WHERE pmod(hash(user_id), 4) = 2 ORDER BY user_id; to show the sampled users.

Practice

1. What is the main purpose of bucketing in Hadoop when working with big data?

easy

A. To split data into fixed parts based on a column for efficient sampling

B. To encrypt data for security

C. To compress data to save storage space

D. To sort data alphabetically

Solution

1. Step 1: Understand bucketing concept

   Bucketing divides data into fixed parts or buckets based on a column value.

2. Step 2: Identify purpose in big data

   This helps in sampling and faster query processing by limiting data scanned.

3. Final Answer:

   To split data into fixed parts based on a column for efficient sampling -> Option A

4. Quick Check:

   Bucketing = Fixed parts for sampling [OK]

Hint: Bucketing splits data into parts for easier sampling [OK]

Common Mistakes:

• Confusing bucketing with data encryption
• Thinking bucketing compresses data
• Assuming bucketing sorts data alphabetically

2. Which of the following is the correct syntax to create a bucketed table in Hive with 4 buckets on column user_id?

easy

A. CREATE TABLE users_bucketed (user_id INT, name STRING) DISTRIBUTED BY (user_id) INTO 4 BUCKETS;

B. CREATE TABLE users_bucketed (user_id INT, name STRING) PARTITIONED BY (user_id) INTO 4 BUCKETS;

C. CREATE TABLE users_bucketed (user_id INT, name STRING) CLUSTERED BY (user_id) INTO 4 BUCKETS;

D. CREATE TABLE users_bucketed (user_id INT, name STRING) SORTED BY (user_id) INTO 4 BUCKETS;

Solution

1. Step 1: Recall Hive bucketing syntax

   Hive uses CLUSTERED BY (column) INTO n BUCKETS to create buckets.

2. Step 2: Match syntax with options

   CREATE TABLE users_bucketed (user_id INT, name STRING) CLUSTERED BY (user_id) INTO 4 BUCKETS; matches the correct syntax for bucketing on user_id with 4 buckets.

3. Final Answer:

   CREATE TABLE users_bucketed (user_id INT, name STRING) CLUSTERED BY (user_id) INTO 4 BUCKETS; -> Option C

4. Quick Check:

   CLUSTERED BY + INTO BUCKETS = correct syntax [OK]

Hint: Use CLUSTERED BY with INTO BUCKETS for bucketing syntax [OK]

Common Mistakes:

• Using PARTITIONED BY instead of CLUSTERED BY
• Confusing DISTRIBUTED BY with bucketing syntax
• Using SORTED BY which is for sorting, not bucketing

3. Given a Hive table sales bucketed by region into 3 buckets, what will be the output of the query SELECT COUNT(*) FROM sales TABLESAMPLE(BUCKET 2 OUT OF 3) if the total rows are 3000 evenly distributed?

medium

A. 1000

B. 2000

C. 3000

D. 0

Solution

1. Step 1: Understand TABLESAMPLE BUCKET syntax

   TABLESAMPLE(BUCKET 2 OUT OF 3) samples 1/3 fraction of the data by selecting bucket number 2 out of 3 buckets.

2. Step 2: Calculate expected rows

   Total rows 3000 evenly split into 3 buckets = 1000 rows per bucket. Sampling bucket 2 out of 3 returns 1000 rows.

3. Final Answer:

   1000 -> Option A

4. Quick Check:

   Sampling bucket 2 out of 3 returns 1 bucket = 1000 rows [OK]

Hint: BUCKET x OUT OF y samples bucket number x only [OK]

Common Mistakes:

• Assuming sampling returns all rows
• Thinking BUCKET 2 OUT OF 3 selects 2 buckets (2000 rows)
• Ignoring even distribution assumption

4. Identify the error in the following Hive query for bucketing:

CREATE TABLE employees (id INT, name STRING) CLUSTERED BY id INTO BUCKETS 5;

medium

A. The number of buckets should come before the keyword BUCKETS

B. The keyword BUCKETS should come before the number 5

C. The keyword BUCKETS is missing

D. The syntax should be CLUSTERED BY (id) INTO 5 BUCKETS

Solution

1. Step 1: Review correct bucketing syntax

   Correct syntax is CLUSTERED BY (column) INTO n BUCKETS.

2. Step 2: Compare with given query

   Given query has CLUSTERED BY id INTO BUCKETS 5, which is incorrect order and missing parentheses.

3. Final Answer:

   The syntax should be CLUSTERED BY (id) INTO 5 BUCKETS -> Option D

4. Quick Check:

   Correct order: INTO number BUCKETS and parentheses around column [OK]

Hint: Use INTO number BUCKETS, not INTO BUCKETS number [OK]

Common Mistakes:

• Placing BUCKETS before the number
• Omitting parentheses around column
• Using wrong keyword order

5. You have a large table transactions bucketed by customer_id into 10 buckets. You want to sample approximately 30% of the data using bucketing. Which TABLESAMPLE clause should you use to get close to this percentage?

hard

A. TABLESAMPLE(BUCKET 4 OUT OF 10)

B. TABLESAMPLE(BUCKET 3 OUT OF 10)

C. TABLESAMPLE(BUCKET 5 OUT OF 10)

D. TABLESAMPLE(BUCKET 7 OUT OF 10)

Solution

1. Step 1: Understand sampling with buckets

   Sampling bucket n out of 10 selects only bucket number n, which is 1/10 of the data.

2. Step 2: Find bucket count for ~30%

   To sample approximately 30%, you need to select multiple buckets, but TABLESAMPLE(BUCKET x OUT OF y) selects only one bucket.

3. Step 3: Conclusion

   None of the options correctly sample ~30% because each selects only one bucket (10%).

4. Final Answer:

   None of the above options correctly sample ~30% -> Option B

Hint: TABLESAMPLE(BUCKET x OUT OF y) selects only one bucket, so cannot sample 30% with one bucket out of 10 [OK]

Common Mistakes:

• Choosing 3 or 4 buckets assuming it samples multiple buckets
• Ignoring that TABLESAMPLE(BUCKET x OUT OF y) selects only one bucket
• Assuming bucket numbers are percentages