探索 Airbyte 的增量数据同步

为了从数据集成策略中获得最大价值，您需要跨多个系统同步查看数据。数据同步涉及将数据从源系统复制到目标系统，在目标系统中，通过将源系统上的修改持续同步到目标系统来保持数据一致性。可以通过多种方式完成数据同步，包括：完全复制、增量复制或更改数据捕获 （CDC） 复制。

这是探索 Airbyte 同步模式的三部分系列教程中的第二个教程。本系列的目的是为您提供分步说明，以便您可以亲自了解 Airbyte 复制的数据在不同同步模式下的外观，并帮助您了解每种同步模式在后台的工作方式。如果您尚未这样做，我建议您先阅读探索 Airbyte 的完全刷新数据同步（本系列的上一篇文章），然后再继续本教程。

本系列的第二个教程重点介绍 Airbyte 的增量同步模式。在本教程中，您将：

• 了解什么是增量同步及其工作原理。
• 了解增量同步何时可能是一个不错的选择。
• 了解什么是游标，以及为什么增量同步需要游标。
• 了解为什么重复数据删除需要主键。
• 创建 Airbyte 源连接器以从源数据库读取数据。
• 创建 Airbyte 目标连接器以将数据写入目标数据库。
• 使用增量同步 - 追加同步模式探索增量数据库复制。
• 使用增量同步 - 重复数据删除历史记录同步模式探索增量数据库复制。
• 检查复制到目标数据库的原始 JSON 数据。
• 检查从原始 JSON 创建的规范化数据。
• 查看 Airbyte 用于规范化 JSON 数据的 SQL 代码。
• 查看源数据库上的插入、更新和删除操作如何反映在目标中。

i️如果您不确定哪种复制模式最适合您的使用案例，在继续本教程之前，您可能有兴趣阅读  Airbyte 的复制模式概述。

软件版本

本教程编写于 2022 年 <> 月，由于 Airbyte 的快速变化性质，将来可能会变得不那么重要。本教程中的示例使用了以下工具/版本：

• macOS：12.3.1
• Docker desktop： 4.10.1 （82475）
• Docker compose：1.29.2
• Debezium/Postgres：Docker image tag: 13
• Airbyte 开源（参见：部署 Airbyte）：0.39.41-alpha

Airbyte的ELT方法简介

当 Airbyte 将数据从源复制到目标时，它首先将数据复制到“原始”表中。然后，如果启用了规范化，则此数据将由目标中的 Airbyte 触发的 SQL 命令进行转换。Airbyte 利用 dbt 创建用于规范化数据的 SQL，并利用公用表表达式（或 CTE）将 SQL 分解为更模块化和易于理解的部分。在本教程的后面部分，您将探索用于规范化的低级别 SQL，并将查看目标数据库中的原始表和规范化表。

下图在高级别上显示了 Airbyte 的 ELT 方法：

什么是增量同步

增量同步是一种复制方法，可有效地使源和目标保持同步。与完全刷新数据同步一样，同步操作会定期从源执行到目标，同步操作的频率由同步计划定义。但是，与完全刷新同步相反，只有自上次同步以来发生的更改才会从源复制到目标 - 换句话说，不会重新复制目标中已有的任何数据。

它是如何工作的

Airbyte 的增量同步在概念上可以被认为是一个周期性执行同步操作的循环。此循环的每次迭代仅 r包含自上次执行此同步循环以来在源系统中插入或更新的记录。这比在每次迭代时复制整个数据集要高效得多，后者是完全刷新同步的行为。

增量同步模式有什么区别

在高级别上，增量同步 - 追加同步模式会产生最终规范化表，其中包含已在源上插入或更新并同步到目标的每条记录的历史副本。如果记录已多次更新和同步，则会导致最终规范化表中出现多个条目。

另一方面，增量同步 - 重复数据删除历史记录同步模式会生成两个规范化的目标表。其中一个表是历史记录表（也称为 scd 表，指的是缓慢变化的维度），其中包括每条记录的历史版本，其中包括每条记录的开始日期和结束日期。另一个表是重复数据删除表，其中包含每条记录的单个副本。

什么是游标

在给定的同步迭代中仅发送更新或新插入的记录需要跟踪在以前的迭代中发送了哪些记录。游标用于此目的，可以将其视为指向已复制的最新记录的指针。在新的同步迭代期间选择要复制的记录时，Airbyte 将游标值作为源系统上查询的一部分包括在内，以便仅从源中选择比游标更新的记录（即自上次同步以来的新记录或更新的记录）并将其发送到目标。

在本教程中，源数据库包含包含名为 updated_at 的字段的记录，该字段存储插入或更新每条记录的最近时间。这用作光标字段。换句话说，Airbyte 将存储在给定同步中看到的最大updated_at值，然后在后续的同步迭代中，通过将游标值作为查询的一部分来检索已在源上插入的新记录或已更新的记录。在查询中包含游标会将响应限制为仅updated_at值大于或等于上一次同步的最大值updated_at值的记录。

i️ 有关使用游标从源数据库中选择数据的查询的实现中考虑的设计决策和权衡的更多详细信息，请参阅有关  Airbyte SQL 源游标的讨论。

游标的持久化方式和位置

Airbyte 协议指定 Airbyte 源应发出 AirbyteStateMessage。此消息包括最近发出的游标的值以及其他状态信息。最终，Airbyte 将此状态（StatePersistence.java）保存到内部 Postgres 数据库中。如果您想自己查看此数据库，可以按如下方式登录：

docker exec -ti airbyte-db psql -U docker -d airbyte

可以使用以下命令查看状态数据库的内容：

SELECT * FROM state;

哪个应该使用类似于下面给出的表格来响应（请注意，为了简洁起见，本文中的响应是缩写的）：

                  id                  |            connection_id             |                                                                                                  state                                                                   |          created_at           |          updated_at           | stream_name | namespace |  type  
--------------------------------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-------------------------------+-------------+-----------+--------
 884a10a7-1c58-4456-af44-c617eec0c1fb | 78cb42b9-e857-4f93-b159-e590ec31d287 | {"state": {"cdc": false, "streams": [{"cursor": "2022-09-02T07:55:58.324989", "stream_name": "table_one", "cursor_field": ["updated_at"], "stream_namespace": "public"}]}} | 2022-09-01 16:52:44.619871+00 | 2022-09-02 07:59:27.304753+00 |             |           | LEGACY

为什么以及何时需要主键

在源系统上插入或更新的记录在同步操作期间由 Airbyte 复制到目标，并最初存储在原始数据表中。如果给定文档多次更新和同步，则原始数据表将具有该记录的多个条目（如本文后面的动手深入探讨所示）。

如果 Airbyte 用户选择了增量同步 - 重复数据删除历史记录同步模式，则必须对数据进行重复数据删除，以便源表中的单个条目仅导致最终规范化重复数据删除目标表中的单个相应条目，即使原始和历史数据表中可能会出现与该记录对应的多个版本。Airbyte 使用主键字段仅从历史记录表中选择给定记录的最新版本以包含在最终的重复数据删除表中，并且不包括该记录的所有其他历史版本。

另一方面，由于增量同步 - 追加同步模式不执行任何重复数据删除，因此不需要定义主键。

何时使用增量同步

假设您的数据具有合适的游标字段，并且（对于重复数据删除模式）您具有合适的主键，那么 Airbyte 的增量同步模式可能适合您的数据复制需求。但是，有一些限制需要注意：

• 删除操作未正确传播。如果您希望传播删除，则更改数据捕获 （CDC） 可能是更合适的同步方法。或者，软删除可能满足你的要求 - 但对此的详细讨论超出了本教程的范围。
• 如果在源系统上插入或更新记录时未正确更新其游标字段值，则可能无法将其复制到目标数据库。但是，有一些技术可以在修改时自动更新时间戳，您将在本文中学习。
• 如果在同步迭代之间多次修改记录，则只会将同步时记录的状态复制到目标。不会记录其他中间状态。
• 目前，只有支持 dbt/SQL 规范化的目标才支持增量同步。

增量追加限制和增量重复数据删除历史记录限制文档中更详细地描述了这些限制。

启动 Postgres 源和目标

上面的讨论简要概述了增量同步模式的工作原理。本文的其余部分重点介绍增量数据库复制期间幕后发生的情况。

如果您已完成本系列中的上一个教程，则可能已经为 Postgres 源和 Postgres 目标运行了容器。如果是这种情况，则可以重复使用这些容器，并可以跳到下一部分。

