- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
lateral view explode in impala?
- Labels:
-
Apache Impala
Created on ‎09-03-2018 02:04 AM - edited ‎09-16-2022 06:39 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have data as source as below. How to transform the multi rows?
Any function like lateral view explode in impala?
Created ‎09-11-2018 11:16 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Created ‎11-09-2022 02:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is it supported now?
Created ‎01-05-2024 07:56 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Do we have any alternative for this to do in impala or introduced any function to support in any newer version of impala?
Thanks
Created ‎01-16-2024 01:49 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There is a workaround to solve this. Is not a definitive solutions but it can help:
The final result would be like this:
- 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.
