if you are doing group by, you could have potential duplicates on your
concat_ws....take a look at using collect_set or collect_list. if you do
select col_a,
collect_set(concat_ws(', ',col_b,col_c))
from t
you will have an array of unique collection pairs...collect_list will give you
all pairs.
hope that helps
From: Deepak Khandelwal [mailto:[email protected]]
Sent: Tuesday, April 26, 2016 11:35 AM
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
======================================================================
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL
and may contain information that is privileged and exempt from disclosure under
applicable law. If you are neither the intended recipient nor responsible for
delivering the message to the intended recipient, please note that any
dissemination, distribution, copying or the taking of any action in reliance
upon the message is strictly prohibited. If you have received this
communication in error, please notify the sender immediately. Thank you.
