Reputation: 1973
I have a table in Hive as below -
create table somedf
(sellers string ,
orders int
)
insert into somedf values
('1--**--2--**--3',50),
('1--**--2', 10)
The table has a column called sellers and it is delimited by the characters described in the insert statement. I would like to split the sellers into multiple rows so that it looks like below -
exploded_sellers orders
1 50
2 50
3 50
1 10
2 10
I am trying to use lateral view explode()
function in Hive but unable to get the results. I am using the below query -
select exploded_sellers, orders
from somedf
lateral view outer explode(split(sellers,'\\--*.*\\*.*--')) t1 as exploded_sellers
which gives me below results as output -
exploded_sellers orders
1 50
3 50
1 10
2 10
This result does not split Row 1('1--**--2--**--3',50)
from the table as desired and ends up in producing only 2 rows instead of 3.
Is there any other function that is needed for this task?
Does lateral view explode()
only work on arrays ?
Upvotes: 2
Views: 9177
Reputation: 12714
This would work too. It expects two occurrences of * in the middle.
select exploded_sellers, orders
from somedf
lateral view outer explode(split(sellers,'--\\*{2}--')) t1 as exploded_sellers;
Upvotes: 1
Reputation: 49270
The pattern passed into split
is incorrect. *
character needs to be escaped. No need to escape -
.
Use
select exploded_sellers, orders
from somedf
lateral view outer explode(split(sellers,'--\\*\\*--')) t1 as exploded_sellers
Upvotes: 2