i️ 本教程中的示例以新的/空的 Postgres 容器的形式提供，但并不要求源数据库和目标数据库为空。

在本地主机上启动在端口 2000 上运行的源 Postgres 容器，如下所示：

docker run --rm --name airbyte-source -e POSTGRES_PASSWORD=password -p 2000:5432 -d debezium/postgres:13

在本地主机上启动在端口 3000 上运行的目标 Postgres 容器，如下所示：

docker run --rm --name airbyte-destination -e POSTGRES_PASSWORD=password -p 3000:5432 -d debezium/postgres:13

在 Airbyte 中定义源和目标

实例化 Postgres 源连接器

通过单击“+ 新建源”创建新数据源，如下所示。

然后选择 Postgres 作为源，如下所示：

 并定义一个名为增量源的源连接器，如下所示：

实例化 Postgres 目标连接器

定义一个名为增量目标的新 Postgres 目标，如下所示：

探索增量追加同步

在本节中，您将探索使用增量同步 - 追加模式的增量数据库复制，并将检查用于规范化目标 Postgres 数据库中数据的 SQL。

定义第一个 Postgres 源表

现在，您将创建并填充一个名为 table_one 的表。首先使用以下命令在源 Postgres 数据库上打开一个 shell：

docker exec -it airbyte-source psql --username=postgres

‍

Postgres终端应该使用诸如postgres=#之类的提示进行响应。在源数据库中创建一个名为 table_one 的新表，如下所示：

CREATE TABLE table_one(
  id integer PRIMARY KEY,
  name VARCHAR(200),
  updated_at timestamp DEFAULT NOW() NOT NULL
);

‍

请注意，该表包含一个名为 updated_at 的字段，该字段将用作游标字段（如上所述）。增量同步需要游标才能跟踪以前已同步的记录，以便它们不会重新发送到目标。

为了使游标正常工作，每次写入或修改记录时都必须更新此表中的updated_at字段。使用 Postgres 时，这可以通过 Postgres 触发器自动完成。通过将以下代码粘贴到源 Postgres 终端来创建一个名为 trigger_set_timestamp 的触发器：

CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS '
 BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
 END;
'
LANGUAGE plpgsql;

‍

触发器应在每次更新table_one表时执行，这是通过执行以下代码完成的：

CREATE TRIGGER set_timestamp_on_table_one
  BEFORE UPDATE ON table_one
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_set_timestamp();

‍

现在用一些数据填充table_one并按如下方式查看：

INSERT INTO table_one(id, name) VALUES(1, 'Eg1 IncApp');
INSERT INTO table_one(id, name) VALUES(2, 'Eg2 IncApp');
SELECT * FROM table_one;

‍

源table_one表应如下所示

 id |    name    |         updated_at         
----+------------+----------------------------
  1 | Eg1 IncApp | 2022-09-01 11:01:41.666004
  2 | Eg2 IncApp | 2022-09-01 11:01:41.68834
(2 rows)

‍

通过使用以下命令更新表中的一行，验证设置 updated_at 字段的触发器是否正确执行

UPDATE table_one SET name='Eg2a IncAp' WHERE id=2;
SELECT * FROM table_one;

‍

源table_one表应如下所示

 id |    name    |         updated_at         
----+------------+----------------------------
  1 | Eg1 IncApp | 2022-09-01 11:01:41.666004
  2 | Eg2a IncAp | 2022-09-01 11:02:05.017416
(2 rows)

‍

如果触发器已正确执行，则应看到 id=2 的记录的updated_at字段已修改，如上所示。

在源和目标之间创建连接

在本节中，您将创建一个连接，该连接将用于演示使用增量同步 |附加。 此新连接将使用您刚刚创建的连接器。

通过单击“连接”，然后单击“+ 新建连接”来创建新连接，如下所示（请注意，如果已实例化某些连接，则此按钮可能会出现在右上角）：

然后选择增量源源，如下所示：

选择增量目标，如下所示：

您将看到如下所示的设置页面。将连接的名称设置为增量同步演示，并按如下所示进行配置：

 

在上述配置中有几个区域进行了注释：

1. 定义将标识此连接的名称 - 在本例中，我将其称为增量同步演示。
2. 为名为 table_one 的表选择增量追加复制模式。
3. 选择“updated_at”作为table_one表的光标。

