Hive query optimization in Hadoop - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to enable vectorized query execution in Hive.

Hadoop

SET hive.vectorized.execution.enabled=[1];

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

Atrue

Bnone

Cauto

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'false' disables vectorization, so no speedup.

Using 'auto' or 'none' are invalid values for this setting.

Setting hive.vectorized.execution.enabled to true enables vectorized query execution, which improves performance by processing batches of rows.

2fill in blank

medium

Complete the code to enable cost-based optimization in Hive.

Hadoop

SET hive.cbo.enable=[1];

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

Ano

Btrue

Cfalse

Dmaybe

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'false' disables cost-based optimization.

Using invalid values like 'no' or 'maybe' causes errors.

Setting hive.cbo.enable to true turns on cost-based optimization, which helps Hive choose efficient query plans.

3fill in blank

hard

Fix the error in the query to enable dynamic partition pruning.

Hadoop

SET hive.optimize.ppd=[1];

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

Aenable

Bfalse

Ctrue

Don

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'enable' or 'on' instead of 'true' causes syntax errors.

Using 'false' disables the optimization.

The correct value to enable dynamic partition pruning is true. Other values cause errors or are invalid.

4fill in blank

hard

Fill both blanks to create a query that uses a map join and disables auto conversion.

Hadoop

SET hive.auto.convert.join=[1];
SELECT /*+ MAPJOIN([2]) */ * FROM sales JOIN customers ON sales.customer_id = customers.id;

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

Afalse

Btrue

Ccustomers

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Setting auto convert join to 'true' disables manual control.

Using the wrong table name in MAPJOIN causes inefficient joins.

Setting hive.auto.convert.join to false disables automatic join conversion. The hint MAPJOIN(customers) forces a map join on the customers table.

5fill in blank

hard

Fill all three blanks to create a query that collects statistics for optimization.

Hadoop

ANALYZE TABLE [1] COMPUTE STATISTICS FOR COLUMNS [2] [3];

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

Asales

Bcustomer_id

CWITH HISTOGRAM

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table or column name.

Omitting 'WITH HISTOGRAM' results in less detailed statistics.

This command collects column statistics with histograms on the 'customer_id' column of the 'sales' table, helping Hive optimize queries.