创建和使用具体化视图

本文档介绍如何通过 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 流式缓冲区中的数据不会保存到具体化视图中。无论是否使用具体化视图,流式缓冲区仍会进行完全扫描。

使用分区表和聚簇表

具体化视图可以按在基表上配置的同一分区列进行分区。

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

下面展示了如何创建一个在 transaction_time 列上具有 DATE 分区的基表:

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)

下面展示了如何创建一个按同一 transaction_time 列分区的具体化视图:

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;

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

下面展示了如何创建一个按提取时间分区的基表:

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;

当基表按 TIMESTAMP 进行分区时,具体化视图可以截断该时间戳,使其达到的粒度为 DAY,然后将截断的时间戳用作分区列。

下面展示了如何创建一个基于 transaction_hour 列分区的具体化视图,该列是基表的 transaction_time 列的截断部分。使用 TIMESTAMP_TRUNC 函数可以截断时间戳。

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;

下面展示了可用于时间戳截断的粒度:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY

如需了解时间戳截断的一般信息,请参阅 TIMESTAMP_TRUNC

分区一致

如果某具体化视图已分区,则 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')

后续步骤