How to specify column list in hive insert into query

Question

I have just installed and configured Apache Hive version 1.1.0. Then I have created a table by quering this query:

create table person (name1 string, surname1 string);

And then I want to add one row by:

insert into person (name1, surname1) values ("Alan", "Green");

And it cause an error:

Error: Error while compiling statement: FAILED: ParseException line 1:20 cannot recognize input near '(' 'name1' ',' in statement (state=42000,code=40000).

But when I execute query without column list it works fine:

insert into person values ("Alan", "Green");

The question is: how to specify column list in hiveQL to make insert into?

score 11 · Accepted Answer · answered Oct 13 '17 at 21:52

According to this bug HIVE-9481, you can specify column list in INSERT statement, since 1.2.0. The syntax is like this:

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) 
[(column_list)]
[IF NOT EXISTS]] select_statement1 FROM from_statement;

example:

CREATE TABLE pageviews (userid VARCHAR(64), link STRING, "from" STRING)
PARTITIONED BY (datestamp STRING) 
CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;

INSERT INTO TABLE pageviews 
PARTITION (datestamp = '2014-09-23')
(userid,link) 
VALUES ('jsmith', 'mail.com');

I tested this with Hive 2.1. It works only with INSERT INTO, not with INSERT OVERWRITE

And I don't know why this syntax is not mentioned in the Apache wiki page LanguageManual DML

https://issues.apache.org/jira/browse/HIVE-9481

score 3 · Answer 2 · answered Aug 02 '16 at 16:28

3

Insert into specific columns in the above query:

insert into table person (name1, surname1) values ("Alan", "Green");

is supported in Hive 2.0

answered Aug 02 '16 at 16:28

Aditya

2,385
18
25

score 2 · Answer 3 · answered Mar 17 '15 at 10:12

2

Hive currently not supports such functionality, details here

You must provide full values list.

answered Mar 17 '15 at 10:12

www

4,365
1
23
24

Is this still true for Hive version 1.2.1 ?? – Frostie_the_snowman Jun 16 '16 at 15:57

score 0 · Answer 4 · answered Mar 17 '15 at 17:24

Hive does not support row level inserts,updates and deletes.

However, after creating a table you can have all your data in a file and load the file into hive table. that way you can insert data into a hive table. You can refer the hive manual for the commands.

There are workarounds for performing inserts/updates and deletes in hive. But its not recommended.

How to specify column list in hive insert into query

4 Answers4