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 实例位置相同,但以下情况除外:Cloud SQL us-central1 映射到 BigQuery 美国,Cloud SQL europe-west1 映射到 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。如需了解详细说明,请参阅向服务账号授予访问权限

后续步骤