Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Insert values in Array data type - Hive

Explorer

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

@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

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

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;

Explorer

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.