Materialize项目中使用SQL Server作为实时数据源的完整指南

Materialize项目中使用SQL Server作为实时数据源的完整指南

materialize The data warehouse for operational workloads. 项目地址: https://gitcode.com/gh_mirrors/mat/materialize

概述

在现代数据架构中，实时数据集成变得越来越重要。Materialize作为一个流式数据库，提供了将SQL Server数据库作为实时数据源的能力。本文将详细介绍如何设置SQL Server并创建实时数据源，实现变更数据捕获(CDC)功能。

前置条件

在开始之前，需要确保SQL Server数据库满足以下要求：

1. 运行SQL Server 2016或更高版本
2. 已为目标数据库启用变更数据捕获(CDC)功能
3. 设置了SNAPSHOT事务隔离级别

SQL Server设置

启用变更数据捕获

变更数据捕获(CDC)是SQL Server提供的一种机制，可以捕获对数据库表的所有插入、更新和删除操作。启用CDC需要执行以下步骤：

1. 在数据库级别启用CDC：

USE [数据库名]
GO
EXEC sys.sp_cdc_enable_db
GO

2. 为需要捕获的表启用CDC：

EXEC sys.sp_cdc_enable_table
  @source_schema = '模式名',
  @source_name = '表名',
  @role_name = '角色名',
  @supports_net_changes = 0;

设置SNAPSHOT隔离级别

SNAPSHOT隔离级别确保Materialize能够获取一致的数据快照：

ALTER DATABASE [数据库名]
SET ALLOW_SNAPSHOT_ISOLATION ON

创建Materialize连接

在Materialize中，首先需要创建一个连接对象，定义如何连接到SQL Server实例：

-- 创建密码密钥
CREATE SECRET sqlserver_pass AS '你的密码';

-- 创建SQL Server连接
CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST '服务器地址',
    PORT 1433,
    USER '用户名',
    PASSWORD SECRET sqlserver_pass
);

对于位于私有网络的SQL Server实例，可以通过SSH隧道建立连接：

-- 创建SSH隧道连接
CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST '堡垒机地址',
    PORT 22,
    USER 'SSH用户名'
);

-- 通过SSH隧道创建SQL Server连接
CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST '私有SQL Server地址',
    SSH TUNNEL ssh_connection
);

创建数据源

基本语法

创建SQL Server数据源的基本语法如下：

CREATE SOURCE 源名称
  FROM SQL SERVER CONNECTION 连接名称
  [FOR ALL TABLES | FOR TABLES (表列表)]
  [WITH (选项)];

创建完整数据库源

要捕获数据库中所有已启用CDC的表：

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sqlserver_connection
  FOR ALL TABLES;

创建特定表源

如果只需要捕获特定表：

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sqlserver_connection
  FOR TABLES (schema1.table1, schema2.table2 AS 别名);

处理不支持的列类型

对于SQL Server CDC不支持的列类型，可以排除这些列：

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sqlserver_connection (
    EXCLUDE COLUMNS (schema.table.unsupported_column)
  )
  FOR ALL TABLES;

监控与维护

查看源信息

创建源后，可以查看所有子源：

SHOW SOURCES;

监控进度

Materialize自动创建一个进度子源，可用于监控复制进度：

SELECT lsn FROM 源名称_progress;

处理模式变更

当上游SQL Server表结构发生变化时，需要重新创建受影响的子源：

-- 删除旧的子源
DROP SOURCE 表名;

-- 添加新的子源
ALTER SOURCE 源名称 ADD SUBSOURCE 表名;

性能优化

1. 历史数据保留：可以设置保留历史数据的时间窗口

CREATE SOURCE ... RETAIN HISTORY FOR '1h';

2. 集群分配：为源指定专用集群

CREATE SOURCE ... IN CLUSTER 集群名称 ...;

注意事项

1. Materialize会在创建源时捕获表结构元数据，之后新增的表需要重新创建源

2. 如果上游有同名表位于不同模式中，需要使用别名避免冲突

3. 某些SQL Server数据类型不受CDC支持，需要排除这些列

4. 大规模表变更可能导致复制延迟，需要监控并及时处理

总结

通过Materialize与SQL Server的集成，可以实现企业级实时数据管道。本文详细介绍了从SQL Server设置到Materialize源创建的完整流程，以及日常监控和维护的最佳实践。这种集成特别适合需要实时数据分析、报表和决策支持的场景。

materialize The data warehouse for operational workloads. 项目地址: https://gitcode.com/gh_mirrors/mat/materialize