Hive之行转列lateral view用法

23,281次阅读

共计 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值生成行。

例如，以下查询返回空结果：