Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Parsing multiple pipe delimited columns into rows with ID with Hive/Impala

avatar
New Contributor

I have a table with an ID, key and 2 columns with each contains pipe separated values. How can i parse column into multiple rows with the respective values?

 

Table that I have:

 

IDkeyitemnovalue
1A1|2|310|20|30
1B1|2|340|50|60
1C1|2|370|80|90

 

Expected output:

 

IDkeyitemnovalue
1A110
1A220
1A330
1B140
1B250
1B360
1C170
1C280
1C390

 

Really appreciate the help!

 

Thanks

1 ACCEPTED SOLUTION

avatar
Expert Contributor

@Hafiz 

I am able to achieve it with posexplode, please find my solution below.

 

create table comm_article(id int, key string, itemno string, value string);
insert into comm_article values(1, 'A', '1|2|3', '10|20|30'), (1, 'B', '1|2|3', '40|50|60'), (1, 'C', '1|2|3', '70|80|90');
select * from comm_article;
+------------------+-------------------+----------------------+---------------------+
| comm_article.id  | comm_article.key  | comm_article.itemno  | comm_article.value  |
+------------------+-------------------+----------------------+---------------------+
| 1                | A                 | 1|2|3                | 10|20|30            |
| 1                | B                 | 1|2|3                | 40|50|60            |
| 1                | C                 | 1|2|3                | 70|80|90            |
+------------------+-------------------+----------------------+---------------------+


with t as (select id, key, split(itemno, '[|]') as itemno_arr, split(value, '[|]') as value_arr from comm_article)
select id, key, itemno, value from t
lateral view posexplode(itemno_arr) myTable1 AS myCol1, itemno
lateral view posexplode(value_arr) myTable2 AS myCol2, value
where myCol1 = myCol2;

+-----+------+---------+--------+
| id  | key  | itemno  | value  |
+-----+------+---------+--------+
| 1   | A    | 1       | 10     |
| 1   | A    | 2       | 20     |
| 1   | A    | 3       | 30     |
| 1   | B    | 1       | 40     |
| 1   | B    | 2       | 50     |
| 1   | B    | 3       | 60     |
| 1   | C    | 1       | 70     |
| 1   | C    | 2       | 80     |
| 1   | C    | 3       | 90     |
+-----+------+---------+--------+

 

If this helps to address your ask, please accept the solution.

View solution in original post

2 REPLIES 2

avatar
Expert Contributor

@Hafiz 

I am able to achieve it with posexplode, please find my solution below.

 

create table comm_article(id int, key string, itemno string, value string);
insert into comm_article values(1, 'A', '1|2|3', '10|20|30'), (1, 'B', '1|2|3', '40|50|60'), (1, 'C', '1|2|3', '70|80|90');
select * from comm_article;
+------------------+-------------------+----------------------+---------------------+
| comm_article.id  | comm_article.key  | comm_article.itemno  | comm_article.value  |
+------------------+-------------------+----------------------+---------------------+
| 1                | A                 | 1|2|3                | 10|20|30            |
| 1                | B                 | 1|2|3                | 40|50|60            |
| 1                | C                 | 1|2|3                | 70|80|90            |
+------------------+-------------------+----------------------+---------------------+


with t as (select id, key, split(itemno, '[|]') as itemno_arr, split(value, '[|]') as value_arr from comm_article)
select id, key, itemno, value from t
lateral view posexplode(itemno_arr) myTable1 AS myCol1, itemno
lateral view posexplode(value_arr) myTable2 AS myCol2, value
where myCol1 = myCol2;

+-----+------+---------+--------+
| id  | key  | itemno  | value  |
+-----+------+---------+--------+
| 1   | A    | 1       | 10     |
| 1   | A    | 2       | 20     |
| 1   | A    | 3       | 30     |
| 1   | B    | 1       | 40     |
| 1   | B    | 2       | 50     |
| 1   | B    | 3       | 60     |
| 1   | C    | 1       | 70     |
| 1   | C    | 2       | 80     |
| 1   | C    | 3       | 90     |
+-----+------+---------+--------+

 

If this helps to address your ask, please accept the solution.

avatar
Community Manager

@Hafiz Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. 



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: