question Re: Insert values in Array data type - Hive in Support Questions

Insert values in Array data type - Hive

nelsonarasu — Thu, 10 Mar 2016 11:15:27 GMT

I am trying to load the data into Array data type in hive and got below errors .

1 ) INSERT INTO table test_array VALUES (1,array('a','b'));

Error: Error while compiling statement: FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values (state=42000,code=10293)

2 ) INSERT INTO test_array (col2) VALUES (array('a','b')) from dummy limit 1;

Error: Error while compiling statement: FAILED: ParseException line 1:54 missing EOF at 'from' near ')' (state=42000,code=40000)

Re: Insert values in Array data type - Hive

shishir_saxena4 — Thu, 10 Mar 2016 20:43:53 GMT

@Nelson KA Rajendran You need to create a dummy table with one row

create table dummy(a string); insert into table dummy values ('a');

Then you can insert to your test_array table using

INSERT INTO table test_array SELECT 1, array('a','b') from dummy;

You can't insert a complex type directly to Hive table.

Re: Insert values in Array data type - Hive

whitsonk — Tue, 30 May 2017 22:05:19 GMT

If you don't already have a table with at least one row, you can accomplish the desired result without creating a dummy table.

insert into test_array select 1, array('a','b') from (select '123') x;

Re: Insert values in Array data type - Hive

carter — Wed, 12 Jul 2017 05:26:57 GMT

If you are using Hive 2 or later (including Hive LLAP), you no longer need the dummy table, statements like:

INSERT INTO table test_array SELECT 1, array('a','b');

will work.