Spanner 联合查询

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

借助 BigQuery Spanner 联合，BigQuery 能够实时查询驻留在 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 精细访问权限控制用户，则在运行联合查询时，您必须使用指定数据库角色的 Spanner 连接。然后，您使用此连接运行的所有查询都使用该数据库角色。

如果您使用未指定数据库角色的连接，则必须具有准备工作中所述的 IAM 角色。

Spanner Data Boost

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

准备工作

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

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

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

启用 Data Boost

如需在发送到 Spanner 的联合查询上启用 Data Boost，您必须先建立与 Spanner 的连接。在连接中启用 Data Boost 后，请查询数据以将联合查询发送到 Spanner。

查询数据

如需将联合查询从 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 可以将某些查询划分为较小部分（即分区），然后并行提取分区。如需了解详情，请参阅 Spanner 文档中的并行读取数据。

如需在联合查询中启用并行读取，请在创建连接资源时配置此设置。此选项将 SQL 查询划分为较小的分区，并并行提取每个分区。但是，此选项仅限于满足以下条件之一的查询：

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

管理查询执行优先级

您可以通过指定 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 的查询会尽最大努力，并且可能被后台负载（例如计划备份）抢占。

查看 Spanner 表架构

您可以使用 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 文档中的以下信息架构参考信息：

问题排查

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

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

后续步骤