arrays - 数组列值与配置单元中正常列值之间的比较

Question

表1

Column1 Column21       1,2,102       11,12,133       1,2,144       20,1,105       11,12,13,14

表2

Column1 Column21       Purchase2       Product View10      Cart Open11      Checkout12      Cart Add13      Cart Remove14      Cart View20      Campaign View

结果表应该如下所示

Column1 Column2     DESC1       1,2,10      Purchase, Product View, Cart Open2       11,12,13    Checkout, Cart Add, Cart Remove3       1,2,14      Purchase, Product View4       20,1,10     Campaign View, Purchase, Cart Open5       11,12,13,14 Checkout, Cart Add, Cart Remove, Cart View

注意:

Table1.column2[0]==table2.column1 然后它会在我们新添加的结果表的 desc 列中显示 table2.column2 的值。

我们可以在此查询中使用 join 吗？如果是，我们如何在 hive 中做？

请帮助解决这个要求。

提前致谢，暗部

Answer 1

查询:

add jar /path/to/jars/brickhouse-0.7.1.jar;
create temporary function collect as "brickhouse.udf.collect.CollectUDAF";

select a.col1
  , collect(b.col1)
  , collect(b.col2)
from (
    select col1, exp_col2
    from db.tbl1
    lateral view explode(col2) exptbl as exp_col2 ) a
join db.tbl2 b
on b.col1=a.exp_col2
group by a.col1

输出:

1       [1, 2, 10]         ["Purchase","Product View","Cart Open"]
2       [11, 12, 13]       ["Checkout","Cart Add","Cart Remove"]
3       [1, 2, 14]         ["Purchase","Product View","Cart View"]
4       [1, 10, 20]        ["Purchase","Cart Open","Campaign View"]
5       [11, 12 ,13 ,14]   ["Checkout","Cart Add","Cart Remove","Cart View"]

确保使用 brickhouse collect并且没有内置在 collect_list() 中，因为后者不(必然)保留顺序。