How to load hive table with map[structs] from another flat/simple hive table How to load hive table with map[structs] from another flat/simple hive table hadoop hadoop

How to load hive table with map[structs] from another flat/simple hive table


json hadoop map struct hive

I found a way to do this using map , named_struct functions and a custom UDF to_map posted by David Worms on to_map UDF blog.Here is the sample,

CREATE TABLE ORDER(  order_id bigint,  total_amount bigint,  customer bigint)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';CREATE TABLE ORDER_DETAILS(  order_id bigint,  Order_Item_id bigint,  Item_amount bigint,  Item_type string)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS INPUTFORMAT  'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';CREATE TABLE ORDERS(  order_id bigint,  Order_Items map < bigint, struct < Item_amount: bigint, Item_type: string >> ,  total_amount bigint,  customer bigint)ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';Insert overwrite table ORDERSselecta.order_id,  a.order_items,  b.total_amount,  b.customerfrom  (select order_id as order_id,    to_map(order_item_id, named_struct("item_amount", item_amount, "item_type", item_type)) as order_items from ORDER_DETAILS group by order_id) aJOIN ORDER b ON(a.order_id = b.order_id);
Expand snippet