数据仓库ETL记录

 

这里维度建模的分层是：业务数据库 ==> RDS库 ==> DW库  【这里RDS还可以叫做ODS，和业务数据库保持一致】

RDS库表使用默认的文本存储格式，可以直接使用 alter table语句修改表结构。

如果表使用ORC格式，使用alter table修改表模式，尤其是增加列的支持老版本的hive【hive1.1.0之前的版本】会有很多问题【

Error: java. io. IOException: java. lang. ArrayIndexOutOfBoundsException: 9】。这个时候只能通过新建表并重新组织数据的方式才能正常执行。

<=> 关系操作符：

1. 对于非空操作数，用法和“=”一样；
2. 如果2边操作数都是NULL，则返回true；
3. 如果只有1边是NULL，则返回false；

<=>操作符可以用作判断地址发生改变

where !(a.address<=>b.address)

装载customer维度表【缓慢变化维新增一行】

设置已删除记录和地址列缓慢变化维【变化的列新增一行记录，但需要start_time和end_time记录有效时间段】的过期 

方法：

1、选出已删除的客户和地址列发生变化的客户。

选出源表中已删除的客户，和地址列发生变化的客户
select a.customer_sk
from
(select customer_sk, customer_id, address 
   from customer_dim
   -- 从没有过期的记录中选择
   where stop_time=${hivevar:max_date}   
) as a
left join rds.customer as b
on customer_dim.customer_id=rds.customer.customer_id
where b is null or
      (!(a.address <=> b.address);

2、将上述选出的客户的stop_time设置为前一天 pre_date ，即标记为过期。【sk的意思为代理键】

update customer_dim set stop_time=${hivevar:pre_date}
-- 括号中为第一步中选出来的那些记录
where customer_dim.customer_sk in ( ... )

3、处理缓慢变化维adress的新增行

     customer_dim已经标记了过期记录，下面将通过inner join RDS.customer表得到过期记录对应的新值

select t2.customer_id, t2.address, ${hivevar:pre_date} start_time, ${hivevar:max_date} stop_time
from customer_dim t1
-- 使用inner join为了去掉源表已经被删除的记录
-- 选出要修改的记录，即发生改变过期记录
inner join rds.customer t2
on t1.customer_id=t2.customer_id and t1.stop_time=${hivevar:pre_date}
-- 避免多次执行生成重复记录
-- 第一次执行 t3.*都为NULL；第二次执行 维度表插入了修改后的新记录，所以t3.*不是NULL
-- 注意left join的意思：不管on条件符不符合，左表的记录肯定都会列出来
left join customer_dim t3
on t1.customer_id=t3.customer_id and t3.stop_time=${hivevar:max_date)
where !(t1.address <=> t2.address) and t3.customer_sk is null

注意：

t1 join t2 on t1.id=t2.id的执行顺序为 选出t1中的一条记录，然后根据这条记录在t2表中找符合on条件的记录，遍历完t2表之后，再从t1中选出第二条记录... 

4、将新值插入customer_dim，并且保证代理键是自增的。

insert into customer_dim
select
  row_number() over (order by t1.customer_id) + t2.sk_max,
  t1.customer_id,
  address,
  t1.start_time,
  t1.stop_time
from 
   -- t1即以上选择出新值的那张表
  t1
cross join
   -- coalesce(a, default)表示如果a非空，返回a；a为空，返回默认值
  (select coalesce(max(custoemr_sk), 0) sk_max from customer_dim) t2;