hive insert / append data from two oracle table in CDH3 Machine

0
votes

I am loading data from Oracle Tables into HDFS then external Hive table. And after adding addition column ("SOURCE") finally moving data into Hive internal table.

Oracle table USA_Transaction => HDFS => External Hive table => Added Column [intermediate table "temp"]=> Hive Internal table

loaded data [usa transactions] into hive internal table:-

from temp INSERT OVERWRITE TABLE ds_transaction PARTITION(source)

select tran_id,
acct_id ,
tran_date,
amount ,
description ,
branch_code ,
product,
tran_state ,
tran_city ,
tran_zip ,
spendby,source DISTRIBUTE BY source;

Above query working fine.

Now I performed same process for Canada Transaction table

When trying to load data into final Hive table with below query :-

from temp INSERT  TABLE ds_transaction PARTITION(source)

select tran_id,
acct_id ,
tran_date,
amount ,
description ,
branch_code ,
product,
tran_state ,
tran_city ,
tran_zip ,
spendby,source DISTRIBUTE BY source;

Getting error :- " mismatched input 'INTO' expecting OVERWRITE in insert clause"

Note: If used OVERWRITE in the 2nd query then existing data [of usa transactions] will be lost.

Please suggest me.

1
oraclehadoophiveappendcloudera-cdh

1 Answers

0
votes

Change your statement to have the 'INTO' included. This will append the data instead of overwriting it.

from temp INSERT INTO TABLE ds_transaction PARTITION(source)

select tran_id, acct_id , tran_date, amount , description , branch_code , product, tran_state , tran_city , tran_zip , spendby,source DISTRIBUTE BY source;