Hive 拉链表实例

一、业务说明场景用户会员等级维度拉链表生效规则[start_dt, end_dt)永久有效 9999-12-31每日凌晨跑T 日更新 T-1 日变更数据下面是关于T日的介绍各位可以参看PS.介绍开始前提只算交易日周一周五开盘周末 / 节假日跳过15:00 是基金 / 理财分界点一、先统一基础定义表格符号含义通俗理解T 日有效下单日15 点前正式算交易的当天T-1 日T 的前一个交易日昨天收盘日T1 日T 的后一个交易日下个开盘日T2 日T 往后两个交易日再往后一天二、分场景规则对照表1. 银行理财 / 转账 / 大额存单 / 赎回取现表格业务T 日T1T2普通跨行转账工作日白天当天受理一般实时 / 当日到极少延 T2理财申购15 点前登记日开始计息 / 确认部分固收 T2 显示收益理财赎回常规固收提交赎回份额确认资金到银行卡常见 T2定期到期到期日 T本息自动回活期可直接取现银行重点理财赎回很多是T 赎回 → T2 拿钱2. 公募基金场外支付宝 / 微信 / 银行买的普通基金表格操作T 日 (15 点前)T1T2申购买入按 T 日净值计价确认份额、开始算收益持仓显示完整赎回卖出偏股 / 混合提交赎回净值结算资金到账银行卡主流 T2货币基金余额宝类T 买入T1 开始计息收益到账显示15 点后下单顺延为下一个交易日当 T往后全部顺延—基金口诀三点前算今天 T三点后算明天 T股票基金赎回大多 T2 到钱3. 可转债A 股场内打新 / 买卖 / 转股 / 中签表格业务T 日T1T2可转债打新申购T 日申购T1 摇号配号T2 公布中签结果中签缴款T2 中签日当天收市前留足资金T3 扣款入账—场内买入转债股票账户T 日买入成交T1 可以卖出和股票一样 T1可转股、套利转债转股操作T 日申请转股T1 变成正股股票T1 即可卖出正股转债卖出回款T 日卖出资金可用买别的T2 资金可取现到银行卡转债重点打新「T 申购→T2 中签」买卖「T 买 T1 卖」卖钱 T2 提现三、高频易错一句话总结所有场景周末节假日不算、15 点决定算不算今天 T银行理财 / 场外基金赎回大多是T 赎回T2 拿钱可转债打新记死「T 申、T2 中签」股票 / 转债买卖T 买 T1 能卖卖出钱 T 可用、T2 能提现PS.介绍结束以下为正文内容二、1. 建表语句拉链主表 增量临时表-- 1. 用户会员拉链主表ORC、分区/索引思路、标准拉链字段 CREATE DATABASE IF NOT EXISTS dw; USE dw; DROP TABLE IF EXISTS dw_user_level_zip; CREATE TABLE dw_user_level_zip ( user_id STRING COMMENT 用户唯一ID, user_name STRING COMMENT 用户名, member_level STRING COMMENT 会员等级:普通/银卡/金卡/钻石, charge_money DECIMAL(18,2) COMMENT 累计充值, start_dt DATE COMMENT 本条记录生效日期(包含), end_dt DATE COMMENT 本条记录失效日期(不包含), is_current TINYINT COMMENT 1当前有效 0历史失效, dw_insert_dt DATE COMMENT 数据入库日期 ) COMMENT 用户会员等级-拉链维度表 STORED AS ORC TBLPROPERTIES ( orc.compressSNAPPY, hive.merge.mapfilestrue, hive.support.concurrencytrue, hive.txn.managerorg.apache.hadoop.hive.ql.lockmgr.DbTxnManager ); -- 2. 每日增量临时表存放T-1日变更数据 DROP TABLE IF EXISTS tmp_user_level_delta; CREATE TABLE tmp_user_level_delta ( user_id STRING, user_name STRING, member_level STRING, charge_money DECIMAL(18,2) ) COMMENT 用户会员每日变更增量临时表;三、2. 首次全量初始化脚本基线数据把源头当前最新全量灌入拉链表全部设为永久有效-- 清空历史 TRUNCATE TABLE dw_user_level_zip; -- 全量初始化 INSERT INTO dw_user_level_zip SELECT user_id, user_name, member_level, charge_money, 2026-01-01 AS start_dt, -- 统一基线起始日 9999-12-31 AS end_dt, -- 永久有效 1 AS is_current, CURRENT_DATE AS dw_insert_dt FROM ods.ods_user_member_full; -- 你的源头全量表四、3. 每日增量调度脚本核心・直接调度变量说明调度平台传入biz_dt业务日期跑 T 日任务biz_dt T-1例2026-04-01 跑任务biz_dt2026-03-31新数据生效日 2026-04-01USE dw; -- 步骤1加载当日增量T-1日变更/新增用户 TRUNCATE TABLE tmp_user_level_delta; INSERT INTO tmp_user_level_delta SELECT user_id, user_name, member_level, charge_money FROM ods.ods_user_member_inc WHERE dt ${biz_dt}; -- 调度传参前一日日期 -- 步骤2拉链闭环保留不变关闭旧记录新增当前记录 INSERT OVERWRITE TABLE dw_user_level_zip -- ① 未发生变化的【当前有效旧数据】直接保留 SELECT user_id,user_name,member_level,charge_money, start_dt,end_dt,is_current,dw_insert_dt FROM dw_user_level_zip WHERE is_current 1 AND user_id NOT IN (SELECT user_id FROM tmp_user_level_delta) UNION ALL -- ② 发生变化的用户旧记录截止到biz_dt置为失效 SELECT user_id,user_name,member_level,charge_money, start_dt, ${biz_dt} AS end_dt, -- 旧数据失效日前一日 0 AS is_current, dw_insert_dt FROM dw_user_level_zip WHERE is_current 1 AND user_id IN (SELECT user_id FROM tmp_user_level_delta) UNION ALL -- ③ 插入新的当前有效记录生效日biz_dt1永久有效 SELECT user_id, user_name, member_level, charge_money, DateAdd(${biz_dt},1) AS start_dt, 9999-12-31 AS end_dt, 1 AS is_current, Current_Date AS dw_insert_dt FROM tmp_user_level_delta;五、4. 常用查询模板直接用① 查当前最新全量报表 / 日常分析SELECT * FROM dw_user_level_zip WHERE is_current 1;② 回溯任意历史时间点比如看 2026-02-15 当时状态SELECT * FROM dw_user_level_zip WHERE start_dt 2026-02-15 AND end_dt 2026-02-15;③ 查单个用户全生命周期变更记录SELECT * FROM dw_user_level_zip WHERE user_id U10086 ORDER BY start_dt ASC;六、5. 配套规范 踩坑说明严禁物理删除拉链只改 end_dt、加新数据不删历史空值统一处理等级 / 金额为空先在 ods 层兜底避免拉链错乱Hive 必须开事务否则 overwrite 会并发脏数据长期累积数据可按 end_dt 归档冷数据到历史分区表提升查询速度新增字段直接加在拉链表末尾初始化 增量同步都带上即可