千亿数仓项目(数仓理论_商品维度数据装载(拉链表))

商品维度数据装载(拉链表)

拉链表设计：

1.采集当日全量数据存储到 ND（当日） 表中。
2.可从历史表中取出昨日全量数据存储到 OD（上日数据）表中。
3.用ND－OD为当日新增和变化的数据（即日增量数据）。
两个表进行全字段比较，将结果记录到tabel_I表中。
4.用OD－ND为状态到此结束需要封链的数据。 (需要修改END_DATE)；
两个表进行全字段比较，将结果记录到tabel_U表中 。
5.历史表（HIS）比ND表和OD表多两个字段（START_DATE，END_DATE）
6.将tabel_I表的内容全部insert插入到HIS表中。
START_DATE=‘当日’,END_DATE可设为’9999-12-31’ ；
7.更新封链记录的END_DATE。
历史表（HIS）和tabel_U表比较，START_DATE，END_DATE除外，以tabel_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。
8.取数据时对日期进行条件选择即可,如：取20100101日的数据为
(where START_DATE<=‘20100101’ and END_DATE>‘20100101’ )。

拉链表的作用：

1. 数据量比较大。
2. 表中的部分字段会被更新，比如用户的地址，银行利率，订单的状态等。
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看利率在历史某一个时间点的状态。
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右。
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;
   拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。

使用拉链表解决商品SCD问题
5.1 dw层建表

-- dw层建表
DROP TABLE IF EXISTS `itcast_dw`.`dim_goods`;
CREATE TABLE `itcast_dw`.`dim_goods`(
  goodsId bigint,
  goodsSn string,
  productNo string,
  goodsName string,
  goodsImg string,
  shopId bigint,
  goodsType bigint,
  marketPrice double,
  shopPrice double,
  warnStock bigint,
  goodsStock bigint,
  goodsUnit string,
  goodsTips string,
  isSale bigint,
  isBest bigint,
  isHot bigint,
  isNew bigint,
  isRecom bigint,
  goodsCatIdPath string,
  goodsCatId bigint,
  shopCatId1 bigint,
  shopCatId2 bigint,
  brandId bigint,
  goodsDesc string,
  goodsStatus bigint,
  saleNum bigint,
  saleTime string,
  visitNum bigint,
  appraiseNum bigint,
  isSpec bigint,
  gallery string,
  goodsSeoKeywords string,
  illegalRemarks string,
  dataFlag bigint,
  createTime string,
  isFreeShipping bigint,
  goodsSerachKeywords string,
  modifyTime string,
  dw_start_date string,
  dw_end_date string
)
STORED AS PARQUET;

以上代码什么意思？
在dw层创建dim_goods表，并多添加两个字段一个是状态开始时间: dw_start_date string，一个是状态结束时间: dw_end_date string
他在干什么？
这个表用来保存表itcast_goods的历史数据和更新数据还有新增数据

5.2 具体步骤
拉链表设计一共分为以下几个步骤：
1、第一次全量导入
所有的ODS数据全部导入到拉链历史记录表中
2、增量导入（某天，举例：2018-09-09） 增量导入某天的数据到ODS分区 合并历史数据
通过连接查询方式更新
1全量导入
将所有 2019年09月08日以前创建的商品以及修改的数据全部导入到拉链历史记录表中
操作步骤：
1、使用Kettle将20190908以前的数据抽取到ods

SELECT *
FROM itcast_ods.itcast_goods
WHERE DATE_FORMAT(createtime, ‘%Y%m%d’) <= ‘20190908’ OR DATE_FORMAT(modifyTime, ‘%Y%m%d’) <= ‘20190908’;

在kettle的表输入组件里查找包含20190908及以前的日期数据
他在干什么？
把20190908日期及以前的数据作为全量数据输入到itcast_ods.itcast_goods

2、使用spark sql将全量数据导入到dw层维度表

set spark.sql.shuffle.partitions=1; --shuffle时的分区数，默认是200个
-- 使用spark sql将全量数据导入到dw层维度表
insert overwrite table `itcast_dw`.`dim_goods`
select
  goodsId,
  goodsSn,
  productNo,
  goodsName,
  goodsImg,
  shopId,
  goodsType,
  marketPrice,
  shopPrice,
  warnStock,
  goodsStock,
  goodsUnit,
  goodsTips,
  isSale,
  isBest,
  isHot,
  isNew,
  isRecom,
  goodsCatIdPath,
  goodsCatId,
  shopCatId1,
  shopCatId2,
  brandId,
  goodsDesc,
  goodsStatus,
  saleNum,
  saleTime,
  visitNum,
  appraiseNum,
  isSpec,
  gallery,
  goodsSeoKeywords,
  illegalRemarks,
  dataFlag,
  createTime,
  isFreeShipping,
  goodsSerachKeywords,
  modifyTime,
    case when modifyTime is not null
      then from_unixtime(unix_timestamp(modifyTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd')
      else from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') 
      end as dw_start_date,
   '9999-12-31' as dw_end_date
from
  `itcast_ods`.`itcast_goods` t
where dt='20190908';

以上代码什么意思？
导入20190908分区全量数据到itcast_dw库的dim_goods表内
他在干什么？
把itcast_ods库的itcast_goods表把分区为20190908的数据作为全量数据导入到dw层的dim_goods表内，因为第一次导入多了两个日期字段，所以在导入进去时给每条数据的默认开始时间是每条数据的修改时间，结束时间定为9999-12-31以表示最新数据

2增量导入
将2019年09月09日创建的、修改的数据全部导入到历史拉链表中
操作步骤：
1、使用Kettle将20190909创建的、或者修改的数据抽取到ods

SELECT *
FROM itcast_goods
WHERE DATE_FORMAT(createtime, ‘%Y%m%d’) = ‘ d t ′ O R D A T E F O R M A T