变化的数据处理和捕获

在数据爆发式增长的时代，记录数据变化和演变，探究内在规律并运用到生产实践中，驱动业务的增长成为这个时代主旋律。本文就如何记录数据变化，处理数据变化谈谈自己的理解

变化数据的存储

1.1. 覆盖重写

所要更改的属性，始终保持最新值，即覆盖重写，但是该技术破坏了历史情况。需要借助其他的方式才能进行处理，这点我们在本文下面会讲到。

1.2. 增加新行

当发生属性的变化时候，不修改原来的行，而是增加新的记录行。所以原先表的设计时候，主键更加需要具备一般意义的类型，因为会出现多行共同描述一个对象，共同描述一个对象的相同成员（属性）。采用这种方式最少需要三个额外的列：行有效的时间戳，行失效的时间戳，当前行的标识。

1.3. 增加新属性

对原先修改的值，不变。对新变化的值，采用新增一列，来记录。这种运用场合有限，eg，freeschema类型的数据库，且变化频度有限且低的场合。

1.4. 增加新表

增加新的表，用来记录变化。这种一般用在源表数据量大，且属性变化较快的表，新表要维护一个属性和源表的映射。优点是对源表无侵入性修改，对写是友好的。而查询需要连表查询，会有一定的影响

1.5. 增加新表，同时对源表进行重写

增加新的表，用来记录变化，同时对原表的需要修改的记录进行重写，即新表纯粹就是用来记录变化的历史，优点是对源表查询是只需要查询源表，写入速度会有一定影响

变化数据的捕获

在变化数据的存储一节中，我们谈到了对变化数据存储。从方式2-5都可以对历史进行捕获。如果一个系统对原先变化数据有处理需求，在系统设计之初可以参考上面的方式。从源头开始设计会对后面的数据处理带来极大便利。如果是现有系统，且设计之初没有考虑对变化数据的处理。可以借助下面几种方式。

2.1 增加标记位

在1.1的基础之上，增加一个行变化的有效标记位。让下游系统可以进行捕获。优点需要修改的地方较为简单：1.对数据库物理设计调整，2.现有应用系统的业务逻辑进行简单调整

update source_table 
set update_col=col_value,valid=1 
where pk_col=pk_col_value

需要考虑的地方：

1. 原系统相同记录俩次更新间隙，下游系统没有及时感知并捕获，更新操作如何处理？
   从尽量不对原先业务系统产生影响的设计原则考虑，更新操作正常进行，但是对于数据采集可能会丢失一部分的数据
2. 业务库写权限，开放给下游业务无关的系统（数据采集系统，为避免架构上的复杂性以及后期应对变化的扩展性能，一般设计为业务无关），会带来侵入式风险，即修改了标记位以外的列。
3. 从系统性能上考虑，下游系统去扫标记位，在现有RDBMS系统上没有对数据库性能产生影响的设计。现有基本可行的方式，1. 建立B+/-Tree索引，但是对于标记位值重复量大的不是一个友好设计。2. 建立bit-map索引。bit-map最适合重复值多的场景，但是会极大影响写入性能，适用于表修改频率不多的情况。3. 脱离就技术而论技术角度看，着眼与实际业务结合，这种没有普适原则，需要对每个业务系统进行分析，但是这违背了采集系统，需要适应并尽量减少接入成本的业务无关的原则。如果数据产生之初，接入之初都很难，那系统有极大的夭折可能性。好像生小孩也是这样？

2.2 使用现成数据库技术

2.2.1 ORACLE

方式1：ORACLE作为一个商用数据提供了，完整系统描述的元数据。通过读取元数据表来记录来查询所有的更改的操作。通过下面的语句

