Cloud SQL 联合查询

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

借助 BigQuery Cloud SQL 联合，BigQuery 能够实时查询驻留在 Cloud SQL 中的数据，而无需复制或移动数据。查询联合支持 Cloud SQL 中的 MySQL（第二代）和 PostgreSQL 实例。

或者，要将数据复制到 BigQuery，您还可以使用 Cloud Data Fusion 或 Datastream。如需详细了解如何使用 Cloud Data Fusion，请参阅将数据从 MySQL 复制到 BigQuery。

准备工作

查询数据

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

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

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;

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

  1. 通过 EXTERNAL_QUERY() 函数，在可用的 PostgreSQL 数据库中运行外部查询 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id，以获取每位客户的第一个订单日期。
  2. 通过 customer_id 联接外部查询结果表与 BigQuery 中的客户表。
  3. 选择客户信息和第一个订单日期。

查看 Cloud SQL 表架构

您可以使用 EXTERNAL_QUERY() 函数查询 information_schema 表以访问数据库元数据，例如列出数据库中的所有表或显示表架构。以下示例 information_schema 查询在 MySQL 和 PostgreSQL 中均有效。您可以通过 MySQL information_schema 表和 PostgreSQL information_schema 表了解详情。

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

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

连接详情

下表展示了 Cloud SQL 连接属性：

属性名称 值 说明
name 字符串 连接资源的名称，格式为：project_id.location_id.connection_id。
location 字符串 连接的位置，必须与 Cloud SQL 实例位置一致，或者为相应管辖区的多区域。例如，us-east4 中的 Cloud SQL 实例可以使用 US，而 europe-north1 中的 Cloud SQL 实例可以使用 EU。只有在此位置运行的 BigQuery 查询才能使用此连接。
friendlyName 字符串 简单易记的连接显示名称。
description 字符串 连接的说明。
cloudSql.type 字符串 可以是“POSTGRES”或“MYSQL”。
cloudSql.instanceId 字符串 Cloud SQL 实例的名称，通常采用以下格式：

Project-id:location-id:instance-id

您可以在 Cloud SQL 实例详情页面中找到实例 ID。
cloudSql.database 字符串 您要连接的 Cloud SQL 数据库。
cloudSql.serviceAccountId 字符串 配置为访问 Cloud SQL 数据库的服务账号。

下表展示了 Cloud SQL 实例凭据的属性：

属性名称 值 说明
username 字符串 数据库用户名
password 字符串 数据库密码

跟踪 BigQuery 联合查询

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

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

如果您要监控针对 MySQL 或 PostgreSQL 数据库的查询用量日志，以下注解可帮助您识别来自 BigQuery 的查询。

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

    转到日志浏览器

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

    resource.type="cloudsql_database"
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 SQL 时可能遇到的问题。

问题：无法连接到数据库服务器。如果您要查询 MySQL 数据库，可能会遇到以下错误：

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

或者，如果您要查询 PostgreSQL 数据库，可能会遇到以下错误：

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.
解决方法：确保使用了有效凭据并满足了所有前提条件，以便创建Cloud SQL 连接。
检查在创建 Cloud SQL 连接时自动创建的服务账号是否具有 Cloud SQL Client (roles/cloudsql.client) 角色。服务账号的格式如下：service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com。如需了解详细说明，请参阅向服务账号授予访问权限。

后续步骤