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

    1.前言

    尽管毫无疑问，传统商业许可数据库拥有丰富的功能和完善的支持，但其严格的定价模式、繁琐的许可条款以及较高的总体拥有成本（TCO）使得企业希望采用成本更低的开源解决方案。某些方面，开源数据库以更低的成本提供了相同甚至更好的功能。从商业数据库迁移到开源数据库可为企业在许可和支持方面节省大量成本。

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

    从传统的 SQL Server 数据库迁移可能非常耗时且需耗费大量资源，任何迁移都涉及三个主要步骤：移动架构、迁移数据和修改客户端应用程序。正如下图中我们所见：迁移数据库时，您可以使用 Amazon Schema Conversion Tool（SCT）配合 Amazon 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 语句改写简单，但能达到了相同的效果。

    演示之前，让我们看看接下来都会使用到两张表的结构：

    1. create table dept(
    2.     deptno int NOT NULL PRIMARY KEY,
    3.     dname varchar(14),
    4.     loc varchar(13)
    5. ) 
    6.  
    7. create table employees (
    8.     empno int NOT NULL PRIMARY KEY,
    9.     ename varchar(10),  
    10.     job varchar(9),  
    11.     mgr int,  
    12.     hiredate datetime,  
    13.     sal money,  
    14.     comm money,  
    15.     deptno int
    16. )

    • ALTER TABLE..CHECK CONSTRAINT

    原语句

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

    修改后语句

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

    说明：

    1. 在 Babelfish 中不支持 CHECK CONSTRAINT 语句启用表的约束，ALTER 表添加约束后会自动启用约束

    2. 在 Babelfish 中添加约束时不支持 WITH CHECK/NOHECK 选项对已有数据进行约束检查

    3. 这种不支持的 ALTER TABLE 特性的语句是迁移过程中最常见的，一般是修改后在 Babelfish 上新建表和约束，再导入表的数据，表的约束会自动检查导入的数据，保证数据约束有效

    • ALTER ROLE..

    原语句

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

    修改后语句

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

    说明：

    1. 目前 Babelfish 只支持用户数据库中的 dbo 用户，您不能创建具有较低权限的用户，例如对某些表的只读权限

    2. 大部分此类语句都是用户以操作系统权限登陆 SQL Server源后倒出的 DDL 语句，可以直接注释屏蔽语句

    • ALTER DATABASE..

    原语句

    1. ALTER DATABASE [???] SET RECOVERY FULL 
    2. GO

    修改后语句

    1. /* ALTER DATABASE [???] SET RECOVERY FULL 
    2. GO */

    说明：

    1. Babelfish 不支持ALTER DATABASE 语法，Aurora PostgreSQL是一个全托管型数据库，会限制一些数据库修改语句，这些语句可以直接注释屏蔽

    • ALTER AUTHORIZATION ON object

    原语句

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

    修改后语句

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

    说明：

    1. Babelfish 不支持AUTHORIZATION的创建、修改和删除，可以直接注释屏蔽

    • EXEC sys.sp_addextendedproperty

    原语句

    1. 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'
    2. GO

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

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

    1. Babelfish 不支持使用系统存储过程 sp_addextendedproperty 为字段增加说明，可以直接注释屏蔽此 SQL 语句，并连接到 PostgreSQL 端使用comment增加字段说明

    • OBJECTPROPERTY

    原语句

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

    修改后语句

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

    说明：

    1. Babelfish 不支持内置的元数据函数 OBJECTPROPERTYEX，可根据SQL语义进行适当改写

    • SET ROWCOUNT

    原语句

    1. CREATE PROCEDURE [dbo].[P_Rowcount]
    2. @id int
    3. as
    4. set nocount on
    5. set rowcount @id 
    6. begin
    7. select * from employees order by empno
    8. end
    9. GO

    修改后语句

    1. CREATE PROCEDURE [dbo].[P_Rowcount]
    2. @id int
    3. as
    4. set nocount on
    5. begin
    6. select top (@id) * from employees order by empno
    7. end
    8. GO

    第二种修改

    1. CREATE PROCEDURE [dbo].[P_Rowcount]
    2. @id int
    3. as
    4. set nocount on
    5. begin
    6. select * from employees order by empno offset 0 rows fetch first @id rows only;
    7. end
    8. GO

    说明：

    1. Babelfish 不支持 SET ROWCOUNT 语句来返回指定的行数，可根据 SQL 语义进行适当改写。从示例中我们看到可以有多种的改写方法，在业务复杂的场景下应从代码的可读性和性能影响方面做选择

    • CURRENT OF

    原语句

    1. CREATE PROCEDURE [dbo].[P_CurrentOf] AS
    2. BEGIN
    3.   DECLARE @empno int
    4.   DECLARE NoResponce CURSOR FOR
    5.     SELECT empno FROM employees;
    6.   OPEN NoResponce;
    7.   FETCH NEXT FROM NoResponce INTO @empno;
    8.   DELETE FROM employees WHERE CURRENT OF NoResponce;
    9. END
    10. GO

    修改后语句

    1. CREATE PROCEDURE [dbo].[P_CurrentOf] AS
    2. BEGIN
    3.   DECLARE @empno int
    4.   DECLARE NoResponce CURSOR FOR
    5.     SELECT empno FROM employees;
    6.   OPEN NoResponce;
    7.   FETCH NEXT FROM NoResponce INTO @empno;
    8.   DELETE FROM employees WHERE empno = @empno;
    9. END
    10. GO

    备注:

    1. Where Current Of 语句允许您更新或者是删除最后由 cursor 取的记录，Babelfish 不支持 Current Of 语句，可根据 SQL 语句上下文语义选取变量

    • IDENTITY

    原语句

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

    修改后语句

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

    说明：

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

    3.4 复杂代码转换

    相对于前面介绍简单代码转换，接下来的这些 SQL 语句会复杂一些，修改内容也比较多。同时，您还需要仔细地审查 SQL 语句中上下文之间的关系，以确保修改后的语句和原语句执行得到相同的效果。

    • MERGE

    在这个案例演示之前，创建两张 MERGE 使用的源表和目标表

    1. create table source
    2. (
    3.    id      int not null primary key ,
    4.    country varchar(20) null,
    5.    city    varchar(20)
    6. );
    7.  
    8. insert into source
    9.   (id, country, city)
    10.  VALUES
    11.   (1, 'RUSSIA',  'MOSCOW'),
    12.   (2, 'FRANCE',  'PARIS'),
    13.   (3, 'ENGLAND', 'LONDON'),
    14.   (4, 'USA',     'NEW YORK'),
    15.   (5, 'GERMANY', 'BERLIN'),
    16.   (6, 'BRAZIL',  'BRASILIA');
    17.  
    18. create table target
    19. (
    20.    id      int not null primary key ,
    21.    country varchar(20) null,
    22.    city    varchar(20)
    23. );
    24.  
    25. insert into target
    26.   (id, country, city)
    27.  VALUES
    28.   (1, 'JAPAN',   'TOKYO'),
    29.   (4, 'USA',     'DENVER'),
    30.   (7, 'CHINA',   'BEI JING');

    原语句

    1. MERGE INTO target AS C2
    2. USING source AS C1 
    3. ON C2.id = C1.id
    4. WHEN MATCHED
    5.    THEN UPDATE 
    6.       SET
    7.          C2.country = C1.country,
    8.          C2.city = c1.city
    9. WHEN NOT MATCHED
    10.    THEN INSERT (id, country, city)
    11.             VALUES (C1.id, C1.country, C1.city);

    修改后语句

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

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

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

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

    1. insert into target (id,country,city) select id,country,city 
    2. from source
    3. on conflict (id)
    4. 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的全文搜索，不支持以下的语句及系统存储过程

    1. CREATE、ALTER、DROP FULLTEXT CATALOG
    2. CREATE、ALTER、DROP FULLTEXT INDEX
    3. CREATE、ALTER、DROP FULLTEXT STOPLIST
    4. exec sp_fulltext_database 'enable';

    Amazon Aurora PostgreSQL 兼容版本增加了对 pg_bigm 扩展程序的支持。pg_bigm 扩展程序在 PostgreSQL 中提供有全文搜索功能。此扩展程序允许用户创建 2-gram（双组），以提高全文搜索速度。以下案例演示如何在PostgreSQL端通过扩展启用全文搜索功能

    1. set search_path=dbo;
    2. create extension pg_bigm;
    3.  
    4. CREATE TABLE fulltext_doc (doc text);
    5. INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 成本优化');
    6. INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 性能优化');
    7. INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 提升使用体验');
    8. INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 2-gram 全文搜索功能的工具');
    9. INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 3-gram 全文搜索功能的工具');
    10.  
    11. CREATE INDEX fulltext_doc_idx ON fulltext_doc USING gin (doc gin_bigm_ops);
    12. alter table fulltext_doc owner to dbo;

    全文搜索设置成功后可以在 TDS 端口通过 T-SQL 调用

    • SWITCHOFFSET

    原语句

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

    修改后语句（在 PostgreSQL 端创建自定义函数）

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

    说明：

    1. Babelfish 不支持SWITCHOFFSET和TODATETIMEOFFSET之类的时区偏移量内置函数，可以在PostgreSQL端创建自定义函数并在TDS端口通过T-SQL调用来实现相同功能

    • XML 方法

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

    1. create table t_xml_test (
    2.   id int,
    3.   country  nvarchar(max),
    4.   industry nvarchar(max)
    5. ); 
    6.  
    7. insert t_xml_test values(1, 'China', 'Manufacturing and foreign trade business');
    8. insert t_xml_test values(2, 'USA', 'Financial and Bioindustry');
    9. insert t_xml_test values(3, 'Russia', 'Resource export');

    原语句

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

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

    1. CREATE OR REPLACE FUNCTION xmlQueryID(in_xml xml) 
    2. RETURNS TABLE (id text) 
    3. AS $$
    4. DECLARE
    5. BEGIN
    6.   RETURN QUERY
    7.     select * from (
    8.       WITH xmldata(data) AS (VALUES (in_xml::xml))
    9.       SELECT xmltable.*
    10.       FROM XMLTABLE('/root/country' PASSING (SELECT data FROM xmldata) COLUMNS id text)) as foo;
    11. END;
    12. $$ LANGUAGE plpgsql;

    连接 TDS 端口在 T-SQL 中修改 SQL 语句并调用 PostgreSQL 端创建的自定义函数

    1. create procedure p_xml_test
    2.  @xml xml
    3. as
    4. begin    
    5.    set nocount on    
    6.    select * from t_xml_test
    7.    where id in (select * from xmlQueryID(@xml)) ;
    8.    set nocount off
    9. end
    10. 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 侧调用。

    点击了解亚马逊云科技数据库服务全景图，即刻上手试用

    本篇作者

    唐晓华

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

  • 相关阅读:
    资金核对平台的发展历程
    重磅推荐 | 朱嘉明：元宇宙——创意、思想、意识协作的下一代网络
    阿里面试——机器学习岗四个面试案例
    一个免杀项目分享
    业务架构、应用架构、技术架构、数据架构
    DirectX12 - Triangle Culling and Winding Order（三角形的剔除与绕序）
    uni-app的优缺点；uniapp进行条件编译的两种方法；小程序端和H5的代表值
    华为云云耀云服务器L实例评测｜用Python的Flask框架加Nginx实现一个通用的爬虫项目
    1.5 编写自定位ShellCode弹窗
    机器学习案例（二）：服务员小费预测
  • 原文地址：https://blog.csdn.net/u012365585/article/details/126835918