创建和使用具体化视图

本文档介绍如何通过 BigQuery 具体化视图创建和使用具体化视图。在阅读本文档之前,请先熟悉具体化视图简介。 现已提供无聚合的具体化视图有联接的具体化视图的预览版。

所需权限

您必须拥有具体化视图操作所需的权限,才能使用具体化视图。下表列出了可以对具体化视图执行的操作类型、可用于执行这些操作的命令和方法、每个操作所需的权限,以及具有这些权限的默认角色。

如需详细了解 BigQuery Identity and Access Management (IAM),请参阅预定义角色和权限

操作类型 命令或方法 所需权限 默认角色
创建 CREATE MATERIALIZED VIEW bq mk --materialized view tables.insert bigquery.tables.create bigquery.dataEditor bigquery.dataOwner bigquery.admin
查询 标准 SQL 查询 bq query tables.getData bigquery.dataViewer bigquery.dataEditor bigquery.dataOwner bigquery.admin
更新 ALTER MATERIALIZED VIEW bq query bq update tables.patch tables.update bigquery.tables.get bigquery.tables.update bigquery.dataEditor bigquery.dataOwner bigquery.admin
删除 DROP MATERIALIZED VIEW bq query bq rm tables.delete bigquery.tables.get bigquery.tables.delete bigquery.dataEditor bigquery.dataOwner bigquery.admin
手动刷新 CALL BQ.REFRESH_MATERIALIZED_VIEW bigquery.tables.getData bigquery.tables.update bigquery.tables.updateData bigquery.dataEditor bigquery.dataOwner bigquery.admin

创建具体化视图

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

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

列名 类型
product_id 整数
clicks 整数

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

列名 类型
product_id 整数
sum_clicks 整数

创建具体化视图的步骤如下所示:

控制台

借助数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表和视图。

详细了解如何使用数据定义语言语句

如需使用 DDL 语句在 Cloud Console 中创建具体化视图,请执行以下操作:

  1. 在 Cloud Console 中,转到 BigQuery 页面。

    转到 BigQuery

  2. 点击编写新查询

  3. 查询编辑器文本区域中,输入 CREATE MATERIALIZED VIEW DDL 语句。

    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 是您要创建的具体化视图中的一列。
  4. 点击运行

除非您停用自动刷新,否则 BigQuery 会对具体化视图启动异步完全刷新。查询可能会立即返回成功,但初始刷新可能仍在运行。成功创建具体化视图后,它会显示在数据集窗格中。

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 是您要创建的具体化视图中的一列。

除非您停用自动刷新,否则 BigQuery 会对具体化视图启动异步完全刷新。查询可能会立即返回成功,但初始刷新可能仍在运行。成功创建具体化视图后,它会显示在数据集窗格中。

或者,您也可以结合使用 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 是您要创建的具体化视图中的一列。

除非您停用自动刷新,否则 BigQuery 会对具体化视图启动异步完全刷新。查询可能会立即返回成功,但初始刷新可能仍在运行。成功创建具体化视图后,它会显示在数据集窗格中。

Java

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

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());
    }
  }
}

访问权限控制机制

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

查询具体化视图

按照与查询常规表或标准视图相同的方式直接查询具体化视图。

当您查询具有具体化视图的基表时,查询优化器可自动重写查询以使用存储在该具体化视图中的缓存结果。仅当基表和具体化视图在同一数据集中时,才会执行重写。查询计划显示查询已被重写为使用具体化视图。

如果可以使用多个具体化视图来重写查询,则会使用预计待扫描行数最少的具体化视图。

如果您查询的具体化视图不是最新的,则该具体化视图中的数据会与基表中的增量更改相结合以计算最新结果。具体化视图可能不是最新的原因包括:

  • 系统未对具体化视图设置自动刷新,因为用户决定手动控制刷新。

  • 基表的更新过于频繁,因此强制实施刷新频率上限

  • 基表是在几秒钟之前更改的,因此具体化视图尚未与基表同步。

即使具体化视图由于一个或多个原因而不是最新的,针对具体化视图的查询仍会保持最新状态,因为基表中的更改也包含在查询结果中。

如需了解如何运行查询,请参阅查询 BigQuery 数据概览

