如何基于Lookup表向Hive表插入关联匹配的数据？

Let's break this down into manageable steps to get your data into the result table correctly, including handling cases where there's no matching entry in the lookup data.

Step 1: Create a Structured Lookup Table #

First, your current lookup data is in unstructured text format—we need to convert it into a proper Hive table so we can join it with input_source. Let's create a table line_type_lookup that maps the lines and type combinations to their descriptions:

CREATE TABLE IF NOT EXISTS line_type_lookup (
    lines STRING,
    type STRING,
    description STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

Then insert the lookup data into this table:

INSERT INTO line_type_lookup VALUES
('A', 'A16', 'Excellent'),
('B', 'D44', 'Average'),
('A', 'L90', 'Good'),
('B', 'Y78', 'Fair');

Step 2: Insert Joined Data into the Result Table #

Now we'll use a LEFT JOIN to combine input_source with our structured lookup table. This ensures we keep all rows from input_source even when there's no matching lines+type pair in the lookup. We'll use COALESCE alongside a CASE statement to set the custom default descriptions for unmatched rows (matching your sample output for keys 3 and 5):

First, ensure your result table exists (create it if it doesn't):

CREATE TABLE IF NOT EXISTS result (
    date STRING,
    key INT,
    desc STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

Then run the insert with join logic:

INSERT INTO result
SELECT
    is.date,
    is.key,
    COALESCE(l.description, 
             CASE 
                 WHEN is.key = 3 THEN 'Not bad'
                 WHEN is.key = 5 THEN 'Fine'
                 ELSE 'Unknown' -- Fallback for any other unmatched rows you might encounter
             END) AS desc
FROM input_source is
LEFT JOIN line_type_lookup l
ON is.lines = l.lines AND is.type = l.type;

Key Details to Keep in Mind: #

• LEFT JOIN: This preserves every row from input_source, which is critical because we have entries (keys 3 and 5) that don't have matching pairs in the lookup table.
• COALESCE: This function picks the first non-null value available—so if a matching description exists in the lookup, it uses that; otherwise, it falls back to the CASE statement to apply your specified defaults.
• String Case Sensitivity: Hive treats string values as case-sensitive, so double-check that lines and type values in input_source match exactly with those in the lookup table (e.g., 'A' vs 'a' would be considered different entries).

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