将 Microsoft SQL Server 数据加载到 BigQuery 中

注意：如需获得有关此功能的支持或提供相关反馈，请联系 dts-preview-support@google.com。

您可以使用适用于 Microsoft SQL Server 的 BigQuery Data Transfer Service 连接器将数据从 Microsoft SQL Server 加载到 BigQuery。Microsoft SQL Server 连接器支持从托管在本地环境和其他云提供商（例如 Cloud SQL、Amazon Web Services [AWS] 或 Microsoft Azure）中的 Microsoft SQL Server 实例加载数据。借助 BigQuery Data Transfer Service，您可以创建按需数据转移作业和周期性数据转移作业，以将数据从 Microsoft SQL Server 实例转移到 BigQuery。

限制

Microsoft SQL Server 数据转移作业受到以下限制：

与 Microsoft SQL Server 数据库的并发连接数有限。因此，单个 Microsoft SQL Server 数据库的并发转移运行数也受限制。确保并发转移作业的数量小于 Microsoft SQL Server 数据库支持的并发连接数上限。
某些 Microsoft SQL Server 数据类型可能会映射到 BigQuery 中的 STRING 类型，以避免数据丢失。例如，Microsoft SQL Server 中未定义精度和标度的某些数值类型可能会映射到 BigQuery 中的 STRING。如需了解详情，请参阅数据类型映射。

增量转移限制

增量 Microsoft SQL Server 转移作业受到以下限制：

您只能选择 TIMESTAMP 列作为水位线列。
仅当资产具有有效的水位线列时，才支持增量提取。
水位线列中的值必须单调递增。
增量转移无法同步源表中的删除操作。
单个转移配置只能支持增量提取或完整提取。
在首次增量提取运行后，您无法更新 asset 列表中的对象。
在首次增量提取运行后，您无法更改转移配置中的写入模式。
在首次增量提取运行后，您无法更改水位线列或主键。
目标 BigQuery 表使用提供的主键进行聚簇，并受聚簇表限制约束。
首次将现有转移配置更新为增量提取模式时，更新后的首次数据转移会转移数据源中的所有可用数据。任何后续增量数据转移作业都只会转移数据源中的新行和更新行。
我们建议您在水印列上创建索引。此连接器在增量传输中使用水位列进行过滤，因此为这些列编制索引可以提高性能。
进行增量转移时，您必须使用更新的数据类型映射。

数据注入选项

以下部分介绍了设置 Microsoft SQL Server 数据转移作业时可用的数据注入选项。

TLS 配置

Microsoft SQL Server 连接器支持配置传输层安全性 (TLS)，以加密向 BigQuery 传输的数据。Microsoft SQL Server 连接器支持以下 TLS 配置：

加密数据，并验证 CA 和主机名模式。此模式使用基于 TCPS 协议的 TLS 对服务器执行完整验证。它会加密传输中的所有数据，并验证数据库服务器的证书是否由受信任的证书授权机构 (CA) 签名。此模式还会检查您要连接的主机名是否与服务器证书上的通用名称 (CN) 或正文备用名称 (SAN) 完全一致。此模式可防止攻击者使用其他网域的有效证书来冒充您的数据库服务器。

如果您的主机名与证书 CN 或 SAN 不匹配，连接会失败。您必须配置与证书匹配的 DNS 解析，或使用其他安全模式。使用此模式可获得最安全的选项，以防止中间人 (PITM) 攻击。
加密数据，仅验证 CA 模式。此模式使用基于 TCPS 协议的 TLS 加密所有数据，并验证服务器的证书是否由客户端信任的 CA 签名。不过，此模式不会验证服务器的主机名。只要证书有效且由可信的 CA 颁发，此模式就能成功连接，无论证书中的主机名是否与您要连接的主机名匹配。

如果您想确保自己连接到的服务器的证书是由可信的 CA 签名的，但主机名无法验证，或者您无法控制主机名配置，请使用此模式。
仅加密模式。此模式会加密客户端与服务器之间传输的所有数据。它不会执行任何证书或主机名验证。

此模式通过保护传输中的数据来提供一定程度的安全性，但可能会受到 PITM 攻击。

如果您需要确保所有数据都经过加密，但无法或不想验证服务器的身份，请使用此模式。建议在处理专用 VPC 时使用此模式。
不加密或不验证模式。此模式不会加密任何数据，也不会执行任何证书或主机名验证。所有数据都以纯文本形式发送。

我们不建议在处理敏感数据的环境中使用此模式。我们建议仅在安全不是问题的隔离网络中将此模式用于测试目的。

可信服务器证书 (PEM)

如果您使用的是加密数据，并验证 CA 和主机名模式或加密数据，并验证 CA 模式，则还可以提供一个或多个 PEM 编码的证书。在某些情况下，BigQuery Data Transfer Service 需要在 TLS 连接期间验证数据库服务器的身份，此时需要这些证书：

如果您使用的是由组织内的私有 CA 签名的证书或自签名证书，则必须提供完整的证书链或单个自签名证书。对于托管云提供商服务（例如 Amazon Relational Database Service (RDS)）的内部证书授权机构 (CA) 颁发的证书，这是必需的。
如果您的数据库服务器证书是由公共 CA（例如 Let's Encrypt、DigiCert 或 GlobalSign）签名的，则无需提供证书。这些公共 CA 的根证书已预安装并受 BigQuery Data Transfer Service 信任。

您可以在转移配置的 Trusted PEM Certificate（受信任的 PEM 证书）字段中指定 PEM 编码的证书，但需满足以下要求：

证书必须是有效的 PEM 编码证书链。
证书必须完全正确。链中缺少任何证书或内容不正确都会导致 TLS 连接失败。
对于单个证书，您可以提供数据库服务器中的单个自签名证书。
对于由私有 CA 颁发的完整证书链，您必须提供完整的信任链。这包括数据库服务器的证书以及任何中间 CA 证书和根 CA 证书。

完整转移或增量转移

在设置 Microsoft SQL Server 转移作业时，您可以在转移作业配置中选择完整或增量写入偏好设置，以指定数据加载到 BigQuery 的方式。预览版支持增量转移。

您可以配置完整数据转移，以便在每次数据转移时转移 Microsoft SQL Server 数据集中的所有数据。

或者，您也可以配置增量数据转移（预览版），以仅转移自上次数据转移以来更改的数据，而不是在每次数据转移时都加载整个数据集。如果您为数据转移选择增量，则必须指定附加或 upsert 写入模式，以定义在增量数据转移期间如何将数据写入 BigQuery。以下各部分介绍了可用的写入模式。

附加写入模式

附加写入模式只会向目标表中插入新行。此选项会严格附加转移的数据，而不检查是否存在现有记录，因此这种模式可能会导致目标表中的数据重复。

选择附加模式时，您必须选择水位线列。Microsoft SQL Server 连接器需要一个水位线列来跟踪源表中的更改。

对于 Microsoft SQL Server 转移，我们建议选择仅在创建记录时更新的列，且该列不会随后续更新而变化。例如，CREATED_AT 列。

Upsert 写入模式

upsert 写入模式会通过检查主键来更新目标表中的行或在其中插入新行。您可以指定主键，以便 Microsoft SQL Server 连接器确定需要进行哪些更改才能使目标表与源表保持同步。如果在数据转移期间，指定的主键存在于目标 BigQuery 表中，则 Microsoft SQL Server 连接器会使用源表中的新数据更新该行。如果在数据转移期间不存在主键，则 Microsoft SQL Server 连接器会插入新行。

选择更新/插入模式时，您必须选择水位线列和主键：

Microsoft SQL Server 连接器需要一个水位线列来跟踪源表中的更改。
- 选择一个每次修改行时都会更新的水位线列。我们建议使用与 UPDATED_AT 或 LAST_MODIFIED 列类似的列。

主键可以是表中的一列或多列，Microsoft SQL Server 连接器需要使用这些列来确定是否需要插入或更新行。

