into Hive table - Non Partitioned table to Partitioned table having multiple partitions - Cannot insert into target table because column number/types

Question

When I tried to insert into a Partiotioned table I am getting the bellow error:

SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different : Table insclause-0 has 6 columns, and the 3 columns are partitioned and we not required any filters we have to dump/store from non partitioned table to partitioned table.

My table:

Source:

id name   salary dep
1  sai    1000   sales
2  syam   2000   hr
3  sundar 3000   bank

Target:

id name   salary dep
1  sai    1000   sales
2  syam   2000   hr
3  sundar 3000   bank

partition (name string, dep string)

Please let me how to copy from source to target

tried below way.

insert into target_partitioned_table partition(name,dep) select id from source_table;

Answer 1

You should list all columns in the select, partition columns should be the last and in the same order. Order of columns matters.

Check the table DDL. If it is partitioned by Name and Dep, then partition columns should be the last: id, salary, name, dep. If columns ordered like in your question, it does not look like the table is partitioned by (Name, Dep), or the order of columns is wrong in the file or in your data example. Insert columns in the same order which DESCRIBE command returns.

Query should contain all columns in exactly the same order.

For static partition load you do not need partition columns in the select, values are static in the partition spec:

insert into table target_partitioned_table partition(name='Some Name',dep='Sales')
select id, salary from source_table;

For dynamic partition load (partitions are taken from the dataset and should be in the select in the same order):

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert into table target_partitioned_table partition(name,dep)
select id, salary, name, dep from source_table;