Cloud SQL 联合查询

本页面介绍如何使用联合查询来查询 BigQuery 和 Cloud SQL 中的数据。

概览

数据通常分散在多个位置。您可能在 BigQuery 中存储客户表,同时在 Cloud SQL 中存储销售表,您希望在单个查询中联接这两个表。

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

在初始的一次性设置之后,您可以使用新的 SQL 函数 EXTERNAL_QUERY() 来编写查询。

工作流

联合查询语法

联合查询引入了一个新函数:EXTERNAL_QUERY

语法

SELECT * FROM EXTERNAL_QUERY(connection_id, external_database_query);
  • connection_id(字符串):您在网页界面、CLI 或 API 中创建数据库连接资源的名称。

    示例连接 ID bigquery-federation-test.us.test-mysql

  • external_database_query(字符串):采用外部数据库的 SQL 方言(MySQL 或 PostgreSQL)编写的只读查询。该查询在 Cloud SQL 中的外部数据库中执行。

说明

EXTERNAL_QUERY 执行 Cloud SQL 中的查询并将结果作为临时表返回。源数据库(MySQL 或 PostgreSQL)数据类型在临时结果表中转换为 BigQuery 数据类型,且具有以下数据类型映射EXTERNAL_QUERY 函数通常用于 FROM 子句。此函数仅在 BigQuery 标准 SQL 中可用。

返回类型

BigQuery 表格。

查询示例

假设您需要将每位客户的第一个订单的日期包含在概览中所述的报告中。此数据目前不在 BigQuery 中,但可在 Cloud SQL 中的操作性 PostgreSQL 数据库中找到。以下联合查询示例可完成此操作。

SELECT c.customer_id, c.name, SUM(t.amount) AS total_revenue,
rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN EXTERNAL_QUERY(
  '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. 选择客户信息和第一个订单日期。

不保留顺序

即使您的外部查询包含 ORDER BYEXTERNAL_QUERY() 也不会遵循外部查询结果的顺序。以下查询示例在 Cloud SQL 中按客户 ID 对行进行排序,但 BigQuery 不会按该顺序输出结果行。

SELECT * EXTERNAL_QUERY(
'connection_id',
'''SELECT * FROM customers AS c ORDER BY c.customer_id'');

准备工作

启用 BigQuery 连接服务

  • 在 API 库中打开 BigQuery Connection API 页面。
  • 从下拉菜单中,选择包含您的 Cloud SQL 实例的项目。
  • 点击“启用”按钮。

服务帐号

BigQuery 使用服务帐号连接到您的 Cloud SQL 实例。启用 BigQuery Connection API 时,系统将代表您自动创建一个 GCP 管理的 IAM 服务帐号。该服务帐号具有以下角色:

角色 说明
cloudsql.client 连接到 Cloud SQL 实例
logging.logWriter 写入到 cloud-logging
metrics.metricWriter 写入到 cloud-monitoring

权限

  • 如需创建和维护连接资源,用户必须具有 bigquery.admin 预定义的 IAM 角色。

  • bigquery.admin 角色拥有以下 BigQuery 连接服务权限:

    • bigquery.connections.create
    • bigquery.connections.get
    • bigquery.connections.list
    • bigquery.connections.update
    • bigquery.connections.use
    • bigquery.connections.delete

如需向其他用户授予权限,以便他们能够使用连接资源进行 Cloud SQL 查询,请参阅向其他用户授予权限

授予 bigquery.admin 访问权限

要授予 bigquery.admin 角色,请执行以下操作:

控制台

  1. 打开 GCP Console 中的 Cloud IAM 页面。

    打开 IAM 页面

  2. 点击选择项目

  3. 选择项目并点击打开

  4. 点击添加向项目添加新成员,然后为这些成员设置相应的权限。

  5. 添加成员对话框中,执行以下操作:

    • 成员部分中,输入用户或群组的电子邮件地址。
    • 选择角色下拉列表中,依次点击 BigQuery > BigQuery 管理员
    • 点击添加

      授予管理员

CLI

您可以使用 gcloud 命令行工具向用户或群组授予 bigquery.admin 角色。

如需向项目的 Cloud IAM 政策添加一个绑定,请键入以下命令。如需添加用户,请按照如下格式提供 --member 标志:user:user@example.com。如需添加群组,请按照如下格式提供 --member 标志:group:group@example.com

gcloud projects add-iam-policy-binding project_id \
--member group/user:address \
--role roles/bigquery.admin

其中:

  • project_id 为您的项目 ID。
  • group/user 是 groupuser
  • address 是用户或群组的电子邮件地址。

例如:

gcloud projects add-iam-policy-binding myproject \
--member group:group@example.com \
--role roles/bigquery.admin

该命令会输出更新后的政策:

    bindings:
    - members:
      - group:group@example.com
        role: roles/bigquery.admin
    

如需详细了解 BigQuery 中的 Cloud IAM 角色,请参阅预定义的角色和权限

公共 IP

BigQuery Cloud SQL 联合仅支持具有公共 IP 连接的 Cloud SQL 实例。请为您的 Cloud SQL 实例配置公共 IP 连接

设置 Cloud SQL 数据库连接

为了避免在联合查询中将数据库凭据以明文写出,您需要首先在 BigQuery 中为每个数据库创建一个数据库连接资源,然后在联合查询中引用该资源。

连接资源具有一组 IAM 权限,您可以将这些权限授予其他用户。连接资源安全存储在 BigQuery 连接服务中且经过安全加密,只能用于联合查询。

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限,请参阅预定义的角色和权限

创建连接资源

控制台

  1. 要创建连接资源,请转到 GCP Console 中的 BigQuery 网页界面。

    转到 GCP Console

  2. + 添加数据下,从菜单中选择创建连接

    创建连接资源

  3. 创建连接窗格上,执行以下操作:

    • 对于连接类型,选择 MySQL 或 PostgresSQL。
    • 对于连接 ID,输入连接资源的标识符。允许使用字母、数字和下划线。
    • 对于连接位置,选择 Cloud SQL 实例位置,但以下情况除外:对于位于 us-central1 区域的 Cloud SQL 实例,选择美国位置,对于位于 europe-west1 区域的 Cloud SQL 实例,选择欧盟位置。
    • (可选):对于易记名称,输入方便用户使用的连接名称,例如 My connection resource。易记名称可以是任何容易辨识的值,方便以后需要时对连接资源进行修改。
    • (可选):对于说明,输入此连接资源的说明。
    • 对于 Cloud SQL 实例 ID,输入完整的 Cloud SQL 实例名称,且通常采用 project-id:location-id:instance-id 格式。您可以在想要查询的 Cloud SQL 实例的详情页面找到实例 ID。
    • 对于数据库名称,输入数据库的名称。
    • 对于用户名,输入数据库的用户名。
    • 对于密码,输入数据库的密码。

      • (可选)勾选显示密码以显示密码。

      新建连接资源

  4. 点击创建连接

命令行

输入 bq mk 命令并提供连接标志:--connection。此外,还必须提供以下标志:

  • --connection_type
  • --properties
  • --connection_credential
  • --project_id
  • --location
  • (连接名称)

    bq mk --connection --connection_type='CLOUD_SQL' --properties=[PROPERTIES] --connection_credential=[CREDENTIALS] --project_id=[PROJECT_ID] --location=[LOCATION] new_connection
    

其中:

  • --connection_type 始终为 CLOUD_SQL
  • --properties 包含所创建的连接的参数(采用 JSON 格式)。例如:--properties='{"param":"param_value"}'。为了创建连接资源,您必须提供 instanceIDdatabasetype 参数。参数 friendly_namedescription 是可选的。
  • --connection_credential 必须包含参数 usernamepassword
  • --project_id 是项目 ID。
  • --location 是您的 Cloud SQL 实例所在的区域。
  • 仅使用字母、数字和下划线输入名称以标识连接。

例如,以下命令在 ID 为 federation-test 的项目中创建一个名为 my_new_connection(易记名称:“我的新连接”)的新连接资源。

bq mk --connection --connection_type='CLOUD_SQL' --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' --connection_credential='{"username":"myusername", "password":"mypassword"}' --project_id=federation-test --location=us my_new_connection

API

使用 projects.locations.transferConfigs.create 方法并提供 TransferConfig 资源的实例。

查看连接资源

控制台

  1. 如需查看连接资源的状态,请转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 连接资源将在您的项目最顶层的名为外部连接的群组下列出。

  3. 点击一个连接,查看有关该连接资源的信息,例如 connection IDCloud SQL instance ID

    查看连接资源

命令行

输入 bq show 命令并提供连接标志 --connection。需要使用完全限定的 connection_id。

    bq show --connection project:location.connection_id

例如,以下命令在位于区域 us 中的 ID 为 federation-test 的项目中创建一个名为 my_new_connection 的新连接资源。

    bq show --connection federation-test:us.my_new_connection

API

使用 projects.locations.connections.list 方法并提供一个 list 资源实例。请参阅参考部分 REST API

向其他用户授予权限

为了让其他用户能够使用连接资源进行 Cloud SQL 联合查询,bigquery.admin 用户应向其授予以下 IAM 角色BigQuery Connection User。此 IAM 角色拥有以下权限:

  • bigquery.connections.get
  • bigquery.connections.list
  • bigquery.connections.use
  • bigquery.connections.getIamPolicy

bigquery.admin 用户也可以向其他用户授予名为 BigQuery Connection Admin 的另一个角色,该角色除了拥有与 BigQuery Connection User 相同的所有权限,还拥有创建、更新和删除现有连接以及为连接设置 IAM 政策的其他权限。

  • bigquery.connections.create
  • bigquery.connections.update
  • bigquery.connections.setIamPolicy
  • bigquery.connections.delete

价格

从 BigQuery 查询 Cloud SQL 时,您需要为查询读取的字节数付费。如需了解详情,请参阅查询价格。在测试版期间,Cloud SQL 联合查询没有额外费用或配额。

参考

查看 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';");

连接资源详细信息

属性名称 说明
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 数据库

连接凭据资源详细信息

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

数据类型映射

执行 Cloud SQL 联合查询时,Cloud SQL 中的数据(MySQL 或 PostgreSQL 数据类型)将转换为 BigQuery 标准 SQL 类型。下面是从 MySQL 到 BigQuery 和从 PostgreSQL 到 BigQuery 的数据类型映射。

关于映射的注意事项:

  • 大多数 MySQL 数据类型可以与相同的 BigQuery 数据类型匹配,但存在一些例外情况,例如 decimaltimestamptime
  • PostgreSQL 支持 BigQuery 中不支持的许多非标准数据类型,例如 moneypathuuidboxer 等。
  • BigQuery 数值范围小于 MySQL 和 PostgreSQL 中的范围。

错误处理

如果外部查询包含 BigQuery 中不支持的数据类型,查询将立即失败。您可以将不支持的数据类型转换为受支持的其他 MySQL/PostgreSQL 数据类型。如需详细了解如何进行类型转换,请参阅问题排查下的不支持的数据类型

MySQL 到 BigQuery 的类型映射

MySQL 类型 MySQL 说明 BigQuery 类型 类型差异
整数
INT 4 个字节,2^32 - 1 INT64
TINYINT 1 个字节,2^8 - 1 INT64
SMALLINT 2 个字节,2^16 - 1 INT64
MEDIUMINT 3 个字节,2^24 - 1 INT64
BIGINT 8 个字节,2^64 - 1 INT64
UNSIGNED BIGINT 8 个字节,2^64 - 1 NUMERIC
精确数字
DECIMAL (M,D) 由 (M,D) 表示的小数,其中,M 是总位数,D 是小数位数。M <= 65 NUMERIC

BigQuery NUMERIC 范围较小,仅支持 38 位精度和 9 位标度的十进制值。
近似数字
FLOAT (M,D) 4 个字节,M <= 23 FLOAT64
DOUBLE (M,D) 8 个字节,M <= 53 FLOAT64
日期和时间
TIMESTAMP “1970-01-01 00:00:01”世界协调时间 (UTC) 至“2038-01-19 03:14:07”世界协调时间 (UTC)。 TIMESTAMP 无论用户在何处调用 BigQuery,MySQL TIMESTAMP 都将检索为世界协调时间 (UTC) 时区
DATETIME “1000-01-01 00:00:00”至“9999-12-31 23:59:59” DATETIME
DATE “1000-01-01”至“9999-12-31” DATE
TIME “HH:MM:SS”格式的时间
“-838:59:59”至“838:59:59”。
TIME
BigQuery TIME 范围较小,从 00:00:00 至 23:59:59
YEAR INT64
字符和字符串
ENUM 字符串对象,其值选自允许值列表 STRING
CHAR (M) 长度介于 1 到 255 个字符之间的固定长度的字符串 STRING
VARCHAR (M) 长度介于 1 到 255 个字符之间的可变长度的字符串。 STRING
TEXT 最大长度为 65535 个字符的字段。 STRING
TINYTEXT 最大长度为 255 个字符的 TEXT 列。 STRING
MEDIUMTEXT 最大长度为 16777215 个字符的 TEXT 列。 STRING
LONGTEXT 最大长度为 4294967295 个字符的 TEXT 列。 STRING
二进制
BLOB 最大长度为 65535 个字符的二进制大型对象。 BYTE
MEDIUM_BLOB 最大长度为 16777215 个字符的 BLOB。 BYTE
LONG_BLOB 最大长度为 4294967295 个字符的 BLOB。 BYTE
TINY_BLOB 最大长度为 255 个字符的 BLOB。 BYTE
其他
SET 声明 SET 列时,预定义一些值。然后,将任何一组预定义值插入此列 STRING
GEOMETRY GEOMETRY 暂不支持
BIT INT64 暂不支持

PostgresSQL 到 BigQuery 的类型映射

名称 说明 BigQuery 类型 类型差异
整数
smallint 2 个字节,-32768 到 +32767 INT64
smallserial 请参阅 smallint INT64
整数 4 个字节,-2147483648 到 +2147483647 INT64
serial 请参阅整数 INT64
bigint 8 个字节,-9223372036854775808 到 9223372036854775807 INT64
bigserial 请参阅 bigint INT64
精确数字
numeric [ (p, s) ] 精度高达 1000。 NUMERIC BigQuery NUMERIC 范围较小,仅支持 38 位精度和 9 位标度的十进制值。
Decimal [ (p, s) ] 请参阅数字 NUMERIC 请参阅数字
money 8 个字节,2 位标度,-92233720368547758.08 到 +92233720368547758.07 不支持
近似数字
real 4 个字节,单精度浮点数 FLOAT64
双精度 8 个字节,双精度浮点数 FLOAT64
日期和时间
date 日历日期(年、月、日) DATE
time [ (p) ] [无时区] 一天中的时间(无时区) TIME
time [ (p) ],有时区 一天中的时间,包含时区 不支持
timestamp [ (p) ] [无时区] 日期和时间(无时区) DATETIME
timestamp [ (p) ],有时区 日期和时间,包含时区 TIMESTAMP 无论用户在何处调用 BigQuery,PostgreSQL TIMESTAMP 都将检索为世界协调时间 (UTC) 时区
interval 持续时间 不支持
字符和字符串
character [ (n) ] 固定长度的字符串 STRING
character varying [ (n) ] 可变长度的字符串 STRING
text 可变长度的字符串 STRING
二进制
bytea 二进制数据(“字节数组”) BYTES
bit [ (n) ] 固定长度位字符串 BYTES
bit varying [ (n) ] 可变长度位字符串 BYTES
其他
boolean 逻辑布尔值 (true/false) BOOL
inet IPv4 或 IPv6 主机地址 不支持
path 层面上的几何图形路径 不支持
pg_lsn PostgreSQL 日志序列号 不支持
point 层面上的几何点 不支持
polygon 层面上封闭的几何图形路径 不支持
tsquery 文本搜索查询 不支持
tsvector 文本搜索文档 不支持
txid_snapshot 用户级交易 ID 快照 不支持
uuid 通用唯一标识符 不支持
xml XML 数据 STRING
box 层面上的矩形框 不支持
cidr IPv4 或 IPv6 网络地址 不支持
circle 层面上的圆圈 不支持
interval [ fields ] [ (p) ] 时间范围 不支持
json 文本 JSON 数据 STRING
jsonb 已分解的二进制 JSON 数据 不支持
line 层面上的无限线条 不支持
lseg 层面上的线段 不支持
macaddr MAC(媒体访问控制)地址 不支持
macaddr8 MAC(媒体访问控制)地址(EUI-64 格式) 不支持

已知问题和限制

区域

仅同时支持 Cloud SQL 和 BigQuery 的区域支持 Cloud SQL 联合查询。(所有 BigQuery 区域都支持查询联合,但并非所有 Cloud SQL 区域都支持查询联合。)

您可以根据以下规则跨区域执行联合查询。

多区域

BigQuery 多区域可以查询同一位置(美国、欧盟)的任何 Cloud SQL 区域。例如:

  • BigQuery 美国多区域可以查询 Cloud SQL us-central1、us-east4、us-west2 等。
  • BigQuery 欧盟多区域可以查询 Cloud SQL europe-north1、europe-west2 等。

单区域

BigQuery 单区域只能查询同一区域中的 Cloud SQL。例如:

  • BigQuery us-east4 只能查询 Cloud SQL us-east4。

虽然从此测试版开始,BigQuery 和 Cloud SQL 可以在同一单区域中使用,但某些区域不受支持。请参阅下表,了解有关映射的详细信息。

Cloud SQL 区域 BigQuery 多区域/区域
northamerica-northeast1 美国 / Northamerica-northeast1
us-central 不支持:Cloud SQL 实例的此区域为 V1。
仅 V2 实例支持联合查询。
us-central1 美国
us-east1 不支持
us-east4 美国/us-east4
us-west1 不支持
us-west2 美国/us-west2
southamerica-east1 southamerica-east1
europe-north1 欧盟/europe-north1
europe-west1 欧盟
europe-west2 欧盟/europe-west2
europe-west3 不支持
europe-west4 不支持
europe-west6 欧盟/europe-west6
asia-east1 asia-east1
asia-east2 asia-east2
asia-northeast1 asia-northeast1
asia-south1 asia-south1
asia-southeast1 asia-southeast1
australia-southeast1 australia-southeast1

配额和其他限制

  • 性能:联合查询很可能不如仅查询 BigQuery 存储那么快。BigQuery 需要等待源数据库执行外部查询并暂时将数据从 Cloud SQL 移动到 BigQuery。MySQL 或 PostgreSQL 等源数据库通常不会针对复杂的分析查询进行优化。
  • 配额:用户应控制 Cloud SQL 中的查询配额。联合查询没有额外的配额设置。为了实现工作负载隔离,建议仅查询数据库读取副本。
  • 允许的最大计费字节数:目前联合查询不支持此字段。目前无法在实际执行联合查询之前计算计费字节数。
  • 有限的 Cloud SQL 实例:只有具有公共 IP(而不是专用 IP)的 Cloud SQL v2 实例才支持联合查询。
  • Cloud SQL MySQLPostgreSQL 配额和限制的约束。
  • 连接数:一个联合查询最多可以有 10 个唯一连接。

问题排查

本部分旨在帮助您排查设置连接时遇到的最常见问题。本部分并未包含所有可能的错误消息或问题。

常规问题

诊断常规连接问题时,请验证以下内容:

  • 确认您已为自己的连接完成了文档页面中“准备工作”部分的所有步骤。
  • 连接配置属性正确。

如果您的连接配置正确并且您具有相应的权限,请参阅以下内容,了解常见问题的解决方案。

问题:BigQuery 和 Cloud SQL 不位于同一位置。
解决方案:只有同时支持 Cloud SQL 和 BigQuery 的区域才支持 Cloud SQL 联合查询。如果 BigQuery 数据集位于 USEU 等多区域位置,则该数据集和 Cloud SQL 实例必须位于同一区域或同一位置。如需详细了解区域,请参阅已知问题和限制
问题:性能低于预期。
解决方案:联合查询的性能不如查询存储在 BigQuery 中的数据那么高,因为联合查询必须从外部查询 Cloud SQL,将数据返回到临时 BigQuery 表格,将数据映射到 BigQuery 数据类型,然后在 BigQuery 中执行查询。其好处是虽然查询性能并不是那么高,但无需再次复制、移动或存储数据。
问题:如何设置连接名称的格式?
解决方案:连接名称应包含项目、位置和 connection_id。连接名称应符合以下格式:project_id.location_id.connection_id 例如:federation-test.us.my_new_connection

不支持的数据类型

如果外部查询包含 BigQuery 中不支持的数据类型,查询将立即失败。您可以将不支持的数据类型转换为受支持的其他 MySQL/PostgreSQL 数据类型。

  • 不支持的 MySQL 数据类型

    • 错误消息Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • 不支持的类型GEOMETRYBIT
    • 解决方案:将不支持的数据类型转换为字符串。
    • 示例SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); 此命令可将不支持的数据类型 GEOMETRY 转换为 STRING
  • 不支持的 PostgresSQL 数据类型

    • 错误消息Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • 不支持的类型money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • 解决方案:将不支持的数据类型转换为字符串。
    • 示例SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); 此命令可将不支持的数据类型 money 转换为 string
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面