You are welcome ☺
I’ve tried to guess the requested result for your last question.
It can be very helpful if you can create a small example containing your
original data and the requested result.
Dudu
Given the following table, ‘t’:
i
c1
c2
c3
1
1
12
15
2
1
13
11
3
3
11
13
4
1
12
13
5
3
12
15
6
1
14
13
7
1
11
13
8
2
15
11
9
1
14
11
10
3
11
13
collect_list contains all values.
collect_set remove duplicates.
Option 1
Collect c2 and c3 together
select c1,collect_list (value),collect_set (value) from t lateral view explode
(map('c2',c2,'c3',c3)) t group by c1;
1 [12,15,13,11,12,13,14,13,11,13,14,11] [12,15,13,11,14]
2 [15,11] [15,11]
3 [11,13,12,15,11,13] [11,13,12,15]
Option 2
Collect c2 and c3 separately
select c1,collect_list (c2),collect_set (c2),collect_list (c3),collect_set (c3)
from t group by c1;
1 [12,13,12,14,11,14] [12,13,14,11] [15,11,13,13,13,11]
[15,11,13]
2 [15] [15] [11] [11]
3 [11,12,11] [11,12] [13,15,13] [13,15]
From: Deepak Khandelwal [mailto:[email protected]]
Sent: Tuesday, April 26, 2016 8:35 PM
To: [email protected]
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
Thanks a lot Dudu.
Could you also tell how can I use concat with group by clause in have. I have
n rows with col1, col2, col3 and i want a result grouped by col1 and concat all
values of col2 and col3.
Id,key,value, value2
______________________
1,fname,Dudu, m1
1,lname,Markowitz, m2
2,fname, Andrew, m3
2,lname, Sears,m4
And I need result like below
Id, appended (group by key)
______________________
Fname , Dudu | m1 | Andrew | m3
Lname, Markowitz| m2 | Sears | m4
Thanks a lot for your help.
On Saturday, April 23, 2016, Markovitz, Dudu
<[email protected]<mailto:[email protected]>> wrote:
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:[email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');>]
Sent: Saturday, April 23, 2016 9:04 AM
To: [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');>
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
