基于Hadoop生态圈的数据仓库实践 —— 进阶技术（二）

二、按需装载
        前面已经做了“初始装载”和“定期装载”。还有一种需要熟悉的装载类型，按需装载。所谓“按需装载”指的是，在正常调度之外，当源数据有效或者数据仓库需要时进行装载。例如，促销销售源数据只有在促销期内有效，而在其它时间是无效的，而对促销期数据就要进行按需装载。
        在“建立数据仓库示例模型”中讨论的日期维度数据生成可以看做是一种按需装载。数据仓库预先装载了日期，当日期用完时，需要再次运行预装载。
        本节的主题是按需装载，首先修改数据库模式，然后在DW数据库上执行按需装载，使用促销期场景进行说明。定期装载不适合促销期场景，因为促销期数据并不是按调度定期装载。下面是需要装载的促销期内容，存储在source.promo_schedule表中。

PROMOTION CODE,PROMOTION NAME,START DATE,END DATE
SO,Special Offer,2016-04-01,2016-04-10
DP,Disk Promotion,2016-05-05,2016-05-20
MS,Month Special,2016-06-01,2016-06-30
MP,Monitor Promotion,2016-07-10,2016-07-15
BS,Back to School,2016-08-10,2016-08-30

        注意源数据提供了促销周期，而不是单个的促销日期。示例假设只需要装载新的促销期数据，而在数据仓库中不需要促销期的历史数据。
        下图显示了修改后的DW数据库模式，date_dim表增加了promo_ind列，用来标识该日期是否为促销日期。

 

1. 修改数据库模式
        使用下面的SQL脚本修改源数据库模式。

use source;
-- 建立促销期表
create table promo_schedule (
    promotion_code varchar(10) comment '促销期代码',
    promotion_name varchar(50) comment '促销期名称',
    start_date date comment '促销期开始日期',
    end_date date comment '促销期截止日期',
    primary key (promotion_code)
);
-- 添加促销期数据
insert into promo_schedule values
('SO','Special Offer','2016-04-01','2016-04-10'),
('DP','Disk Promotion','2016-05-05','2016-05-20'),
('MS','Month Special','2016-06-01','2016-06-30'),
('MP','Monitor Promotion','2016-07-10','2016-07-15'),
('BS','Back to School','2016-08-10','2016-08-30');

commit;

        使用下面的HiveQL脚本修改RDS数据库模式。

use rds;
create table promo_schedule (
    promotion_code varchar(10) comment 'promotion code',
    promotion_name varchar(50) comment 'promotion name',
    start_date date comment 'start date',
    end_date date comment 'end date'
);

        使用下面的HiveQL脚本修改DW数据库模式。  

use dw;

alter table date_dim rename to date_dim_old; 
-- 原来的日期维度表是普通的CSV文件格式，因为需要行级更新促销标记字段，所以新建ORC文件格式的表。
create table date_dim (    
    date_sk int comment 'surrogate key',  
    date date comment 'date,yyyy-mm-dd',  
    month tinyint comment 'month',  
    month_name varchar(9) comment 'month name',  
    quarter tinyint comment 'quarter',  
    year smallint comment 'year',
    promo_ind char(1) comment 'promotion index'
)  
comment 'date dimension table'
clustered by (date_sk) into 8 buckets
stored as orc tblproperties ('transactional'='true');
-- 装载日期维度数据，促销期标记为'N'
insert into date_dim select *,'N' from date_dim_old;
-- 删除老的日期维度表
drop table date_dim_old;

2. 新建按需装载脚本
        使用下面的on_demand.sh脚本完成按需装载过程。

#!/bin/bash  
# 整体拉取promo_schedule表数据  
sqoop import --connect jdbc:mysql://cdh1:3306/source?useSSL=false --username root --password mypassword --table promo_schedule --hive-import --hive-table rds.promo_schedule --hive-overwrite  
# 调用 on_demand.sql 文件执行按需装载  
beeline -u jdbc:hive2://cdh2:10000/dw -f on_demand.sql

        on_demand.sql文件中的HiveQL脚本如下:

-- 设置变量以支持事务  
set hive.support.concurrency=true;  
set hive.exec.dynamic.partition.mode=nonstrict;  
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;  
set hive.compactor.initiator.on=true;  
set hive.compactor.worker.threads=1;  
  
use dw;
update date_dim   
   set promo_ind = 'Y'    
 where date_dim.date_sk in
(select a.date_sk 
   from date_dim a, rds.promo_schedule b
  where a.date between b.start_date and b.end_date);

        需要在日期装载后运行该脚本，换句话说，所有促销期内从开始到结束的日期，在日期维度里都是存在的。实际上装载所做的就是，如果一个日期在一个促销期内，则设置date_dim表的promo_ind列为‘Y’。
  
2. 测试
        使用下面的命令执行按需装载。

./on_demand.sh

        使用下面的查询验证结果。

select case when year is null then '' else cast(year as string) end as year,
       case when year is null then '' else cast(month as string) end as month,
       count,
       case when year is null then '' else cast(start_date as string) end as start_date,
       case when year is null then '' else cast(end_date as string) end as end_date
from (
select year,
       month,
       count(*) count,
       min(date) start_date,
       max(date) end_date,
       case when year is null then 1 else 0 end as flg 
  from date_dim 
 where promo_ind = 'Y' 
 group by year,month
 grouping sets((year,month),())
 cluster by flg,year,month) t;

        查询结果如下图所示。

        在五个促销期里有共有83的促销日。第一个周期有10天（2016年4月1日、2016年4月2日;…2016年4月10日），第二个促销周期有16天（2016年5月5日，2016年5月6日;…2016年5月20日），等等。查询第一个周期应该有10天的promo_ind列上具有‘ Y’值。