Hive实现条件截断加载表：能否无需Shell脚本完成？

Absolutely! You can achieve this "truncate and load only if all source categories exist in the target table" logic entirely within a Hive script, eliminating the need for a separate Shell wrapper. Here's how to do it:

Approach Overview #

Instead of creating intermediate temp tables and parsing results in Shell, we'll:

• Directly check if all distinct program_type_cd values from the source table exist in the master table
• Use Hive's built-in conditional execution (available in Hive 2.0+) to run the INSERT OVERWRITE only when the check passes

Complete Hive Script #

-- Set necessary configurations (adjust if needed for your environment)
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.cli.print.header=false;

-- Calculate validation indicator: 1 if all source categories exist in master, 0 otherwise
SET hivevar:all_categories_valid = 
`SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END 
 FROM (
     SELECT DISTINCT program_type_cd 
     FROM source 
     WHERE program_type_cd NOT IN (SELECT DISTINCT program_type_cd FROM master)
 ) missing_categories`;

-- Conditionally execute truncate/load based on the validation result
IF ${hivevar:all_categories_valid} = 1 THEN
    INSERT OVERWRITE TABLE temp.master
    SELECT * FROM temp.source;
    
    SELECT 'Success: All source categories are present in master. Truncated and loaded temp.master.' AS status;
ELSE
    SELECT 'Info: Some source categories are missing from master. No load operation performed.' AS status;
END IF;

Key Details #

• Indicator Calculation: The subquery missing_categories identifies any program_type_cd values in the source that aren't present in the master table. If this subquery returns 0 rows, we set all_categories_valid to 1 (meaning all categories are valid).
• Conditional Execution: Hive's IF-THEN-ELSE syntax lets us conditionally trigger the load operation without relying on external Shell parsing. This keeps all logic contained within the Hive environment.
• Streamlined Logic: We eliminate the need for temporary tables like temp.master_source_join and temp.indicator by handling the validation check in a single, efficient subquery.

For Older Hive Versions (Pre-2.0) #

If you're working with a Hive version that doesn't support conditional execution, you can use a WHERE clause trick to conditionally insert data (note: this will truncate the master table even if no rows are inserted, which differs from your original logic):

INSERT OVERWRITE TABLE temp.master
SELECT * FROM temp.source
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT program_type_cd FROM source WHERE program_type_cd NOT IN (SELECT DISTINCT program_type_cd FROM master)) t) = 0;

We strongly recommend the conditional execution approach if your Hive version supports it, as it aligns perfectly with your original "do nothing if validation fails" requirement.

内容的提问来源于stack exchange，提问作者Gaurang Shah