Hive inserting values to an array complex type column

Question

I am unable to append data to tables that contain an array column using insert into statements; the data type is array < varchar(200) >

Using jodbc I am unable to insert values into an array column by values like :

INSERT INTO demo.table (codes) VALUES (['a','b']);

does not recognises the "[" or "{" signs.

Using the array function like ...

INSERT INTO demo.table (codes) VALUES (array('a','b'));

I get the following error using array function:

Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

Tried the workaround...

INSERT into demo.table (codes) select array('a','b');

unsuccessfully:

Failed to recognize predicate '<EOF>'. Failed rule: 'regularBody' in statement

How can I load array data into columns using jdbc ?

Answer 1

My Table has two columns: a STRING, b ARRAY<STRING>.

When I use @Kishore Kumar Suthar's method, I got this:

FAILED: ParseException line 1:33 cannot recognize input near '(' 'a' ',' in statement

But I find another way, and it works for me:

INSERT INTO test.table 
SELECT "test1", ARRAY("123", "456", "789") 
FROM dummy LIMIT 1;

dummy is any table which has atleast one row.

Answer 2

make a dummy table which has atleast one row.

INSERT INTO demo.table (codes) VALUES (array('a','b')) from dummy limit 1;

hive> select codes demo.table;
OK
["a","b"]
Time taken: 0.088 seconds, Fetched: 1 row(s)