选择包含非 null 值且在表的所有行中都唯一的列。我们建议使用包含系统生成的标识符、唯一参考代码（例如自动递增的 ID）或不可变、基于时间的序列 ID 的列。

为防止潜在的数据丢失或数据损坏，您选择的主键列必须具有唯一值。如果您对所选主键列的唯一性有疑问，建议您改用附加写入模式。

增量提取行为

当您更改数据源中的表架构时，这些表中的增量数据转移会在 BigQuery 中以以下方式反映出来：

数据源变更	增量提取行为
添加新列	系统会在目标 BigQuery 表中添加一个新列。此列的所有先前记录都将具有 null 值。
删除列	已删除的列仍保留在目标 BigQuery 表中。系统会使用 null 值填充此已删除列的新条目。
更改列中的数据类型	该连接器仅支持 `ALTER COLUMN` DDL 语句支持的数据类型转换。任何其他数据类型转换都会导致数据转移失败。如果您遇到任何问题，建议您创建新的转移配置。
重命名列	原始列会按原样保留在目标 BigQuery 表中，同时系统会向目标表添加一个具有更新名称的新列。

准备工作

在安排 Microsoft SQL Server 数据转移作业之前，您必须满足以下前提条件。

Microsoft SQL Server 前提条件

您必须已在 Microsoft SQL Server 数据库中创建用户账号。如需了解详情，请参阅创建具有登录权限的用户。

BigQuery 前提条件

确认您已完成启用 BigQuery Data Transfer Service 所需的所有操作。
创建 BigQuery 数据集来存储数据。

所需的角色

如需获得创建 Microsoft SQL Server 数据转移所需的权限，请让您的管理员为您授予项目的 BigQuery Admin (roles/bigquery.admin) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

此预定义角色包含创建 Microsoft SQL Server 数据转移所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

创建 Microsoft SQL Server 数据转移作业需要以下权限：

bigquery.transfers.update
bigquery.datasets.get

您也可以使用自定义角色或其他预定义角色来获取这些权限。

网络配置

如果公共 IP 地址不适用于 Microsoft SQL Server 数据库连接，您必须设置特定的网络配置。如需了解详情，请参阅以下部分：

设置 Microsoft SQL Server 数据转移作业

从下列选项中选择一项：

控制台

进入数据传输页面。

转到“数据传输”
点击 创建转移作业。
在来源类型部分的来源中，选择 Microsoft SQL Server。
在数据源详细信息部分，执行以下操作：
- 对于网络连接，选择现有网络连接或点击创建网络连接。
- 对于主机，输入 Microsoft SQL Server 数据库的主机名或 IP 地址。
- 对于端口号，输入 Microsoft SQL Server 数据库的端口号。
- 对于数据库名称，输入 Microsoft SQL Server 数据库的名称。
- 对于用户名，输入启动 Microsoft SQL Server 数据库连接的 Microsoft SQL Server 用户的用户名。
- 对于密码，输入启动 Microsoft SQL Server 数据库连接的 Microsoft SQL Server 用户的密码。
- 对于 TLS 模式，请从菜单中选择一个选项。如需详细了解 TLS 模式，请参阅 TLS 配置。
- 对于可信 PEM 证书，请输入颁发数据库服务器 TLS 证书的证书授权机构 (CA) 的公共证书。如需了解详情，请参阅可信服务器证书 (PEM)。
- 对于启用旧版映射，选择 true（默认）以使用旧版数据类型映射。选择 false 以使用更新的数据类型映射。如果您要进行增量转移，此值必须为 false。如需详细了解数据类型映射更新，请参阅 2027 年 3 月 16 日。数据库服务器。
- 对于注入类型，请选择完整或增量。
  - 如果您选择增量（预览版），请为写入模式选择附加或更新/插入。如需详细了解不同的写入模式，请参阅完整转移或增量转移。
