How do you insert data into complex data type "Struct" in Hive

Question

I'm completely new to Hive and Stack Overflow. I'm trying to create a table with complex data type "STRUCT" and then populate it using INSERT INTO TABLE in Hive.

I'm using the following code:

CREATE TABLE struct_test
(
 address STRUCT<
                houseno:    STRING
               ,streetname: STRING
               ,town:       STRING
               ,postcode:   STRING
               >
);

INSERT INTO TABLE struct_test
SELECT NAMED_STRUCT('123', 'GoldStreet', London', W1a9JF') AS address
FROM dummy_table
LIMIT 1;

I get the following error:

Error while compiling statement: FAILED: semanticException [Error 10044]: Cannot insert into target because column number type are different 'struct_test': Cannot convert column 0 from struct to array>.

I was able to use similar code with success to create and populate a data type Array but am having difficulty with Struct. I've tried lots of code examples I've found online but none of them seem to work for me... I would really appreciate some help on this as I've been stuck on it for quite a while now! Thanks.

Beware, nulls take more effort https://issues.apache.org/jira/browse/HIVE-4022 — Chris, Mar 03 '17 at 14:44

score 10 · Accepted Answer · answered Sep 09 '16 at 04:15

10

your sql error. you should use sql:

INSERT INTO TABLE struct_test 
       SELECT NAMED_STRUCT('houseno','123','streetname','GoldStreet', 'town','London', 'postcode','W1a9JF') AS address 
           FROM dummy_table LIMIT 1;

answered Sep 09 '16 at 04:15

aaronshan

382
3
9

could you please let us know which version of hive you have tested it . – sandeep rawat Sep 09 '16 at 05:32
@sandeeprawat, I test this sql on hive-0.11.0 and hive-2.0.0. It works fine. – aaronshan Sep 09 '16 at 06:20
@aaronshan, your answer worked perfectly, thank you so much this problem had caused me hours of pain! I believe I'm using Hive version 1.1.0. – data101 Sep 09 '16 at 10:16
@data101 you are welcome. Can u vote to my answer? – aaronshan Sep 09 '16 at 11:11
@aaronshan I'd love to but I tried to push the upward arrow icon and it said I need a reputation of 15 or more for my up votes to be public... I think because I'm new to Stack Overflow I need to get more up votes for myself before I can vote. Unless you know another way I'd be happy to! – data101 Sep 09 '16 at 12:45
@aaronshan.... I finally got enough reputation to upvote so I just plus oned you! – data101 Mar 07 '17 at 12:32
the select can be more simple, if you remove the last part, from the AS: INSERT INTO TABLE struct_test SELECT NAMED_STRUCT('houseno','123','streetname','GoldStreet', 'town','London', 'postcode','W1a9JF') – Flamma Mar 09 '20 at 15:57

score 10 · Answer 2 · answered Feb 13 '17 at 10:21

10

You can not insert complex data type directly in Hive.For inserting structs you have function named_struct. You need to create a dummy table with data that you want to be inserted in Structs column of desired table. Like in your case create a dummy table

CREATE TABLE DUMMY ( houseno:    STRING
           ,streetname: STRING
           ,town:       STRING
           ,postcode:   STRING);

Then to insert in desired table do

INSERT INTO struct_test SELECT named_struct('houseno',houseno,'streetname'
                  ,streetname,'town',town,'postcode',postcode) from dummy;

answered Feb 13 '17 at 10:21

Amit_Hora

716
1
8
27

you don't need the from clause. – Flamma Mar 09 '20 at 15:59
I confirm, the from clause is unnecessary and only complicates everything. Maybe in old versions of hive it was necessary. – pavel_orekhov Aug 15 '23 at 00:56

score 5 · Answer 3 · edited Jan 08 '18 at 00:35

5

No need to create any dummy table : just use command :

insert into struct_test
select named_struct("houseno","house_number","streetname","xxxy","town","town_name","postcode","postcode_name");

edited Jan 08 '18 at 00:35

Nawrez

3,314
8
28
42

answered Jan 07 '18 at 15:42

Suman Kumar

51
1
1

score 0 · Answer 4 · edited Jul 18 '17 at 17:06

is Possible:

you must give the columns names in sentence from dummy or other table.

INSERT INTO TABLE struct_test
SELECT NAMED_STRUCT('houseno','123','streetname','GoldStreet', 'town','London', 'postcode','W1a9JF') AS address 
 FROM dummy

Or

INSERT INTO TABLE struct_test
SELECT NAMED_STRUCT('houseno',tb.col1,'streetname',tb.col2, 'town',tb.col3, 'postcode',tb.col4) AS address 
 FROM table1 as tb

score 0 · Answer 5 · edited Jan 13 '23 at 17:52

0

CREATE TABLE IF NOT EXISTS sunil_table(
id INT,
name STRING,
address STRUCT<state:STRING,city:STRING,pincode:INT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '.';

INSERT INTO sunil_table  1,"name" SELECT named_struct(
"state","haryana","city","fbd","pincode",4500);???

how to insert both (normal and complex)data into table

edited Jan 13 '23 at 17:52

jmoerdyk

5,544
7
38
49

answered Jan 13 '23 at 17:47

user21002259

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 15 '23 at 23:17

How do you insert data into complex data type "Struct" in Hive

5 Answers5

Linked