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 ?

White · Accepted Answer · 2016-04-07 07:56:34Z

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.

answered Apr 7, 2016 at 7:56

White

611 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Kishore · Accepted Answer · 2015-05-26 05:11:20Z

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)

answered May 26, 2015 at 5:11

Kishore

5,9215 gold badges32 silver badges54 bronze badges

3 Comments

Jove Kuang Over a year ago

Could you please specific what dummy table need to contain?

Kishore Over a year ago

dummy table is a simply a demo table which have atleast one row.

SrinR Over a year ago

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!!

Ruben Bhattacharya · Accepted Answer · 2018-11-02 09:51:03Z

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.

answered Nov 2, 2018 at 9:51

Ruben Bhattacharya

1325 bronze badges

1 Comment

Ruben Bhattacharya Over a year ago

Limit 1 means data will be entered once in employee_address.

Collectives™ on Stack Overflow

Hive inserting values to an array complex type column

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related