Hive 行转列，Hive 列转行， UDF，UDTF

原创

已于 2022-04-09 14:37:28 修改 · 441 阅读

标签

#hive

收录于

于 2022-04-07 21:56:08 首次发布

Hive 行转列

hive> create table testArray2(
    >     name string,
    >     weight array<string>
    > )row format delimited 
    > fields terminated by '\t'
    > COLLECTION ITEMS terminated by ',';
OK
Time taken: 0.172 seconds
hive> load data local inpath '/usr/local/soft/data/testArray2.txt' into table testArray2;
Loading data to table test6.testarray2
Table test6.testarray2 stats: [numFiles=1, totalSize=50]
OK
Time taken: 0.288 seconds
hive> select * from testArray2;
OK
志凯	["\"150\"","\"170\"","\"180\""]
上单	["\"150\"","\"180\"","\"190\""]
Time taken: 0.14 seconds, Fetched: 2 row(s)
hive> select explode(weight) from testArray2;
OK
"150"
"170"
"180"
"150"
"180"
"190"
Time taken: 0.062 seconds, Fetched: 6 row(s)
hive> select name
    > ,col1
    > from testArray2
    > lateral view explode(weight) v1 as col1;
OK
志凯	"150"
志凯	"170"
志凯	"180"
上单	"150"
上单	"180"
上单	"190"
Time taken: 0.043 seconds, Fetched: 6 row(s)
hive> select posexplode(weight) from testArray2;
OK
0	"150"
1	"170"
2	"180"
0	"150"
1	"180"
2	"190"
Time taken: 0.032 seconds, Fetched: 6 row(s)
hive> select name
    > ,col1
    > ,val
    > from testArray2
    > lateral view posexplode(weight) v1 as col1,val;
OK
志凯	0	"150"
志凯	1	"170"
志凯	2	"180"
上单	0	"150"
上单	1	"180"
上单	2	"190"
Time taken: 0.034 seconds, Fetched: 6 row(s)
hive> select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;
OK
key1
key2
key3
Time taken: 0.453 seconds, Fetched: 3 row(s)
hive> select n