构建AWS Glue作业：SQL Server到Redshift增量数据抽取SQL查询需求

需求背景 #

我正在创建一个AWS Glue作业，将OLTP数据库中的数据抽取至Redshift数据库，需要编写SQL查询语句从指定表中抽取增量数据，该表通过CreatedOn和LastUpdatedOn字段追踪数据变更。

源表结构 #

CREATE TABLE [dbo].[Table](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CreatedOn] [datetimeoffset](7) NOT NULL,
    [CreatedBy] [nvarchar](255) NOT NULL,
    [LastUpdatedBy] [nvarchar](255) NOT NULL,
    [LastUpdatedOn] [datetimeoffset](7) NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [Type] [nvarchar](128) NULL,
    [Title] [nvarchar](256) NULL,
    [FirstName] [nvarchar](256) NULL,
    [MiddleName] [nvarchar](256) NULL,
    [HasMiddleName] [bit] NULL,
    [Surname] [nvarchar](256) NULL,
    [DateOfBirth] [datetime] NULL,
    [Gender] [nvarchar](16) NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )
)

全量抽取参考语句 #

SELECT 
        Id
        ,CONVERT(VARCHAR, CreatedOn, 20) AS CreatedOn
        ,CreatedBy
        ,LastUpdatedBy
        ,CONVERT(VARCHAR, LastUpdatedOn, 20) AS LastUpdatedOn
        ,Type
        ,Title
        ,TRIM(REPLACE(FirstName,CHAR(10),' ')) AS FirstName
        ,TRIM(REPLACE(MiddleName,CHAR(10),' ')) AS MiddleName
        ,HasMiddleName
        ,REPLACE(Surname,CHAR(10),' ') AS Surname
        ,TRIM(CONVERT(VARCHAR, DateOfBirth, 20)) AS DateOfBirth
        ,Gender
    FROM
        [dbo].[Table]

增量抽取SQL实现 #

核心逻辑 #

增量数据包含两类：

• 上次抽取后新增的数据：CreatedOn大于上次抽取的时间戳
• 上次抽取后更新的数据：LastUpdatedOn大于上次抽取的时间戳，且LastUpdatedOn不等于CreatedOn（避免重复抽取新增数据）

具体查询语句 #

假设用变量@last_extraction_time记录上次抽取的结束时间（实际需根据Glue作业的状态管理逻辑动态传入，比如存储在Glue书签或外部元数据表中），SQL语句如下：

DECLARE @last_extraction_time DATETIMEOFFSET(7);
-- 示例值，实际替换为上次抽取的时间
SET @last_extraction_time = '2024-01-01 00:00:00 +00:00';

SELECT 
        Id
        ,CONVERT(VARCHAR, CreatedOn, 20) AS CreatedOn
        ,CreatedBy
        ,LastUpdatedBy
        ,CONVERT(VARCHAR, LastUpdatedOn, 20) AS LastUpdatedOn
        ,Type
        ,Title
        ,TRIM(REPLACE(FirstName,CHAR(10),' ')) AS FirstName
        ,TRIM(REPLACE(MiddleName,CHAR(10),' ')) AS MiddleName
        ,HasMiddleName
        ,REPLACE(Surname,CHAR(10),' ') AS Surname
        ,TRIM(CONVERT(VARCHAR, DateOfBirth, 20)) AS DateOfBirth
        ,Gender
    FROM
        [dbo].[Table]
    WHERE
        -- 新增数据：创建时间晚于上次抽取时间
        CreatedOn > @last_extraction_time
        OR
        -- 更新数据：更新时间晚于上次抽取时间，且排除刚创建就更新的重复数据
        (LastUpdatedOn > @last_extraction_time AND LastUpdatedOn != CreatedOn)

补充优化说明 #

1. 时间戳类型匹配：确保@last_extraction_time的类型与CreatedOn/LastUpdatedOn一致（DATETIMEOFFSET(7)），避免类型转换导致的筛选错误
2. RowVersion字段强化精准度：如果需要更精准的增量判断，可结合RowVersion字段（避免LastUpdatedOn同一时间多条更新的冲突），修改WHERE条件为：

   WHERE
       CreatedOn > @last_extraction_time
       OR
       (LastUpdatedOn > @last_extraction_time AND RowVersion > @last_row_version)
   

   需额外记录上次抽取的最大RowVersion值
3. Glue作业集成：在Glue作业中可通过书签（Bookmark）功能自动管理上次抽取的时间戳，无需手动维护变量，只需在SQL中引用Glue内置参数或作业参数传入

内容的提问来源于stack exchange，提问作者Suraj