9

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 ?

Salvador Aceves
  • 348
  • 1
  • 4
  • 13

3 Answers3

6

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.

White
  • 61
  • 1
  • 1
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)
Kishore
  • 5,761
  • 5
  • 28
  • 53
  • Could you please specific what dummy table need to contain? – Jove Kuang Aug 04 '15 at 20:58
  • dummy table is a simply a demo table which have atleast one row. – Kishore Aug 06 '15 at 04:58
  • I created a dummy table with two STRING fields and used the command `INSERT INTO ADS (pageid,adid_list) VALUES ( 'front_page',array(1,2,3)) FROM DUMMY LIMIT 1;` and got the error : `FAILED: ParseException line 1:16 cannot recognize input near '(' 'pageid' ',' in statement` . Can you help here!! – SrinR Mar 01 '16 at 13:27
0

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.

Ruben Bhattacharya
  • 112
  • 5