本文详细介绍了如何在SQLServer中实现增量数据采集，利用CDC（Change Data Capture）技术跟踪数据库变化，并结合Flink进行数据集成。同时，文章探讨了SQLServer的批量数据导入方法，包括JDBC批量导入和bcp工具的使用，强调了bcp工具在批量导入时的高效稳定特性。
分享须知
分享主题：SQL Server 增量数据采集与批量数据导入
分享内容：
01. SQL Server 增量数据采集
02. SQL Server 批量导入
基础储备：Java，SQL Server，Flink 相关知识或经验
环境准备：
01. SQL Server 2008 版本以上数据库
02. SQL Server SMMS 管理客户端（演示增量数据采集），下载载地址: https://aka.ms/ssmsfullsetup
03. SQL Server BCP 客户端（SQL Server命令客户端，演示批量导入）
1. SQL Server 增量数据采集
在客户项目现场，我遇到了需要增量抓取SQL Server数据的需求，这种需求主要描述了捕获数据库插入、删除、变更的项，用以跟踪表数据改变。
其实这种需求在很多数据库中广泛存在，一般有两种实现增量获取的方案，一种是通过数据库数据查询，where条件过滤来实现；一种是通过数据库日志的方式来实现；前者会直接的操作数据库，对于实时性的需求往往难以实现，并且可能会影响服务器性能，后一种由于是基于日志的方式，不会直接操作数据库的数据，因此性能会高于基于查询的方案。
基于查询的方案呢，需要会对表进行独特的设计，例如增加时间或其他标志字段，进行对比查询，对于表的增、改实现还比较容易，对于删除的情况，就会难以实现；
基于日志的方案呢，因为增、删、改都会落地的日志，因此可以实现增量抓取，不过一般需要表设置主键，例如Mysql基于binlog日志来实现增量数据抓取，阿里开源的Cannal中间件就基于此实现，经过实际使用体验不错；Oracle利用Logminer来实现增量抓取，例如OGG组件就可以实现增量数据抓取，不过它是收费组件，经过实际使用体验较差；而在SQL Server中，就可以使用CDC 来实现增量抓取，它是SQL Server数据库自带组件，实际使用体验不错。
1. 1 SQL Server CDC 原理
CDC，即Change Data Caputure，顾名思义就是变化数据抓取，SQL Server的CDC原理图如下：
具体步骤描述如下：
01. 开启CDC后，插入、更新和删除应用于跟踪的源表时，SQL Server将会把变更的数据写入log日志中；
02. capture process进程读取log日志到单独的change tables 变化表中；
03. SQL Server会生成CDC查询函数来对变化的数据进行读取解析（可以理解为log日志解析接口）；
04. 最后将抓取到数据应用于其他应用或保存到数据库；
1. 2 SQL Server CDC 实践
本次分享会的SQL Server服务器版本为 Microsoft SQL Server 2017，今天CDC增量数据采集的内容包括：
01. 配置CDC，需要管理员权限，先开启库的CDC功能，再开启表的CDC功能；
02. 集成Flink获取增量数据
1.2.1 配置SQL Server CDC
配置SQL Server CDC主要分以下几个步骤：
-
检查SQL Server版本
select @@version; -
创建新的数据库，或在原有数据库上启用CDC
create database class; -
数据库启用CDC功能
if exists(select 1 from sys.databases where name='class' and is_cdc_enabled=0) begin exec sys.sp_cdc_enable_db end; select is_cdc_enabled from sys.databases where name='class'; -
创建测试用表，开启CDC的表需要指定主键，不开启CDC的表则不需要指定主键；
use class; create table student( sid int primary key, sname varchar(30) ); create table score ( sid int primary key, score int ); -
表启用CDC功能，目前只能一张表一张表的启用，如果想启用所有表，需要写脚本通过循环遍历执行实现；具体启用，指定schema、指定table名称、cdc实例名称、角色名称等；详细参数见：sp_cdc_enable_table ;
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='student' AND is_tracked_by_cdc = 0)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', -- source_schema
@source_name = 'student', -- table_name
@capture_instance = NULL, -- capture_instance
@supports_net_changes = 1, -- supports_net_changes
@role_name = NULL -- role_name
END;
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='student';
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='score' AND is_tracked_by_cdc = 0)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', -- source_schema
@source_name = 'score', -- table_name
@capture_instance = NULL, -- capture_instance
@supports_net_changes = 1, -- supports_net_changes
@role_name = NULL -- role_name
END;
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='score';
- 开启成功后，发现会多出来以下表，这些表就记录了CDC表的变更记录，后面会一一解析；
-
如果表结构改变，需要重新启动CDC
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = 'student', @capture_instance ='all'; IF EXISTS(SELECT 1 FROM sys.tables WHERE name='student' AND is_tracked_by_cdc=0) BEGIN EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', -- source_schema @source_name = 'student', -- table_name @capture_instance = NULL, -- capture_instance @supports_net_changes = 1, -- supports_net_changes @role_name = NULL -- role_name END;
1.2.2 CDC变更表
数据库、数据库的表启用CDC后，捕获变化的表就创建了，路径在tables/system tables，
select * from [cdc].[captured_columns];
select * from [cdc].[change_tables];
select * from [cdc].[dbo_score_CT];
select * from [cdc].[dbo_student_CT];
select * from [cdc].[ddl_history];
select * from [cdc].[index_columns];
select * from [cdc].[lsn_time_mapping];
select * from [dbo].[systranschemas];
每个表内容如下：
-
captured_columns 表示CDC表中哪些列被跟踪
-
change_tables 记录CDC跟踪表实例的详细信息，相当于一个表的CDC实例
-
dbo_ ${ table_name }_CT 捕获实例表所有DML操作记录
__$start_lsn: 提交事务日志序列号（Log Sequence Number） __$end_lsn: 此列始终为NULL __$seqval: 更改事务中的行顺序 __$operation: 源表DML操作 1 =删除 2 =插入 3 =更新（旧值） 4 =更新（新值） __$update_mask: 基于更改表的列号的位掩码，用于标识那些更改的列 __$command_id: 跟踪事务中的操作顺序- 对于主键进行的更新，会留下两个记录：主键的删除+主键的插入，对于非主键的更新，会留下更新前和更新后两条记录。
- update_mask字段的值表示发生变更的字段序列。这是一个以16进制表示的数字，在进行对修改字段进行判断的时候需要将其转换成2进制。例如更新操作对应的__$update_mask值为0x04，转化成2进制就是100，这三位分别代表3个字段，不过这里的顺序是从右到左，1表示改变，0则表示保持不变。
-
ddl_history 记录所有源表DDL变更
-
index_columns 每个CDC实例表的索引列
-
lsn_time_mapping 日志序列号的时间，即事务时间与日志的映射关系表
-
systranschemas schema发生变更的记录
以及cdc获取函数，路径在programmability/functions/table-valued functions -
fn_cdc_get_all_changes_dbo_ 通过事务的时间筛选所有变更记录
-----------插入 insert into dbo.score values (1,100);--2 :插入 select * from [cdc].[dbo_score_CT]; insert into dbo.score values (2,200); select * from [cdc].[dbo_score_CT]; select * from [cdc].[fn_cdc_get_all_changes_dbo_student](0x0000002600000348001C,0x00000026000003F00003,'all') ----------删除 delete from dbo.score where sid=2; select * from [cdc].[dbo_score_CT]; --1：删除操作 ----------更新 update dbo.score set score=1000 where sid=1; select * from [cdc].[dbo_score_CT]; --1：更新操作 -
fn_cdc_get_net_changes_dbo_ 查询净变更记录，所谓的净变更记录既是最后一次DML操作后源表的记录
update dbo.score set score=100 where sid=1; update dbo.score set score=1000 where sid=1; update dbo.score set score=10000 where sid=1; select * from [cdc].[dbo_score_CT]; select * from [cdc].[fn_cdc_get_all_changes_dbo_score](0x00000026000006B80003,0x00000026000006E80003,'all') ; select * from [cdc].[fn_cdc_get_net_changes_dbo_score](0x00000026000006B80003,0x00000026000006E80003,'all') ;
1.2.3 SQL Server CDC与Flink集成
Flink CDC Connectors 是 Apache Flink 的一组源连接器，是可以从 MySQL、PostgreSQL 数据直接读取全量数据和增量数据的 Source Connectors，开源地址：flink-cdc-connectors。
1.2.3.1 Flink SQL Server CDC原理
Flink SQL Server CDC 插件的原理，就是利用fn_cdc_get_all_changes_dbo_抓取数据，官方描述如下: How the SQL Server connector works，具体源码可以阅读类SqlServerStreamingChangeEventSource的execute()方法。CDC抓取流程如下:
01. 初始化SQL Server链接和参数
02. 查询数据库当前LSN
03. 与当前记录LSN对比，如果相等休眠一段时间，如果不相等，调用fn_cdc_get_all_changes_dbo_函数查询变更数据，解析后送入下游处理，更新LSN
04. 轮询上一步操作
1.2.3.1 引入Flink CDC 依赖
<dependency>
<groupId>com.ververica</groupId>
<artifactId>flink-connector-sqlserver-cdc</artifactId>
<version>2.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-java</artifactId>
<version>1.13.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-streaming-java_${scala.binary.version}</artifactId>
<version>1.13.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-clients_${scala.binary.version}</artifactId>
<version>1.13.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-api-java-bridge_${scala.binary.version}</artifactId>
<version>1.13.0</version>
</dependency>
1.2.3.3 进行编程
public class SqlServerSourceExample {
public static void main(String[] args) throws Exception {
SourceFunction<String> sourceFunction = SqlServerSource.<String>builder()
.hostname("localhost")
.port(1433)
.database("sqlserver") // monitor sqlserver database
.tableList("dbo.products") // monitor products table
.username("sa")
.password("Password!")
.deserializer(new JsonDebeziumDeserializationSchema()) // converts SourceRecord to JSON String
.build();
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
env
.addSource(sourceFunction)
.print().setParallelism(1); // use parallelism 1 for sink to keep message ordering
env.execute();
}
}
1.2.3.4 程序运行
1.2.3.5 exact-once保证
The SQLServer CDC connector is a Flink Source connector which will read database snapshot first and then continues to read change events with exactly-once processing even failures happen.
1.2.3.6 单线程读
The SQLServer CDC source can't work in parallel reading, because there is only one task can receive change events.
2. SQL Server 批量导入
对于批量数据导入的情况，一般会采用JDBC的方式导入和文件直接导入的方式。
2.1 JDBC批量导入
使用JDBC来批量导入数据到数据库，一般的步骤如下：
01. 关闭数据库自动提交
02. 开启事务
03. 使用PreparedStatement来进行预编译，并使用batch提交
04. 提交事务
代码逻辑如下：
delete from student;
delete from score;
@Test
void jdbc_import() throws SQLException {
// 准备批量数据
List<Student> studentList = new ArrayList<>(500000000);
for (int i = 0; i < 100; i++) {
studentList.add(new Student(i, "sname_" + i));
}
// 准备好数据库链接
Connection connection = DriverManager.getConnection("jdbc:sqlserver://192.168.73.128:1433;DatabaseName=class", "sa", "123456Psw");
connection.setAutoCommit(false);
String sql = "insert into student values(?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
// 批量插入
for (int i = 0; i < studentList.size(); i++) {
ps.setInt(1, studentList.get(i).getSid());
ps.setString(2, studentList.get(i).getSname());
ps.addBatch();
if (i % 50 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
ps.executeBatch();
ps.clearBatch();
//所有语句都执行完毕后才手动提交sql语句
connection.commit();
ps.close();
connection.close();
}
select count(*) from student;
实际批量导入性能，会受限于网络带宽和数据服务器性能，效率不会很高。
2.2 bcp工具批量导入
2.2.1 bcp简介
在SqlServer bcp 实用工具 章节，
bcp [database_name.] schema.{table_name | view_name | "query"}
{in data_file | out data_file | queryout data_file | format nul}
[-a packet_size]
[-b batch_size]
[-c]
[-C { ACP | OEM | RAW | code_page } ]
[-d database_name]
[-D]
[-e err_file]
[-E]
[-f format_file]
[-F first_row]
[-G Azure Active Directory Authentication]
[-h"hint [,...n]"]
[-i input_file]
[-k]
[-K application_intent]
[-l login_timeout]
[-L last_row]
[-m max_errors]
[-n]
[-N]
[-o output_file]
[-P password]
[-q]
[-r row_term]
[-R]
[-S [server_name[\instance_name]]
[-t field_term]
[-T]
[-U login_id]
[-v]
[-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
[-w]
[-x]
其中重点参数:
-
datafile
-
-U（username）：指定用户名
- 如果不使用用户名登录也可以，使用-T 来使用可信任链接而不需要用户名密码
-
-P（password）：指定密码
- 在命令行中，不指定-P，则需要手动 ENTER 输入密码
- 如果不使用密码也可以，使用-T 来使用可信任链接而不需要用户名密码
-
-b （batch_size）：指定每批导入数据的行数，默认不指定表示整个文件一个批次导入
- 如果指定-b，则表示分批次导入，每次导入行数用整数指定；
- 每个批次作为一个事务提交，事务失败则数据回滚，但不影响已经导入的数据
bcp是一个工具，意味着如果本地安装有bcp工具，则可以远程批量导入数据。官方提供了多种安装方式，例如：
2.2.2 bcp批量导入测试
2.2.2.1 bcp工具准备
由于本地未安装MS SQL Server服务器，无法使用bcp客户端工具，可以单独安装windows 版本的BCP客户端工具，具体地址Windws，双击安装即可；
进入cmd控制台，输入bcp后显示如下，即表明bcp安装成功;
如果使用docker服务器安装了SQL Server服务器，则在目录下/opt/mssql-tools/bin可以找到bcp命令工具。
2.2.2.2 程序编程实现
程序编程思路如下：
01. 将批量对象生成到文件
02. runtime获取bcp命令行环境并织入导入命令
具体代码如下：
@Test
void bcp_import() throws SQLException, IOException, InterruptedException {
// 准备批量数据
List<Student> studentList = new ArrayList<>(500000000);
for (int i = 1000; i < 2000; i++) {
studentList.add(new Student(i, "sname_" + i));
}
// 将数据写入文件
File dir = new File(new ApplicationHome(SqlserverdemoApplication.class) + File.separator + "/logs");
dir.mkdirs();
File studentFile = new File(dir + File.separator + "student.txt");
FileOutputStream fileOutputStream = new FileOutputStream(studentFile);
FileChannel fileChannel = fileOutputStream.getChannel();
for (Student student : studentList) {
String record = student.toString();
ByteBuffer byteBuffer = ByteBuffer.allocate(record.length());
byteBuffer.put(record.getBytes(Charset.forName("UTF-8")));
byteBuffer.flip();
fileChannel.write(byteBuffer);
}
fileChannel.close();
fileOutputStream.close();
// 通过CMD执行BCP指令，写入数据库
Runtime runtime = Runtime.getRuntime();
String cmd = String.join(" ", "bcp", "class.dbo.student",
"in", studentFile.getPath(), "-U", "sa", "-c ", "-S 192.168.73.128", "-P 123456Psw");
int i = runtime.exec(cmd).waitFor();
if (0 == i) {
log.info("写入成功!");
} else {
log.info("写入失败!");
}
}
@Data
class Student {
private Integer sid;
private String sname;
public Student(Integer sid, String sname) {
this.sid = sid;
this.sname = sname;
}
@Override
public String toString() {
return String.join("\t", sid + "", sname) + "\r\n";
}
}
2.2.2.3 实践情况
-
bcp客户端不一定安装在SQL Server服务器本地，可以安装在网络可达的其他地方；
-
生产上利用bcp工具远程导入数据到数据库很快速，很稳定，实践中batch size 批处理10000行在1-2s中能够完成；
-
导入数据的数据表无需有主键，没有主键也可以进行导入
1804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