更改具体化视图

您可以将 DDL 与 ALTER MATERIALIZED VIEWSET OPTIONS 结合使用,通过 Cloud Console 或 bq 命令行工具来更改具体化视图。

下面展示了一个将 enable_refresh 设置为 true 的示例。根据您的用例按需进行调整。

控制台

如需使用 DDL 语句在 Cloud Console 中更改具体化视图,请执行以下操作:

  1. 在 Cloud Console 中,打开 BigQuery 页面。

转到 BigQuery

  1. 点击编写新查询

  2. 查询编辑器文本区域中,输入 ALTER MATERIALIZED VIEW DDL 语句。

    ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    SET OPTIONS (enable_refresh=true)
    

    其中:

    • project-id 是您的项目 ID。
    • my_dataset 是项目中数据集的 ID。
    • my_mv_table 是您要更改的具体化视图的 ID。
  3. 点击运行

bq

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

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)

其中:

  • project-id 是您的项目 ID。
  • my_dataset 是项目中数据集的 ID。
  • my_mv_table 是您要更改的具体化视图的 ID。

或者,您也可以运行 bq update命令。

bq update \
--enable_refresh=true \
--refresh_interval_ms= \
project-id.my_dataset.my_mv_table

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryMaterializedView {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query = String.format("SELECT * FROM %s.%s", datasetName, materializedViewName);
    queryMaterializedView(query);
  }

  public static void queryMaterializedView(String query) throws InterruptedException {
    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();

      TableResult results = bigquery.query(QueryJobConfiguration.of(query));
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s\n", val.toString())));

      System.out.println("Query performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query was not performed. \n" + e.toString());
    }
  }
}

处理具体化视图

具体化视图只能通过 CREATEDROPALTER 语句及其 API 等效项以及通过手动刷新来处理。

不允许对具体化视图执行以下操作。

  • 在来源或目标为具体化视图的情况下运行复制作业、导入作业或导出作业。

  • 将查询结果写入具体化视图。

  • 调用 tabledata.list

  • 使用 BigQuery Storage Read API。

以下示例将更改一个具体化视图:

Java

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

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.Table;
import com.google.cloud.bigquery.TableId;

// Sample to alter materialized view
public class AlterMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    alterMaterializedView(datasetName, materializedViewName);
  }

  public static void alterMaterializedView(String datasetName, String materializedViewName) {
    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);

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Alter materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view altered successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not altered. \n" + e.toString());
    }
  }
}

删除具体化视图

您可以通过 Cloud Console、bq 命令行工具或 API 删除具体化视图。

控制台

如需使用 DDL 语句在 Cloud Console 中删除具体化视图,请执行以下操作:

  1. 在 Cloud Console 中,打开 BigQuery 页面。

转到 BigQuery

  1. 点击编写新查询

  2. 查询编辑器文本区域中,输入 DELETE MATERIALIZED VIEW DDL 语句。

    DROP MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    

    其中:

    • project-id 是您的项目 ID。
    • my_dataset 是项目中数据集的 ID。
    • my_mv_table 是您要删除的具体化视图的 ID。
  3. 点击运行

bq

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

bq query --use_legacy_sql=false '
DROP MATERIALIZED VIEW
project-id.my_dataset.my_mv_table'

其中:

  • project-id 是您的项目 ID。
  • my_dataset 是项目中数据集的 ID。
  • my_mv_table 是您要删除的具体化视图的 ID。

或者,您也可以使用 bq rm 命令来删除具体化视图。

API

调用 tables.delete 方法并为 projectIddatasetIdtableId 参数指定值:

  • 为项目 ID 分配 projectId 参数。
  • 为数据集 ID 分配 datasetId 参数。
  • 为要删除的具体化视图的表 ID 分配 tableId 参数。

Java

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

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

// Sample to delete materialized view
public class DeleteMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    deleteMaterializedView(datasetName, materializedViewName);
  }

  public static void deleteMaterializedView(String datasetName, String materializedViewName) {
    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);

      boolean success = bigquery.delete(tableId);
      if (success) {
        System.out.println("Materialized view deleted successfully");
      } else {
        System.out.println("Materialized view was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not found. \n" + e.toString());
    }
  }
}

监控具体化视图

您可以使用 BigQuery API 获取有关具体化视图和具体化视图刷新作业的信息。

具体化视图监控

如需发现具体化视图,请调用 tables.list 方法或查询 INFORMATION_SCHEMA.TABLES 表。

如需检索具体化视图的属性,请调用 tables.get 方法,或查询 INFORMATION_SCHEMA.TABLE_OPTIONS 表。

INFORMATION_SCHEMA.VIEWS 表中未列出具体化视图。

具体化视图刷新作业监控

如需探索具体化视图刷新作业,请调用 jobs.list 方法列出这些作业。如需检索作业详细信息,请调用 jobs.get 方法。自动刷新作业的作业 ID 包含 materialized_view_refresh 前缀,而且这些作业是由 BigQuery 管理员帐号启动的。

例如:

SELECT job_id, total_slot_ms, total_bytes_processed
FROM region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE "%materialized_view_refresh_%"
LIMIT 10

支持的具体化视图

具体化视图必须是对单个表的聚合。GROUP BY 的使用是可选的。 现已提供无聚合的具体化视图有联接的具体化视图的预览版。

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

SELECT
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

目前仅支持以下聚合函数

  • ANY_VALUE(但不超过 STRUCT
  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG(但不超过 ARRAYSTRUCT
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • HLL_COUNT.INIT
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • SUM

支持不会添加基于聚合的计算或过滤的特定子查询。例如,支持以下 SQL 查询:

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
SELECT ts_hour, COUNT(*) as cnt
FROM tmp
GROUP BY ts_hour

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT * FROM tmp

SELECT ts_hour, COUNT(*) as cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
GROUP BY ts_hour

SELECT * FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)

由于以下 SQL 查询应用基于聚合的计算,因此具体化视图不支持该查询:

-- Not supported for a materialized view
WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT TIMESTAMP_TRUNC(ts_hour, DAY) as ts_day, cnt+1 as cnt FROM tmp

由于以下 SQL 查询应用基于聚合的过滤,因此具体化视图不支持该查询:

-- Not supported for a materialized view
SELECT ts_hour, cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
WHERE ts_hour != TIMESTAMP(DATE('2020-01-01'))

FROM 子句

FROM 子句必须包含单个表,并且可以将一个或多个数组表达式解除嵌套。

示例

FROM mytable

FROM mytable AS t, t.struct_column.array_field AS x

FROM mytable AS t LEFT JOIN UNNEST(t.array_column) AS x

FROM mytable AS t, t.array_column AS x, x.array_field AS y

FROM mytable AS t, UNNEST(SPLIT(t.string_column)) AS x

不支持 WITH OFFSET

支持的查询重写模式

基表查询和具体化视图定义支持以下查询重写模式。

模式 1

此模式表明查询中的分组键和聚合器是具体化视图中的分组键和聚合器的子集。

对于此基表查询:

SELECT
  ss_sold_date_sk,
  SUM(ss_net_profit) AS sum_profit
FROM store_sales
GROUP BY 1

下面是一个具体化视图查询,将基表查询的分组键和聚合器显示为该具体化视图查询的子集。

SELECT
  ss_store_sk,
  ss_sold_date_sk,
  SUM(ss_net_paid) AS sum_paid,
  SUM(ss_net_profit) AS sum_profit,
  COUNT(*) AS cnt_sales
FROM store_sales
GROUP BY 1, 2

模式 2

此模式表明基表查询中的分组键可以根据具体化视图中的分组键计算得出。

对于此基表查询:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

下面是一个计算基表中的分组键的具体化视图查询:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

模式 3

此模式表明查询中的过滤条件表达式可派生自具体化视图中的分组键,也可以与具体化视图完全匹配。

对于此基表查询:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE DATE(l_shipdate) >= DATE(2016, 01, 01) AND l_shipmode = "AIR"
GROUP BY 1

下面是一个从基表派生过滤条件表达式的具体化视图查询:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE l_shipmode = "AIR"
GROUP BY 1

模式 4

此模式表明基表查询中的表达式选择具体化视图中的部分数据。

对于此基表查询:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount > 20.0
GROUP BY 1

以下是一个其结果是基本表查询的超集的具体化视图查询:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount IS NOT NULL
GROUP BY 1

增量更新

在基表仅在附加时不断更改的情况下,使用具体化视图的查询将扫描所有具体化视图数据以及自上次刷新以来基表中的增量。无论具体化视图是由查询优化器明确引用还是明确选择,这一点都适用,在这两种情况下查询速度会更快、费用更低。

但是,如果自上次具体化视图刷新以来,基表中有任何更新或删除,则可能不会扫描具体化视图。

以下是导致更新或删除的操作示例:

  • DML UPDATE
  • DML MERGE
  • DML DELETE
  • 截断
  • 分区到期
  • 控制台、bq 命令行和此列表中前面几项的 API 等效项

如果发生这些操作,则在进行下一次视图刷新之前,具体化视图查询可能无法实现任何节省。实际上,基表中的任何更新或删除都会使部分具体化视图状态失效。对于未分区视图,整个视图会失效。对于分区视图,在大多数情况下,只有受影响的分区才会失效。

请注意,基表的 BigQuery 流式缓冲区中的数据不会保存到具体化视图中。无论是否使用具体化视图,流式缓冲区仍会进行完全扫描。

使用分区表和聚簇表

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

如果基表按提取时间分区,则具体化视图可以按基表的 _PARTITIONDATE 列分组,也可以按此列分区。

具体化视图可以按任意列进行聚簇,但要受到 BigQuery 聚簇表限制的约束。

示例 1

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

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

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_time)
CLUSTER BY employee_id
AS SELECT
  employee_id,
  transaction_time,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

示例 2

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

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64)
PARTITION BY _PARTITIONDATE
OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW project-id.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 1, 2;

示例 3

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

请注意以下几点:

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

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

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

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_hour)
AS SELECT
  employee_id,
  TIMESTAMP_TRUNC(transaction_time, HOUR) as transaction_hour,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

分区一致

如果某具体化视图已分区,则 BigQuery 会确保其分区与基表的分区一致。例如,基表的分区 2020-01-01 中的行会聚合到该具体化视图的分区 2020-01-01 中的行。

分区一致有助于保证具体化视图得到高效维护。刷新期间,具体化视图中唯一会更新的分区是在基表的相应分区中附加、更新或删除的分区。(在极少数情况下,基表中的删除或更新操作可能会导致重新计算整个视图。)查询时,分区一致可确保分区过滤条件同时应用于具体化视图扫描和基表扫描。

分区到期

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

在分区基表的基础上创建具体化视图后,无法更改该表的分区到期时间。如需更改基表的分区到期时间,您必须先删除基于该表创建的所有具体化视图。

刷新具体化视图

您可以指定 BigQuery 是使用自动刷新还是手动刷新来更新基表中的预计算结果。如果您不想使用默认值,则可以在创建具体化视图时配置刷新设置。您也可以在创建具体化视图后更改刷新设置。

您可以随时手动刷新具体化视图。

自动刷新

默认情况下,具体化视图会在更改基表后的 5 分钟内自动刷新,但频率不能超过每 30 分钟一次。更改示例包括行插入或行删除。

您可以随时启用或停用自动刷新。

如需在创建表时停用自动刷新,请将 enable_refresh 设置为 false

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY RANGE_BUCKET(column, buckets)
OPTIONS (enable_refresh = false)
AS SELECT ...

对于现有具体化视图,您可以使用 ALTER MATERIALIZED VIEW 修改 enable_refresh 值。

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true)

即使停用了自动刷新,具体化视图也可以随时手动刷新。

设置频率上限

您可以对自动刷新运行频率配置频率上限。默认情况下,具体化视图的刷新频率不超过每 30 分钟刷新一次。

您可以随时更改刷新频率上限。

如需在创建具体化视图时设置刷新频率上限,请将 DDL 中的 refresh_interval_minutes(或 API 和 bq 命令行工具中的 refresh_interval_ms)设置所需的值。

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT ...

同样,您可以在修改表时设置频率上限。此示例假定您已启用自动刷新,但只想更改频率上限:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (refresh_interval_minutes = 60)

刷新频率最小上限为 1 分钟。刷新频率最大上限为 7 天。

