Support Questions

Find answers, ask questions, and share your expertise

lateral view explode in impala?

avatar
New Contributor

I have data as source as below. How to transform the multi rows?

Any function like lateral view explode in impala?

impala.JPG

4 REPLIES 4

avatar
We don't support this in Impala right now. We'd generally recommend using Hive to prepare such data for querying.

avatar
New Contributor

Is it supported now?

avatar
Explorer

Do we have any alternative for this to do in impala or introduced any function to support in any newer version of impala?

Thanks

avatar
New Contributor

There is a workaround to solve this. Is not a definitive solutions but it can help:

The final result would be like this: 

Honorio_0-1705440992384.png

  • Firstly, I created a table like your example (I used ";" as separator):

insert overwrite t_1
select 'Asia' as cont,'Japan;China;Singapore;' Country_list union
select 'Europe' as cont,'UK;Spain;Italy;German;Norway;' Country_list

  • After, I created a external table. It must be stored as textfile:

CREATE EXTERNAL TABLE IF NOT EXISTS t_transpose (
field_transpose string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ";"
STORED AS TEXTFILE;

  • Then insert on this table like this:

insert overwrite t_transpose
select REGEXP_REPLACE(Country_list, ';', concat("|", cont, '\n' ) ) as transpose
from t_1;

  • After you can select like in my example before: 

 select split_part(field_transpose,"|",1), split_part(field_transpose,"|",2) from t_transpose;

Ps: The final result could have some blank lines, just filter/ignore it. I also put one more ";" in the line comparing with the example informed.