Merge 2 external hive tables into single new table

Question

I am new to hive. I have created 2 external hive tables and also imported data from oracle using sqoop. Also I have created a new external table which will have data of both External table 1 and External table 2 as follows

create external table transaction_usa_canada
(
tran_id int,
acct_id int,
tran_date string,
amount double,
description string,
branch_code string,
tran_state string,
tran_city string,
speendby string,
tran_zip int,
source_table string
)
row format delimited
stored as textfile
location '/user/gds/bank_ds/tran_usa_canada';

Now , I dont know how to merge the data of 2 external tables into above external table.

Please help.

Can you provide the metadata of table1 and table2. Also table3 as well? — Farooque, May 18 '16 at 10:42
Pls refer to new question of mine... http://stackoverflow.com/questions/37299136/insert-data-of-2-hive-external-tables-in-new-external-table-with-additional-colu — user2998990, May 18 '16 at 12:08

score 0 · Answer 1 · answered May 18 '16 at 12:10

0

If you have have same column structure for your 2 external table, then you can copy the text files to a common location or folder and create a new table that is pointing to new location.

If the metadata is different for 2 external table, you can consider the option of "create table as select " to load to new table.

answered May 18 '16 at 12:10

BigData_Consultant

96
4

hi.... I have elaborated more in this link http://stackoverflow.com/questions/37299136/insert-data-of-2-hive-external-tables-in-new-external-table-with-additional-colu – user2998990 May 18 '16 at 12:15

score 0 · Answer 2 · answered May 18 '16 at 18:50

You can use a union statement to read them into a new table.

    INSERT OVERWRITE TABLE [database].[table]
SELECT 
* 
FROM (
SELECT 
Col_1 STRING,
Col_2 STRING,
Col_3 STRING
FROM
[table]
UNION ALL
SELECT
Col_1 STRING,
Col_2 STRING,
Col_3 STRING
FROM 
[table]) [table];

Merge 2 external hive tables into single new table

2 Answers2