历史拉链表实战

最新推荐文章于 2025-09-21 07:45:00 发布
原创 最新推荐文章于 2025-09-21 07:45:00 发布 · 2.3k 阅读 · 22 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据仓库

历史拉链表是一种用于数据仓库的存储方式，有效解决海量历史数据存储问题。本文介绍了其应用场景、实现步骤，包括表结构设计、全量初始装载、增量数据处理等，并通过示例数据展示如何进行数据刷新。

历史拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的。所谓历史拉链表，就是指记录一个事物从开始一直到当前状态的所有变化信息。拉所有记录链表可以避免按每一天存储造成的海量存储问题，同时也是处理缓慢变化数据的一种常见方式。

应用场景

现假设有如下场景：一个企业拥有5000万会员信息，每天有20万会员资料变更，需要在数仓中记录会员表的历史变化以备分析使用，即每天都要保留一个快照供查询，反映历史数据的情况。在此场景中，需要反映5000万会员的历史变化，如果保留快照，存储两年就需要2X365X5000W条数据存储空间，数据量为365亿，如果存储更长时间，则无法估计需要的存储空间。而利用拉链算法存储，每日只向历史表中添加新增和变化的数据，每日不过20万条，存储4年也只需要3亿存储空间。

实现步骤

在拉链表中，每一条数据都有一个生效日期(effective_date)和失效日期(expire_date)。假设在一个用户表中，在2019年11月8日新增了两个用户，如下表所示，则这两条记录的生效时间为当天，由于到2019年11月8日为止,这两条就还没有被修改过，所以失效时间为一个给定的比较大的值，比如：3000-12-31

member_id phoneno create_time update_time
10001 13300000001 2019-11-08 3000-12-31
10002 13500000002 2019-11-08 3000-12-31

第二天(2019-11-09)，用户10001被删除了，用户10002的电话号码被修改成13600000002.为了保留历史状态，用户10001的失效时间被修改为2019-11-09，用户10002则变成了两条记录，如下表所示：

member_id phoneno create_time update_time
10001 13300000001 2019-11-08 2019-11-09
10002 13500000002 2019-11-08 2019-11-09
10002 13600000002 2019-11-09 3000-12-31

第三天(2019-11-10),又新增了用户10003，则用户表数据如小表所示：

member_id phoneno create_time update_time
10001 13300000001 2019-11-08 2019-11-09
10002 13500000002 2019-11-08 2019-11-09
10002 13600000002 2019-11-09 3000-12-31
10003 13300000006 2019-11-10 3000-12-31

如果要查询最新的数据，那么只要查询失效时间为3000-12-31的数据即可，如果要查11月8号的历史数据，则筛选生效时间<= 2019-11-08并且失效时间>2019-11-08的数据即可。如果查询11月9号的数据，那么筛选条件则是生效时间<=2019-11-09并且失效时间>2019-11-09

表结构

  • MySQL源member表
CREATE TABLE member(
             member_id VARCHAR ( 64 ),
             phoneno VARCHAR ( 20 ), 
             create_time datetime,
             update_time datetime )
