Hive 批量插入数据到多个表

-- drop table vn09jj5.test1094_order_line_text ;
create table if not exists vn09jj5.test1094_order_line_text
(
 store_id          smallint     ,
 order_id          string       ,
 paid_time         timestamp    ,
 department        smallint     ,
 category          int          ,
 upc               string       ,
 upc_desc          string       ,
 gmv               decimal(14,2) 
)
partitioned by (channel string,paid_date date) 
stored as textfile ;

-- drop table vn09jj5.test1094_order_line_orc_snappy ;
create table if not exists vn09jj5.test1094_order_line_orc_snappy
(
 store_id          smallint     ,
 order_id          string       ,
 paid_time         timestamp    ,
 department        smallint     ,
 category          int          ,
 upc               string       ,
 upc_desc          string       ,
 gmv               decimal(14,2) 
)
partitioned by (channel string,paid_date date) 
stored as orc tblproperties('orc.compression'='SNAPPY');

 

SET hive.exec.dynamic.partition =true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;

 

--1
from cn_ec_bi_dl_secure.wm_order_line_o
--插入表_1(test1094_order_line_text)
insert overwrite table vn09jj5.test1094_order_line_text partition(channel,paid_date) --分区
select 
 store_id,order_id,paid_time,department,category,upc,upc_desc,gmv,
 channel,paid_date
where paid_date = '2019-12-01' and channel='SNG' and store_id=1094
--插入表_2(test1094_order_line_orc_snappy)
insert overwrite table vn09jj5.test1094_order_line_orc_snappy partition(channel,paid_date) --分区
select 
 store_id,order_id,paid_time,department,category,upc,upc_desc,gmv,
 channel,paid_date 
where paid_date = '2019-12-02' and channel='SNG' and store_id=1094
;

 

--2
with cte_SNG as  
(
select 
 store_id,order_id,paid_time,department,category,upc,upc_desc,gmv,
 channel,paid_date
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date in( '2020-01-03','2020-01-04') 
and channel='SNG' and store_id=1094 
)
 
from cte_SNG
--插入表_1(test1094_order_line_text)
insert overwrite table vn09jj5.test1094_order_line_text partition(channel,paid_date) --分区
select 
 store_id,order_id,paid_time,department,category,upc,upc_desc,gmv,
 channel,paid_date
where paid_date = '2020-01-03' and channel='SNG' and store_id=1094
--插入表_2(test1094_order_line_orc_snappy)
insert overwrite table vn09jj5.test1094_order_line_orc_snappy partition(channel,paid_date) --分区
select 
 store_id,order_id,paid_time,department,category,upc,upc_desc,gmv,
 channel,paid_date 
where paid_date = '2020-01-04' and channel='SNG' and store_id=1094
;