hiveql - how to iterate through a comma-delimited column in hive? -

i have hive external table data_table (id bigint, dataset string) dataset comma-delimited set of integer or string values like

id  dataset  1   4,3,0,6  2   1,9,7  3   0,8,2,7 

my question: how can iterate through comma-delimited dataset values using hive?

let's when id=2 (and dataset=1,9,7), want have looping structure allows me create like:

when id=2,  select val source x=1;  select val source x=9;  select val source x=7; 

any suggestions?

in ideal scenario, should not have usecase need iterate on dataset. if need access particular element, can using square bracket operator index myarray[index].

in other cases, explode() function hive(as mentioned above) should case. syntax:

select id, explodetable.dataset data_table lateral view explode(dataset) testtable explodetable 

make sure split data_set array out of it. can use split function that. syntax:

split(dataset,'\\,') 

your final query should this:

select id, explodetable.dataset data_table lateral view explode(split(dataset,'\\,')) testtable explodetable 

explode function takes array or map it's argument.