question Re: Parsing multiple pipe delimited columns into rows with ID with Hive/Impala in Support Questions

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

Hafiz — Wed, 29 Jun 2022 09:19:03 GMT

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:

ID	key	itemno	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

Expected output:

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

Really appreciate the help!

Thanks

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

nramanaiah — Mon, 25 Jul 2022 07:21:41 GMT

@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.

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

VidyaSargur — Tue, 02 Aug 2022 05:41:42 GMT

@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.