AlloyDB 联合查询

作为数据分析师，您可以使用联合查询从 BigQuery 查询 AlloyDB for PostgreSQL 中的数据。

借助 BigQuery AlloyDB 联合，BigQuery 能够实时查询驻留在 AlloyDB 中的数据，而无需复制或移动数据。

准备工作

查询数据

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

假设您在 BigQuery 中存储客户表，同时在 AlloyDB 中存储销售表，您希望在单个查询中联接这两个表。以下示例向名为 orders 的 AlloyDB 表发出联合查询，并将结果与名为 mydataset.customers 的 BigQuery 表联接。

此查询示例包括 3 个部分：

  1. 通过 EXTERNAL_QUERY 函数，在 AlloyDB 数据库中运行外部查询 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id，以获取每位客户的第一个订单日期。

  2. 通过 customer_id 联接外部查询结果表与 BigQuery 中的客户表。

  3. 选择最终结果集中的客户信息和第一个订单日期。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

查看 AlloyDB 表架构

您可以使用 EXTERNAL_QUERY 函数查询 information_schema 表以访问数据库元数据。例如，您可以列出数据库中的所有表，或查看表架构。如需了解详情，请参阅 PostgreSQL information_schema 表。

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.columns where table_name='x';");

跟踪 BigQuery 联合查询

对 AlloyDB 运行联合查询时，BigQuery 会使用如下所示的注释为查询添加注解：

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

如果您要监控查询使用量日志，以下注解可帮助您识别来自 BigQuery 的查询。

  1. 转到日志浏览器页面。

    转到日志浏览器

  2. 在查询标签页中，输入以下查询：

    resource.type="alloydb.googleapis.com/Instance"
textPayload=~"Federated query from BigQuery"

  3. 点击运行查询。

    如果有 BigQuery 联合查询的记录，查询结果中会显示如下所示的记录列表。

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

    如需详细了解 Cloud Logging，请参阅 Cloud Logging。

问题排查

本部分介绍了将联合查询发送到 AlloyDB 时可能出现的错误，并提供了可能的问题排查方法和纠正措施。

问题：无法连接到数据库服务器，并显示以下错误：Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

解决方法：确保在创建 AlloyDB 连接时使用了有效凭据并满足了所有前提条件。检查在创建 AlloyDB 连接时自动创建的服务账号是否具有 AlloyDB Client (roles/alloydb.client) 角色。如需了解详情，请参阅向服务账号授予访问权限。

后续步骤