Hive lateral view 與 explode

explode（官網(wǎng)鏈接）

?? ?explode 是一個 UDTF（表生成函數(shù)），將單個輸入行轉(zhuǎn)換為多個輸出行。一般和 lateral view 結(jié)合使用，主要有兩種用法：

示例：

explode(array)

hive?(default)>?select?explode(array('A','B','C'));
OK
col
A
B
C
Time?taken:?0.402?seconds,?Fetched:?3?row(s)
hive?(default)>?select?explode(array('A','B','C'))?as?col1;
OK
col1
A
B
C
Time?taken:?0.145?seconds,?Fetched:?3?row(s)
hive?(default)>?select?tf.*?from?(select?0)?t?lateral?view?explode(array('A','B','C'))?tf;
OK
tf.col
A
B
C
Time?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;
OK
tf.col1
A
B
C

explode(map)

hive?(default)>?select?explode(map('A',10,'B',20,'C',30));
OK
key????value
A????10
B????20
C????30
Time?taken:?0.153?seconds,?Fetched:?3?row(s)
hive?(default)>?select?explode(map('A',10,'B',20,'C',30))?as?(my_key,my_value);
OK
my_key????my_value
A????10
B????20
C????30
Time?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;
OK
tf.key????tf.value
A????10
B????20
C????30
Time?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;
OK
tf.my_key????tf.my_value
A????10
B????20
C????30
Time?taken:?0.109?seconds,?Fetched:?3?row(s)

LateralView(官網(wǎng)鏈接)

語法

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

fromClause: FROM baseTable (lateralView)*

描述

? ? lateralview 與用戶自定義表生成函數(shù)（UDTF）（例如 explode）結(jié)合使用，UDTF 為每個輸入行生成零個或多個輸出行。lateralview 首先將 UDTF 應(yīng)用于基礎(chǔ)表的每一行，然后將結(jié)果輸出行與輸入行連接起來形成具有所提供表別名的虛擬表。

實例

? ? 基礎(chǔ)表?pageads 具有兩列：pageid（頁面名稱）和 add_list（頁面上顯示的廣告數(shù)組）。

hive?(test)>?!cat?pageads
???????????>?;
front_page????1,2,3
contact_page????3,4,5
hive?(test)>?create?table?pageads(
??????>?pageid?string,
??????>?add_list?array<int>)
??????>?ROW?FORMAT?delimited
??????>?fields?terminated?by?'\t'
??????>?collection?items?terminated?by?','
??????>?lines?terminated?by?'\n'
?????>?;
OK
Time?taken:?0.099?seconds
hive?(test)>?load?data?local?inpath?'/home/hadoop/pageads'?into?table?pageads;
Loading?data?to?table?test.pageads
OK
Time?taken:?0.331?seconds
hive?(test)>?select?*?from?pageads;
OK
pageads.pageid????pageads.add_list
front_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;
OK
pageid????addid
front_page????1
front_page????2
front_page????3
contact_page????3
contact_page????4
contact_page????5
Time?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;
.......
OK
addid????_c1
1????1
2????1
3????2
4????1
5????1

多個lateralview

? ? from 子句可以具有多個 lateralview 子句。后續(xù)的 lateralview 子句可以引用 lateralview 左側(cè)表中的任何列。例如以下查詢：

SELECT * FROM exampleTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

? ? 注意，lateralview 子句按其出現(xiàn)的順序應(yīng)用。

實例

hive?(test)>?!cat?basetable;
1,2????a,b,c
3,4????d,e,f
hive?(test)>?create?table?basetable(
???????????>?col1?array<int>,
???????????>?col2?array<string>)
???????????>?ROW?FORMAT?delimited
???????????>?fields?terminated?by?'\t'
???????????>?collection?items?terminated?by?','
???????????>?lines?terminated?by?'\n'
???????????>?;
OK
Time?taken:?0.113?seconds
hive?(test)>?load?data?local?inpath?'/home/hadoop/basetable'?into?table?basetable;
Loading?data?to?table?test.basetable
OK
Time?taken:?0.329?seconds
hive?(test)>?select?*?from?basetable;
OK
basetable.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;
OK
mycol1????col2
1????["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;
OK
mycol1????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
Time?taken:?0.093?seconds,?Fetched:?12?row(s)
Outer?Lateral?Views
hive?(test)>?SELECT?*?FROM?basetable?LATERAL?VIEW?explode(array())?C?AS?a?limit?10;
OK
basetable.col1????basetable.col2????c.a
Time?taken:?0.063?seconds
hive?(test)>?SELECT?*?FROM?basetable?LATERAL?VIEW?OUTER?explode(array())?C?AS?a?limit?10;
OK
basetable.col1????basetable.col2????c.a
[1,2]????["a","b","c"]????NULL
[3,4]????["d","e","f"]????NULL
Time?taken:?0.092?seconds,?Fetched:?2?row(s)