Differences Between Internal and External Tables

• External tables require the keyword EXTERNAL when creating.
• When creating an internal table, data is moved to the specified path in the data warehouse.
• When creating an external table, data is not moved; only the path to the data is recorded.
• Deleting an internal table removes both metadata and data.
• Deleting an external table only removes metadata, leaving the data intact.

Differences Between CLUSTER BY, SORT BY, DISTRIBUTE BY, and ORDER BY

• ORDER BY: Global sorting, uses a single reducer, can be slow with large data.
• SORT BY: Local sorting, only guarantees order within each reducer, not global.
• DISTRIBUTE BY: Controls how map output is distributed; rows with the same value are sent to the same reducer.
• CLUSTER BY: Groups data by specified field, and within each group sorts by that field in ascending order (only ascending). CLUSTER BY is equivalent to DISTRIBUTE BY + SORT BY.

Differences Between Partitioning and Bucketing

• Partitioning separates table data into multiple directories.
• Bucketing divides the data file into several parts.
• Partitioning columns must be columns not present in the table.
• Bucketing columns must be columns that already exist in the table.

Differences Between UNION and UNION ALL

• UNION: Combines multiple result sets, removing duplicates and sorting.
• UNION ALL: Combines multiple result sets without deduplication or sorting.

Principle of JOIN in Hive

In the Map phase, the join key is set as the key, and selected fields are set as the value. In the Reduce phase, rows with the same key are sent to the same reducer.

How to Optimize JOIN Operations in Hive

• If there are many null keys, either filter them out or assign random values.
• For large table and small table joins, use MapJoin.
• For two large tables, filter out skewed keys and join them separately, then distribute across multiple tasks, and finally perform a UNION.

Three Types of User-Defined Functions (UDFs) in Hive

• UDF: User-defined function, one-to-one output, e.g., round.
• UDTF: User-defined table-generating function, one-to-many output, e.g., explode.
• UDAF: User-defined aggregate function, many-to-one output, e.g., count, sum.

Data Skew in Hive

What is Data Skew?

• Data skew refers to uneven distribution of data during distributed processing, where some data is concentrated.
• Data skew can cause low processign efficiency on certain nodes or even memory overflow.

Causes of Data Skew

• Inherent business logic.
• Poor table design leading to uneven key distribution.
• Certain SQL operations prone to data skew.

Main Operations Causing Data Skew

GROUP BY If there are too few dimensions and some values are large, distribution to different reducers can cause skew.

JOIN Some key values appear frequently, or there are many null keys, causing excessive data sent to a single reducer.

Solutions for Data Skew

Data Skew Caused by GROUP BY

When some groups have more data, adjust parameters:

SET hive.map.aggr = true;
SET hive.groupby.skewindata = true;

• hive.map.aggr = true enables aggregation at the map side.
• hive.groupby.skewindata = true enables load balancing when skew is detected. This triggers two MapReduce jobs: the first randomly distributes rows to reducers for aggregation, achieving load balancing; the second processes the pre-aggregated results, grouping by the same key to complete aggregation.

Data Skew Caused by JOIN

When there are many null keys in a join:

• Filter out null values.
• Assign random values to nulls.

When joining a large table with a small table:

• Use MapJoin to load the small table into memory and perform the join in the map phase.

Example:

SELECT /*+ MAPJOIN(b) */ a.a1, a.a2, b.b2
FROM table_a a
JOIN table_b b ON a.a1 = b.b1; -- b is the small table

When a key is skewed:

Filter out the skewed keys and join them separately, distributing across multiple tasks, then union the results.