如何构建数据仓库分层

ODS层-->DM层-->DWD层

第一步：

　　先构建ods_delta表（分区：日期，小时，分钟），开始源源不断写入ods_delta表中，只存储增量数据。

　　ods_delta表需要在原表基础上新增如下字段：

　　　　　　cdc_record_id STRING COMMENT '唯一自增序列号',
　　　　　　cdc_operation STRING COMMENT 'I 插入 U 更新 D 删除',
　　　　　　cdc_timestamp DATETIME COMMENT 'ODS源表数据同步时间',
　　　　　　cdc_before_image STRING COMMENT '是否更新前数据：Y 是 N 否',
　　　　　　cdc_after_image STRING COMMENT '是否更新后数据：Y 是 N 否',

第二步：

　　新建ods_full表（无分区），ods_full表没有分区，只存储第一次初始化的全量数据。

　　ods_full表需要在原表基础上新增如下字段：

　　　　　　cdc_record_id STRING COMMENT '唯一自增序列号',
　　　　　　cdc_operation STRING COMMENT 'I 插入 U 更新 D 删除',
　　　　　　cdc_timestamp DATETIME COMMENT 'ODS源表数据同步时间',
　　　　　　cdc_before_image STRING COMMENT '是否更新前数据：Y 是 N 否',
　　　　　　cdc_after_image STRING COMMENT '是否更新后数据：Y 是 N 否',

第三步：

　　dm_delta表（分区：日期，小时，分钟）第一次初始化的时候，从ods_full全量抽取数据，放到dm_delta表的19000101,00,00分区。

　　dm_delta表需要在原表基础上新增如下字段：

　　　　　　cdc_record_id STRING COMMENT '唯一自增序列号',
　　　　　　cdc_operation STRING COMMENT 'I 插入 U 更新 D 删除',
　　　　　　cdc_timestamp DATETIME COMMENT 'ODS源表数据同步时间',
　　　　　　cdc_before_image STRING COMMENT '是否更新前数据：Y 是 N 否',
　　　　　　cdc_after_image STRING COMMENT '是否更新后数据：Y 是 N 否',

第四步：

　　dm_full表（分区：日期）第一次初始化时先从ods_full表抽取全量数据，放到当前运行时的当天的日期分区中。

　　dm_full表需要在原表基础上新增如下字段：

　　　　　　cdc_record_id STRING COMMENT '唯一自增序列号',
　　　　　　cdc_operation STRING COMMENT 'I 插入 U 更新 D 删除',
　　　　　　cdc_timestamp DATETIME COMMENT 'ODS源表数据同步时间',
　　　　　　cdc_before_image STRING COMMENT '是否更新前数据：Y 是 N 否',
　　　　　　cdc_after_image STRING COMMENT '是否更新后数据：Y 是 N 否',

第五步：

　　第二天凌晨运行脚本，将dm_full表（分区：日期）将会以脚本运行时间的日期作为分区的日期，从dm_delta表中按id分组，用row_number() over()函数只取最大时间戳或最大记录id的那一条记录存放到dm_full当天分区中。也就是说：dm_full表20210901分区存放的是20210831 23:59:59时刻的快照数据。

 

第六步：

　　每个小时运行一次dm_delta表的抽取作业，将ods_delta表每小时的增量数据（包含增删改）抽取到dm_delta的小时分区中。

第七步：

　　构建dwd表，dwd表没有full表,delta表区分，就以dwd加表名称命名。只有一个分区。

第八步：

　　dwd每次运行会将dm_full表(当日分区）和dm_delta表（当天所有分区）做一个union all，不去重。

　　再从union all后的结果中根据id分组，用row_number() over()函数只取最大时间戳或最大记录id的那一条记录存放到dwd层表中去，每次运行都是覆盖dwd原先所有数据。