解决方案:RDBMS变化数据设计，采集和接入大数据平台

　　解决方案:RDBMS变化数据设计，采集和接入大数据平台

　　数据处理和捕获的变化

　　在数据爆炸式增长的时代，记录数据的变化和演变，探索其内在规律并应用到生产实践中，驱动业务增长成为这个时代的主旋律。这篇文章说说我自己对如何记录数据变化和处理数据变化的理解

　　更改数据的存储 1.1。覆盖和重写

　　待改变的属性始终保持最新的值，即覆盖重写，但这种技术破坏了历史情况。需要通过其他方式进行处理，我们将在本文下面讲到。

　　1.2. 添加新行

　　当发生属性更改时，不会修改原创行，而是添加新的记录行。所以在设计原表的时候，主键需要有一个通用的含义类型，因为会有多行描述一个对象，描述一个对象的相同成员（属性）。这种方法至少需要三个额外的列：行的有效时间戳、行的无效时间戳和当前行的标识符。

　　1.3. 添加新属性

　　之前修改的值保持不变。对于新更改的值，使用一个新的列来记录。这种应用是有限的，例如，freeschema 类型的数据库，并且更改的频率有限且低。

　　1.4. 添加新表

　　添加一个新表来记录更改。这种类型一般用于源表中数据量大，属性变化快的表。新表需要维护属性和源表之间的映射。优点是对源表没有侵入式修改，写起来友好。而且查询需要加入表查询，会有一定的影响

　　### 1.5。添加新表，同时重写源表

　　新增一个表来记录变化，同时重写原表中需要修改的记录，即新表纯粹是用来记录变化历史的。好处是只需要查询源表，写入速度会有一定的影响

　　捕获变化的数据

　　在存储变化的数据部分，我们讨论了存储变化的数据。可以从方法 2-5 中捕获历史记录。如果一个系统对原来变化的数据有处理要求，可以在系统设计之初就参考上面的方法。从源头开始设计，将大大方便后续的数据处理。如果是已有的系统，在设计之初没有考虑到变化数据的处理。您可以使用以下方法。

　　2.1 增加标记位

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

　　update source_table

<p>

set update_col=col_value,valid=1

where pk_col=pk_col_value</p>

　　需要考虑的事项：

　　原系统也记录了两次更新之间的间隙，只是下游系统没有及时检测和捕捉。如何处理更新操作？考虑到尽量不影响原有业务系统的设计原则，更新操作正常进行，但对于数据采集可能会失去部分数据业务数据库写权限，对下游开放业务无关系统（数据采集系统，为了避免架构的复杂性和后期应对变化的扩展性能，一般设计成业务无关的），会带来侵入性风险，即修改标志位以外的列。从系统性能上看，下游系统不扫描标记位，并且在现有的RDBMS系统上没有影响数据库性能的设计。基本上有可行的办法， 1. 建立B+/-Tree索引，但是对于tag位的重复值来说不是一个友好的设计。2. 创建位图索引。位图最适合重复值较多的场景，但会极大影响写入性能，适用于表修改频率不高的情况。3、从技术上看，注重与实际业务的结合，没有通用的原则，需要对每个业务系统进行分析，但这违反了采集体系，需要适配和最小化. 投入成本与业务无关的原则。如果在数据生成和访问之初困难重重，系统过早失效的可能性很大。跟生孩子一样吗？2.2 使用现成的数据库技术 2.2.1 ORACLE

　　方式一：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

　　方式二：使用表的触发器，通过每次写入和触发触发器的动作来完成对更新动作的识别和分析。现有的开源框架——databus，oracle的解析原理就是使用这种方法

　　2.2.2 SQL服务器

　　sqlserver也有类似的表结构sys.dm_exec_sql_text

　　参考资料：SQL SERVER 文档

　　Oracle方法一、sqlserver方法，利用这些方法的优点， 1.完全复用现有技术，使用jdbc，select查询操作，可以找到所有的修改。2、在保证库内可扩展性的同时，不影响系统现有的设计。因为所有的表更新操作都可以在v$sql中找到，所以访问数据时不需要对单个表进行重新设计和处理，所有的更新查询都使用一套sql。缺点：1、需要不断训练v$sql，延迟在秒级或分钟级。查看系统设置。2.需要v$sql权限，一般是administrator权限。

　　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的解析需要随着软件版本的升级不断升级，后续维护成本高。

　　落地方案设计

　　在捕获变更数据的部分，我们分享了如何在不考虑事先存储历史变更的情况下捕获变更数据。综合以上方法的优缺点，

　　对于SQL Server&amp;Oracle，做一个存储过程（v$sql的读权限，如果需要扩展数据库或者机器可以提供服务应用，使用jdbc连接），放到数据库中调度任务，写数据写入history_log表，history_log开启读权限，下游系统使用kafka connector jdbc源连接。连接kafka时，需要记录最后读取的offset，history_log表设计如下： table schema:fino_id,sql_fulltext,exec_time,command_typefino_id:auto -incsql_fulltext:执行更新的sql脚本 exec_time:执行时间command_type: sql语句类型对于mysql来说比较简单，在binlog本地盘部署kafka connector localfilesource，将binlog日志传输到kafka。

　　总结：采用该方案主要是基于以下考虑：

　　基于客户敏感的数据库权限需求，结合客户对关系数据库运维技术的积累，保证数据源的稳定性，弱依赖上下游系统。即使下游系统出现问题，源数据依然存在，并继续生产。实现源数据的强容错，可以实现强大的可扩展性，不需要针对库中的单表和不同的数据库产品（具体是SQL Server和Oracle）做单独的业务设计。降低接入成本。数据ETL可以放在数据平台上进行统一清洗和挖掘。history_log，使用IOT表，将所有读写请求转换为顺序读写，

　　采集工具 汇总:商务数据分析李萍

　　行情数据采集，其操作步骤和关键节点如下：

　　第 1 步：确定数据源。

　　百度搜索和360搜索是目前国内用户量比较大的两个平台。以百度为例。作为全球最大的中文搜索引擎，其提供的索引工具基于百度搜索数据，因此数据具有较高的参考性，可以使用智能门锁相关的百度索引数据关键词作为数据采集来源。

　　第 2 步：确定 采集 指标。

　　本任务中的数据索引为相关关键词的搜索索引，索引数据按日期显示。因此，本着综合性和选择性的原则，有必要对采集采集关键词指数和对应日期两个指标进行。

　　第 3 步：确定 采集 范围。

　　该任务需要过去三年的 采集 市场趋势数据，因此根据 采集 三年后 采集 在 采集 开始 采集

　　第 4 步：导出屏幕截图

　　在百度指数中搜索相关关键词（如智能锁、电子锁等），选择时间段，获取相应数据，将截图粘贴到文档中。