RE: 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

[Markovitz, Dudu]

Hi Mich, it seems the request was for unpivot.

Dudu

From: Mich Talebzadeh [mailto:mich.talebza...@gmail.com]
Sent: Saturday, April 23, 2016 10:04 AM
To: user <user@hive.apache.org>
Subject: Re: 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

try this

-- populate table user_parameters with user_id values (unique)from user_details
INSERT user_parameters
SELECT user_id, null, null FROM user_details

-- Update remaining columnsd
UPDATE user_parameters
SET
        param_name = t1.user_name
        param_value = t1.user_address
FROM
        user_parameters t2 JOIN user_details t1
ON t2.user_id = t1.user_id;




Dr Mich Talebzadeh



LinkedIn  
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw



http://talebzadehmich.wordpress.com<http://talebzadehmich.wordpress.com/>



On 23 April 2016 at 07:04, Deepak Khandelwal 
<dkhandelwal....@gmail.com<mailto:dkhandelwal....@gmail.com>> wrote:
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