json - 如何从另一个平面/简单的配置单元表中使用 map[structs] 加载配置单元表

Question

我在 hive 中有 2 个表，其中包含 Order 和 Order_Detail(具有 1:n 关系并在 order_id 上连接)，我试图利用 hive 复杂数据类型 - map[struct] 加载到单个表。

假设 ORDER 有以下数据，

Order_id total_amount 客户

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 2 5.00 B

456 1 6.00 A

456 2 3.00 B

456 3 3.00℃

我想创建单个表 ORDERS，其中包含所有订单列和 order_detail 列作为结构映射。这有助于我将相关数据和查询组合在一起，从而避免频繁连接。我尝试使用带有相应 serde 的 txt/json 文件输入来加载具有复杂数据类型的表，并且效果很好。但在这种情况下，我想将现有的 2 个 ORCFile 格式的配置单元表中的数据加载到新表中。已尝试使用 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 个表中选择 INSERT INTO 表来实现此目的的方法。提前致谢。

Answer 1

我找到了一种使用 map 、 named_struct 函数和 David Worms 在 to_map UDF blog 上发布的自定义 UDF to_map 来执行此操作的方法。这是示例，

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);

从订单中选择*；

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

希望对大家有帮助。