Cloud SQL 联合查询

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

概览

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

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

准备工作

启用 BigQuery 连接服务

  1. 在 API 库中打开 BigQuery Connection API 页面。
  2. 从下拉菜单中选择包含外部数据源的项目。
  3. 点击“启用”按钮。

    BigQuery Connection API

服务帐号

当您启用 BigQuery Connection API 时,系统会自动创建一个服务帐号。当您在包含 Cloud SQL 数据源的项目中启用 BigQuery Connection API 时,系统会应用以下角色:

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

如需详细了解服务帐号,请参阅服务代理

公共 IP

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

设置 Cloud SQL 数据库连接

启用 BigQuery Connection API 后,创建与 Cloud SQL 数据库的连接。

控制台

  1. 如需创建连接资源,请转到 Cloud Console 中的 BigQuery 页面。

    转到 BigQuery 页面

  2. 添加数据菜单中,选择外部数据源

    创建连接资源。

  3. 外部数据源窗格中,输入以下信息:

    • 对于连接类型,请选择来源类型,例如 MySQL 或 Postgres。
    • 对于连接 ID,输入连接资源的标识符。允许使用字母、数字和下划线。
    • 对于连接位置,选择与您的外部数据源区域兼容的 BigQuery 位置(或区域)。
    • (可选):对于易记名称,输入方便用户使用的连接名称,例如 My connection resource。易记名称可以是任何容易辨识的值,让您以后在需要修改连接资源时能够轻松识别。
    • (可选):对于说明,输入此连接资源的说明。
    • 如果您选择了 Cloud SQL MySQL 或 Postgres 作为连接类型,对于 Cloud SQL 实例 ID,请输入完整的 Cloud SQL 实例的名称,格式通常为 project-id:location-id:instance-id。您可以在想要查询的 Cloud SQL 实例的详情页面找到实例 ID。
    • 对于数据库名称,输入数据库的名称。
    • 对于用户名,输入数据库的用户名。
    • 对于密码,输入数据库的密码。

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

      新建连接资源。

  4. 点击创建连接

bq

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

  • --connection_type
  • --properties
  • --connection_credential
  • --project_id
  • --location

以下标志是可选的:

  • --display_name 连接的易记名称。
  • --description 连接的说明。

连接 ID 是可选参数,可以添加为在内部用于存储的命令的最后一个参数。如果未提供连接 ID,则系统会自动生成一个唯一 ID。连接 ID 可以包含字母、数字和下划线。

    bq mk --connection --display_name='friendly name' --connection_type=TYPE \
      --properties=PROPERTIES --connection_credential=CREDENTIALS \
      --project_id=PROJECT_ID --location=LOCATION \
      CONNECTION_ID

请替换以下内容:

  • TYPE:外部数据源的类型。
  • PROPERTIES:所创建的连接的参数(采用 JSON 格式)。例如 --properties='{"param":"param_value"}'。为了创建连接资源,您必须提供 instanceIDdatabasetype 参数。
  • CREDENTIALS:参数 usernamepassword
  • PROJECT_ID:您的项目 ID。
  • LOCATION:您的 Cloud SQL 实例所在的区域。
  • CONNECTION_ID:连接标识符。

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

bq mk --connection --display_name='friendly name' --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_connection_id

API

通过 BigQuery Connection API,您可以在 ConnectionService 中调用 CreateConnection 来实例化连接。如需了解详情,请参阅客户端库页面

Java

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  public static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

如需了解如何查看、列出、共享、更新和删除连接资源,请参阅使用连接

示例

假设您在 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. 选择客户信息和第一个订单日期。

支持的区域

下表显示了 BigQuery 和 Cloud SQL 支持的区域。

单区域位置

区域说明 Cloud SQL 区域 兼容的 BigQuery 单区域 兼容的 BigQuery 多区域
美洲
爱荷华 us-central 不支持:Cloud SQL 实例的此区域为 V1。
联合查询仅支持 Cloud SQL V2 实例。
爱荷华 us-central1 us-central1 US
拉斯维加斯 us-west4 us-west4 US
洛杉矶 us-west2 us-west2 US
蒙特利尔 northamerica-northeast1 northamerica-northeast1 US
北弗吉尼亚 us-east4 us-east4 US
俄勒冈 us-west1 us-west1 US
盐湖城 us-west3 us-west3 US
圣保罗 southamerica-east1 southamerica-east1
南卡罗来纳 us-east1 us-east1 US
多伦多 northamerica-northeast2 northamerica-northeast2 US
欧洲
比利时 europe-west1 europe-west1 EU
芬兰 europe-north1 europe-north1 EU
法兰克福 europe-west3 europe-west3 EU
伦敦 europe-west2 europe-west2 EU
荷兰 europe-west4 europe-west4 EU
华沙 europe-central2 europe-central2 EU
苏黎世 europe-west6 europe-west6 EU
亚太地区
德里 asia-south2 asia-south2
香港 asia-east2 asia-east2
雅加达 asia-southeast2 asia-southeast2
墨尔本 australia-southeast2 australia-southeast2
孟买 asia-south1 asia-south1
大阪 asia-northeast2 asia-northeast2
首尔 asia-northeast3 asia-northeast3
新加坡 asia-southeast1 asia-southeast1
悉尼 australia-southeast1 australia-southeast1
台湾 asia-east1 asia-east1
东京 asia-northeast1 asia-northeast1

