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?

yetsun yetsun · Accepted Answer · 2017-10-13T21:52:51

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

How to specify column list in hive insert into query

4 Answers