创建具体化视图

本文档介绍了如何在 BigQuery 中创建具体化视图。在阅读本文档之前，请先熟悉具体化视图简介。

准备工作

授予为用户提供执行本文档中的每个任务所需权限的 Identity and Access Management (IAM) 角色。

所需权限

如需创建具体化视图，您需要拥有 bigquery.tables.create IAM 权限。

以下每个预定义 IAM 角色都包含创建具体化视图所需的权限：

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

如需详细了解 BigQuery Identity and Access Management (IAM)，请参阅使用 IAM 进行访问权限控制。

创建具体化视图

您可以通过 Google Cloud Console、bq 命令行工具或 BigQuery API 创建 BigQuery 具体化视图。

在这些示例中，假设您的基表名为 my_base_table 并具有以下架构：

列名	类型
product_id	整数
clicks	整数

另外还假设您需要一个具体化视图，其中汇总了每个 product_id 的点击次数。以下步骤将创建一个名为 my_mv_table 且具有以下架构的具体化视图。

列名	类型
product_id	整数
sum_clicks	整数

如需创建具体化视图，请选择以下选项之一：

SQL

使用 CREATE MATERIALIZED VIEW 语句。以下示例将创建一个具体化视图，其中包含每个产品 ID 的点击次数：

1. 在控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，输入以下语句：

   CREATE MATERIALIZED VIEW  myproject.mydataset.my_mv_table AS (
     SELECT
       product_id,
       SUM(clicks) AS sum_clicks
     FROM
       myproject.mydataset.my_base_table
     GROUP BY
       product_id
   );

3. 点击 运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

bq

使用 bq query 命令并提供 DDL 语句作为查询参数。

bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM project-id.my_dataset.my_base_table
GROUP BY 1'

其中：

• project-id 是您的项目 ID。
• my_dataset 是项目中数据集的 ID。
• my_mv_table 是您要创建的具体化视图的 ID。
• my_base_table 是数据集中用作具体化视图的基表的表 ID。
• product_id 是基表中的一列。
• clicks 是基表中的一列。
• sum_clicks 是您要创建的具体化视图中的一列。

或者，您也可以结合使用 bq mk 命令和 --materialized_view 参数来创建具体化视图。以下参数可与 --materialized_view 参数搭配使用：

• --enable_refresh：指定是否启用自动刷新。
• --refresh_interval_ms：指定最大刷新频率的时间（以毫秒为单位）。如需详细了解这些参数的含义，请参阅刷新具体化视图。

API

在您的 API 请求中，调用 tables.insert 方法，并结合使用已定义的 materializedView 资源。materializedView 资源包含 query 字段。例如：

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from project-id.my_dataset.my_base_table
                group by 1"
  }
}

其中：

• project-id 是您的项目 ID。
• my_dataset 是项目中数据集的 ID。
• my_mv_table 是您要创建的具体化视图的 ID。
• my_base_table 是数据集中用作具体化视图的基表的表 ID。
• product_id 是基表中的一列。
• clicks 是基表中的一列。
• sum_clicks 是您要创建的具体化视图中的一列。

Java

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

在 GitHub 上查看 反馈

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

成功创建具体化视图后，它会显示在 Google Cloud 控制台内 BigQuery 的探索器面板中。具体化视图会列在控制台的探索器面板上。以下示例展示了具体化视图架构的显示方式：

除非您停用自动刷新，否则 BigQuery 会对具体化视图启动异步完全刷新。查询将快速完成，但初始刷新可能会继续运行。

访问权限控制

您可以在数据集级层、视图级层或列级层授予对具体化视图的访问权限。您还可以在 IAM 资源层次结构中的更高级层设置访问权限。

查询具体化视图需要访问视图及其基表。如需共享具体化视图，您可以向基表授予权限，或将具体化视图配置为已获授权的视图。如需了解详情，请参阅已获授权的视图。

如需控制对 BigQuery 中视图的访问权限，请参阅控制对视图的访问权限。

具体化视图查询支持

具体化视图使用受限 SQL 语法。查询必须使用以下模式：

[ WITH cte [, …]]
SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

from_item:
    {
      table_name [ as_alias ]
      | { join_operation | ( join_operation ) }
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

查询限制

具体化视图具有以下限制。

聚合要求

具体化视图查询中的聚合必须是输出。不支持根据聚合值进行计算或过滤。例如，不支持从以下查询创建视图，因为它会生成从聚合 COUNT(*) / 10 as cnt 计算得出的值。

SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt
FROM mydataset.mytable
GROUP BY ts_hour;

目前仅支持以下聚合函数：

• ANY_VALUE（但不超过 STRUCT）
• APPROX_COUNT_DISTINCT
• ARRAY_AGG（但不超过 ARRAY 或 STRUCT）
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• COUNTIF
• HLL_COUNT.INIT
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• SUM

不支持的 SQL 功能

在具体化视图中，不支持以下 SQL 功能：

• 左/右/全外联接。
• 自联接（多次使用同一个表的联接）。
• 分析函数
• 非确定性函数，例如 RAND()、CURRENT_DATE() 或 CURRENT_TIME()。
• 用户定义的函数 (UDF)。
• TABLESAMPLE。
• FOR SYSTEM_TIME AS OF。

WITH 子句和通用表表达式 (CTE)

具体化视图支持 WITH 子句和通用表表达式。具有 WITH 子句的具体化视图仍必须遵循没有 WITH 子句的具体化视图的模式和限制。

示例

以下示例显示了使用 WITH 子句的具体化视图：

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, *
  FROM mydataset.mytable
)
SELECT ts_hour, COUNT(*) AS cnt
FROM tmp
GROUP BY ts_hour;

