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

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