SiD SiD - 2 months ago 6
MySQL Question

combining multiple columns in to single columns in hive

I have one table shown in below.

Id v1 v2 v3
A 01 03 23
B 11 21 05
C 02 10 24
D 22 14 23


here first column has id and it's related with three columns. SO i have to combine that three column into one single column with related id.

For example.

id value
A 01
A 03
A 23
B 11
B 21
B 05
. ..
. ..


So i have to done this in to hive so please let me know.

Answer
insert into table result_table select Id, v1 as value from orig_table;
insert into table result_table select Id, v2 as value from orig_table;
insert into table result_table select Id, v3 as value from orig_table;
select Id,value from result_table;

or

select Id, value 
       from orig_table LATERAL VIEW explode(array(v1,v2,v3)) orig_table_alias AS value;
Comments