Shulou(Shulou.com)05/31 Report--

This article mainly introduces how hive realizes row and column conversion. The introduction in this article is very detailed and has certain reference value. Interested friends must read it!

1。problem

Hive how to

a b 1,2,3

c d 4,5,6

becomes:

a b 1

a b 2

a b 3

c d 4

c d 5

c d 6

The answer is as follows:

2。Raw data:

test.txt

a b 1,2,3

c d 4,5,6

3。solution

Option 1:

drop table test_jzl_20140701_test;

create table test_jzl_20140701_test

(

col1 string,

col2 string,

col3 string

)

row format delimited fields terminated by ' '

stored as textfile;

load data local inpath '/home/jiangzl/shell/test.txt' into table test_jzl_20140701_test;

select * from test_jzl_20140701_test

a b 1,2,3

c d 4,5,6

Go through each column in the array

select col1,col2,name

from test_jzl_20140701_test

lateral view explode(split(col3,',')) col3 as name;

a b 1

a b 2

a b 3

c d 4

c d 5

c d 6

Option 2:

drop table test_jzl_20140701_test1;

create table test_jzl_20140701_test1

(

col1 string,

col2 string,

col3 array

)

row format delimited

fields terminated by ' '

collection items terminated by ','//Defines the delimiter of the array

stored as textfile;

load data local inpath '/home/jiangzl/shell/test.txt' into table test_jzl_20140701_test1;

select * from test_jzl_20140701_test1;

a b [1,2,3]

c d [4,5,6]

Go through each column in the array

select col1,col2,name

from test_jzl_20140701_test1

lateral view explode(col3) col3 as name;

a b 1

a b 2

a b 3

c d 4

c d 5

c d 6

4。Additional knowledge points:

select * from test_jzl_20140701_test;

a b 1,2,3

c d 4,5,6

select t.list[0],t.list[1],t.list[2] from (

select (split(col3,',')) list from test_jzl_20140701_test)t;

OK

1 2 3

4 5 6

--View array length

select size(split(col3,',')) list from test_jzl_20140701_test;

3

3

The above is all the content of this article "how to realize row conversion in hive". Thank you for reading it! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.