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 个部分：

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 连接属性：

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

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

后续步骤

• 了解联合查询。
• 了解 MySQL 与 BigQuery 数据类型之间的映射。
• 了解 PostgreSQL 与 BigQuery 数据类型之间的映射。
• 了解不支持的数据类型。