Informix到HiveQL

Question

我为Informix编写了一个SQL查询

SELECT cols
FROM table1 t1, outer(table2 t2, table3 t3)
WHERE t1.id = t2.id and t2.type = t3.type

我们在Hive上有相同的表和数据，我想将其转换为HiveQL

Answer 1

使用左联接而不是外部连接

SELECT cols
FROM table1 t1
     LEFT JOIN 
         (select t2.id as join_key, other_cols 
            from table2 t2
                 inner join table3 t3 ON t2.type = t3.type
         ) s ON s.join_key=t1.id