您可以随时手动刷新具体化视图,其时间不受频率上限的约束。

尽力而为

系统会尽最大努力执行自动刷新。BigQuery 会尝试在基表更改后的 5 分钟内开始刷新(如果上一次刷新是在 30 分钟之前执行的),但无法保证刷新操作会在这个时间开始,也无法保证其完成时间。(查询具体化视图会反映基表的最新状态,但如果视图在最近未经过刷新,则查询费用/延迟时间可能会高于预期。)

自动刷新的处理方式与具有批量优先级的查询类似。 如果具体化视图的项目当前没有容量,则刷新会延迟。如果项目包含多个最终证明其刷新开销较大的视图,则每个视图可能会相对于其基表出现显著延迟。

手动刷新

如需更新具体化视图中的数据,请随时调用 BQ.REFRESH_MATERIALIZED_VIEW 系统过程。调用此过程后,BigQuery 会识别在基表中发生的更改并将这些更改应用到具体化视图。刷新完成后,对运行 BQ.REFRESH_MATERIALIZED_VIEW 的查询也将完成。

CALL BQ.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')

无聚合的具体化视图(预览版)

无聚合的具体化视图(也称为投影具体化视图)可用于提前重新聚簇、过滤或计算数据。无聚合的具体化视图类似于索引,可让 BigQuery 通过估算基表中查询的字节数较少还是一个或多个可用的限定具体化视图中查询的字节数较少,来优化查询。无聚合的具体化视图还可以提供预过滤或预计算的数据,以优化未来查询的性能。

用例

以下示例演示了如何使用无聚合的具体化视图,并假设您有以下表:

CREATE TABLE dataset.base_table (
  x INT64,
  y INT64,
  string_field STRING)
CLUSTER BY x;

重新聚簇数据

如果您经常发出的查询可受益于与包含具体化视图的基表不同的聚簇方案,则可能会提高查询性能。

上面的 dataset.base_tablex 列聚簇,因此它会针对按该列过滤或聚合的查询进行优化。如果您经常运行过滤 y 列而非 x 列的查询,则以下具体化视图可提高这些查询的性能。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY y
AS SELECT * FROM dataset.base_table;

查询 SELECT COUNT(*) FROM dataset.base_table WHERE y = 123 可能会被 BigQuery 优化器重写为使用具体化视图,因为它会减少 BigQuery 需要扫描的数据量。

预过滤数据

如果您经常运行只读取表的特定部分的查询,则具体化视图可能会提高查询性能。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY x
AS SELECT * FROM dataset.base_table
WHERE y < 1000;

假设 y 值通常大于 1000,则查询 SELECT COUNT(*) FROM dataset.base_table WHERE x = 123 AND y < 500 可能会被 BigQuery 优化器重写为使用具体化视图。请注意,该查询中的谓词不必与视图中的谓词完全匹配。如受支持的查询重写模式中所述,查询可以选择视图中的部分数据。

预计算数据

如果您经常使用计算开销很大的函数或从较大的列中提取少量数据,则具体化视图可能会提高查询性能。

如果 string_field 包含 JSON 数据,并且您通常对特定子字段的值感兴趣,则以下具体化视图可能会提高这些查询的性能。

CREATE MATERIALIZED VIEW dataset.mv
AS SELECT x, JSON_EXTRACT(string_field, "$.subfield1.subfield2") subfield2
FROM dataset.base_table;

查询 SELECT JSON_EXTRACT(string_field, "$.subfield1.subfield2") FROM dataset.base_table WHERE x = 123 可能会被 BigQuery 优化器重写为使用具体化视图。BigQuery 优化器在选择查询时仅考虑查询的字节数,而不考虑最终的计算成本。

有内部联接的具体化视图(预览版)

对于具有星型和雪花型架构(其中的单个大型事实表经常与一个或多个较小的维度表联接)的数据集,有联接的具体化视图可以提高性能并降低费用。有联接的具体化视图可以通过提前联接和聚合数据来减少或消除在查询时联接数据的需求。该具体化视图对计算费用高且返回小型数据集的复杂查询最具有优势。

限制

