Insert data into selected columns in HIVE from another table

Question

I have 2 hive tables, one with lots of columns and data the other with some matching columns some non-matching.

I want to be able to insert data from the first table and specify the columns to insert it into for example:

Table 1(fruit): Apples string, Oranges string, Pears string, Grapes string, Kiwi string;

Table 2 (fruitAndVeg): Pears string, Carrot string, Kiwi string;

I want an insert statement close to the following:

insert overwrite table fruitAndVeg x (x.Pears, x.kiwi) select y.Pears, y.kiwi from fruit y;

I know that the code below is possible but I have a lot more data in my tables so trying to specify the columns rather than insert nulls or empty strings in manually:

insert overwrite table fruitAndVeg select y.Pears, emptystring, y.kiwi from fruit y;

Any help is appreciated.

NB. Can also use Impala if that is easier.

Solution

Please be careful when using Insert overwrite as it will replace any existing data in your table. As you said, one of the tables consists of a lot of data.

Coming to your question, you can do the following:

Insert into table fruitandveg(pears,kiwi) select pears,kiwi from fruit;

Be careful with the case(preferred lowercase) of pears and kiwi in the insert into clause as Hive is case sensitive.

Comments

[Amos]

I can't see why your first SQL is not enough.

[Alexei Osipov]

This will not work in Hive because it does not allow to specify columns to insert to.

[Alexei Osipov]

I've spent quite a lot of time on Hive language DSL pages and there is no possibility to list column names for "insert overwrite". The syntax you mentioned is for ACID tables only and it's not compatible with "insert overwrite".

[Utsav Jha]

thats strange. because I have queries running in that format in hive. I have a table with x columns, and the ''insert into table'' statement has x columns specified. I have changed the columns' ordering a few times, yet my query executes perfectly and the output is in the format I expect it to be.

[Utsav Jha]

@AlexeiOsipov please refer to this:issues.apache.org/jira/browse/HIVE-9481

[Alexei Osipov]

I suspect that you have have "ACID" tables in Hive. HIVE-9481 describes changes for "insert" statement. Not "insert overwrite".

[Utsav Jha]

I have written the Insert statement only.