0.背景
在使用Hive的时候有时候会遇到需要将一行“拆分”成多行的操作,如下。
原始数据格式,表名为student_table
class student_array
1 [Tom,Jone]
2 [Lily,lucy]
目标数据格式
class student_name
1 Tom
1 Jone
2 Lily
2 Lucy
1.做法
先上做法,如下:
SELECT class, student_name
FROM student_table LATERAL VIEW explode(student_array) tableAlias as student_name
2.解释
explode:首先我们知道UDF是user-defined function,例如concat(),都是输入一行输出一行。UDTF是 user-defined table-generating functions,相比较来说它们是输入一行输出多行,典型的就是explode()方法。https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inTable-GeneratingFunctions(UDTF)
LATERAL VIEW:侧视表,用来连接UDTF(比如explode)产生的结果。如上所述,UTDF对每一行可以产生0行或者更多行,侧视表可以把这个结果join到原始表上,然后起一个别名。1中tableAlias即表别名。
基本语法
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
3.进阶
MULtiple LATERAL
VIEW。多重侧视表,生成表的顺序和sql中写的顺序有关。在下例中,交换两行表中数据顺序是不一样的。
数据如下:
col1 col2
[1, 2] [a", "b", "c"]
[3, 4] [d", "e", "f"]
SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;
OUTER参数
UDTF不同于UDF肯定有一行输出(哪怕是NULL),在有些情况下可能输出0行(典型的情况就是explode的列有空值),这样的话source row也不会再表中出现,有时候这并不是我们希望的,可以在sql中添加OUTER参数,相当于我们将原始表和explode的结果做OUTER JOIN,避免source row完全丢失的情况。
SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10
4.参考
1.https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView