关于云原生:走向云原生数据库-使用-Babelfish-加速迁移-SQL-Server-的代码实践

1. 前言

只管毫无疑问，传统商业许可数据库领有丰盛的性能和欠缺的反对，但其严格的定价模式、繁琐的许可条款以及较高的总体领有老本（TCO）使得企业心愿采纳老本更低的开源解决方案。某些方面，开源数据库以更低的老本提供了雷同甚至更好的性能。从商业数据库迁徙到开源数据库可为企业在许可和反对方面节俭大量老本。

PostgreSQL 是企业级的，功能丰富的开源数据库系统，它高度牢靠且性能卓越，非常适合实时和工作要害型应用程序。Amazon Aurora 是一种关系数据库服务，既有高端商用数据库的高速度和可用性，也有开源数据库的简略性和老本效益。Aurora 与 MySQL 和 PostgreSQL 齐全兼容，使现有应用程序和工具无需批改即可运行。与典型的 PostgreSQL 数据库相比，它将性能进步了三倍，并且减少了可扩展性、持久性和安全性。

从传统的 SQL Server 数据库迁徙可能十分耗时且需消耗大量资源，任何迁徙都波及三个次要步骤：挪动架构、迁徙数据和批改客户端应用程序。正如下图中咱们所见：迁徙数据库时，您能够应用 AWS Schema Conversion Tool（SCT）配合 AWS Database Migration Service (DMS) 主动迁徙数据库架构和数据，但迁徙应用程序自身时，通常须要实现更多的工作，包含重写与数据库交互的利用程序代码，将 T-SQL 代码迁徙到 PL/pgSQL 中，这是简单、耗时且有危险的。

Babelfish for Aurora PostgreSQL 是 Amazon Aurora PostgreSQL 兼容版本的一项新性能，能够了解 Microsoft SQL Server 专有的 SQL 语言 T-SQL，并反对雷同的通信协议，因而，批改 SQL Server 上运行的应用程序并将其挪动到 Aurora 所需的工作量将缩小，从而可实现更快、危险更低且更具老本效益的迁徙。

Babelfish 通过反对 Aurora PostgreSQL 的 Microsoft SQL Server 数据类型、语法和函数来反对 T-SQL 和 SQL Server 行为。但请留神，Babelfish 并不提供对 T-SQL 的 100% 残缺反对，依然有一些差别和限度，某些状况下须要做手工的代码转换。

本文将列举并演示一些高频及常见的典型代码转换案例，帮忙您更高效疾速地实现迁徙工作。

2. 环境筹备

在开始咱们的演示之前，假如在您的工作环境，已有一个筹备迁徙的 SQL Server 源库，那么除此之外，您还须要设置好以下相干的组件：

• Babelfish Compass

这是一个开源的 SQL Server 迁徙到 Babelfish 的语法评估工具，能够在 GitHub 上下载。它能在 Windows 和 Linux 平台下运行，须要 Java 环境反对，以后的版本是 v2022-04

• Babelfish for Aurora PostgreSQL

Babelfish 从 2021 年秋公布第一个版本 1.0.0 开始，目前版本曾经更新到 1.2.1，对应的 Aurora PostgreSQL 版本是 13.6。您能够依据官网文档阐明来操作，只需简略几步即可创立一个 Babelfish for Aurora PostgreSQL 集群环境。配置过程中须要留神的就是数据库迁徙模式的抉择，还有如果有中文数据的话那么在排序规定中请抉择“chinese_prc_ci_as”

到目前为止，一个蕴含 SQL Server 源和 Aurora PostgreSQL 指标以及迁徙评估工具的环境曾经筹备好。接下来，请参考这个博客的内容，您只须要花短短的几分钟就能生成一个 Babelfish 迁徙评估报告。

3. 代码转换

3.1 转换评估

Babelfish Compass 工具生成的评估报告是评估迁徙工作内容和工作量的指引，您能够依据其中列出的须要批改的我的项目，逐个编写 SQL 代码转换内容。

评估报告的 Summary 章节列出了迁徙 SQL Server 源到 Babelfish 指标的 T-SQL 的语法个性兼容统计，包含反对、不反对、语义审查、手动审查及可疏忽项。其中最要害的是不反对个性的内容，这些含有不反对个性的 SQL 语句，如果不作批改，在 Babelfish for Aurora PostgreSQL 环境中大部分执行会报错：“‘???’is not currently supported in Babelfish”，而其余的一些 SQL 语句尽管没有报错，但不会真正失效。

在评估报告中咱们能够查看这些不反对个性的 SQL 分类统计，下图中的评估报告列出了每一类不反对个性的 SQL 语句，它显示了咱们的案例所用的 DDL 脚本在 Babelfish 中不反对的个性次要有对表减少束缚语句，Merge 语句、批改数据库、批改角色、执行某些零碎存储过程等。

3.2 转换准则

Babelfish 为 Aurora PostgreSQL 数据库集群提供了一个额定的端点，使其可能理解 SQL Server 线路级协定和罕用的 SQL Server 语句。迁徙之后，您依然能够应用雷同的 T-SQL 开发工具和驱动，连贯到 TDS 端口实现相干的开发。您也能够应用原生 PostgreSQL 连贯在 PostgreSQL 这一端做开发，再从 T-SQL 这端进行调用。这一种兼容模式，能帮忙咱们解决大部分的 Babelfish 对 T-SQL 的兼容性问题。

• 抉择转换模式：如上所述，对于局部不反对的 SQL 语句，咱们能够抉择在 T-SQL 中进行改写，也能够在 PostgreSQL 中批改再从 T-SQL 中调用。转换的准则是依据利用的连贯开发模式而定，例如 .net 利用连贯到 TDS 端开发，那么首选转换模式就是在 T -SQL 中进行转换。如果在 T -SQL 这端无奈改写或存在批改后的性能问题，那么能够尝试在 PostgreSQL 中进行批改。
• 代码可读性：对于要批改的 SQL 语句，可能有好几种的改写办法。简略、高效、可读性好永远都是首选。例如，大部分状况下，应用 Case 语句比应用..Then 更容易了解。

3.3 简略代码转换

归于这一类的代码转换，其特点就是批改简略，但其数量经常在评估报告中列出的所有不反对个性的 SQL 语句中占绝大部分。此类代码转换工作一般而言只需屏蔽相干选项、正文整条语句或简略批改即可。如此批改的起因，是缘于 PostgreSQL 和 SQL Server 的两者间的个性差别或 Babelfish 的限度。SQL Server 中的某些选项或操作，在 Babelfish 不反对且不会对性能执行有影响，能够间接疏忽。尽管这类 SQL 语句改写简略，但能达到了雷同的成果。

演示之前，让咱们看看接下来都会应用到两张表的构造：

create table dept(
    deptno int NOT NULL PRIMARY KEY,
    dname varchar(14),
    loc varchar(13)
) 

create table employees (
    empno int NOT NULL PRIMARY KEY,
    ename varchar(10),  
    job varchar(9),  
    mgr int,  
    hiredate datetime,  
    sal money,  
    comm money,  
    deptno int
)

• ALTER TABLE..CHECK CONSTRAINT

原语句

ALTER TABLE [dbo].[employees]  WITH CHECK ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])
REFERENCES [dbo].[dept] ([deptno])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_DEPT]
GO

批改后语句

ALTER TABLE [dbo].[employees] ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])
REFERENCES [dbo].[dept] ([deptno])
GO

阐明：

1. 在 Babelfish 中不反对 CHECK CONSTRAINT 语句启用表的束缚，ALTER 表增加束缚后会主动启用束缚
2. 在 Babelfish 中增加束缚时不反对 WITH CHECK/NOHECK 选项对已有数据进行束缚查看
3. 这种不反对的 ALTER TABLE 个性的语句是迁徙过程中最常见的，个别是批改后在 Babelfish 上新建表和束缚，再导入表的数据，表的束缚会主动查看导入的数据，保证数据束缚无效

• ALTER ROLE..

原语句

ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO

批改后语句

/* ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO */

阐明：

1. 目前 Babelfish 只反对用户数据库中的 dbo 用户，您不能创立具备较低权限的用户，例如对某些表的只读权限
2. 大部分此类语句都是用户以操作系统权限登陆 SQL Server 源后倒出的 DDL 语句，能够间接正文屏蔽语句

• ALTER DATABASE..

原语句

ALTER DATABASE [???] SET RECOVERY FULL 
GO

批改后语句

/* ALTER DATABASE [???] SET RECOVERY FULL 
GO */

阐明：

1. Babelfish 不反对 ALTER DATABASE 语法，Aurora PostgreSQL 是一个全托管型数据库，会限度一些数据库批改语句，这些语句能够间接正文屏蔽

• ALTER AUTHORIZATION ON object

原语句

ALTER AUTHORIZATION ON [dbo].[employees] TO  SCHEMA OWNER 
GO

批改后语句

/* ALTER AUTHORIZATION ON [dbo].[employees] TO  SCHEMA OWNER 
GO */

阐明：

1. Babelfish 不反对 AUTHORIZATION 的创立、批改和删除，能够间接正文屏蔽

• EXEC sys.sp_addextendedproperty

原语句

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dept', @level2type=N'COLUMN',@level2name=N'deptno'
GO

批改后语句（在 PostgreSQL 端批改）

COMMENT ON COLUMN dept.deptno IS '编号';
阐明：

1. Babelfish 不反对应用零碎存储过程 sp_addextendedproperty 为字段减少阐明，能够间接正文屏蔽此 SQL 语句，并连贯到 PostgreSQL 端应用 comment 减少字段阐明

• OBJECTPROPERTY

原语句

select name from sysobjects where objectproperty(id, N'IsTable') = 1 and name not like N'#%%' order by name
select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND objectproperty(id, N'IsUserTable') = 1

批改后语句

select name from sysobjects where xtype in ('U','IT','S') and name not like N'#%%' order by name
select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND xtype='U'

阐明：

1. Babelfish 不反对内置的元数据函数 OBJECTPROPERTYEX，可依据 SQL 语义进行适当改写

• SET ROWCOUNT

原语句

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
set rowcount @id 
begin
select * from employees order by empno
end
GO

批改后语句

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select top (@id) * from employees order by empno
end
GO

第二种批改

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select * from employees order by empno offset 0 rows fetch first @id rows only;
end
GO

阐明：

1. Babelfish 不反对 SET ROWCOUNT 语句来返回指定的行数，可依据 SQL 语义进行适当改写。从示例中咱们看到能够有多种的改写办法，在业务简单的场景下应从代码的可读性和性能影响方面做抉择

• CURRENT OF

原语句

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
  DECLARE @empno int
  DECLARE NoResponce CURSOR FOR
    SELECT empno FROM employees;
  OPEN NoResponce;
  FETCH NEXT FROM NoResponce INTO @empno;
  DELETE FROM employees WHERE CURRENT OF NoResponce;
END
GO

批改后语句

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
  DECLARE @empno int
  DECLARE NoResponce CURSOR FOR
    SELECT empno FROM employees;
  OPEN NoResponce;
  FETCH NEXT FROM NoResponce INTO @empno;
  DELETE FROM employees WHERE empno = @empno;
END
GO

备注:

1. Where Current Of 语句容许您更新或者是删除最初由 cursor 取的记录，Babelfish 不反对 Current Of 语句，可依据 SQL 语句上下文语义选取变量

• IDENTITY

原语句

SELECT IDENTITY(INT,1,1) AS rowid,* INTO #tmp
FROM employees
ORDER BY empno

批改后语句

SELECT row_number() over () as rowid, * INTO #tmp
FROM employees
ORDER BY empno

阐明：

1. Babelfish 不反对 IDENTITY 函数，用于在带有 INTO 子句的 SELECT 语句中将标识列插入到新表中，可应用 row_number() over () 形式改写

3.4 简单代码转换

绝对于后面介绍简略代码转换，接下来的这些 SQL 语句会简单一些，批改内容也比拟多。同时，您还须要认真地审查 SQL 语句中上下文之间的关系，以确保批改后的语句和原语句执行失去雷同的成果。

• MERGE

在这个案例演示之前，创立两张 MERGE 应用的源表和指标表

create table source
(
   id      int not null primary key ,
   country varchar(20) null,
   city    varchar(20)
);

insert into source
  (id, country, city)
 VALUES
  (1, 'RUSSIA',  'MOSCOW'),
  (2, 'FRANCE',  'PARIS'),
  (3, 'ENGLAND', 'LONDON'),
  (4, 'USA',     'NEW YORK'),
  (5, 'GERMANY', 'BERLIN'),
  (6, 'BRAZIL',  'BRASILIA');

create table target
(
   id      int not null primary key ,
   country varchar(20) null,
   city    varchar(20)
);

insert into target
  (id, country, city)
 VALUES
  (1, 'JAPAN',   'TOKYO'),
  (4, 'USA',     'DENVER'),
  (7, 'CHINA',   'BEI JING');

原语句

MERGE INTO target AS C2
USING source AS C1 
ON C2.id = C1.id
WHEN MATCHED
   THEN UPDATE 
      SET
         C2.country = C1.country,
         C2.city = c1.city
WHEN NOT MATCHED
   THEN INSERT (id, country, city)
            VALUES (C1.id, C1.country, C1.city);

批改后语句

begin
update target set country = C1.country, city = C1.city from (select id, country, city from source) C1 where target.id = C1.id;
insert into target (id, country, city) select * from source as C1 where not exists (select id from target where id = C1.id);
end
go

第二种批改（在 PostgreSQL 端批改）

with upsert as
(update target c2 set country=c1.country, city=c1.city 
 from source c1 where c1.id=c2.id
  RETURNING c2.*
)
insert into target select a.id, a.country, a.city 
from source a where a.id not in (select b.id from upsert b);

第三种批改（在 PostgreSQL 端批改）

insert into target (id,country,city) select id,country,city 
from source
on conflict (id)
do update set country=excluded.country,city=excluded.city;

阐明：

1. MERGE 是罕用的一种数据合并更新语句，Babelfish 不反对 MERGE 语句，一般来说可依据 SQL 语义在 T-SQL 中拆分成多个 DML 语句，也能够在 PostgreSQL 端进行等价的改写。
2. PostgreSQL 目前还不反对 MERGE 语句，能够应用 UPSET 或 CONFLICT 语句实现，INSERT ON CONFLICT 的执行开销要小于 UPDATE 语句

• FULLTEXT 全文搜寻

Babelfish 不反对 SQL Server 的全文搜寻，不反对以下的语句及零碎存储过程

CREATE、ALTER、DROP FULLTEXT CATALOG
CREATE、ALTER、DROP FULLTEXT INDEX
CREATE、ALTER、DROP FULLTEXT STOPLIST
exec sp_fulltext_database 'enable';

Amazon Aurora PostgreSQL 兼容版本减少了对 pg_bigm 扩大程序的反对。pg_bigm 扩大程序在 PostgreSQL 中提供有全文搜寻性能。此扩大程序容许用户创立 2-gram（双组），以进步全文搜寻速度。以下案例演示如何在 PostgreSQL 端通过扩大启用全文搜寻性能

set search_path=dbo;
create extension pg_bigm;

CREATE TABLE fulltext_doc (doc text);
INSERT INTO fulltext_doc VALUES('Babelfish 助力 SQL 迁徙 老本优化');
INSERT INTO fulltext_doc VALUES('Babelfish 助力 SQL 迁徙 性能优化');
INSERT INTO fulltext_doc VALUES('Babelfish 助力 SQL 迁徙 晋升应用体验');
INSERT INTO fulltext_doc VALUES('Babelfish 助力 SQL 迁徙 中提供 2-gram 全文搜寻性能的工具');
INSERT INTO fulltext_doc VALUES('Babelfish 助力 SQL 迁徙 中提供 3-gram 全文搜寻性能的工具');

CREATE INDEX fulltext_doc_idx ON fulltext_doc USING gin (doc gin_bigm_ops);
alter table fulltext_doc owner to dbo;

