Hive - cannot recognize input 'insert' in select clause

user_9715637 0
Viewed 318

Say I've already created table3, and try to insert data into it using the following code

WITH table1
AS
(SELECT 1 AS key, 'One' AS value),
table2
AS
(SELECT 1 AS key, 'I' AS value)
INSERT TABLE table3
SELECT t1.key, t1.value, t2.value
  FROM table1 t1 
  JOIN table2 t2
  ON (t1.key = t2.key)

However, I got an error as cannot recognize input 'insert' in select clause. If I simply delete the insert sentence, then the query runs just fine.

Is this a syntax problem? Or I cannot use with clause to insert?

hiveql hive
1 Answers

Use INTO or OVERWRITE depending on what you need:

INSERT INTO TABLE table3  --this will append data, keeping the existing data intact

or

INSERT OVERWRITE TABLE table3 --will overwrite any existing data

Read manual: Inserting data into Hive Tables from queries

user_27003440
Related