以下示例显示了使用 WITH 子句的具体化视图，这是不受支持的，因为它包含两个 GROUP BY 子句：

WITH tmp AS (
  SELECT city, COUNT(*) AS population
  FROM mydataset.mytable
  GROUP BY city
)
SELECT population, COUNT(*) AS cnt
GROUP BY population;

分区具体化视图

分区表上的具体化视图可以分区。对具体化视图分区的方式类似于对普通表分区的方式，因为当查询经常访问部分分区时，此方法可以带来诸多好处。此外，对具体化视图进行分区可以改善基表或基表中的数据修改或删除时的行为。如需了解详情，请参阅分区一致。

如果基表已分区，则可以基于同一分区列对具体化视图进行分区。对于基于时间的分区，粒度必须一致（每小时、每天、每月或每年）。对于整数范围分区，范围规范必须完全一致。您无法基于非分区基表对具体化视图进行分区。

如果基表按提取时间分区，则具体化视图可以按基表的 _PARTITIONDATE 列分组，也可以按此列分区。如果您在创建具体化视图时没有明确指定分区，则具体化视图会取消分区。

如果基表已分区，请考虑对具体化视图也进行分区，以减少刷新作业维护费用和查询费用。

分区到期

无法对具体化视图设置分区到期时间。具体化视图会隐式继承基表中的分区到期时间。具体化视图分区与基表分区一致，因此它们会同时到期。

注意：基于具有分区过期时间的表的非分区具体化视图已失效，并且在分区过期时必须完全刷新。因此，您应该对具体化视图进行分区，以避免额外的刷新和查询费用。

示例 1

在此示例中，基表基于 transaction_time 列，通过每日分区进行分区。具体化视图基于同一列进行分区，并基于 employee_id 列进行聚簇。

CREATE TABLE my_project.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time)
  OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_time)
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    transaction_time,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id, transaction_time
);

示例 2

在此示例中，基表按注入时间通过每日分区进行分区。具体化视图选择注入时间作为名为 date 的列。具体化视图按 date 列分组，并按同一列分区。

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY date
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    _PARTITIONDATE AS date,
    COUNT(1) AS count
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    date
);

示例 3

在此示例中，基表基于名为 transaction_time 的 TIMESTAMP 列通过每日分区进行分区。具体化视图定义了一个名为 transaction_hour 的列，并使用 TIMESTAMP_TRUNC 函数将值截断到最接近的小时数。具体化视图按 transaction_hour 分组和分区。

请注意以下几点：

• 应用于分区列的截断函数的粒度必须至少与基表的分区一样。例如，如果基表使用每日分区，则截断函数不能使用 MONTH 或 YEAR 粒度。

• 在具体化视图的分区规范中，粒度必须与基表匹配。

CREATE TABLE my_project.my_dataset.my_base_table (
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_hour)
AS (
  SELECT
    employee_id,
    TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    transaction_hour
);

聚簇具体化视图

您可以根据具体化输出列对具体化视图进行聚簇，但受到 BigQuery 聚簇表限制的约束。聚合输出列不能用作聚簇列。将聚簇列添加到具体化视图可以改善包含这些列的过滤条件的查询性能。

创建具体化视图时的注意事项

要创建哪些具体化视图

创建具体化视图时，请确保具体化视图定义反映了针对基表的查询模式。由于每个表最多有 20 个具体化视图，因此您不应为查询的每个排列都创建一个具体化视图。相反，您应该创建一些能够提供更广泛的查询集的具体化视图。

例如，假设有一个针对某个表的查询，用户经常按 user_id 或 department 列过滤该表。您可以按这些列进行分组，也可以选择按这些列进行聚簇，而不是将 user_id = 123 之类的过滤条件添加到具体化视图中。

再举一个例子，用户经常使用日期过滤条件，即按特定日期（例如 WHERE order_date = CURRENT_DATE()）或日期范围（例如 WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'）进行过滤。在具体化视图中添加日期范围过滤条件，用于涵盖查询中的预期日期范围：

CREATE MATERIALIZED VIEW ...
  ...
  WHERE date > '2019-01-01'
  GROUP BY date

联接

以下建议适用于具有联接的具体化视图。

先放置最常更改的表

确保最大或最常更改的表是视图查询中引用的第一个/最左侧表。有联接的具体化视图支持增量查询，并在查询中第一个或最左侧的表附加时刷新，但对其他表的更改会使视图缓存完全失效。在星型或雪花型架构中，第一个或最左侧的表通常应该是事实表。

避免在聚簇键上联接

如果数据包含大量聚合或原始联接查询费用高昂，此时最适合使用有联接的具体化视图。对于选择性查询，BigQuery 往往已能够高效地执行联接，无需具体化视图。例如，请考虑以下具体化视图定义。

CREATE MATERIALIZED VIEW dataset.mv
  CLUSTER BY s_market_id
AS (
  SELECT
    s_market_id,
    s_country,
    SUM(ss_net_paid) AS sum_sales,
    COUNT(*) AS cnt_sales
  FROM dataset.store_sales
  INNER JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY s_market_id, s_country
);

假设 store_sales 聚簇在 ss_store_sk 上，并且您经常运行如下查询：

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany';

具体化视图可能不如原始查询高效。为获得最佳结果，请使用一组具有代表性的查询（使用和不使用具体化视图）进行实验。

后续步骤

• 管理具体化视图
• 使用具体化视图。