全文搜寻设置胜利后能够在 TDS 端口通过 T-SQL 调用

• SWITCHOFFSET

原语句

SELECT CAST(SWITCHOFFSET(TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00'),'+08:00') AS DATETIME)

批改后语句（在 PostgreSQL 端创立自定义函数）

CREATE OR REPLACE FUNCTION dbo.f_get_cst()
RETURNS sys.datetime AS $$ 
  BEGIN
      RETURN cast(timezone('Asia/Shanghai',now()) as sys.datetime);
END;
$$ LANGUAGE plpgsql;

阐明：

1. Babelfish 不反对 SWITCHOFFSET 和 TODATETIMEOFFSET 之类的时区偏移量内置函数，能够在 PostgreSQL 端创立自定义函数并在 TDS 端口通过 T -SQL 调用来实现雷同性能

• XML 办法

在本案例演示之前，创立一张和 xml 解析相干的表

create table t_xml_test (
  id int,
  country  nvarchar(max),
  industry nvarchar(max)
); 

insert t_xml_test values(1, 'China', 'Manufacturing and foreign trade business');
insert t_xml_test values(2, 'USA', 'Financial and Bioindustry');
insert t_xml_test values(3, 'Russia', 'Resource export');

原语句

create procedure p_xml_test
 @xml xml
as
begin    
   set nocount on
   select * from t_xml_test
   where id in (select imgXML.Item.value('id[1]','int') from @xml.nodes('/root/country') as imgXML(Item)); 
   set nocount off
end
go

批改后语句（首先在 PostgreSQL 端创立自定义函数解析 XML）

CREATE OR REPLACE FUNCTION xmlQueryID(in_xml xml) 
RETURNS TABLE (id text) 
AS $$
DECLARE
BEGIN
  RETURN QUERY
    select * from (WITH xmldata(data) AS (VALUES (in_xml::xml))
      SELECT xmltable.*
      FROM XMLTABLE('/root/country' PASSING (SELECT data FROM xmldata) COLUMNS id text)) as foo;
END;
$$ LANGUAGE plpgsql;

连贯 TDS 端口在 T-SQL 中批改 SQL 语句并调用 PostgreSQL 端创立的自定义函数

create procedure p_xml_test
 @xml xml
as
begin    
   set nocount on    
   select * from t_xml_test
   where id in (select * from xmlQueryID(@xml)) ;
   set nocount off
end
go

在 T-SQL 中调用存储过程测试，查问结果显示 xml 解析失常，数据显示正确

阐明：

1. Babelfish 不反对解析 XML 数据的办法，包含 VALUES、XML.NODES 和其余办法，能够在 PostgreSQL 端创立自定义函数并在 TDS 端口通过 T-SQL 调用来实现 XML 数据的解析工作

4. 总结

通过后面的案例介绍，咱们为您展现了应用 Babelfish 迁徙 SQL Server 时一些最常见的不反对个性 SQL 的转换方法。以后，Babelfish for PostgreSQL 我的项目继续向前倒退，版本在不断更新。每个新的版本都会增加一些重要的性能，包含减少语法的兼容和 SQL Server 原生性能的反对。建议您在布局和施行 SQL Server 迁徙时常常查看 Babelfish 的个性反对阐明，应用最新的个性反对来实现代码的转换。同时，在 2021 年 10 月 28 日，亚马逊云科技正式发表推出 Babelfish for PostgreSQL 开源我的项目。此举使用户可能在本人的 PostgreSQL 服务器上利用 Babelfish。

更多更具体的 SQL Server 迁徙到 Amazon Aurora PostgreSQL 代码转换请参考官网迁徙手册，但请留神，这些转换都是在 PostgreSQL 端改写，须要思考如何在 T-SQL 侧调用。

本篇作者

唐晓华

亚马逊云科技数据库解决方案技术专家，二十余年数据库行业教训，负责基于亚马逊云计算数据库产品的技术咨询与解决方案工作。专一于云上关系型数据库架构设计、测试、运维、优化及迁徙等工作。

Database Minisite“AWS 云原生数据库”