《Hive官方文档》Hive - Lateral View 与 explode() 结合使用

官网链接：Hive官方文档

一、Lateral View 语法

lateralView: 
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

fromClause: 
FROM baseTable (lateralView)*

Lateral View用于UDTF(user-defined table generating functions)中将行转成列，例如explode()。

二、示例

1.单个 Lateral View

以下名为pageAds的基本表。它有两列：pageid(页面名称)和adid_list(页面上显示的广告数组)：

Column name	Column type
pageid	STRING
adid_list	Array<int>

有两行的数据：

pageid	adid_list
front_page	[1, 2, 3]
contact_page	[3, 4, 5]

计算广告在所有页面上展示的总次数。

带有explode()的Lateral View可将adid_list转换为单独的行：

SELECT 
    pageid, 
    adid
FROM pageAds 
LATERAL VIEW explode(adid_list) adTable AS adid;

结果输出将是

pageid (string)	adid (int)
"front_page"	1
"front_page"	2
"front_page"	3
"contact_page"	3
"contact_page"	4
"contact_page"	5

然后，为了计算特定广告的展示次数，可以使用 count/group by：

SELECT 
    adid, 
    count(1)
FROM pageAds 
LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;

adid	count(1)
1	1
2	1
3	2
4	1
5	1

2.多个 Lateral View

FROM 子句可以具有多个 LATERAL VIEW 子句。随后的 LATERAL VIEWS 可以引用 LATERAL VIEW 左侧出现的任何表中的列。

例如：

SELECT 
    * 
FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

LATERAL VIEW 子句按它们出现的顺序应用。例如基本表：

Array<int> col1	Array<string> col2
[1, 2]	[a", "b", "c"]
[3, 4]	[d", "e", "f"]

The query:

SELECT 
    myCol1, col2 
FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1;

将产生如下结果：

int mycol1	Array<string> col2
1	[a", "b", "c"]
2	[a", "b", "c"]
3	[d", "e", "f"]
4	[d", "e", "f"]

多个 Lateral View 的HQL查询：

SELECT 
    myCol1, 
    myCol2 
FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

将产生如下结果：

int myCol1	string myCol2
1	"a"
1	"b"
1	"c"
2	"a"
2	"b"
2	"c"
3	"d"
3	"e"
3	"f"
4	"d"
4	"e"
4	"f"