将 PostgreSQL 数据加载到 BigQuery 中

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

限制

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

  • 单个 PostgreSQL 数据库的并发迁移作业运行数上限取决于 PostgreSQL 数据库支持的并发连接数上限。并发迁移作业的数量应限制为小于 PostgreSQL 数据库支持的并发连接数上限的值。
  • 在 PostgreSQL 中,某些数据类型会映射到 BigQuery 中的字符串类型,以避免任何数据丢失。例如,在 PostgreSQL 中定义的没有明确精度和标度的数值类型会映射到 BigQuery 中的字符串类型。

准备工作

所需的角色

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

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

此预定义角色包含创建 PostgreSQL 数据转移作业所需的权限。如需查看所需的确切权限,请展开所需权限部分:

所需权限

创建 PostgreSQL 数据转移作业需要以下权限:

  • bigquery.transfers.update
  • bigquery.datasets.get

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

网络连接数

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

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

设置 PostgreSQL 数据转移作业

从下列选项中选择一项:

控制台

  1. 进入数据传输页面。

    转到“数据传输”

  2. 点击 创建转移作业

  3. 来源类型部分的来源中,选择 PostgreSQL

  4. 转移配置名称部分的显示名中,输入转移作业的名称。转移作业名称可以是任何易于识别该作业的值,以便您以后在需要修改时找到作业。

  5. 时间表选项部分,执行以下操作:

    • 选择重复频率。如果您选择小时(默认)、选项,还必须指定频率。您还可以选择自定义选项来创建更具体的重复频率。如果您选择按需选项,则只有当您手动触发转移作业时,此数据转移作业才会运行。
    • 如果适用,请选择立即开始从设置的时间开始选项,并提供开始日期和运行时间。
  6. 目标设置部分的数据集字段中,选择您创建用来存储数据的数据集,或点击新建数据集,然后创建一个数据集用作目标数据集。

  7. 数据源详细信息部分,执行以下操作:

    • 对于网络连接,选择现有网络连接或点击创建网络连接。如需了解详情,请参阅本文档的网络连接部分。
    • 对于主机,输入 PostgreSQL 数据库服务器的主机名或 IP 地址。
    • 对于端口号,输入 PostgreSQL 数据库服务器的端口号。
    • 对于数据库名称,输入 PostgreSQL 数据库的名称。
    • 对于用户名,请输入启动 PostgreSQL 数据库连接的 PostgreSQL 用户的用户名。
    • 对于密码,请输入发起 PostgreSQL 数据库连接的 PostgreSQL 用户的密码。
    • 对于要转移的 PostgreSQL 对象,请执行以下任一操作:

      • 点击浏览以选择转移所需的 PostgreSQL 表,然后点击选择
      • 手动输入要转移的 PostgreSQL 对象中表的名称。

    “创建转移”页面上的数据源详细信息

  8. 可选:在服务账号菜单中,指定用于授权转移的自定义服务账号。确保所用的服务账号具有所有必要的角色和权限。如需了解详情,请参阅以服务账号身份转移所有权

  9. 可选:在通知选项部分,执行以下操作:

    • 如需启用电子邮件通知,请点击电子邮件通知切换开关,将其切换到开启位置。启用此选项后,转移作业管理员会在转移作业运行失败时收到电子邮件通知。
    • 如需为转移作业配置 Pub/Sub 运行通知,请点击 Pub/Sub 通知切换开关,将其切换到开启位置。您可以选择主题名称,也可以点击创建主题来创建一个主题。
  10. 点击保存

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 转移作业的参数:

    • networkAttachment(可选):要连接到 PostgreSQL 数据库的网络连接的名称。
    • connector.database:PostgreSQL 数据库的名称。
    • connector.endpoint.host:数据库的主机名或 IP 地址。
    • connector.endpoint.port:数据库的端口号。
    • connector.authentication.username:数据库用户的用户名。
    • connector.authentication.password:数据库用户的密码。
    • 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}'

API

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

数据类型映射

下表将 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 转移作业问题

后续步骤