Loading data from one Hive table to another with partition

Question

I have data in one Hive table and would like to load data into another hive table.

The source table is reg_logs which has 2 partitions, date and hour. The data gets loaded into this table hourly. The schema is:

CREATE EXTERNAL TABLE IF NOT EXISTS reg_logs (
id int,
region_code int,
count int
)
PARTITIONED BY (utc_date STRING, utc_hour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/ad_data/raw/reg_logs';

The destination table is reg_logs_org all I would like to do is copy all data from reg_logs beside utc_hour column.

Schema I created is: (please correct if I am wrong)

CREATE EXTERNAL TABLE IF NOT EXISTS reg_logs_org (
id int,
region_code int,
count int
)
PARTITIONED BY (utc_date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/ad_data/reg_logs_org';

Insert data into reg_logs_org from reg_logs:

insert overwrite table reg_logs_org
select id, region_code, sum(count), utc_date
from 
reg_logs
group by 
utc_date, id, region_code

error message:

 FAILED: SemanticException 1:23 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'reg_logs_org'

==

Thank you,
Rio

Answer 1

this is because you are missing the partition info in your insert query

  insert overwrite table reg_logs_org PARTITION (utc_date)
  select id, region_code, sum(count), utc_date
  from 
  reg_logs
  group by 
  utc_date, id, region_code