Bucketing for sampling in Hadoop - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a bucketed table with 4 buckets.

Hadoop

CREATE TABLE user_data_bucketed (user_id INT, name STRING) CLUSTERED BY (user_id) INTO [1] BUCKETS STORED AS ORC;

Drag options to blanks, or click blank then click option'

A4

B2

C6

D8

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong number of buckets that doesn't match the sampling requirement.

Forgetting to specify the CLUSTERED BY column.

The code creates a bucketed table with 4 buckets using the CLUSTERED BY clause.

2fill in blank

medium

Complete the code to sample 25% of data from a bucketed table.

Hadoop

SELECT * FROM user_data_bucketed TABLESAMPLE(BUCKET [1] OUT OF 4 ON user_id);

Drag options to blanks, or click blank then click option'

A2

B4

C3

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using bucket number 0 which is invalid.

Using bucket number equal to total buckets which returns all data.

Sampling bucket 1 out of 4 buckets gives approximately 25% of the data.

3fill in blank

hard

Fix the error in the bucketing clause to correctly bucket by user_id.

Hadoop

CREATE TABLE user_logs (user_id INT, action STRING) CLUSTERED BY ([1]) INTO 8 BUCKETS STORED AS PARQUET;

Drag options to blanks, or click blank then click option'

Auser

Buser_id

Cuserid

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in the table.

Misspelling the column name.

The column name is user_id, so it must be used exactly in the CLUSTERED BY clause.

4fill in blank

hard

Fill both blanks to create a bucketed table and sample bucket 3 out of 5.

Hadoop

CREATE TABLE sales_data (sale_id INT, amount FLOAT) CLUSTERED BY ([1]) INTO [2] BUCKETS STORED AS TEXTFILE;
SELECT * FROM sales_data TABLESAMPLE(BUCKET 3 OUT OF 5 ON sale_id);

Drag options to blanks, or click blank then click option'

Asale_id

Bamount

C5

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Mismatching bucket count between table and sampling.

Using wrong column for bucketing.

The table is bucketed by sale_id into 5 buckets to match the sampling of bucket 3 out of 5.

5fill in blank

hard

Fill all three blanks to create a bucketed table, insert data, and sample bucket 2 out of 4.

Hadoop

CREATE TABLE employee_data (emp_id INT, name STRING) CLUSTERED BY ([1]) INTO [2] BUCKETS STORED AS ORC;
INSERT INTO employee_data VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM employee_data TABLESAMPLE(BUCKET [3] OUT OF 4 ON emp_id);

Drag options to blanks, or click blank then click option'

Aemp_id

B4

C2

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column for bucketing.

Mismatching bucket count and sampling bucket number.

The table is bucketed by emp_id into 4 buckets, and sampling bucket 2 out of 4 selects 50% of data.

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