Hive中如何利用另一表匹配值替换目标表所有行的字段值

需求是将Table1中所有字段的取值，替换为Table2中对应name值的id：

Table1结构及数据：

name    first_name  last_name
xyz          abc          def

Table2结构及数据：

name           id   
xyz             1   
abc             2   
def             3   

期望输出：

name    first_name  last_name
1           2            3

解决方案1：多次左连接Table2 #

通过多次左连接Table2，分别匹配Table1的每个字段，获取对应的id值：

SELECT 
    t2.id AS name,
    t3.id AS first_name,
    t4.id AS last_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.name = t2.name
LEFT JOIN table2 t3 ON t1.first_name = t3.name
LEFT JOIN table2 t4 ON t1.last_name = t4.name;

• 这种方式适合字段较多的场景，维护性较好
• 如果Table1中某个字段值在Table2中不存在，会返回NULL；若需要默认值，可使用COALESCE(t2.id, 默认值)替换

解决方案2：标量子查询 #

针对每个字段直接通过子查询获取对应id，写法更简洁：

SELECT 
    (SELECT id FROM table2 WHERE name = t1.name) AS name,
    (SELECT id FROM table2 WHERE name = t1.first_name) AS first_name,
    (SELECT id FROM table2 WHERE name = t1.last_name) AS last_name
FROM table1 t1;

• 注意：需保证Table2的name字段是唯一的，否则子查询会返回多行导致报错

关于CASE语句的补充 #

如果之前尝试用CASE语句，其实也能实现，但需要为每个字段的所有可能值写WHEN分支，当取值较多时会非常繁琐，比如：

SELECT 
    CASE name 
        WHEN 'xyz' THEN 1 
        WHEN 'abc' THEN 2 
        ... 
    END AS name,
    CASE first_name 
        WHEN 'abc' THEN 2 
        WHEN 'def' THEN 3 
        ... 
    END AS first_name
FROM table1;

这种方式扩展性差，不推荐使用，还是上面两种关联/子查询的方法更高效灵活。

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