如何将数据从SQLServer同步至AnalyticDB for PostgreSQL

2021-12-24 22:22:35

使用DTS，Sqlserver -> AnalyticDB for PostgreSQL 同步链路，可以很方便的将SQLServer中分散的业务数据批量/实时导入至AnalyticDB for PostgreSQL，进行复杂的OLAP查询与分析。

需要准备的条件：

正在运行的SQLServer实例与AnalyticDB for PostgreSQL实例
在源/目标数据库实例开通的网络权限/白名单, 有合适权限的数据库账号
被同步的SQLServer表需要有主键（如果源表没有主键，为了保证增量同步的幂等性，需要在配置主键的页面勾选出主键，并保证数据无主键冲突，否则会在数据同步过程中出现异常）

注意事项

当前已经支持了结构迁移功能，可以自动在AnalyticDB for PostgreSQL端创建表结构，结构迁移的类型映射关系参考下方支持的类型与映射关系表格。
当前支持了DDL同步。即数据同步过程中，源表发生表结构修改（当前已支持alter table add/drop column），DTS可将DDL修改的表结构在目标表执行同样的操作。
当前已支持了使用同一条dts链路同步多个SQLServer database内的表到AnalyticDB for PostgreSQL。
SQLServer支持类型，2005、2008、2008R2、2012、2014、2016、2017
DTS在执行全量数据初始化时将占用源库和目标库一定的读写资源，可能会导致数据库的负载上升，在数据库性能较差、规格较低或业务量较大的情况下（例如源库有大量慢SQL、存在无主键表或目标库存在死锁等），可能会加重数据库压力，甚至导致数据库服务不可用。因此您需要在执行数据同步前评估源库和目标库的性能，同时建议您在业务低峰期执行数据同步（例如源库和目标库的CPU负载在30%以下）。

支持的类型与映射关系

SQLServer中的数据类型	AnalyticDB for PostgreSQL对应的数据类型
INT	INTEGER
SMALLINT	SMALLINT
TINYINT	SMALLINT
BIGINT	BIGINT
BIT	BIT
DECIMAL	DECIMAL
NUMERIC	DECIMAL
CHAR	CHARACTER[(N)]
VARCHAR	CHARACTER[(N)]
NCHAR	CHARACTER[(N)]
TEXT/NTEXT	TEXT
FLOAT	DOUBLE
REAL	REAL
DATE	DATE
DATETIME	TIMESTAMP(3) WITHOUT TIME ZONE
DATETIME2	TIMESTAMP(7) WITHOUT TIME ZONE
DATETIMEOFFSET	TIMESTAMP(7) WITH TIME ZONE
SMALLDATETIME	TIMESTAMP WITHOUT TIME ZONE
TIME	TIME WITHOUT TIME ZONE
BINARY/VARBINARY	BYTEA
IMAGE	BYTEA
MONEY	DECIMAL(19, 4)
SMALLMONEY	DECIMAL(10, 4)
UNIQUEIDENTIFIER	CHARACTER(36)
XML	XML
SYSNAME	CHARACTER VARYING(128)

不支持的数据类型

SQLServer中的数据类型	说明
TIMESTAMP	SQLServer中的timestamp用来给数据行加版本戳，与时间及日期无关，如果需要记录日期或时间需要使用date/datatime/datetime2/datetimeoffset等类型
CURSOR	无
ROWVERSION	无
HIERACHYID	无
SQL_VARIANT	无
SPATIAL GEOMETRY	无
SPATIAL GEOGRAPHY	无
TABLE	无

操作步骤

准备源表与目标表

1.在SQLServer创建源表
样例建表语句：

-- 创建数据库
create database test_adb;
......
CREATE TABLE [dbo].[demo_table]
(
    [id]  bigint            NOT NULL primary key,
    [t0]  smallint          NULL,
    [t1]  int               NULL,
    [t2]  binary(8)         NULL,
    [t3]  bit               NULL,
    [t4]  bit               NULL,
    [t5]  decimal(18, 3)    NULL,
    [t6]  numeric(18)       NULL,
    [t7]  char(3)           NULL,
    [t8]  varchar(MAX)      NULL,
    [t9]  nchar(7)          NULL,
    [t10] nvarchar(16)      NULL,
    [t11] text              NULL,
    [t12] ntext             NULL,
    [t13] float(53)         NULL,
    [t14] real              NULL,
    [t15] date              NULL,
    [t16] datetime          NULL,
    [t17] datetime2(7)      NULL,
    [t18] datetimeoffset(7) NULL,
    [t19] smalldatetime     NULL,
    [t20] time(7)           NULL,
    [t21] varbinary(MAX)    NULL,
    [t22] image             NULL,
    [t23] money             NULL,
    [t24] smallmoney        NULL,
    [t25] uniqueidentifier  NULL,
    [t26] xml NULL
);

2.将测试数据插入到sqlserver表

INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2000', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2001', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2002', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2003', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2004', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2005', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2006', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');

购买dts数据同步服务

登陆dts控制台
购买同步服务
1)点击创建同步

2)选择同步服务，SQLServer源和AnalyticDB for PostgreSQL 目标实例类型

3)开通服务

配置同步链路

1.dts数据同步控制台，找到刚购买好的dts同步服务，配置链路

2.选择需要被同步的表

3.按照建表语句，选择主键与分布键

4.预检查并启动

5.检查同步任务执行情况

