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

[#1]

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

[#2 · Accepted solution]

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

[#3]

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