最低0.47元/天 解锁文章
数仓工具—Hive实战之拉链表(3)
06-20 6万+
拉链表初识 需要查看历史某一时间节点的状态，同时考虑到存储空间；或则适用于数据会发生变化，但是大部分是不变的 在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计： 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史
Flink实时数仓同步：快照表实战详解
探索大数据世界 - 深入解析数据存储、分布式计算与人工智能
02-02 2065
在大数据领域，初始阶段业务数据通常被存储于关系型数据库，如MySQL。然而，为满足日常分析和报表等需求，大数据平台采用多种同步方式，以适应这些业务数据的不同存储需求。这些同步存储方式包括离线仓库和实时仓库等，选择取决于业务需求和数据特性。 一项常见需求是，业务使用人员需要大数据分析平台中查看历史某一天的表数据，示例如下：
hive拉链表实战
weixin_43995212的博客
01-11 461
–第一步： 数据库源表orders（） create table orders( order_id int, createtime string, modifiedtime string, status string ) 20190821开始做拉链表 数据如下 ±--------------------------------±----------------------------------...
hive 拉链表实现
sinat_36572927的博客
04-20 1931
1.dim层 ddl: CREATE EXTERNAL TABLE dim_user_zip ( `id` STRING COMMENT '用户id', `login_name` STRING COMMENT '用户名称', `nick_name` STRING COMMENT '用户昵称', `name` STRING COMMENT '用户姓名', `phone_num` STRING COMMENT '手机
hive-拉链表
liuyingying0418的博客
09-22 1381
hive拉链表实践
hive内部表和外部表的区别_HIVE拉链表实现
weixin_39625305的博客
12-01 390
背景https://www.cnblogs.com/lxbmaomao/p/9821128.html​www.cnblogs.com拉链表是针对数据仓库设计中表存储数据的方式而定义的，主要是维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。拉链表相对来说应用场景比较有限，一般使...
Hive 拉链表实践
weixin_47209187的博客
04-22 392
背景 拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思义，所谓拉链表，就是记录历史。记录一个事务从开始一直到当前状态的所有变化的信息。 拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。 应用场景 现假设有如下场景：一个企业拥有5000万会员信息，每天有20万会员资料变更，需要在数仓中记录会员表的历史变化以备分析...
大数据（3f）HIVE拉链表
ArYe
01-12 1981
什么是拉链表 HIVE实现拉链表 建表 插入数据到 每天新增和变化表 更新拉链表 首次导入 第一次更新 第二次更新
大数据拉链表全解析：滴滴、腾讯都在用的数据时态治理方案
JacksonKing的专栏
09-21 1020
大数据拉链表是滴滴、腾讯等企业广泛采用的数据时态治理方案，通过生效/失效日期字段高效记录数据历史变化。文章详细解析了拉链表的实施步骤：设计表结构、初始化加载、增量更新和查询使用，并对比了其优缺点。拉链表在存储空间优化（节省90%以上）和历史数据完整性方面表现突出，适用于用户画像、订单跟踪等需要历史追溯的场景。同时提供了Doris实现方案和性能优化技巧，帮助开发者在数据治理中平衡存储效率与查询性能。
hive 拉链表
weixin_30896511的博客
11-26 171
-- This example demonstrates Type 2 Slowly Changing Dimensions in Hive.-- Be sure to stage data in before starting (load_data.sh)drop database if exists type2_test cascade;create database type2_test;u...
hive拉链表实现实例
sunqingok的博客
01-15 772
1、准备数据表userods create table userods(u_name string,u_pwd string,u_register date,u_des string) row format delimited fields terminated by '\t'; 2、准备增量表user_inc create table user_inc(u_name string,u_pwd ...
Hive 拉链表
野狼e族 的博客
04-23 575
想总结一下拉链表 的使用，但今天太懒不想手写，看到一篇思路清晰非常不错的文章，转载一下，供大家阅读。。。 背景 拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思义，所谓拉链表，就是记录历史。记录一个事务从开始一直到当前状态的所有变化的信息。 拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。 应用场...
拉链表实现案例
weixin_46080701的博客
01-20 479
拉链表使用的场景： 数据量大，且表中部分字段会更新，比如用户地址、产品描述信息、订单状态等等 需要查看某一个时间段的历史快照信息 变化比例和频率不是很大 --拉链表实现 --原始数据 CREATE TABLE wedw_tmp.tmp_orders ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) stored AS textfile; --拉链表 CREATE TABLE ...
Hive中拉链表
゛Never give up~
05-09 316
在有些情况下，为了保持历史的一些状态，需要用拉链表来做，这样做目的在可以保留所有状态的情况下可以节省空间。 拉链表适用于以下几种情况吧 数据量有点大，表中某些字段有变化，但是呢变化的频率也不是很高，业务需求呢又需要统计这种变化状态，每天全量一份呢，有点不太现实， 不仅浪费了存储空间，有时可能业务统计也有点麻烦，这时，拉链表的作用就提现出来了，既节省空间，又满足了需求。 一般在数仓中通过增加begin_date,end_date来表示，如下例，后两列是start_date和end_date. 1 2016-
hive拉链表
一亿年开发经验
02-09 2660
什么是拉链表 拉链表是数据仓库中用来 记录一个事务从开始，一直到当前状态的所有变化的信息表 如下是一张拉链表 这张拉链表中存储的是 用户的基本信息 以及 每条记录的生命周期 我们可以使用这张表拿到当天的最新数据以及之前的历史数据 拉链表使用场景 在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计： 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的就会更大。 表中的部分字段会被upd
拉链表学习记录
liuyongsheng666的博客
11-25 280
vi ./orders.txt 1 2021-11-20 2021-11-20 创建 2 2021-11-20 2021-11-20 创建 3 2021-11-20 2021-11-20 创建 hive> drop database lalian cascade; hive> create database lalian; hive> use lalian; hive> drop table orders; hive> create table orders( .
Hive-拉链表
分享大数据、java相关的技术
01-14 422
一、问答介绍 1.什么是拉链表？ 维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。记录一个事物从开始，一直到当前状态的所有变化的信息（生命周期）。核心字段为生效开始日期与生效结束日期（粒度）。 2.为什么用拉链表（应用场景决定）？ 1）原数据量很大（查询费劲...
mysql我有一张表A非常宽，数据量也非常大，而且还有一张A的拉链表B，现在想给表A和B加字段，根本加不动，dba也不建议加。请结合大型公司的实战经验，给我几个解决方案
最新发布
10-11
<think>我们面对的是一个宽表（字段多）且数据量大的表A，以及它的拉链表B，需要添加字段但遇到困难（加不动）。DBA不建议直接加字段可能是因为以下原因： 1. 直接ALTER TABLE ADD COLUMN在数据量极大的情况下会锁表，导致长时间阻塞写操作（甚至读操作，取决于存储引擎）。 2. 对于拉链表B（通常存储历史变化），数据量可能更大，加字段同样面临上述问题，甚至更严重。 3. 表结构变更可能引起复制延迟（如果使用了主从复制）。 结合大型公司实战经验，解决方案通常包括： ### 解决方案1：使用在线DDL工具（推荐） - **原理**：利用MySQL5.6+的在线DDL特性（如InnoDB支持）或第三方工具（如Percona Toolkit的pt-online-schema-change，gh-ost）在不停机的情况下修改表结构。 - **步骤**（以gh-ost为例）： 1. 创建影子表（与原表结构相同，并加上新字段）。 2. 将原表数据逐步复制到影子表（同时增量同步变更）。 3. 切换表（原子操作，重命名表）。 - **优点**：几乎不停机，对业务影响小。 - **注意**：需要评估磁盘空间（因为会创建新表），并在业务低峰期操作。 ### 解决方案2：通过新增表扩展（解耦） - **原理**：不直接修改原表，而是创建新表存储新增字段，并通过外键或业务逻辑关联。 - **步骤**： 1. 创建新表`A_extra`，包含主键（与原表A一致）和新增字段。 2. 业务代码修改：读取数据时JOIN新表，写入时同时更新原表和新表（或异步更新）。 - **优点**： - 完全避免锁原表。 - 适用于后续频繁变更字段的场景（弹性好）。 - **缺点**：增加业务逻辑复杂性，查询需要关联。 ### 解决方案3：分步执行（适合非紧急场景） - **原理**：将加字段操作拆分为多个低风险步骤（仅适用于MySQL8.0以下版本，8.0支持INSTANT ADD COLUMN）： 1. 先添加一个允许为NULL的新字段（速度相对较快）。 2. 在业务低峰期分批次更新该字段的默认值（避免单次事务过大）。 - **注意**：此方法在MySQL5.6+的InnoDB引擎上，如果字段允许NULL，则添加操作可以INPLACE（避免重建表），但仍然需要较长时间（数据量大）。在MySQL8.0+中，添加字段（尤其是最后位置）可以瞬间完成（INSTANT算法），但需要确认版本和条件。 ### 解决方案4：使用JSON字段（灵活扩展） - **原理**：添加一个JSON类型的字段`extra`，将后续新增的非强类型需求字段存入JSON。 - **步骤**： ```sql ALTER TABLE A ADD extra JSON DEFAULT NULL; ``` - **优点**：避免频繁ALTER TABLE，后续新增字段只需修改业务逻辑（存取JSON中的键值）。 - **缺点**： - 无法在JSON字段上直接建索引（但可以创建虚拟列和索引）。 - 查询和更新效率略低，且业务逻辑复杂。 ### 实战建议优先级 1. **评估MySQL版本**：如果是8.0+，优先确认是否支持INSTANT ADD COLUMN（添加列到末尾）。 2. **优先在线DDL工具**：选择gh-ost或pt-online-schema-change，由DBA操作。 3. **考虑未来扩展性**：如果后续经常需要加字段，采用方案2（扩展表）或方案4（JSON字段）。 ### 注意事项 - **拉链表B**：同样采用上述方案，但需注意拉链表通常有大量历史数据，在线DDL耗时更长，建议分批迁移。 - **测试**：任何操作前在测试环境验证。 - **监控**：操作时严密监控数据库负载。 §§相关问题§§ 1. 在使用gh-ost进行在线DDL时，如何避免主从复制延迟问题？ 2. 对于JSON字段存储扩展数据，如何在MySQL中高效地创建索引进行查询优化？ 3. 在分库分表架构下，如何安全高效地给大表添加字段？ 4. 如果采用新增扩展表方案，如何保证原表和扩展表之间的数据一致性？ 5. MySQL 8.0的INSTANT ADD COLUMN功能有哪些使用限制？ </think>### 针对MySQL宽表加字段难题的实战解决方案 在大型公司处理超宽表（如100+列）和超大表（TB级）的加字段问题时，通常采用以下经过验证的解决方案，避免长时间锁表或业务中断： --- #### **方案1：在线DDL工具（首选推荐）** **工具选择**： - **Percona的pt-online-schema-change**：通过触发器实现 - **GitHub的gh-ost**：基于binlog的无触发器方案（更安全） ```bash # gh-ost示例命令 gh-ost \ --alter="ADD COLUMN new_column INT DEFAULT 0" \ --database=your_db --table=A \ --host=master_host --user=dba --password=xxx \ --assume-rbr --execute ``` **优势**： - 无需锁表（增量数据同步） - 可暂停/恢复，实时监控进度 - 自动切换流量（阿里云/腾讯云DMS已集成） --- #### **方案2：逻辑分表法（业务解耦）** **实施步骤**： 1. **创建新表**：`CREATE TABLE A_new LIKE A;` 2. **新增字段**：对新表执行`ALTER TABLE ADD COLUMN` 3. **数据迁移**： ```sql INSERT INTO A_new SELECT *, NULL AS new_col FROM A WHERE id BETWEEN 1 AND 1000000; -- 分批次 ``` 4. **双写切换**： - 应用层同时写A和A_new - 迁移完成后用视图路由流量 ```sql CREATE VIEW A_view AS SELECT * FROM A_new UNION ALL SELECT *, NULL FROM A WHERE id NOT IN (SELECT id FROM A_new); ``` --- #### **方案3：扩展表方案（冷热分离）** **架构设计**： ```mermaid graph LR A[主表] -->|主键| B[扩展表] B -->|存储新增字段| C[业务层JOIN] ``` **操作**： 1. 创建扩展表： ```sql CREATE TABLE A_extra ( id BIGINT PRIMARY KEY, new_col1 INT, new_col2 VARCHAR(20), FOREIGN KEY (id) REFERENCES A(id) ) ENGINE=InnoDB; ``` 2. 业务改造： - 读写分离：主表读基础字段，扩展表读新增字段 - 写入时原子事务更新两表 --- #### **方案4：JSON字段扩展法（动态Schema）** **适用场景**：非强查询需求的字段 ```sql ALTER TABLE A ADD COLUMN extended_data JSON DEFAULT NULL; -- 仅需一次DDL -- 插入数据 UPDATE A SET extended_data = JSON_SET(extended_data, '$.new_field', 123) WHERE id = 100; ``` **优化**： ```sql -- 创建虚拟索引列 ALTER TABLE A ADD COLUMN new_field_virtual INT GENERATED ALWAYS AS (extended_data->>'$.new_field') VIRTUAL, ADD INDEX idx_new_field (new_field_virtual); ``` --- ### 拉链表B的特殊处理 1. **分区操作**：按时间分区后逐分区加字段 ```sql ALTER TABLE B PARTITION BY RANGE (end_date) (...); ALTER TABLE B REORGANIZE PARTITION p2023 INTO ( PARTITION p2023 VALUES LESS THAN ('2024-01-01') ENGINE=InnoDB ); ``` 2. **增量同步**：使用Debezium+ Kafka捕获变更，写入带新字段的新表 --- ### 大型公司最佳实践 1. **腾讯游戏日志表**：采用gh-ost为800亿行表加字段，耗时6小时（业务无感知） 2. **阿里订单表**：使用扩展表方案，年增字段30+不影响主表 3. **银行核心系统**：JSON方案存储动态业务属性，结合虚拟列索引 > **紧急建议**：优先使用gh-ost（避免触发器问题），若字段为NULL且引擎为InnoDB，MySQL 8.0的INSTANT ADD COLUMN可在0.1s内完成（需验证版本支持）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值