hadoop - 使用分区将数据从一个 Hive 表加载到另一个

Question

我在一个 Hive 表中有数据，想将数据加载到另一个 Hive 表中。

源表是 reg_logs，它有 2 个分区，日期和小时。数据每小时加载到该表中。架构是:

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';

目标表是 reg_logs_org 我想做的就是从 utc_hour 列旁边的 reg_logs 复制所有数据。

我创建的架构是:(如果我错了请更正)

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';

将数据从 reg_logs 插入 reg_logs_org:

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

错误信息:

 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

这是因为您在插入查询中缺少分区信息

  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