Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Insert values in Array data type - Hive

avatar
Contributor

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)

1 ACCEPTED SOLUTION

avatar

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

View solution in original post

3 REPLIES 3

avatar

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

avatar
Explorer

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;

avatar
Contributor

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.