运行 MySQL 联合查询

作为数据分析师，您可以直接从 BigQuery 实时查询 MySQL 中的关系型数据，而无需复制或移动数据。

BigQuery MySQL 联合查询使用 BigQuery Connection API 连接到 MySQL 数据库引擎。然后，您可以使用 EXTERNAL_QUERY 函数将 SQL 查询直接发送到 MySQL 执行，结果会直接流式传输回来。

限制

MySQL 联合查询存在以下限制：

  • 您只能通过 EXTERNAL_QUERY 函数执行只读查询。不支持数据定义语言 (DDL) 和数据操纵语言 (DML) 语句。
  • 只能通过 EXTERNAL_QUERY 函数访问联合。不支持创建直接映射到 MySQL 表的 BigQuery 外部表或永久数据集。
  • 联合查询延迟时间可能会明显长于直接在 MySQL 中执行查询。
  • 底层连接服务可能会使非活跃连接进入休眠状态。在一段时间不活动后，使用 MySQL 连接的第一个查询可能需要超过一分钟的时间才能运行，因为连接器需要初始化。
  • 不支持 DNS 对等互连。您必须使用 VPC 网络中的 IP 地址或完全可解析的主机名进行连接。
  • MySQL SQL 查询必须为包含函数结果或表达式（例如 SELECT COUNT(*) AS total_count）的任何列指定显式列别名。无别名的表达式会生成包含在 BigQuery 表架构定义中无效的特殊字符的列名称。
  • 当查询项目中 Compute Engine API 的使用受 VPC Service Controls 的限制时，如果连接使用网络连接，则联合查询会失败。确保配置了适当的 VPC Service Controls 入站和出站规则。

  • MySQL 不支持其他 SQL 下推。外部 BigQuery 查询中的过滤条件和聚合不会下推到 EXTERNAL_QUERY 函数内的内部查询中。您必须直接在内部 MySQL SQL 语句中指定谓词和列投影，以限制通过网络传输的行和列。

  • 仅支持 MySQL 数据库用户名和密码身份验证。不支持第三方身份提供方、Kerberos 和云提供商 IAM 身份验证。

  • 如果查询选择 32 位整数列（TINYINT、SMALLINT 和 BOOLEAN / TINYINT(1)）或单精度浮点列 (FLOAT)，则 CREATE TABLE ... AS SELECT (CTAS) 语句会失败，并显示 Unsupported type INT32 或 Unsupported type FLOAT 错误。如需将查询结果保存到新的 BigQuery 表中，请在查询中将整数列显式转换为 INT64，将浮点列显式转换为 FLOAT64：

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    使用 INSERT INTO <table> SELECT ... 插入到现有表时，无需手动转换即可成功，因为 BigQuery 会自动扩大 INT32 和 FLOAT 值，以匹配目标表的 INT64 和 FLOAT64 列。

  • 虽然 MySQL DECIMAL 和 NUMERIC 列映射到 BIGNUMERIC(76, 38)，但 MySQL 允许精度高达 65。如果值包含超过 38 个整数位数（精度减去小数位数大于 38），则会超出 BIGNUMERIC 整数范围，并且查询会在运行时失败。如需查询整数位数超过 38 位的值，请在 EXTERNAL_QUERY 语句中将其转换为 CHAR 或 VARCHAR。

  • TIME 数据类型会将秒以下的小数（微秒）值截断为整秒（例如，12:34:56.789123 变为 12:34:56）。此外，MySQL TIME 支持从 -838:59:59 到 838:59:59 的经过时长，而 BigQuery TIME 仅支持从 00:00:00 到 23:59:59.999999 的一天中的时间值。00:00:00 到 23:59:59 范围之外的值会导致运行时查询错误。如果需要微秒精度或经过的时长值，请在 EXTERNAL_QUERY 语句中将列转换为 CHAR 或 VARCHAR。

  • 由于历史上的公历转换，1582 年 10 月 15 日之前的日期和时间戳会发生 10 天的日历偏移。如需保留 1582 年之前的确切历史日期，请在 EXTERNAL_QUERY 语句中将其转换为 CHAR 或 VARCHAR。

支持的 MySQL 环境和版本

联合查询功能支持以下部署环境中的 MySQL 数据库：

  • 可通过公共 IP 地址或网络连接（通过 Cloud VPN 或 Cloud Interconnect）访问的云托管或本地 MySQL 数据库
  • Compute Engine 或其他基础架构上自行托管的 MySQL 实例
  • MySQL 8.4 LTS 和 8.0

准备工作

确保您的 BigQuery 管理员已创建 MySQL 连接并与您共享。

所需的角色

如需获得查询 MySQL 所需的权限，请让管理员为您授予项目的 BigQuery Connection User (roles/bigquery.connectionUser) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

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

查询数据

如需将联合查询从 GoogleSQL 查询发送到 MySQL，请使用 EXTERNAL_QUERY 函数。

以下示例是一个联合查询，该查询将 BigQuery 中名为 mydataset.customers 的客户记录与 MySQL 中的事务性销售订单联接：

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

查看 MySQL 表架构

您可以通过 EXTERNAL_QUERY 查询 MySQL INFORMATION_SCHEMA 视图，以便在运行分析之前检查数据库元数据、发现表并查看列架构。

列出所有数据库

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

列出数据库中的表

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

查看表中的列和数据类型

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

数据类型映射

执行联合查询时，BigQuery 会根据下表将 MySQL 中的数据转换为 GoogleSQL 数据类型：

MySQL 数据类型 BigQuery 数据类型
数值类型
BOOLEAN、BOOL、TINYINT(1) INT64（交互式查询；请参阅 CREATE TABLE ... AS SELECT 的限制）
TINYINT，SMALLINT INT64（交互式查询；请参阅 CREATE TABLE ... AS SELECT 的限制）
MEDIUMINT、INT、INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED、BIGINT ZEROFILL、SERIAL BIGNUMERIC(76, 38)
DECIMAL、DEC、NUMERIC、FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64（交互式查询；请参阅 CREATE TABLE ... AS SELECT 的限制）
DOUBLE、DOUBLE PRECISION、REAL FLOAT64
日期和时间类型
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
字符串和文本类型
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB BYTES
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT STRING
ENUM STRING
SET STRING

价格

运行联合查询的费用取决于三个因素：

  • 在 MySQL 中执行查询的计算费用（由 MySQL 数据库提供商或托管环境收取）
  • 将查询结果从 MySQL 转移到 BigQuery 的带宽费用（数据库托管服务提供商收取的出站流量网络费用）
  • 在 BigQuery 中执行查询的计算费用

任何 MySQL 计算和出站流量费用均由您的 MySQL 提供商或基础架构托管服务商直接收取，而不是由 Google 收取。为了限制带宽费用，我们建议您在 EXTERNAL_QUERY 语句中编写查询，使其排除所有不需要计算最终结果的列和行。

在 BigQuery 中运行联合查询不会产生额外费用。如需详细了解 BigQuery 价格，请参阅价格。

如果您的连接使用 Google Cloud 网络组件，则可能还需要支付以下费用：

后续步骤