单击“设置连接”后，将开始初始同步。完成后，您应该会在同步历史记录中看到以下状态：

 

记下作业 ID 和尝试 ID，在本例中分别为 149 和 0，如上面的屏幕截图中的日志路径 （/tmp/workspace/149/0/logs.log） 所示.log 您将需要这些值来查找用于第一个增量追加同步的 SQL 代码。

初始创建：概述

在第一次同步中，Airbyte 会将源表中的所有记录复制到目标数据库中的原始表中。然后，执行 dbt 生成的 SQL 命令，将原始数据规范化为最终目标表，如下所示：

初始创建：查看 Postgres 目标

第一次同步完成后，您可以查看 Postgres 目标以查看复制数据的外观。执行以下命令以打开目标 Postgres 数据库的外壳：

docker exec -it airbyte-destination psql --username=postgres

‍

然后，您可以使用以下命令查看目标中表的名称：

\dt;

‍

这应该回应如下。

                 List of relations
 Schema |          Name          | Type  |  Owner   
--------+------------------------+-------+----------
 public | _airbyte_raw_table_one | table | postgres
 public | table_one              | table | postgres
(2 rows)

初始创建：在目标中查看原始表

作为每个同步操作的第一步，Airbyte 将源数据库中table_one的记录复制到目标数据库中名为 _airbyte_raw_table_one 的原始表中。通过执行以下命令查看其内容：

SELECT * FROM _airbyte_raw_table_one;

‍

它应该响应一个如下所示的表：

            _airbyte_ab_id            |                                _airbyte_data                                |    _airbyte_emitted_at     
--------------------------------------+-----------------------------------------------------------------------------+----------------------------
 2abc7493-bfc8-4493-ab62-de6ffe094a2d | {"id": 1, "name": "Eg1 IncApp", "updated_at": "2022-09-01T11:01:41.666004"} | 2022-09-01 11:12:03.301+00
 06e67da7-9c6a-46b6-a2e5-e1d102e16c7e | {"id": 2, "name": "Eg2a IncAp", "updated_at": "2022-09-01T11:02:05.017416"} | 2022-09-01 11:12:03.301+00
(2 rows)

‍

除了包含源数据的字段之外，原始表中还有两个附加字段：

• _airbyte_emitted_at告诉您 airbyte 将记录发送到目的地的时间。
• _airbyte_ab_id是目标连接器在将每条记录发送到目标之前添加到每条记录的 UUID 值。这是一个 UUID（不是哈希），因此每次同步后，即使数据尚未修改，它也会针对每一行进行更改。

初始创建：查看目标中的规范化表

通过执行以下命令查看规范化表的内容：

SELECT * FROM table_one;

‍

规范化table_one表如下所示

 id |    name    |         updated_at         |            _airbyte_ab_id            |    _airbyte_emitted_at     |    _airbyte_normalized_at     |    _airbyte_table_one_hashid     
----+------------+----------------------------+--------------------------------------+----------------------------+-------------------------------+----------------------------------
  1 | Eg1 IncApp | 2022-09-01 11:01:41.666004 | 2abc7493-bfc8-4493-ab62-de6ffe094a2d | 2022-09-01 11:12:03.301+00 | 2022-09-01 11:12:50.300405+00 | 43ed5ad798cc8204591c1254dabc9da8
  2 | Eg2a IncAp | 2022-09-01 11:02:05.017416 | 06e67da7-9c6a-46b6-a2e5-e1d102e16c7e | 2022-09-01 11:12:03.301+00 | 2022-09-01 11:12:50.300405+00 | 2ec9ac8c405a07d980f9559637ec60df
(2 rows)

‍

除了从原始表中提取的列外，此规范化表还包括以下附加字段：

• _airbyte_normalized_at告诉您记录何时规范化（即何时从原始数据创建记录）。
• _airbyte_table_one_hashid是在规范化期间计算并添加的源字段的 md5 哈希。dbt 博客上这篇关于 md5 哈希的文章讨论了 md5 哈希的值。但是，请注意，因为源表中的updated_at字段包含在哈希计算中（正如我将在讨论 SQL 时看到的那样），并且每次修改给定记录时都会更新它，这与我在第一个教程中介绍的示例不同（仅使用 id 和名称计算哈希）， 对于多个同步的给定记录，此哈希不一致。

