使用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 | |
TINYINT | |
BIGINT | |
BIT | |
DECIMAL | |
NUMERIC | |
CHAR | CHARACTER[(N)] |
VARCHAR | |
NCHAR | |
TEXT/NTEXT | TEXT |
FLOAT | DOUBLE |
REAL | |
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 | |
MONEY | DECIMAL(19, 4) |
SMALLMONEY | DECIMAL(10, 4) |
UNIQUEIDENTIFIER | CHARACTER(36) |
XML | |
SYSNAME | CHARACTER VARYING(128) |
不支援的資料類型
說明 | |
---|---|
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控制台
如何将資料從SQLServer同步至AnalyticDB for PostgreSQL -
購買同步服務
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
。