多区域位置

多区域位置不适用于 Cloud SQL 实例。Cloud SQL 多区域不能用于联合查询。

位于 EU 多区域的数据不会存储在 europe-west2(伦敦)或 europe-west6(苏黎世)数据中心中。

限制

Cloud SQL 联合查询存在以下限制:

  • 性能。联合查询可能不如仅查询 BigQuery 存储那么快。BigQuery 需要等待源数据库执行外部查询并暂时将数据从外部来源移动到 BigQuery。此外,源数据库可能未针对复杂的分析查询进行优化。

  • 联合查询是只读的。将在源数据库中执行的外部查询必须是只读的。因此,DML 或 DDL 语句不受支持。

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

  • 有限的 Cloud SQL 实例。只有具有公共 IP 地址(而不是专用 IP 地址)的 Cloud SQL V2 实例才支持联合查询。

配额和限制

除了联合查询的一般配额和限制之外,Cloud SQL 数据库还存在以下限制。

  • 跨区域联合查询:如果 BigQuery 查询处理位置与外部数据源位置不同,则表示此查询是一个跨区域查询。您每天最多可以为每个项目运行 1 TB 的跨区域查询。 以下是一个跨区域查询示例。
    • Cloud SQL 实例位于 us-west1,而 BigQuery 连接位于 US 多区域。BigQuery 查询处理位置是 US
  • 配额:用户应控制外部数据源 Cloud SQL 中的查询配额。联合查询没有额外的配额设置。为了实现工作负载隔离,建议仅查询数据库读取副本。
  • 需遵循 Cloud SQL MySQLPostgreSQL 配额及限制。

参考

查看 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 等。
  • 默认情况下,MySQL 和 PostgreSQL 中的数字数据类型将映射到 BigQuery NUMERIC 值。BigQuery NUMERIC 值范围小于 MySQL 和 PostgreSQL 中的范围。它也可以通过 EXTERNAL_QUERY 选项中的“default_type_for_decimal_columns”来映射到 BIGNUMERICFLOAT64STRING

错误处理

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

您可以将不支持的数据类型转换为受支持的其他 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
  • 不支持的 PostgreSQL 数据类型

    • 错误消息Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • 不支持的类型moneytime with time zoneinetpathpg_lsnpointpolygontsquerytsvectortxid_snapshotuuidboxcidrcircleintervaljsonblinelsegmacaddrmacaddr8
    • 解决方案:将不支持的数据类型转换为字符串。
    • 示例SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); 此命令可将不支持的数据类型 money 转换为 STRING

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、BIGNUMERIC、FLOAT64 或 STRING

DECIMAL (M,D) 将默认映射到 NUMERIC,也可以通过 default_type_for_decimal_columns 映射到 BIGNUMERIC、FLOAT64 或 STRING。
近似数字
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 个字符的二进制大型对象。 BYTES
MEDIUM_BLOB 最大长度为 16777215 个字符的 BLOB。 BYTES
LONG_BLOB 最大长度为 4294967295 个字符的 BLOB。 BYTES
TINY_BLOB 最大长度为 255 个字符的 BLOB。 BYTES
BINARY 长度介于 1 到 255 个字符之间的固定长度的二进制字符串。 BYTES
VARBINARY 长度介于 1 到 255 个字符之间的可变长度的二进制字符串。 BYTES
其他
SET 声明 SET 列时,预定义一些值。然后,使用 INSERT 将任何一组预定义值添加到此列。 STRING
GEOMETRY GEOGRAPHY 暂不支持
BIT INT64 暂不支持

PostgreSQL 到 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、BIGNUMERIC、FLOAT64 或 STRING Numeric [ (p, s) ] 将默认映射到 NUMERIC,也可以通过 default_type_for_decimal_columns 映射到 BIGNUMERIC、FLOAT64 或 STRING。
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
其他
布尔值 逻辑布尔值 (true/false)。 BOOL
inet IPv4 或 IPv6 主机地址。 不支持
路径 平面上的几何图形路径。 不支持
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 格式)。 不支持