初始创建：查看 SQL

为了理解上述列的创建过程，查看用于规范化数据的 SQL 代码会很有帮助。为此，您将需要您之前提取的感兴趣的同步操作中的作业 ID 和尝试 ID。

在 airbyte-server docker 容器中创建一个 bash shell，如下所示：

docker exec -it airbyte-server /bin/bash

‍

现在，您将查看用于从具有增量追加模式的名为 _airbyte_raw_table_one 的表中创建名为 table_one 的表的 SQL。我使用之前记下的作业 ID 149 和尝试 ID 0 来查看带有 cat 命令的 SQL，如下所示：

cat /tmp/workspace/149/0/build/run/airbyte_utils/models/generated/airbyte_incremental/public/table_one.sql

‍

这将返回已用于将原始数据转换为规范化表的 SQL。用于创建增量追加表的 SQL 与上一教程中讨论的完整刷新追加 SQL 相同。但是，与完全刷新追加模式相反，只有已在源中插入或更新的数据才复制到原始目标表中 – 这由源连接器处理，该连接器仅选择源上的新记录以复制到目标，因此不会影响 SQL。

i️ 由于此首次同步会创建目标表，因此后续同步中使用的 SQL 的组织方式不同。本教程稍后将对此进行讨论。

我将这个SQL分解为四个主要部分，供下面讨论。SQL 的第一部分显示代码创建最终的table_one表，并从原始_airbyte_raw_table_one json 表中提取各种字段。

  create  table "postgres".public."table_one"
  as (
    
with __dbt__cte__table_one_ab1 as (

-- SQL model to parse JSON blob stored in a single column and extract into separated field columns as described by the JSON Schema
-- depends_on: "postgres".public._airbyte_raw_table_one
select
    jsonb_extract_path_text(_airbyte_data, 'id') as "id",
    jsonb_extract_path_text(_airbyte_data, 'name') as "name",
    jsonb_extract_path_text(_airbyte_data, 'updated_at') as updated_at,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    now() as _airbyte_normalized_at
from "postgres".public._airbyte_raw_table_one as table_alias
-- table_one
where 1 = 1
), 

‍

SQL 的下一部分将每个字段强制转换为适当的类型，如下所示：

 __dbt__cte__table_one_ab2 as (

-- SQL model to cast each column to its adequate SQL type converted from the JSON schema type
-- depends_on: __dbt__cte__table_one_ab1
select
    cast("id" as 
    bigint
) as "id",
    cast("name" as text) as "name",
    cast(nullif(updated_at, '') as 
    timestamp
) as updated_at,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    now() as _airbyte_normalized_at
from __dbt__cte__table_one_ab1
-- table_one
where 1 = 1

),

‍

SQL 的下一部分将添加一个 md5 哈希。

 __dbt__cte__table_one_ab3 as (

-- SQL model to build a hash column based on the values of this record
-- depends_on: __dbt__cte__table_one_ab2
select
    md5(cast(coalesce(cast("id" as text), '') || '-' || coalesce(cast("name" as text), '') || '-' || coalesce(cast(updated_at as text), '') as text)) as _airbyte_table_one_hashid,
    tmp.*
from __dbt__cte__table_one_ab2 tmp
-- table_one
where 1 = 1

)

‍

最后，SQL 的最后一部分选择将写入目标table_one表中的字段。

-- Final base SQL model
-- depends_on: __dbt__cte__table_one_ab3
select
    "id",
    "name",
    updated_at,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    now() as _airbyte_normalized_at,
    _airbyte_table_one_hashid
from __dbt__cte__table_one_ab3
-- table_one from "postgres".public._airbyte_raw_table_one
where 1 = 1
  );

更新：概述

下图显示了在源系统中修改记录，然后执行同步时发生的情况。在这种情况下，将从源复制记录的更新版本，并将其追加到原始表和目标中的最终表。

i️ 请注意，即使源仅包含两条记录，原始目标表和规范化目标表也分别包含三条记录。这是因为  id 为 2 的记录的当前版本和先前版本（上一次同步的版本，其中 updated_at=t1 和新版本，其中  updated_at=t2）将存储在每个目标表中。

 

 

更新：修改源中的记录

返回到本教程前面打开的 airbyte-sour