How do you insert data into complex data type “Str

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.

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;

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;

Answer 3

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

Answer 4

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