Hive: How to insert data in a column of type array <string>

Question

Have a table with following schema:

CREATE TABLE `student_details`(
  `id_key` string, 
  `name` string, 
  `subjects` array<string>)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'path'

When trying to insert the values in the table getting an error message:

Tried:

INSERT INTO student_details  values ('AA87U','BRYAN',array('ENG','CAL_1','CAL_2','HST','MUS'));

Error:

FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

This doesn't make sense to me. Tried looking online and found a similar one: ExternalLink. The solution also not making any sense.

Any help please.

F. Müller · Accepted Answer · 2020-11-24 07:37:37Z

3

You can't insert a complex type directly in Hive.

Either you have to create a dummy table like below:

INSERT INTO student_details select 'AA87U','BRYAN', array('ENG','CAL_1','CAL_2','HST','MUS') from dummy;

For Hive 2+, you can run without dummy table.

INSERT INTO student_details select 'AA87U','BRYAN', array('ENG','CAL_1','CAL_2','HST','MUS');

edited Nov 24, 2020 at 7:37

F. Müller

4,0629 gold badges44 silver badges53 bronze badges

answered Feb 13, 2019 at 23:07

Nishu Tayal

20.9k8 gold badges53 silver badges105 bronze badges

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

1 Comment

Pavel Orekhov Over a year ago

insert into blablabla select array('a', 'b') doesn't work, when blablabla contains a single column of type array<string>.

Pavel Orekhov · Accepted Answer · 2022-05-25 19:27:47Z

0

You have to first create a dummy table with one row:

create table dummy(a int);
insert into dummy values (1);

then you can do this:

INSERT INTO student_details select 'AA87U','BRYAN', array('ENG','CAL_1','CAL_2','HST','MUS') from dummy;

answered May 25, 2022 at 19:27

Pavel Orekhov

2,2723 gold badges22 silver badges49 bronze badges

Collectives™ on Stack Overflow

Hive: How to insert data in a column of type array <string>

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related