Hive SQL 行转列 LateralView
更新时间：2022-06-14 18:49:53 标签：hive
sql
行转列
提示
Hive SQL 教程 编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》 。作者开办 Python 数据分析培训，详情 Python 数据分析培训。
横向视图（LateralView）与用户定义的表格生成函数（如explode()）结合使用。 如内置表生成函数（UDTF）中所述，UDTF 为每个输入行生成零个或多个输出行。
语法
LateralView 的语法如下：
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
横向视图（LateralView）首先将 UDTF 应用于基表的每一行，然后将生成的输出行连接到输入行以形成具有所提供表别名的虚拟表。
示例
考虑以下名为 pageAds 的基表。 它有两列：pageid（页面名称）和 adid_list（出现在页面上的广告数组），数据类型是：
| Column name | Column type |
|---|---|
| pageid | STRING |
| adid_list | Array |
包含两行的示例表：
| pageid | adid_list |
|---|---|
| front_page | [1, 2, 3] |
| contact_page | [3, 4, 5] |
并且用户想要计算广告在所有页面上出现的总次数。
带有explode() 的横向视图可用于使用查询将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;
| int adid | count(1) |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
多个横向视图
一个 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 |
Array |
|---|---|
| [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 |
|---|---|
| 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 VIEW 通常不会生成行，用户也可以指定可选的 OUTER 关键字来生成行。 当使用的 UDTF 不生成任何行时会发生这种情况，当要分解的列为空时，分解很容易发生这种情况。 在这种情况下，源行将永远不会出现在结果中。 OUTER 可用于防止这种情况发生，并且将在来自 UDTF 的列中生成带有 NULL 值的行。
例如，以下查询返回空结果：
SELEC * FROM src LATERAL VIEW explode(array()) C AS a limit 10;
但是使用 OUTER 关键字：
SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10;
将输出：
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
...
参考
- https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
