Hive inserting values to an array complex type col

问题:

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 ?

回答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.

回答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)

回答3:

Suppose I have a table employee containing the fields ID and Name.

I create another table employee_address with fields ID and Address. Address is a complex data of type array(string).

Here is how I can insert values into it:

insert into table employee_address select 1, 'Mark', 'Evans', ARRAY('NewYork','11th 
avenue') from employee limit 1;

Here the table employee just acts as a dummy table. No data is copied from it. Its schema may not match employee_address. It doesn't matter.