hadoop - 如何在配置单元中为复杂数组 > 类型编写插入注释？

Question

表结构:

CREATE TABLE IF NOT EXISTS test.test_complex_alter(
    `id` STRING,
    `items` array<struct<
        quantity: INT,
        articleData:struct<subsysNumber:string>,
        pickedReason:string>> 
) STORED AS ORC;

如何使用 INSERT 查询将数据INSERT 到这个复杂的表中？

Answer 1

对 OP 的回答:

1. 创建一个只有 1 行的虚拟表(模式无关紧要)。我们称它为dummy。
2. 运行以下查询以将 1 行插入到您的 test.test_complex_alter 表中:

INSERT INTO TABLE test.test_complex_alter SELECT "id1", array(named_struct('quantity',20, 'articleData', named_struct('subsysNumber', 'subsys1'), 'pickedReason', 'reason1') ,named_struct('quantity',30, 'articleData', named_struct('subsysNumber', 'subsys2'), 'pickedReason', 'reason2')) 来自 dummy;

数组结构(第二列):

array(named_struct(n1,v1,..,nn,vn),...,named_struct(n1,v1,..,nn,vn))

结果:

hive> select * from test100.test_complex_alter;
OK
test_complex_alter.id   test_complex_alter.items
id1 [{"quantity":20,"articleData":{"subsysNumber":"subsys1"},"pickedReason":"reason1"},{"quantity":30,"articleData":{"subsysNumber":"subsys2"},"pickedReason":"reason2"}]

引用:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF -> “复杂类型构造函数”