Spanner 联合查询

作为数据分析师，您可以使用联合查询从 BigQuery 查询 Spanner 中的数据。

借助 BigQuery Spanner 联合，BigQuery 能够实时查询驻留在 Spanner 中的数据，而无需复制或移动数据。

您可以通过以下两种方式查询 Spanner 数据：

使用外部数据集

查询 Spanner 表的最简单方法是创建外部数据集。创建外部数据集后，您在相应 Spanner 数据库中的表就会显示在 BigQuery 中，您可以在查询中使用这些表，例如在联接、联合或子查询中。不过，系统不会将任何数据从 Spanner 移至 BigQuery 存储空间。

如果您创建外部数据集，则无需创建连接即可查询 Spanner 数据。

使用 EXTERNAL_QUERY 函数

与其他联邦数据库一样，您还可以使用 EXTERNAL_QUERY 函数查询 Spanner 数据。如果您要查询使用 PostgreSQL 方言的 Spanner 数据库，或者想要更好地控制连接参数，这可能会很有用。

准备工作

  • 确保您的 BigQuery 管理员已创建 Spanner 连接并与您共享。请参阅选择合适的连接。
  • 如需获得查询 Spanner 实例所需的权限，请让您的管理员为您授予 BigQuery Connection User (roles/bigquery.connectionUser) Identity and Access Management (IAM) 角色。您还需要让管理员向您授予以下其中一种角色：
    • 如果您是精细访问权限控制用户，则需要有权访问对查询中的所有 Spanner 架构对象具有 SELECT 特权的数据库角色。
    • 如果您不是精细访问权限控制用户，则需要 Cloud Spanner Database Reader (roles/spanner.databaseReader) IAM 角色。

    如需了解如何授予 IAM 角色，请参阅管理对项目、文件夹和组织的访问权限。如需了解精细访问权限控制，请参阅精细访问权限控制简介。

选择合适的连接

如果您是 Spanner 精细访问权限控制用户，则在使用 EXTERNAL_QUERY 函数运行联邦查询时，必须使用指定数据库角色的 Spanner 连接。然后，您使用此连接运行的所有查询都使用该数据库角色。

如果您使用的连接未指定数据库角色，则必须具有开始前须知中所述的 IAM 角色。

查询数据

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

根据数据库的指定语言，在 GoogleSQL 或 PostgreSQL 中编制 Spanner 查询。

以下示例向名为 orders 的 Spanner 数据库发出联合查询，并将结果与名为 mydataset.customers 的 BigQuery 表联接。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''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;

Spanner Data Boost

Data Boost 是一项全代管式无服务器功能，可为受支持的 Spanner 工作负载提供独立的计算资源。Data Boost 使您可以执行分析查询和数据导出，且对预配的 Spanner 实例上的现有工作负载几乎没有影响。借助 Data Boost，您可以在与预配的实例不同的独立计算容量中运行联合查询，以避免影响 Spanner 上的现有工作负载。如果您运行复杂的临时查询，或者您希望处理大量数据而不影响现有 Spanner 工作负载，则 Data Boost 的影响最大。使用 Data Boost 运行联合查询可以显著降低 CPU 消耗，在某些情况下，还可缩短查询延迟时间。

准备工作

如需获得允许访问 Data Boost 所需的权限，请让您的管理员为您授予 Spanner 数据库的 Cloud Spanner Database Reader with DataBoost (roles/spanner.databaseReaderWithDataBoost) IAM 角色。 如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

此预定义角色可提供 spanner.databases.useDataBoost 权限，这是允许访问 Data Boost 所必需的。

您也可以使用自定义角色或其他预定义角色来获取此权限。

启用 Data Boost

使用外部数据集时，系统始终会使用 Data Boost，您无需手动启用它。

如果您想为 EXTERNAL_QUERY 查询使用 Data Boost，则必须在创建查询使用的连接时启用它。

并行读取数据

Spanner 可以将某些查询划分为较小部分（即分区），然后并行提取分区。如需了解详情，请参阅 Spanner 文档中的并行读取数据。

不过，此选项仅适用于满足以下条件之一的查询：

其他查询会返回错误。如需查看 Spanner 查询的查询执行计划，请参阅了解 Spanner 如何执行查询。

使用外部数据集运行联合查询时，系统始终会使用“并行读取数据”选项。

如需在使用 EXTERNAL_QUERY 时启用并行读取，请在创建连接时启用它。

管理查询执行优先级

使用 EXTERNAL_QUERY 函数运行联合查询时，您可以通过指定 query_execution_priority 选项来为各个查询分配优先级（high、medium 或 low）：

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

默认优先级为 medium。

优先级为 high 的查询将与事务流量竞争。优先级为 low 的查询会尽最大努力，并且可能被后台负载（例如计划备份）抢占。

使用外部数据集运行联合查询时，所有查询始终具有 medium 优先级。

查看 Spanner 表架构

如果您使用外部数据集，您的 Spanner 表会直接显示在 BigQuery Studio 中，并且您可以查看其架构。

不过，您也可以在不定义外部数据集的情况下查看架构。您还可以使用 EXTERNAL_QUERY 函数查询 information_schema 视图以访问数据库元数据。以下示例返回有关表 MyTable 中的列的信息：

Google SQL 数据库

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

PostgreSQL 数据库

SELECT * from EXTERNAL_QUERY(
 'my-project.us.postgresql.example-db',
  '''SELECT t.column_name, t.data_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_schema = 'public' and t.table_name='MyTable'
    ORDER BY t.ordinal_position
  ''');

如需了解详情，请参阅 Spanner 文档中的以下信息架构参考信息：

价格

  • 在 BigQuery 端，您需要按标准的联合查询价格付费。
  • 在 Spanner 端，查询需遵循 Spanner 价格
  • 从预览版阶段转换为正式版阶段时，价格可能会发生变化

问题排查

本部分可帮助您排查在将联合查询发送到 Spanner 时可能遇到的问题。

问题：查询不是根可分区的。
解决方法：如果配置连接以并行读取数据，则查询执行计划中的第一个运算符必须是分布式联合运算符，或者执行计划不得有任何分布式联合运算符。如要解决此错误，请查看查询执行计划并重写查询。如需了解详情，请参阅了解 Spanner 如何执行查询。
问题：已超出期限。
解决方法：选择并行读取数据的选项，并将查询重写为根分区的。如需了解详情，请参阅了解 Spanner 如何执行查询。

后续步骤