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

Editor to share with you how to achieve row conversion hive, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

one. problem

How does hive put

A b 1,2,3

C d 4,5,6

Change to:

A b 1

A b 2

A b 3

C d 4

C d 5

C d 6

The answers are as follows:

two. Raw data:

Test.txt

A b 1,2,3

C d 4,5,6

three. Solution method

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

Traverse 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]

Traverse 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

four. 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 the array length

Select size (split (col3,',')) list from test_jzl_20140701_test

three

three

The above is all the content of the article "how to realize row forwarding in hive". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow 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.