Hive之行转列lateral view用法

23,322次阅读
没有评论

共计 2291 个字符,预计需要花费 6 分钟才能阅读完成。

一般写sql经常会遇到行转列或者列转行之类的操作,就像concat_ws之类的函数被广泛的使用,今天这个也是经常要使用的拓展方法。

Lateral View 语法

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

描述

横向视图与用户定义的表生成函数(如explode())结合使用。 如内置表生成函数中所述,UDTF为每个输入行生成零个或多个输出行。 横向视图首先将UDTF应用于基表的每一行,然后将结果输出行连接到输入行,以形成具有所提供的表别名的虚拟表。

在Hive 0.6.0之前,横向视图不支持谓词下推优化。 在Hive 0.5.0及更早版本中,如果您使用WHERE子句,则查询可能尚未编译。 解决方法是添加set hive.optimize.ppd = false; 在你的查询之前。 修复是在Hive 0.6.0中完成的; 请参阅https://issues.apache.org/jira/browse/HIVE-1056:谓词下推不适用于UDTF。

从Hive 0.12.0开始,可以省略列别名。 在这种情况下,别名是从UTDF返回的StructObjectInspector的字段名继承的。

举个栗子

下表 pageAds. 它有两个字段: pageid (页码) and adid_list (页面上的adid):

Column name

Column type

pageid STRING
adid_list Array<int>

另外一个实例:

pageid

adid_list

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

用户想要统计各个页面出现的广告的次数

此时可以使用lateral view 和explode处理adid_list 字段得到如下效果

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

The resulting output will be

pageid (string)

adid (int)

“front_page” 1
“front_page” 2
“front_page” 3
“contact_page” 3
“contact_page” 4
“contact_page” 5

因为要统计频次,所以使用group by聚合操作

SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;
int adid count(1)
1 1
2 1
3 2
4 1
5 1

Multiple Lateral Views

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

如下所示:

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

上面的示例sql中后面一个直接饮用之前的输出结果作为输入,假设有以下数据表:

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

查询语句

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查询之后:

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”

Lateral Views 外连接

在Hive版本0.12.0中引入

即使LATERAL VIEW通常不会生成行,用户也可以指定可选的OUTER关键字来生成行。 当使用的UDTF没有生成任何行时,当爆炸列为空时爆炸很容易发生这种情况,就会发生这种情况。 在这种情况下,源行永远不会出现在结果中。 可以使用OUTER来防止这种情况,并且将在来自UDTF的列中使用NULL值生成行。

例如,以下查询返回空结果:

SELEC * FROM src LATERAL VIEW explode(array()) C AS a limit 10;

But with the OUTER keyword

SELECT FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10;

it will produce:

238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL

正文完
请博主喝杯咖啡吧!
post-qrcode
 2
admin
版权声明:本站原创文章,由 admin 2019-07-24发表,共计2291字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)
验证码