运行 PostgreSQL 联合查询

作为数据分析师，您可以直接从 BigQuery 实时查询 PostgreSQL 中的关系型数据，而无需复制或移动数据。

BigQuery PostgreSQL 联合查询使用 BigQuery Connection API 连接到 PostgreSQL 数据库引擎。然后，您可以使用 EXTERNAL_QUERY 函数将 SQL 查询直接发送到 PostgreSQL 进行执行，结果会直接流式传输回来。

限制

PostgreSQL 联合查询存在以下限制：

  • 您只能通过 EXTERNAL_QUERY 函数执行只读查询。不支持数据定义语言 (DDL) 和数据操纵语言 (DML) 语句。
  • 只能通过 EXTERNAL_QUERY 函数访问联合。不支持创建直接映射到 PostgreSQL 表的 BigQuery 外部表或永久数据集。
  • 联合查询延迟时间可能会明显长于直接在 PostgreSQL 中执行查询。
  • 底层连接服务可能会使非活跃连接进入休眠状态。在一段时间不活动后，使用 PostgreSQL 连接的第一个查询可能需要超过一分钟的时间才能运行，因为连接器需要初始化。
  • 不支持 DNS 对等互连。您必须使用 VPC 网络中的 IP 地址或完全可解析的主机名进行连接。
  • PostgreSQL SQL 查询必须为包含函数结果或表达式（例如 SELECT COUNT(*) AS total_count）的任何列指定显式列别名。无别名的表达式会生成包含特殊字符的列名，这些字符在 BigQuery 表架构定义中无效。
  • 当查询项目中 Compute Engine API 的使用受 VPC Service Controls 的限制时，如果连接使用网络连接，则联合查询会失败。确保配置了适当的 VPC Service Controls 入站和出站规则。

  • PostgreSQL 不支持其他 SQL 下推。外部 BigQuery 查询中的过滤条件和聚合不会下推到 EXTERNAL_QUERY 函数内的内部查询中。您必须直接在内部 PostgreSQL SQL 语句中指定谓词和列投影，以限制通过网络传输的行和列。

  • 仅支持 PostgreSQL 数据库用户名和密码身份验证。不支持第三方身份提供方、Kerberos 和云提供商 IAM 身份验证。

  • 如果查询选择 16 位整数列（int2、smallint、smallserial）或单精度浮点列（float4、real），则 CREATE TABLE ... AS SELECT (CTAS) 语句会失败，并显示 Unsupported type INT32 或 Unsupported type FLOAT 错误。如需将查询结果保存到新的 BigQuery 表中，请在查询中将整数列显式转换为 INT64，将浮点列显式转换为 FLOAT64：

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    使用 INSERT INTO <table> SELECT ... 插入到现有表时，无需手动转换即可成功，因为 BigQuery 会自动扩大 INT32 和 FLOAT 值，以匹配目标表的 INT64 和 FLOAT64 列。

  • time [ without time zone ] 和 time with time zone (timetz) 都会将秒以下的小数（微秒）值截断为整秒。如果需要微秒精度，请在 EXTERNAL_QUERY 语句中将列格式化或转换为 text 或 varchar。

  • 所有 PostgreSQL numeric 和 decimal 列都会映射到 BIGNUMERIC(76, 38)。小数位数大于 38 的列会四舍五入到 38 位小数，超出 BIGNUMERIC 范围（超过 38 位完整整数）的值会失败，并显示超出范围错误。此外，不支持特殊值 NaN、Infinity 和 -Infinity，这些值会在运行时失败。如需查询具有较大整数部分、较高小数精度或特殊值的数字，请在 EXTERNAL_QUERY 语句中将列转换为 text。

  • 映射到 BigQuery STRING 时，固定长度的 character(n) / char(n) (bpchar) 值会保留尾随空格，直到长度达到 n。

  • 由于历史上的公历转换，1582 年 10 月 15 日之前的日期和时间戳会发生 10 天的日历偏移。在 EXTERNAL_QUERY 语句中将历史日期转换为 text，以保留确切日期。

支持的 PostgreSQL 环境和版本

联合查询功能支持以下部署环境中的 PostgreSQL 数据库：

  • 可通过公共 IP 地址或网络连接（通过 Cloud VPN 或 Cloud Interconnect）访问的云托管或本地 PostgreSQL 数据库
  • Compute Engine 或其他基础架构上的自托管 PostgreSQL 实例
  • PostgreSQL 16（推荐）和 PostgreSQL 15

准备工作

确保您的 BigQuery 管理员已创建 PostgreSQL 连接并与您共享。

所需的角色

如需获得查询 PostgreSQL 所需的权限，请让管理员授予您项目的 BigQuery Connection User (roles/bigquery.connectionUser) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

您也可以通过自定义角色或其他预定义角色来获取所需的权限。

查询数据

如需将联合查询从 GoogleSQL 查询发送到 PostgreSQL，请使用 EXTERNAL_QUERY 函数。

以下示例是一个联合查询，该查询将 BigQuery 中名为 mydataset.customers 的客户记录与 PostgreSQL 中的交易销售订单联接：

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

查看 PostgreSQL 表架构

您可以使用 EXTERNAL_QUERY 查询 PostgreSQL information_schema 表，以检索数据库元数据：

列出架构中的所有表

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

查看表中的列和数据类型

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

数据类型映射

执行联合查询时，BigQuery 会根据下表将 PostgreSQL 中的数据转换为 GoogleSQL 数据类型：

PostgreSQL 数据类型 BigQuery 数据类型
布尔值类型
bool，boolean BOOL
数值类型
int2、smallint、smallserial INT64（交互式查询；请参阅 CREATE TABLE ... AS SELECT 的限制）
int4、integer、serial INT64
int8、bigint、bigserial INT64
numeric，decimal BIGNUMERIC(76, 38)
float4，real FLOAT64（交互式查询；请参阅 CREATE TABLE ... AS SELECT 的限制）
float8，double precision FLOAT64
日期和时间类型
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone，timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone，timetz TIME
字符和文本类型
character(n)、char(n)、bpchar STRING
character varying(n)，varchar(n) STRING
text STRING

价格

运行联合查询的费用取决于三个因素：

  • 在 PostgreSQL 中执行查询的计算费用（由 PostgreSQL 数据库提供商或托管环境收取）
  • 将查询结果从 PostgreSQL 转移到 BigQuery 的带宽费用（数据库托管服务提供商收取的出站流量网络费用）
  • 在 BigQuery 中执行查询的计算费用

任何 PostgreSQL 计算和出站流量费用均由您的 PostgreSQL 提供商或基础设施托管服务商直接计费，而不是由 Google 计费。为了限制带宽费用，我们建议您在 EXTERNAL_QUERY 语句中编写查询，使其排除所有不需要计算最终结果的列和行。

在 BigQuery 中运行联合查询不会产生额外费用。如需详细了解 BigQuery 价格，请参阅价格。

如果您的连接使用 Google Cloud 网络组件，则可能还需要支付以下费用：

后续步骤