如何使用来自另一个平面/简单蜂巢表的map [structs]加载配置单元表 [英] How to load hive table with map[structs] from another flat/simple hive table

问题描述

我在配置单元中有2个有Order和Order_Detail（具有1：n关系并加入order_id）的表，我尝试使用hive复杂数据类型 - 地图[结构]加载到单个表中。

说ORDER有以下数据，

Order_id total_amount customer

123 10.00 1

456 12.00 2

和ORDER_DETAILS有

Order_id Order_Item_id Item_amount Item_type

123 1 5.00 A

<123> 5.00 B

456 1 6.00 A

456 2 3.00 B

456 3 3.00 C

我想创建带有所有order列和order_detail列的单个表ORDERS作为结构图。
这帮助我将相关数据和查询结合在一起，从而避免频繁加入。
我尝试使用txt / json文件输入各自的serde来加载复杂数据类型的表，并且效果很好。
但是在这种情况下，我想从ORFile格式的现有2个hive表中加载数据到新表中。尝试了一些使用named_struct函数的基本插入，但是它会分别加载每一行，并且不会将相同的order_id组合到一行中。

期望的输出类似于

123 10.00 1 [1：{5.00，A}，2：{5.00，B}]

456 12.00 2 {1 ：{6.00，A}，2：{3.00，B}，3：{3.00，C}]

但是我得到了，

123 10.00 1 [1：{5.00，A}]

123 10.00 1 [2：{5.00，B}]

456 12.00 2 {1：{6.00，A}]

<456 12.00 2 {2：{3.00，B}]

<456 12.00 2 {3：{3.00，C}]

请帮助我了解实现方式这与只需插入从2表中选择。
在此先感谢。

解决方案

我找到了一种使用map，named_struct函数和自定义UDF to_map由David Worms发布在 to_map UDF博客上。
以下是样本，

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'STOR ED AS INPUTFORMAT'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'OUTPUTFORMAT'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';插入覆盖表ORDERSselecta.order_id，a.order_items， b.total_amount，b.customerfrom（选择order_id作为order_id，to_map（order_item_id，named_struct（item_amount，item_amount，item_type，item_type））作为来自ORDER_DETAILS组的order_items，由order_id）aJOIN命令b ON（a.order_id = b .order_id）;

select * from ORDERS;

123 {1：{Item_amount：5，Item_type：A}，2：{Item_amount：5，Item_type：B}} 10 1

456 {1：{Item_amount：6，Item_type：A}，2：{Item_amount：3，Item_type： B}，3：{Item_amount：3，Item_type：C}} 12 2

希望这有助于每个人。

$ b

I have 2 tables in hive having Order's and Order_Detail's (having 1:n relation and joined on order_id) which I am trying to load to a single table taking advantage of hive complex data type - map[struct].

Say ORDER has below data,

Order_id total_amount customer

123 10.00 1

456 12.00 2

and ORDER_DETAILS have

Order_id Order_Item_id Item_amount Item_type

123 1 5.00 A

123 2 5.00 B

456 1 6.00 A

456 2 3.00 B

456 3 3.00 C

I would like to create single table ORDERS with all order columns and order_detail columns as map of structs. This helps me to combine related data and query together thus avoiding frequent joins. I tried loading table with complex data types using txt/json files input with respective serde's and it works well. But in this scenario I want to load data from existing 2 hive tables of ORCFile format into the new table. Have tried some basic insert using named_struct function but it loads each row separately and does not combine same order_id's into a single row.

Expected output something like ,

123 10.00 1 [1:{5.00,A},2:{5.00,B}]

456 12.00 2 {1:{6.00,A}, 2:{3.00,B},3:{3.00,C}]

but i get ,

123 10.00 1 [1:{5.00,A}]

123 10.00 1 [2:{5.00,B}]

456 12.00 2 {1:{6.00,A}]

456 12.00 2 {2:{3.00,B}]

456 12.00 2 {3:{3.00,C}]

Kindly help me understand the way of achieving this with just INSERT INTO table select from 2 tables. Thanks in advance.

解决方案

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 DELIMITED
FIELDS 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 DELIMITED
FIELDS 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 ORDERS
select
a.order_id,
  a.order_items,
  b.total_amount,
  b.customer
from
  (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) a
JOIN ORDER b ON(a.order_id = b.order_id);

select * from ORDERS;

123 {1:{"Item_amount":5,"Item_type":"A"},2:{"Item_amount":5,"Item_type":"B"}} 10 1

456 {1:{"Item_amount":6,"Item_type":"A"},2:{"Item_amount":3,"Item_type":"B"},3:{"Item_amount":3,"Item_type":"C"}} 12 2

Hope this helps everyone.

这篇关于如何使用来自另一个平面/简单蜂巢表的map [structs]加载配置单元表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！