-- SQL_FULLTEXT操作的sql语句
-- COMMAND_TYPE 命令类型，2-insert，6-update，7-delete
-- ROWS_PROCESSED 影响的行数
-- last_load_time 最近一次执行的时间
-- first_load_time 第一次执行的时间
select  SQL_FULLTEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE,CPU_TIME，USER_IO_WAIT_TIME,
PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,last_load_time from V$SQL
Where SQL_FULLTEXT like '%TBL_TEST%' and COMMAND_TYPE in（2,6,7）and ROWS_PROCESSED>0

REF:ORACLE docs
方式2：利用表的触发器，通过每次写且触发触发器的动作完成更新动作的识别和解析。现有开源框架-databus，oracle的解析原理就是采用这种方式

2.2.2 SQLSEVER

sqlserver也有类似的表结构sys.dm_exec_sql_text
REF:SQL SERVER docs

oracle方式1，sqlserver的方式，利用这些方式的优点，1.完全重用现有技术，利用jdbc，select查询操作，就可以找到所有修改。2.保证库内扩展性同时，不对系统现有设计产生影响。因为对所有的表更新操作，都在v$sql中都可以找到，不需在接入数据时，对单个表进行重新设计和业务处理，所有更新查询都使用一套sql。缺点：1.需要不断轮训v$sql,延迟在秒，分钟级别。看系统设置。2. 需要v$sql的权限，一般是管理员权限。
oracle 方式2的缺点，触发器使用会增加系统的开销，影响系统的吞吐量，特别是在频繁的更新（update，insert，delete）情况。触发器使用需要对表做谨慎评估

2.3 使用日志完成

2.3.1 简单解析型-MySQL

借助binlog的明文日志，需要设置下面俩个选项

set binlog_rows_query_log_events=1
set binlog_format=ROW
在my.cnf中配置
log-bin=binlog的目录和binlog文件前缀

所有更新的操作都会明文打印到log-bin设置的文件下。借助kafka connector-filesystem source 将binlog明文sql传输到kafka中。

2.3.2 复杂解析型-MYSQL

通过对数据库日志的挖掘，完成解析,现有的开源框架OpenReplicator，databus，mysql的解析就是借助OpenReplicator，完成对binlog的解析。

上述俩种方式的共同优点，只需要要开启binlog打印，对系统负担小，下游程序不会对现有系统产生冲击此外，使用简单型日志，还有解析明文sql，由于采用sql的通用标准，解析程序具有较好的通用性，对于后期维护负担小，而复杂解析型SQL，随着软件版本的升级binlog的解析也需要不断升级，后续维护成本较高

落地方案设计

在变化数据的捕获一节中，我们对事前没有考虑存储历史变更的情况，如何捕获变化数据做了分享。综合上面几种方式的优缺点，

1. 针对SQL Server&Oracle，做一个存储过程（v$sql的读权限，如果需要夸库或者机器可以以service application方式提供，连接使用jdbc），放到数据库定时调度任务，将数据写入到history_log表中，将history_log开方出读权限供，下游系统采用kafka connector jdbc source 进行连接，接入到kafka，需要记录上次读取的offset，history_log 表设计，如下

table schema:fino_id,sql_fulltext,exec_time,command_type
fino_id:auto-inc
sql_fulltext:执行更新的sql脚本
exec_time:执行时间
command_type:sql语句类型

1. 针对mysql，则较为简单，在binlog本地磁盘部署kafka connector localfilesource，将binlog日志传输到kafka。

小结：采用这种方案主要是基于以下几点考虑：

1. 基于客户方敏感的数据库权限要求，和客户方对关系型数据库运维技术沉淀，保证数据源头的稳定性
2. 上下游系统弱依赖，即使下游系统出现问题，源头的数据还是存在，和持续生产的。实现源头数据较强容错
3. 可以做到较强的扩展性，在库内以及不同数据库产品（特指sql server和oracle）不用针对单个表，做单独业务设计。降低接入成本。
4. 数据的ETL可以放到数据平台进行统一清洗和挖掘。
5. history_log,采用IOT表，读写请求都转化为顺序读写，实现了较高的读写性能