I have the below data in one of my oracle db tables -
ZONE_ID,ZONE_NAME,OPERATORS,GEOGRAPHIES,PRODUCT_ID
3285,'EUROPE',null,'3,6,14,21,32',2
3293,'USA AND MEXICO',null,'133,215',3
3061,'Europe','11,238,352,398',null,4
3221,'USA','1079',null,5
In operators and geographies columns, data is comma delimited. For each row, data is either there in operators column or geographies column.
I need to get this delimited data in separate rows as below :
ZONE_ID,ZONE_NAME,OPERATORS,GEOGRAPHIES,PRODUCT_ID
3285,'EUROPE',null,3,2
3285,'EUROPE',null,6,2
3285,'EUROPE',null,14,2
3285,'EUROPE',null,21,2
3285,'EUROPE',null,32,2
3293,'USA AND MEXICO',null,133,3
3293,'USA AND MEXICO',null,215,3
3061,'Europe',11,null,4
3061,'Europe',238,null,4
3061,'Europe',352,null,4
3061,'Europe',398,null,4
3221,'USA',1079,null,5
Is this transformation possible during sqoop import from oracle db? Else how can we import this data using sqoop and do this transformation in hive.
Please help.
Got it working using below query in hive:
select ZONE_ID
,ZONE_NAME
,col1
,col2
,product_id
from table
lateral view explode(OPERATORS) ptable1 as col1
lateral view explode(GEOGRAPHIES) ptable2 as col2
;