CODEWITHSUNDEEP
mysqlhadoophivehdfs
shwetha
shwetha

Reputation: 73

Insert into Select command causing exception ParseException line 1:12 missing TABLE at 'table_name' near '<EOF>'

I am 2 days old into hadoop and hive. So, my understanding is very basic. I have a question which might be silly. Question :I have a hive external table ABC and have created a sample test table similar to the table as ABC_TEST. My goal is to Copy certain contents of ABC to ABC_TEST depending on select clause. So I created ABC_TEST using the following command:

 CREATE TABLE ABC_TEST LIKE ABC;

Problem with this is:

1) this ABC_TEST is not an external table.

2) using Desc command, the LOCATION content for ABC_TEST was something like

hdfs://somepath/somdbname.db/ABC_TEST

--> On command "hadoop fs -ls hdfs://somepath/somdbname.db/ABC_TEST " I found no files .

--> Whereas, "hadoop fs -ls hdfs://somepath/somdbname.db/ABC" returned me 2 files.

3) When trying to insert values to ABC_TEST from ABC, I have the above exception mentioned in the title. Following is the command I used to insert values to ABC_TEST:

INSERT INTO ABC_TEST select * from ABC where column_name='a_valid_value' limit 5;

Is it wrong to use the insert into select option in Hive? what am I missing? Please help

Upvotes: 1

Views: 1108

Answers (2)

Priya
Priya

Reputation: 1

I faced exactly the same issue and the reason is the Hive version.

In one of our clusters, we are using hive 0.14 and on a new set up we're using hive-2.3.4.

In hive 0.14 "TABLE" keyword is mandatory to be used in the INSERT command.

However in version hive 2.3.4, this is not mandatory.

So while in hive 2.3.4, the query you've mentioned above in your question will work perfectly fine but in older versions you'll face exception "FAILED: ParseException line 1:12 missing TABLE <>".

Hope this helps.

Upvotes: 0

mattinbits
mattinbits

Reputation: 10428

The correct syntax is "INSERT INTO TABLE [TABLE_NAME]"

INSERT INTO TABLE ABC_TEST select * from ABC where column_name='a_valid_value' limit 5;

Upvotes: 1

Related Questions