当前位置：首页 > web >正文

大数据平台数仓数湖hive之拉链表高效实现

web 2025/8/3 12:48:11

对于缓慢变化的维度表，如客户表，员工表，为了不丢失历史数据，又不至于太浪费存储空间，我们采用拉链表实现。
实现过程如下：

1、采集初始数据：

1.1 从mysql导出数据到hdfs

/data/dolphinscheduler/loadMysqlTable.sh "${dbConnect}" "select ${slctColums} from loan.${tableName} t where update_time < date_add('${bizDate}', INTERVAL 1 DAY) or update_time is null" "/dmp/${DMP_DB}" "${srcSystem}" "${bizDate}"

关于loadMysqlTable.sh脚本可以看我的另一篇文章：
https://blog.csdn.net/weixin_45357522/article/details/149720803
其中，变量的示例：
${slctColums}：“t.id, t.name, t.age …”
${dbConnect}：“-h host1 -uroot -ppwd123”
${tableName}: “t_customer”
${bizDate}: “2025-07-30”
${srcSystem}: “crm”
${DMP_DB}: 数据中台的hive db name, 如：“rdm”

这里要注意的是sql语句加入一个条件，update_time < date_add(‘${bizDate}’, INTERVAL 1 DAY) or update_time is null，只把T-1以前更新的数据导出来，当时更新的数据要等到第二天才导出，以免重复导入，产生假的变更记录。

1.2 基于上传到hdfs的文件创建一个临时表

1.3 从临时表中过滤掉非法数据，如id为空的数据，加载到ODS

1.4 从ods表加载到dwd，并初始化valid_from = ‘1900-01-01’, valid_to = ‘2999-12-31’

insert overwrite table ${DMP_DB}.dwd_dim_${tableName}
SELECT ${slctColums}, '1900-01-01', '2999-12-31'
from ${DMP_DB}.ods_${srcSystem}_${tableName} t;

其中，${slctColums}的示例：t.id, t.name, t.age …
dwd表最后两个字段分别是valid_from, valid_to, 日期型
表结构示例：

CREATE TABLE cust.dwd_dim_loan_individual_customer (id varchar(32) COMMENT '个人客户id',code varchar(50) COMMENT '客户编号',name varchar(100) COMMENT '客户名称',status tinyint COMMENT '客户状态',id_type int COMMENT '证件类型 id_type',id_code varchar(22) COMMENT '证件号码',id_expire_date timestamp COMMENT '身份证有效期到',gender tinyint COMMENT '性别',age TINYINT COMMENT '年龄',valid_from date COMMENT '生效日期（含）',valid_to date COMMENT '失效日期（不含）'
)  COMMENT '个人客户'
STORED AS parquet 
location '/dmp/cust/dwd/dim/loan_individual_customer';

取数逻辑：

select ... from table1 
where valid_from <= current_date and current_date < valid_to

2、增量数据加载

2.1 从mysql导出数据到hdfs

/data/dolphinscheduler/loadMysqlTable.sh "${dbConnect}" "select ${slctColums} from loan.${tableName} t where update_time < date_add('${bizDate}', INTERVAL 1 DAY) and update_time >= '${bizDate}'" "/dmp/${DMP_DB}" "${srcSystem}" "${bizDate}"

其中，只取更新时间是T-1的数据，T日更新数据留到下一天处理。

2.2 基于上传到hdfs的文件创建一个临时表

2.3 从临时表中过滤掉非法数据，如id为空的数据，加载到ODS

2.4 从ods表加载到dwd

这步比较关键，有些逻辑：

insert overwrite table ${DMP_DB}.dwd_dim_${tableName}
SELECT ${slctColums}, valid_from,
CASE WHEN t.valid_to = to_date('2999-12-31') AND B.id IS NOT NULLTHEN date_add('${bizDate}', 1)  -- 有新记录匹配到了，把valid_to设置为下一天ELSE t.valid_to  -- 没有新记录匹配到，valid_to不变END AS valid_to
FROM cust.dwd_dim_loan_individual_customer t
LEFT JOIN cust.ods_yecai_loan_individual_customer AS b
ON t.id = b.id where valid_from != date_add('${bizDate}', 1) -- 重跑这个条件用于滤除当天先前加入的数据
UNION all -- 把新记录加进去
SELECT ${slctColums},date_add('${bizDate}', 1) AS valid_from, to_date('2999-12-31') AS valid_to
FROM ${DMP_DB}.ods_${srcSystem}_${tableName} AS t