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

在無極等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需求定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站設(shè)計(jì),網(wǎng)絡(luò)營銷推廣,外貿(mào)網(wǎng)站建設(shè),無極網(wǎng)站建設(shè)費(fèi)用合理。

?? ?explode 是一個(gè) UDTF（表生成函數(shù)），將單個(gè)輸入行轉(zhuǎn)換為多個(gè)輸出行。一般和 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 為每個(gè)輸入行生成零個(gè)或多個(gè)輸出行。lateralview 首先將 UDTF 應(yīng)用于基礎(chǔ)表的每一行，然后將結(jié)果輸出行與輸入行連接起來形成具有所提供表別名的虛擬表。

實(shí)例

? ? 基礎(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)
??????>?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

多個(gè)lateralview

? ? from 子句可以具有多個(gè) 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)用。

實(shí)例

hive?(test)>?!cat?basetable;
1,2????a,b,c
3,4????d,e,f
hive?(test)>?create?table?basetable(
???????????>?col1?array,
???????????>?col2?array)
???????????>?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)