CODEWITHSUNDEEP
sqlhadoophivehiveql
banditKing
banditKing

Reputation: 9579

Hive Insert overwrite from 1 table to another with different number of columns

I have 2 hive tables.

source table has following columns:

  correspondence_id       | decimal(22,0)  |          |
| template_id             | decimal(18,0)  |          |
| language_cd             | varchar(6)     |          |
| delivery_channel_cd     | varchar(20)    |          |
| job_id                  | decimal(18,0)  |          |
| correspondence_content  | string         |          |
| create_user_id          | varchar(40)    |          |
| create_ts               | timestamp      |          |
| last_updt_user_id       | varchar(40)    |          |
| last_updt_ts            | timestamp      |          |
| data_src_id             | decimal(18,0)  |          |
| src_app_resource_cd     | varchar(50)

destination has the following columns:

   correspondence_id        | decimal(22,0)         |                       |
| template_id              | decimal(18,0)         |                       |
| template_cd              | varchar(20)           |                       |
| template_type_cd         | varchar(40)           |                       |
| category_cd              | varchar(20)           |                       |
| language_cd              | varchar(6)            |                       |
| delivery_channel_cd      | varchar(20)           |                       |
| job_id                   | decimal(18,0)         |                       |
| correspondence_content   | string                |                       |
| create_user_id           | varchar(40)           |                       |
| create_ts                | timestamp             |                       |
| last_updt_user_id        | varchar(40)           |                       |
| last_updt_ts             | timestamp             |                       |
| data_src_id              | decimal(18,0)         |                       |
| src_app_resource_cd      | varchar(50)           |                       |
| part_create_year_num     | int                   |                       |
| part_create_month_num    | int                   |                       |
|                          | NULL                  | NULL                  |
| # Partition Information  | NULL                  | NULL                  |
| # col_name               | data_type             | comment               |
|                          | NULL                  | NULL                  |
| part_create_year_num     | int                   |                       |
| part_create_month_num    | int

Im using the following query to transfer data:

FROM source_table cc insert overwrite table 
destination_table partition 
(part_create_year_num=2016, part_create_month_num=9 )
select cc.correspondence_id, cc.template_id, cc.language_cd, cc.delivery_channel_cd, cc.job_id, 
cc.correspondence_content, cc.create_user_id, cc.create_ts, cc.last_updt_user_id, cc.last_updt_ts, 
cc.data_src_id, cc.src_app_resource_cd

But when I run this query I get the following error

rror: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:79 Cannot insert into target table because column number/types are different '9': Table insclause-0 has 15 columns, but query has 12 columns. (state=42000,code=10044)
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:79 Cannot insert into target table because column number/types are different '9': Table insclause-0 has 15 columns, but query has 12 columns.

obviously source and destination tables are different, but how can i make this query work, I ve tried putting in placeholder values but that didnt work either.

Upvotes: 0

Views: 5625

Answers (1)

Sanket
Sanket

Reputation: 194

It seems that destination table has five extra columns extra

Normal Columns: 1 template_cd | varchar(20) | 2 template_type_cd | varchar(40) | | 3 category_cd | varchar(20) | |

Partition Columns 4. part_create_year_num | int | | 5. part_create_month_num | int

Query Should be

Insert overwrite table Destination_table partition(part_create_year_num=2016, part_create_month_num=9 )  select
 correspondence_id,        
 template_id,      
 '' as template_cd,      
'' as  template_type_cd, 
 '' as category_cd,      
 language_cd,      
 delivery_channel_cd,
 job_id,
 correspondence_content,
 create_user_id,
 create_ts,
 last_updt_user_id,
 last_updt_ts,
 data_src_id,
 src_app_resource_cd
 from source_table

Upvotes: 1

Related Questions