- 对于要转移的 Microsoft SQL Server 对象，请浏览 Microsoft SQL Server 表，或手动输入转移所需的表的名称。
在目标设置部分，对于数据集，选择您创建用来存储数据的数据集，或点击创建新数据集，然后创建一个数据集用作目标数据集。
在转移作业配置名称部分的显示名称中，输入转移作业的名称。转移作业名称可以是任何易于识别该转移作业的值，以便您以后在需要修改时找到转移作业。
在时间表选项部分，执行以下操作：
- 选择重复频率。如果您选择小时、天（默认）、周或月选项，还必须指定频率。您还可以选择自定义选项来创建更具体的重复频率。如果您选择按需选项，则只有当您手动触发转移作业时，此数据转移作业才会运行。
- 如果适用，请选择立即开始或从设置的时间开始选项，并提供开始日期和运行时间。
可选：在通知选项部分，执行以下操作：
- 如需启用邮件通知，请点击邮件通知切换开关，将其切换到开启位置。启用此选项后，转移作业管理员会在转移作业运行失败时收到邮件通知。
- 如需为转移作业配置 Pub/Sub 运行通知，请点击 Pub/Sub 通知切换开关，将其切换到开启位置。您可以选择主题名称，也可以点击创建主题来创建一个主题。
可选：在高级选项部分中，为此迁移选择加密类型。您可以选择 Google-owned and Google-managed encryption key或客户拥有的 Cloud Key Management Service 密钥。如需详细了解加密密钥，请参阅客户管理的加密密钥 (CMEK)。
点击保存。

bq

输入 bq mk 命令并提供转移作业创建标志 --transfer_config：

bq mk \
    --transfer_config \
    --project_id=PROJECT_ID \
    --data_source=DATA_SOURCE \
    --display_name=DISPLAY_NAME \
    --target_dataset=DATASET \
    --params='PARAMETERS'

替换以下内容：

PROJECT_ID（可选）：您的 Google Cloud 项目 ID。如果未提供 --project_id 标志来指定特定项目，则系统会使用默认项目。
DATA_SOURCE：数据源，即 sqlserver。
DISPLAY_NAME：数据转移作业配置的显示名称。转移作业名称可以是任何可让您在需要修改转移作业时识别该转移作业的名称。
DATASET：数据转移作业配置的目标数据集。
PARAMETERS：所创建转移作业配置的参数（采用 JSON 格式）。例如 --params='{"param":"param_value"}'。以下是 Microsoft SQL Server 转移作业的参数：
- connector.networkAttachment（可选）：要连接到 Microsoft SQL Server 数据库的网络连接的名称。
- connector.database：Microsoft SQL Server 数据库的名称。
- connector.endpoint.host：数据库的主机名或 IP 地址。
- connector.endpoint.port：数据库的端口号。
- connector.authentication.username：数据库用户的用户名。
- connector.authentication.password：数据库用户的密码。
- connector.legacyMapping：设置为 true（默认值）以使用旧版数据类型映射。设置为 false 可使用更新的数据类型映射。如果您要进行增量转移，此值必须为 false。如需详细了解数据类型映射更新，请参阅 2027 年 3 月 16 日。
- connector.tls.mode：指定要用于此转移的 TLS 配置：
  - ENCRYPT_VERIFY_CA_AND_HOST 用于加密数据，并验证 CA 和主机名
  - ENCRYPT_VERIFY_CA 用于加密数据，并且仅验证 CA
  - ENCRYPT_VERIFY_NONE 仅用于数据加密
  - DISABLE 表示不加密或不验证
- connector.tls.trustedServerCertificate：（可选）提供一个或多个 PEM 编码的证书。仅当 connector.tls.mode 的值为 ENCRYPT_VERIFY_CA_AND_HOST 或 ENCRYPT_VERIFY_CA 时才需要。
- ingestionType：指定 FULL 或 INCREMENTAL。预览版支持增量转移。如需了解详情，请参阅完整转移或增量转移。
- writeMode：指定 WRITE_MODE_APPEND 或 WRITE_MODE_UPSERT。
- watermarkColumns：将表中的列指定为水位线列。此字段是增量转移的必需字段。
- primaryKeys：将表中的列指定为主键。此字段是增量转移的必需字段。
- assets：要从 Microsoft SQL Server 数据库转移的 Microsoft SQL Server 表的名称列表（作为转移的一部分）。

