将 PostgreSQL 数据加载到 BigQuery 中

您可以使用 BigQuery Data Transfer Service for PostgreSQL 连接器将数据从 PostgreSQL 加载到 BigQuery。该连接器支持托管在本地环境、Cloud SQL 以及其他公共云提供商（例如 Amazon Web Services [AWS] 和 Microsoft Azure）中的 PostgreSQL 实例。借助 BigQuery Data Transfer Service，您可以安排周期性转移作业，以将 PostgreSQL中的最新数据添加到 BigQuery。

限制

PostgreSQL 数据转移作业受到以下限制：

单个 PostgreSQL 数据库的并发迁移作业运行数上限取决于 PostgreSQL 数据库支持的并发连接数上限。并发转移作业的数量应限制为小于 PostgreSQL 数据库支持的并发连接数上限的值。
单个转移配置在给定时间只能支持一次数据转移运行。如果第二次数据转移的预定运行时间在第一次转移完成之前，则只有第一次数据转移会完成，而与第一次转移时间重叠的任何其他数据转移都会被跳过。
- 为避免在单个转移配置中跳过转移，建议您通过配置重复频率来增加大型数据转移之间的时长。

数据注入选项

以下部分介绍了设置 PostgreSQL 数据转移时可用的数据注入选项。

TLS 配置

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

加密数据，并验证 CA 和主机名：此模式使用 TLS 通过 TCPS 协议对服务器执行完整验证。它会对传输中的所有数据进行加密，并验证数据库服务器的证书是否由可信的证书授权机构 (CA) 签名。此模式还会检查您要连接的主机名是否与服务器证书上的通用名称 (CN) 或主题备用名称 (SAN) 完全一致。此模式可防止攻击者使用其他网域的有效证书来冒充您的数据库服务器。
- 如果您的主机名与证书 CN 或 SAN 不匹配，连接会失败。您必须配置与证书匹配的 DNS 解析，或使用其他安全模式。
- 使用此模式可获得最安全的选项，以防止中间人 (PITM) 攻击。
加密数据，仅验证 CA：此模式使用 TCPS 协议通过 TLS 加密所有数据，并验证服务器的证书是否由客户端信任的 CA 签名。不过，此模式不会验证服务器的主机名。只要证书有效且由可信的 VA 签发，此模式就能成功连接，无论证书中的主机名是否与您要连接的主机名匹配。
- 如果您想确保连接到证书由可信 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 的信任。

创建 PostgreSQL 转移配置时，您可以在受信任的 PEM 证书字段中提供 PEM 编码的证书，但需满足以下要求：

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

准备工作

在 PostgreSQL 数据库中创建用户。
确认您已完成启用 BigQuery Data Transfer Service 所需的所有操作。
创建 BigQuery 数据集来存储数据。
确保您拥有所需的角色，以便完成本文档中的任务。

所需的角色

如果您想要为 Pub/Sub 设置转移作业运行通知，请确保拥有 pubsub.topics.setIamPolicy Identity and Access Management (IAM) 权限。如果您只是要设置邮件通知，则无需 Pub/Sub 权限。如需了解详情，请参阅 BigQuery Data Transfer Service 运行通知。

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

此预定义角色可提供创建 BigQuery Data Transfer Service 数据转移作业所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

创建 BigQuery Data Transfer Service 数据转移作业需要以下权限：

BigQuery Data Transfer Service 权限：
- bigquery.transfers.update
- bigquery.transfers.get
BigQuery 权限：
- bigquery.datasets.get
- bigquery.datasets.getIamPolicy
- bigquery.datasets.update
- bigquery.datasets.setIamPolicy
- bigquery.jobs.create

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

如需了解详情，请参阅授予 bigquery.admin 访问权限。

网络连接数

如果 PostgreSQL 数据库连接没有可用的公共 IP 地址，您必须设置网络连接。

如需详细了解所需的网络设置，请参阅以下文档：

如果您要从 Cloud SQL 转移，请参阅配置 Cloud SQL 实例访问权限。
如果您要从 AWS 转移，请参阅设置 AWS-Google Cloud VPN 和网络连接。
如果您要从 Azure 转移，请参阅设置 Azure-Google Cloud VPN 和网络连接。

设置 PostgreSQL 数据转移

通过使用以下选项之一设置转移配置，将 PostgreSQL 数据添加到 BigQuery 中：

控制台

进入数据传输页面。

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

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：数据源，即 postgresql。
DISPLAY_NAME：数据转移作业配置的显示名称。转移作业名称可以是任何可让您在需要修改转移作业时识别该转移作业的名称。
DATASET：数据转移作业配置的目标数据集。
PARAMETERS：所创建转移作业配置的参数（采用 JSON 格式）。例如 --params='{"param":"param_value"}'。以下是 PostgreSQL转移作业的参数：
- connector.networkAttachment（可选）：要连接到 PostgreSQL 数据库的网络连接的名称。
- connector.database：PostgreSQL 数据库的名称。
- connector.endpoint.host：数据库的主机名或 IP 地址。
- connector.endpoint.port：数据库的端口号。
- connector.authentication.username：数据库用户的用户名。
- connector.authentication.password：数据库用户的密码。
- 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 时才需要提供此值。
- assets：要从 PostgreSQL 数据库转移的 PostgreSQL 表的名称列表（作为转移作业的一部分）。

例如，以下命令会创建一个名为 My Transfer 的 PostgreSQL 转移作业：

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

API

使用 projects.locations.transferConfigs.create 方法并提供 TransferConfig 资源实例。

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

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

数据类型映射

下表将 PostgreSQL 数据类型映射到相应的 BigQuery 数据类型。

PostgreSQL 数据类型	BigQuery 数据类型
`bigint`	`INTEGER`
`bigserial`	`INTEGER`
`bit(n)`	`STRING`
`bit varying(n)`	`STRING`
`boolean`	`BOOLEAN`
`bytea`	`BYTES`
`character`	`STRING`
`character varying`	`STRING`
`double precision`	`FLOAT`
`integer`	`INTEGER`
`numeric(precision, scale)/decimal(precision, scale)`	`NUMERIC`
`real`	`FLOAT`
`smallint`	`INTEGER`
`smallserial`	`INTEGER`
`serial`	`INTEGER`
`text`	`STRING`
`date`	`DATE`
`time [ (p) ] [ without timezone ]`	`TIMESTAMP`
`time [ (p) ] with time zone`	`TIMESTAMP`
`timestamp [ (p) ] [ without timezone ]`	`TIMESTAMP`
`timestamp [ (p) ] with time zone`	`TIMESTAMP`
`xml`	`STRING`
`tsquery`	`STRING`
`tsvector`	`STRING`
`uuid`	`STRING`
`box`	`STRING`
`cidr`	`STRING`
`circle`	`STRING`
`inet`	`STRING`
`interval`	`STRING`
`json`	`STRING`
`jsonb`	`STRING`
`line`	`STRING`
`lseg`	`STRING`
`macaddr`	`STRING`
`macaddr8`	`STRING`
`money`	`STRING`
`path`	`STRING`
`point`	`STRING`
`polygon`	`STRING`

问题排查

如果您在设置数据转移作业时遇到问题，请参阅 PostgreSQL转移作业问题。

后续步骤

如需大致了解 BigQuery Data Transfer Service，请参阅什么是 BigQuery Data Transfer Service？。
如需了解如何使用转移作业，包括获取有关转移作业配置的信息、列出转移作业配置以及查看转移作业的运行历史记录，请参阅管理转移作业。
了解如何通过跨云操作加载数据。