背景

之前在下面这篇文档里，介绍了行转列的方法：
collect_list/set()
这里补齐列转行的方法（虽然用得很少）。

函数

1 split()

将字符串分割成数组。

  1. split(string, pattern)

其中 pattern 是正则表达式，注意：

单字符类特殊字符（.） 转义字符类特殊字符（\d）
不加转义符（\） “.”，表示特殊字符 “\d”，表示“d”
加转义符（\） “\.”，表示“.” “\\d”，表示特殊字符

例如：

  1. split("1,2,3", ",") = ["1", "2", "3"]

2 explode()

用于将数组转换成多行数据，可以近似实现“列（一个值）转行”的效果。
是一个 HIVE 内置的“表生成函数”

  1. explode(array)

SELECT 一个 explode 结果是可以的，但不能直接 SELECT 多个字段其中包含 explode 结果，必须使用 LATERAL VIEW 进行视图拼接。意味着 SELECT province, explode(split(cities, ‘,’)) … 是错误的。

3 LATERAL VIEW

懒得翻译了。

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

  1. LATERAL VIEW udtf(expression) tableAlias AS columnAlias
  • 看上去，tableAlias 是这个 VIEW 实际的名字，但无法引用，所以更多时候用 columnAlias 直接在正文 SELECT 中查询。
  • 多个 LATERAL VIEW 可以拼接，生成笛卡尔积。
  • 不知道使用相同的 tableAlias 会是什么情况。
  • UDTF = User-Defined Table-generating Function。
  • 在这个场景中，UDTF = explode()。

例子

baseTable =

A B
[a, b, c] [1, 2, 3]

那么

  1. SELECT LVA,
  2.        B
  3.   FROM baseTable LATERAL VIEW explode(split(A, ',')) tmp AS LVA

结果是：

LVA B
a [1, 2, 3]
b [1, 2, 3]
c [1, 2, 3]

联合两个

  1. SELECT LVA,
  2.        LVB
  3.   FROM baseTable LATERAL VIEW explode(split(A, ',')) tmp1 AS LVA LATERAL VIEW explode(split(B, ',')) tmp2 AS LVB

结果是：

LVA LVB
a 1
a 2
a 3
b 1
b 2
b 3
c 1
c 2
c 3

技巧

1 对位展开

如果想把上文的 baseTable 拆成这个样子：

LVA LVB
a 1
b 2
c 3

可以使用 posexplode() 函数，该函数会提取索引值，另两张生成标（view）的索引值相等，即可对位取出想要的值，实际上还可以自定义 index 关系：

  1. LATERAL VIEW posexplode(array) tableAlias AS columnId, columnAlias

这样搞：

  1. SELECT LVA,
  2.        LVB
  3.   FROM baseTable LATERAL VIEW posexplode(split(A, ',')) tmp1 AS LVAid, LVA LATERAL VIEW posexplode(split(B, ',')) tmp2 AS LVBid, LVB
  4.  WHERE LVAid = LVBid

从而实现想要的效果，因为此时 tmp1 结构是：

LVAid LVA
0 a
1 b
2 c

同理，tmp2 结构是：

LVBid LVB
0 1
1 2
2 3

2 空值保留

explode(array) 当 array 为空时不会生成空表格，而是不生成表格，这可能导致合并字段时丢失其他列的内容。
可以使用 OUTER 关键字解决这个问题，当 array 为空时，生成 null 值补位。

  1. LATERAL VIEW OUTER udtf(expression) tableAlias AS columnAlias

参考

  1. 列转行分函数用法：https://blog.csdn.net/yahahassr/article/details/97911676
  2. 解决多个 VIEW 笛卡尔积问题：https://blog.csdn.net/weixin_38073361/article/details/109386873
  3. DOC about LATERAL VIEW：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
  4. Built-in Table-Generating Functions (UDTF)：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
  5. posexplode 一个例子：https://blog.csdn.net/dzysunshine/article/details/101110467