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。

准备工作

确保您的 BigQuery 管理员已创建 Cloud SQL 连接并与您共享。
如需获得查询 Cloud SQL 实例所需的权限，请让管理员授予您项目的 BigQuery Connection User (roles/bigquery.connectionUser) IAM 角色。如需详细了解如何授予角色，请参阅管理访问权限。

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

查询数据

如需将联合查询从 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 个部分：

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

查看 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 的查询。

转到日志浏览器页面。

转到日志浏览器

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

resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

点击运行查询。

如果有可用于 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。如需了解详细说明，请参阅向服务账号授予访问权限。