6.在目标表查询已同步的数据

7.源端修改数据，观察同步情况
在源端删除之前已经成功写入的id=2000的行

-- SQLServer端执行
DELETE FROM [dbo].[demo_table] WHERE [id]=N'2000';

在目标端查询

-- AnalyticDB for PostgreSQL端执行
select * from dbo.demo_table;

多表合一功能介绍

OLTP场景下，为了提高业务表响应速度，通常会对数据做分库分表处理。但是到了AnalyticDB for PostgreSQL ，单表即可存储海量数据，使用单表查询更为方便。为了应对这种情况，我们设计了多表归并功能，即将源库多张表结构相同的表同步至同一张AnalyticDB for PostgreSQL表。

原理介绍

在目标表结构增加名为"__dts_data_source"的列，配置链路页面勾选多表归并功能，这样dts会在同步过程中向该列写入DTS_ID.DATABASE.SCHEMA.TABLE格式的值，用来追踪数据来源。
例如，将上面的sqlserver数据分散到两张分区表内：

-- 在sqlserver端执行
CREATE TABLE [dbo].[demo_table_01]
(
    [id]  bigint            NOT NULL primary key,
    [t0]  smallint          NULL,
    [t1]  int               NULL,
    [t2]  binary(8)         NULL,
    [t3]  bit               NULL,
    [t4]  bit               NULL,
    [t5]  decimal(18, 3)    NULL,
    [t6]  numeric(18)       NULL,
    [t7]  char(3)           NULL,
    [t8]  varchar(MAX)      NULL,
    [t9]  nchar(7)          NULL,
    [t10] nvarchar(16)      NULL,
    [t11] text              NULL,
    [t12] ntext             NULL,
    [t13] float(53)         NULL,
    [t14] real              NULL,
    [t15] date              NULL,
    [t16] datetime          NULL,
    [t17] datetime2(7)      NULL,
    [t18] datetimeoffset(7) NULL,
    [t19] smalldatetime     NULL,
    [t20] time(7)           NULL,
    [t21] varbinary(MAX)    NULL,
    [t22] image             NULL,
    [t23] money             NULL,
    [t24] smallmoney        NULL,
    [t25] uniqueidentifier  NULL,
    [t26] xml NULL
);
CREATE TABLE [dbo].[demo_table_02]
(
    [id]  bigint            NOT NULL primary key,
    [t0]  smallint          NULL,
    [t1]  int               NULL,
    [t2]  binary(8)         NULL,
    [t3]  bit               NULL,
    [t4]  bit               NULL,
    [t5]  decimal(18, 3)    NULL,
    [t6]  numeric(18)       NULL,
    [t7]  char(3)           NULL,
    [t8]  varchar(MAX)      NULL,
    [t9]  nchar(7)          NULL,
    [t10] nvarchar(16)      NULL,
    [t11] text              NULL,
    [t12] ntext             NULL,
    [t13] float(53)         NULL,
    [t14] real              NULL,
    [t15] date              NULL,
    [t16] datetime          NULL,
    [t17] datetime2(7)      NULL,
    [t18] datetimeoffset(7) NULL,
    [t19] smalldatetime     NULL,
    [t20] time(7)           NULL,
    [t21] varbinary(MAX)    NULL,
    [t22] image             NULL,
    [t23] money             NULL,
    [t24] smallmoney        NULL,
    [t25] uniqueidentifier  NULL,
    [t26] xml NULL
);

-- 向两张分区表插入少量数据
INSERT INTO [dbo].[demo_table_01]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2000', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_01]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2001', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_01]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2002', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_01]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2003', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_02]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2004', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_02]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2005', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');
INSERT INTO [dbo].[demo_table_02]([id], [t0], [t1], [t2], [t3], [t4], [t5], [t6], [t7], [t8], [t9], [t10], [t11], [t12], [t13], [t14], [t15], [t16], [t17], [t18], [t20], [t21], [t22], [t23], [t24], [t25], [t26]) values ( N'2006', N'0', N'2', 0x616D702D74657374, '1', '0', N'123413124.123', N'123413124.123', N'abc', N'你好谢谢再见', N'ABCdefg', N'hijklmN', N'amp-test', 'amp-test amp-text', N'122.33333', N'22.222222', N'2015-03-12', N'2015-03-12 21:40:39.456', N'2015-03-12 21:40:35.123456', N'2015-03-12 21:43:32.1234567 +08:00', N'16:59:11.1234567', 0x616D702D74657374, 0x313131616D702D74657374313131616D702D74657374313131313131616D702D74657374313131616D702D74657374313131, N'12912.131', N'912.121', N'28DB9939-F43F-4B7C-8511-6497F045D60C', N'<HTML/>');

配置步骤

1.配置同步表的页面，勾选多表归并功能，并点击编辑，修改目标表名

2.编辑所有的表名，比如案例上去掉源表名后缀

3.修改好目标表名的效果

4.配置主键及分布key

5.开启同步任务，在目标库进行查询

select id, __dts_data_source from dbo.demo_table;

6.查询结果，从__dts_data_source列可以看出

以这一行结果为例

dtsu3uv3qs311y6apm:test_adb.dbo.demo_table_01

dtsu3uv3qs311y6apm为dts同步任务的id，在dts控制台可以找到匹配的任务

剩下的test_adb.dbo.demo_table_01分别为源database.源schema.源table。

码农公寓