Importing data from two tables to a single table in Hive

johnydoe — Fri, 17 Feb 2017 23:47:05 GMT

I've two external tables in Hive that have exactly the same schema and I want to import the data from these two tables into a single table. I can do a UNION ALL from two tables but it seems to be very slow. Example show below. Is there a better way to do this? Thanks.

CREATE TABLE MYTABLE
ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
STORED AS RCFILE
AS
SELECT row_number() OVER(), *
FROM
(
SELECT * FROM x
UNION ALL
SELECT * FROM Y
) T2

Re: Importing data from two tables to a single table in Hive

aervits — Sat, 18 Feb 2017 00:31:50 GMT

@john doe use STORED AS ORC instead of RCFILE

also, use CTAS to create a table from one table first, then do on the second table

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] (z,y) select_statement1 FROM from_statement;

see if that improves performance, I know it's 2 steps but interesting use case.

question Re: Importing data from two tables to a single table in Hive in Archives of Support Questions (Read Only)

Importing data from two tables to a single table in Hive

Re: Importing data from two tables to a single table in Hive