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.

Aindrea

Beware, nulls take more effort issues.apache.org/jira/browse/HIVE-4022 – Clary

Answer 1

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;

Jolenejolenta

could you please let us know which version of hive you have tested it . – Poilu

@sandeeprawat, I test this sql on hive-0.11.0 and hive-2.0.0. It works fine. – Jolenejolenta

@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. – Aindrea

@Aindrea you are welcome. Can u vote to my answer?😜 – Jolenejolenta

@Jolenejolenta 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! – Aindrea

@aaronshan.... I finally got enough reputation to upvote so I just plus oned you! – Aindrea

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') – Teacake

Answer 2

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;

Prioress

you don't need the from clause. – Teacake

I confirm, the from clause is unnecessary and only complicates everything. Maybe in old versions of hive it was necessary. – Wildon

Answer 3

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

Kickoff

Answer 4

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

Ucayali

Answer 5

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

Saunderson

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. – Logging