• 为了保证你在浏览本网站时有着更好的体验,建议使用类似Chrome、Firefox之类的浏览器~~
    • 如果你喜欢本站的内容何不Ctrl+D收藏一下呢,与大家一起分享各种编程知识~
    • 本网站研究机器学习、计算机视觉、模式识别~当然不局限于此,生命在于折腾,何不年轻时多折腾一下

Hive之行转列lateral view用法

Sql admin 5个月前 (07-23) 2367次浏览 0个评论 扫描二维码

一般写 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

pageidSTRING
adid_listArray<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 adidcount(1)
11
21
32
41
51

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> col1Array<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 mycol1Array<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 myCol1string 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


Deeplearn, 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明Hive 之行转列 lateral view 用法
喜欢 (2)
admin
关于作者:
互联网行业码农一枚/业余铲屎官/数码影音爱好者/二次元

您必须 登录 才能发表评论!