Another example (with first name and last name), same principal
Dudu
Given the following table:
id, first_name,last_name
______________________
1,Dudu,Markovitz
2,Andrew,Sears
select id,key,value from my_table lateral view explode
(map('fname',first_name,'lname',last_name)) t;
The result will look like:
Id,key,value
______________________
1,fname,Dudu
1,lname,Markovitz
2,fname, Andrew
2,lname, Sears
From: Deepak Khandelwal [mailto:dkhandelwal....@gmail.com]
Sent: Saturday, April 23, 2016 9:04 AM
To: user@hive.apache.org
Subject: Hive query to split one row into many rows such that Row 1 will have
col 1 Name, col 1 Value and Row 2 will have col 2 Name and col 2 value
Hi All,
I am new to Hive and I am trying to create a query for below aituation. Would
appreciate if someone could guide on same. Thans a lot in advance.
I have two TABLES shown below
TABLE1 (USER_dETAILS)
**USER_ID** | **USER_NAME** | **USER_ADDRESS**
------------+------------------+----------------
1 USER1 ADDRESS111
2 USER2 ADDRESS222
TABLE2 (USER_PARAMETERS)
**USER_ID** | **PARAM_NAME** | **PARAM_VALUE**
------------+------------------+------------------
1 USER_NAME USER1
1 USER_ADDRESS ADDRESS111
2 USER_NAME USER2
2 USER_ADDRESS ADDRESS222
I need to insert data in table2(USER_PARAMETERS) FROM table1(USER_DETAILS) in
the format shown above. I can do this using UNION ALL but I want to avoid it as
there are like 10 such columns that i need to split like above.
Can someone suggest a efficient hive query so that i can achieve the results
shown in table 2 from data in table 1 (Hive query to split one row of data into
multiple rows like such that Row 1 will have column1 Name, column1 Value and
Row 2 will have column 2 Name and column 2 value...).
Thanks a lot
Deepak