除了现有具体化视图的限制之外,有联接的具体化视图还存在以下限制:

  • 必须使用与现有具体化视图相同的一组聚合器进行聚合。
  • 仅支持 INNER 联接。不支持 CROSS、FULL、LEFT 和 RIGHT 联接。
  • 分区视图必须从查询中的第一个/最左侧表中获取其分区列。
  • 不支持自联接。

创建有联接的具体化视图

您可以使用 DDL CREATE MATERIALIZED VIEW 语句或 API 创建有联接的具体化视图,就像单表具体化视图一样。查询中的表顺序会影响性能。为获得最佳性能,请将最大的表放在首位。如果有多个大型表,则将最常更改的表设为联接顺序中的第一个表,如优化查询计算所述。如需了解详情,请参阅有联接的具体化视图的最佳做法

查询有联接的具体化视图

您可以直接查询有联接的具体化视图,就像查询单表具体化视图、常规表或标准视图一样。您发出匹配查询时,查询优化器可自动重写查询,以使用存储在该具体化视图中的预计算结果。查询计划显示查询是否已被重写为使用具体化视图。

支持的查询重写模式

为了允许自动查询重写,查询必须使用与具体化视图完全相同的一组表,并且顺序也要相同。例如,使用以下具体化视图:

SELECT
 s_country,
 SUM(ss_net_paid) as sum_sales,
 COUNT(*) AS cnt_sales
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
GROUP BY 1;

以下查询将被自动重写为使用具体化视图,这样可能会节省费用和时间。

SELECT
 SUM(ss_net_paid),
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"
GROUP BY 1;

但是,以下查询不会被重写,因为它按不同的顺序引用 storestore_sales

SELECT
 SUM(ss_net_paid),
FROM store
INNER JOIN store_sales
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"

对有联接的具体化视图的增量更新

有联接的具体化视图支持对查询中的第一个/最左侧表进行增量查询。这意味着 BigQuery 可以使用缓存的视图数据,即使自具体化视图上一次刷新后,数据已附加到第一个/最左侧表也是如此。如果视图中的第一个/最左侧表有更新或删除,或者视图中的其他表发生了更改,BigQuery 将无法使用缓存的视图数据。以下示例展示了如何对有联接的具体化视图进行增量更新。

CREATE MATERIALIZED VIEW dataset.mv AS SELECT
 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 1;

CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

INSERT INTO dataset.store_sales …;

-- This query and refresh will use the cached data, and read new data from
-- store_sales and all data from store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

DELETE FROM dataset.store_sales WHERE …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

INSERT INTO dataset.store …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

对于分区视图,只有在第一个/最左侧表发生更新或删除时,受影响的分区才会失效。对其他联接表的更改会导致整个视图失效。

刷新具体化视图

有联接的具体化视图可以自动刷新,也可以手动刷新,就像单表具体化视图一样。BigQuery 会尝试通过增量方式维护有联接的具体化视图,从而联接所需的最少数据量。但是,如果自具体化视图上次刷新以来给定基表发生更改,则需要 BigQuery 扫描其他基表中的所有数据。

有联接的具体化视图的最佳做法

除了现有具体化视图的最佳做法之外,以下最佳做法还针对有联接的具体化视图。

先放置最常更改的表

确保最大的表是视图查询中引用的第一个/最左侧表。当查询中的第一个/最左侧表发生更改时,有联接的具体化视图支持增量查询和更新,但其他表发生更改会导致视图缓存完全失效。在星型或雪花型架构中,这可能是事实表。

手动维护具体化视图,以获得更具预测性的性能

如果您更新或删除第一个/最左侧表中的数据,或者在可预测的时间更改查询中的其他表,请考虑在这些更改后立即执行手动视图刷新,从而获得视图维护时间表的所有权。

对分组键进行聚簇

按分组键对视图进行聚簇可提高包含这些键的过滤条件的查询的性能。

避免在聚簇键上联接

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

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY s_marked_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 1;

如果 store_salesss_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'

那么具体化视图可能不如原始查询高效。为获得最佳结果,请使用一组具有代表性的查询(使用和不使用具体化视图)进行实验。如需了解如何不使用具体化视图运行查询,请参阅常见问题解答。

视图安全性

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

后续步骤