Lateral View 语法
Lateral View 语法
1 lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)* 2 fromClause: FROM baseTable (lateralView)*
Lateral View 描述
Lateral View 用于UDTF(user-defined table generating functions)中将行转成列，和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。Lateral View 首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，Lateral View 再把结果组合，产生一个支持别名表的虚拟表。
目前Lateral View 不支持有上而下的优化。如果使用Where子句，查询可能将不被编译。解决方法：在查询之前执行set hive.optimize.ppd=false
例子
表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] |
执行语句：
1 SELECT pageid, adid 2 FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
将输出如下结果：
|
pageid (string) |
adid (int) |
|---|---|
|
"contact_page" |
3 |
|
"contact_page" |
4 |
|
"contact_page" |
5 |
|
"front_page" |
1 |
|
"front_page" |
2 |
|
"front_page" |
3 |
count/group可以被使用：
1 SELECT adid, count(1) 2 FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid 3 GROUP BY adid;
|
int adid |
count(1) |
|
1 |
1 |
|
2 |
1 |
|
3 |
2 |
|
4 |
1 |
|
5 |
1 |
多个Lateral View
例如下面的语句：
1 SELECT * FROM exampleTable 2 LATERAL VIEW explode(col1) myTable1 AS myCol1 3 LATERAL VIEW explode(myCol1) myTable2 AS myCol2;
假设有base表如下：
|
Array<int> col1 |
Array<string> col2 |
|
[1, 2] |
[a", "b", "c"] |
|
[3, 4] |
[d", "e", "f"] |
语句：
1 SELECT myCol1, col2 FROM baseTable 2 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 的语句：
1 SELECT myCol1, myCol2 FROM baseTable 2 LATERAL VIEW explode(col1) myTable1 AS myCol1 3 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" |
