Search code examples
sqlsplithivehiveqlexplode

Insert values into multiple rows separated by commas

I have a table with one column id. Now I want to write a hive query that takes an input of comma separated id values, split them and insert them row by row. For eg:

Input - abc,def,ghi

Output -

-----id-----
abc
def
ghi
Solution

  • Use lateral view [outer] + explode and split:

    insert into table t2 

select 
       s.id
  from table1 t1 
       lateral view explode (split(t1.id,',')) s as id

    Demo:

    select 
       s.id
  from (select 'abc,def,ghi' as id) t1 
       lateral view explode (split(t1.id,',')) s as id

    Result:

    id

abc
def
ghi

    split(t1.id,',') produces an array. explode - is a table generation function (UDTF), it converts array to rows. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows.