CODEWITHSUNDEEP
hadoophivehdfs
Rumesh Krishnan
Rumesh Krishnan

Reputation: 443

How to write insert comment for complex array<struct<...>> type in hive?

Table Structure:

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;

How to INSERT data into this complex table using INSERT Query?

Upvotes: 0

Views: 787

Answers (1)

sujit
sujit

Reputation: 2328

Answer to the OP:

  1. Create a dummy table with 1 row ( schema doesn't matter). Let's call it dummy.
  2. Run the below query to insert 1 row into your test.test_complex_alter table:

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')) from dummy;

Structure of the array(2nd col):

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

Results:

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"}]

Ref:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF -> "Complex Type Constructors"

Upvotes: 1

Related Questions