Hive lateral view 与 explode

explode（官网链接）

explode 是一个 UDTF（表生成函数），将单个输入行转换为多个输出行。一般和 lateral view 结合使用，主要有两种用法：

示例：

explode(array)

hive (default)> select explode(array('A','B','C'));OKcolABCTime taken: 0.402 seconds, Fetched: 3 row(s)hive (default)> select explode(array('A','B','C')) as col1;OKcol1ABCTime taken: 0.145 seconds, Fetched: 3 row(s)hive (default)> select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;OKtf.colABCTime taken: 0.191 seconds, Fetched: 3 row(s)hive (default)> select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col1;OKtf.col1ABC

explode(map)

hive (default)> select explode(map('A',10,'B',20,'C',30));OKkey    valueA    10B    20C    30Time taken: 0.153 seconds, Fetched: 3 row(s)hive (default)> select explode(map('A',10,'B',20,'C',30)) as (my_key,my_value);OKmy_key    my_valueA    10B    20C    30Time taken: 0.137 seconds, Fetched: 3 row(s)hive (default)> select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;OKtf.key    tf.valueA    10B    20C    30Time taken: 0.128 seconds, Fetched: 3 row(s)hive (default)> select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as my_key,my_value;OKtf.my_key    tf.my_valueA    10B    20C    30Time taken: 0.109 seconds, Fetched: 3 row(s)

LateralView(官网链接)

语法

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

fromClause: FROM baseTable (lateralView)*

描述

lateralview 与用户自定义表生成函数（UDTF）（例如 explode）结合使用，UDTF 为每个输入行生成零个或多个输出行。lateralview 首先将 UDTF 应用于基础表的每一行，然后将结果输出行与输入行连接起来形成具有所提供表别名的虚拟表。

实例

基础表 pageads 具有两列：pageid（页面名称）和 add_list（页面上显示的广告数组）。

hive (test)> !cat pageads           > ;front_page    1,2,3contact_page    3,4,5hive (test)> create table pageads(      > pageid string,      > add_list array)      > ROW FORMAT delimited      > fields terminated by '\t'      > collection items terminated by ','      > lines terminated by '\n'     > ;OKTime taken: 0.099 secondshive (test)> load data local inpath '/home/hadoop/pageads' into table pageads;Loading data to table test.pageadsOKTime taken: 0.331 secondshive (test)> select * from pageads;OKpageads.pageid    pageads.add_listfront_page    [1,2,3]contact_page    [3,4,5]Time taken: 0.106 seconds, Fetched: 2 row(s)hive (test)> select pageid,addid from pageads lateral view explode(add_list) adTable as addid;OKpageid    addidfront_page    1front_page    2front_page    3contact_page    3contact_page    4contact_page    5Time taken: 0.105 seconds, Fetched: 6 row(s)hive (test)> select addid,count(1) from pageads lateral view explode(add_list) adTable as addid group by addid;.......OKaddid    _c11    12    13    24    15    1

多个lateralview

from 子句可以具有多个 lateralview 子句。后续的 lateralview 子句可以引用 lateralview 左侧表中的任何列。例如以下查询：

SELECT * FROM exampleTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

注意，lateralview 子句按其出现的顺序应用。

实例

hive (test)> !cat basetable;1,2    a,b,c3,4    d,e,fhive (test)> create table basetable(           > col1 array,           > col2 array)           > ROW FORMAT delimited           > fields terminated by '\t'           > collection items terminated by ','           > lines terminated by '\n'           > ;OKTime taken: 0.113 secondshive (test)> load data local inpath '/home/hadoop/basetable' into table basetable;Loading data to table test.basetableOKTime taken: 0.329 secondshive (test)> select * from basetable;OKbasetable.col1    basetable.col2[1,2]    ["a","b","c"][3,4]    ["d","e","f"]Time taken: 0.104 seconds, Fetched: 2 row(s)hive (test)> SELECT myCol1, col2 FROM basetable           > LATERAL VIEW explode(col1) myTable1 AS myCol1;OKmycol1    col21    ["a","b","c"]2    ["a","b","c"]3    ["d","e","f"]4    ["d","e","f"]Time taken: 0.089 seconds, Fetched: 4 row(s)hive (test)> SELECT myCol1, myCol2 FROM baseTable           > LATERAL VIEW explode(col1) myTable1 AS myCol1           > LATERAL VIEW explode(col2) myTable2 AS myCol2;OKmycol1    mycol21    a1    b1    c2    a2    b2    c3    d3    e3    f4    d4    e4    fTime taken: 0.093 seconds, Fetched: 12 row(s)Outer Lateral Viewshive (test)> SELECT * FROM basetable LATERAL VIEW explode(array()) C AS a limit 10;OKbasetable.col1    basetable.col2    c.aTime taken: 0.063 secondshive (test)> SELECT * FROM basetable LATERAL VIEW OUTER explode(array()) C AS a limit 10;OKbasetable.col1    basetable.col2    c.a[1,2]    ["a","b","c"]    NULL[3,4]    ["d","e","f"]    NULLTime taken: 0.092 seconds, Fetched: 2 row(s)