例如，以下命令会创建一个名为 My Transfer 的 Microsoft SQL Server 转移：

bq mk \
    --transfer_config
    --target_dataset=mydataset
    --data_source=sqlserver
    --display_name='My Transfer'
    --params='{"assets":["DB1/DEPARTMENT","DB1/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":"1520",
        "connector.networkAttachment":"projects/dev-project1/regions/us-central1/networkattachments/na1",
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

在增量转移期间指定多个资产时，watermarkColumns 和 primaryKeys 字段的值对应于 assets 字段中值的位置。在以下示例中，dep_id 对应于表 DB1/DEPARTMENT，而 report_by 和 report_title 对应于表 DB1/EMPLOYEES。

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/DEPARTMENT","DB1/EMPLOYEES"],

保存转移配置后，Microsoft SQL Server 连接器会根据您的安排选项自动触发转移运行。在每次转移运行时，Microsoft SQL Server 连接器都会将 Microsoft SQL Server 中的所有可用数据转移到 BigQuery。

如需在常规安排之外手动运行数据转移，您可以启动回填运行。

数据类型映射

下表将 Microsoft SQL Server 数据类型映射到相应的 BigQuery 数据类型：

Microsoft SQL Server 数据类型	BigQuery 数据类型	更新后的 BigQuery 数据类型
`tinyint`	`INTEGER`
`smallint`	`INTEGER`
`int`	`INTEGER`
`bigint`	`BIGNUMERIC`
`bit`	`BOOLEAN`
`decimal`	`BIGNUMERIC`
`numeric`	`NUMERIC`
`money`	`BIGNUMERIC`
`smallmoney`	`BIGNUMERIC`
`float`	`FLOAT`
`real`	`FLOAT`
`date`	`DATE`
`time`	`TIME`
`datetime2`	`TIMESTAMP`	`DATETIME`
`datetimeoffset`	`TIMESTAMP`
`datetime`	`TIMESTAMP`	`DATETIME`
`smalldatetime`	`TIMESTAMP`	`DATETIME`
`char`	`STRING`
`varchar`	`STRING`
`text`	`STRING`
`nchar`	`STRING`
`nvarchar`	`STRING`
`ntext`	`STRING`
`binary`	`BYTES`
`varbinary`	`BYTES`
`image`	`BYTES`
`geography`	`STRING`
`geometry`	`STRING`
`hierarchyid`	`BYTES`
`rowversion`	`BYTES`
`sql_variant`	`BYTES`
`uniqueidentifier`	`STRING`
`xml`	`STRING`
`json`	`STRING`
`vector`	`STRING`

json 和 vector 数据类型仅在 Azure 中受支持。

Azure SQL 数据库和 Azure SQL 托管实例（配置为始终保持最新状态的更新政策）支持 JSON 数据类型。如果 Azure SQL 托管实例配置了 Microsoft SQL Server 2022 更新政策，则不支持 JSON 数据类型。

Microsoft SQL Server 将 JSON 存储为 NVARCHAR(MAX)，而不是 JSON 类型。我们建议您使用 CHECK (ISJSON(json_col) = 1) 进行验证，并使用 JSON_VALUE() 进行查询。

Microsoft SQL Server 不支持 vector 数据类型的向量。我们建议您将向量存储为 NVARCHAR(MAX) 中的 JSON 数组，并使用 JSON_VALUE() 进行提取，同时手动进行 FLOAT 相似度计算。

问题排查

如需排查数据转移问题，请参阅 Microsoft SQL Server 转移问题。

价格

将 Microsoft SQL Server 数据转移到 BigQuery 的功能处于预览版阶段时，您无需付费即可使用此功能。

后续步骤

阅读 BigQuery Data Transfer Service 概览。
了解如何管理转移作业，包括获取有关转移作业配置的信息、列出转移作业配置以及查看转移作业的运行历史记录。
了解如何通过跨云操作加载数据。