创建物化视图

注意：使用通过特定 BigQuery 版本创建的预留时，此功能可能不可用。如需详细了解每个版本中启用的功能，请参阅 BigQuery 版本简介。

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

准备工作

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

所需权限

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

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

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

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

创建物化视图

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

SQL

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

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

   转到 BigQuery

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

   CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
     QUERY_EXPRESSION
   );

   替换以下内容：

   • PROJECT_ID：要创建物化视图的项目的名称，例如 myproject。
   • DATASET：要创建物化视图的 BigQuery 数据集的名称，例如 mydataset。如果您要通过 Amazon Simple Storage Service (Amazon S3) BigLake 表（预览版）创建物化视图，请确保数据集位于支持的区域。
   • MATERIALIZED_VIEW_NAME：要创建的物化视图的名称，例如 my_mv。
   • QUERY_EXPRESSION：定义物化视图的 GoogleSQL 查询表达式，例如 SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table。

3. 点击 运行。

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

示例

以下示例将创建一个物化视图，其中包含每个产品 ID 的点击次数：

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

Terraform

使用 google_bigquery_table 资源。

注意：如需使用 Terraform 创建 BigQuery 对象，您必须启用 Cloud Resource Manager API。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

以下示例创建了一个名为 my_materialized_view 的视图：

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "my_materialized_view"
  deletion_protection = false # set to "true" in production

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

如需在 Google Cloud 项目中应用 Terraform 配置，请完成以下部分中的步骤。

准备 Cloud Shell

1. 启动 Cloud Shell。

2. 设置要在其中应用 Terraform 配置的默认 Google Cloud 项目。

   您只需为每个项目运行一次以下命令，即可在任何目录中运行它。

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   如果您在 Terraform 配置文件中设置显式值，则环境变量会被替换。

准备目录

每个 Terraform 配置文件都必须有自己的目录（也称为“根模块”）。

1. 在 Cloud Shell 中，创建一个目录，并在该目录中创建一个新文件。文件名必须具有 .tf 扩展名，例如 main.tf。在本教程中，该文件称为 main.tf。

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. 如果您按照教程进行操作，可以在每个部分或步骤中复制示例代码。

   将示例代码复制到新创建的 main.tf 中。

   （可选）从 GitHub 中复制代码。如果端到端解决方案包含 Terraform 代码段，则建议这样做。

3. 查看和修改要应用到您的环境的示例参数。
4. 保存更改。
5. 初始化 Terraform。您只需为每个目录执行一次此操作。

   terraform init

   （可选）如需使用最新的 Google 提供程序版本，请添加 -upgrade 选项：

   terraform init -upgrade

应用更改

1. 查看配置并验证 Terraform 将创建或更新的资源是否符合您的预期：

   terraform plan

   根据需要更正配置。

2. 通过运行以下命令并在提示符处输入 yes 来应用 Terraform 配置：

   terraform apply

   等待 Terraform 显示“应用完成！”消息。

3. 打开您的 Google Cloud 项目以查看结果。在 Google Cloud 控制台的界面中找到资源，以确保 Terraform 已创建或更新它们。

注意：Terraform 示例通常假定您的 Google Cloud 项目中启用了所需的 API。

API

调用 tables.insert 方法并传入一个已定义 materializedView 字段的 Table 资源：

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "PROJECT_ID",
    "datasetId": "DATASET",
    "tableId": "MATERIALIZED_VIEW_NAME"
  },
  "materializedView": {
    "query": "QUERY_EXPRESSION"
  }
}

替换以下内容：

• PROJECT_ID：要创建物化视图的项目的名称，例如 myproject。
• DATASET：要创建物化视图的 BigQuery 数据集的名称，例如 mydataset。如果您要通过 Amazon Simple Storage Service (Amazon S3) BigLake 表（预览版）创建物化视图，请确保数据集位于支持的区域。
• MATERIALIZED_VIEW_NAME：要创建的物化视图的名称，例如 my_mv。
• QUERY_EXPRESSION：定义物化视图的 GoogleSQL 查询表达式，例如 SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table。

示例

以下示例将创建一个物化视图，其中包含每个产品 ID 的点击次数：

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

Java

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

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

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 会对物化视图启动异步完全刷新。查询会快速完成，但初始刷新可能会继续运行。

注意：每个基表在同一数据集内最多只能有 20 个物化视图、同一项目内 100 个物化视图以及同一组织内 500 个物化视图。

访问权限控制

您可以在数据集级层、视图级层或列级层授予对物化视图的访问权限。 您还可以在 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
• MAX_BY（但不超过 STRUCT）
• MIN_BY（但不超过 STRUCT）
• SUM

不支持的 SQL 功能

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

• UNION ALL。（ 预览版支持）
• LEFT OUTER JOIN（ 预览版支持）
• RIGHT/FULL OUTER JOIN。
• 自联接（也称为对同一个表多次使用 JOIN）。
• 窗口函数。
• ARRAY 子查询。
• 非确定性函数，例如 RAND()、CURRENT_DATE()、SESSION_USER() 或 CURRENT_TIME()。
• 用户定义的函数 (UDF)。
• TABLESAMPLE。
• FOR SYSTEM_TIME AS OF。

LEFT OUTER JOIN 和 UNION ALL 支持

预览

此功能须遵守服务专用条款的“通用服务条款”部分中的“非正式版产品条款”。 非正式版功能“按原样”提供，可能只能获得有限的支持。 如需了解详情，请参阅发布阶段说明。

如需针对此功能请求反馈或支持，请发送电子邮件至 bq-mv-help @google.com。

增量物化视图支持 LEFT OUTER JOIN 和 UNION ALL。具有 LEFT OUTER JOIN 和 UNION ALL 语句的物化视图与其他增量物化视图具有相同的限制。此外，智能调优不适用于包含 UNION ALL 或 LEFT OUTER JOIN 的物化视图。

示例

以下示例使用 LEFT JOIN 创建汇总增量物化视图。当数据附加到左侧表时，此视图会以增量方式更新。

CREATE MATERIALIZED VIEW dataset.mv
AS (
  SELECT
    s_store_sk,
    s_country,
    s_zip,
    SUM(ss_net_paid) AS sum_sales,
  FROM dataset.store_sales
  LEFT JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY 1, 2, 3
);

以下示例使用 UNION ALL 创建汇总增量物化视图。当数据附加到任一表或两个表时，此视图会以增量方式更新。如需详细了解增量更新，请参阅增量更新。

CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour)
AS (
  SELECT
    SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales
  FROM
    (SELECT ts, sales from dataset.table1 UNION ALL
     SELECT ts, sales from dataset.table2)
  GROUP BY 1
);

访问权限控制限制

• 如果用户对物化视图的查询包括因列级安全性而无法访问的基表列，则查询会失败并显示消息 Access Denied。
• 如果用户查询物化视图，但不具备物化视图基表中所有行的完整访问权限，则 BigQuery 会对基表运行查询，而不是读取物化视图数据。这样可以确保查询遵循所有访问权限控制限制条件。查询具有数据遮盖列的表时，也适用此限制。

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;

BigLake 表的物化视图

如需创建 BigLake 表的物化视图，BigLake 表必须对 Cloud Storage 数据启用元数据缓存，并且物化视图的 max_staleness 选项值必须大于基表。BigLake 表的物化视图支持与其他物化视图相同的查询集。

示例

使用 BigLake 基表创建简单的聚合视图：

CREATE MATERIALIZED VIEW sample_dataset.sample_mv
    OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND)
AS SELECT COUNT(*) cnt
FROM dataset.biglake_base_table;

如需详细了解 BigLake 表的物化视图的限制，请参阅 BigLake 表的物化视图。

Apache Iceberg 表的物化视图

预览

此功能须遵守服务专用条款的“通用服务条款”部分中的“非正式版产品条款”。 非正式版功能“按原样”提供，可能只能获得有限的支持。 如需了解详情，请参阅发布阶段说明。

如需针对此功能请求反馈或支持，请发送电子邮件至 [e-mail]。

您可以在经处理视图中引用大型 Iceberg 表，而不是将这些数据迁移到 BigQuery 托管存储空间。

在 Iceberg 表上创建物化视图

如需在 Iceberg 上创建物化视图，请按以下步骤操作：

1. 使用以下方法之一获取 Iceberg 表：

   • 使用 JSON 元数据文件创建 Iceberg 表。
   • 使用 BigLake Metastore 创建 Iceberg 表。
   • 在 AWS Glue 联合数据集中发现数据。

   示例

   CREATE EXTERNAL TABLE mydataset.myicebergtable
     WITH CONNECTION `myproject.us.myconnection`
     OPTIONS (
           format = 'ICEBERG',
           uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
     )
   

2. 使用以下分区规范引用您的 Iceberg 表：

   "partition-specs" : [ {
      "spec-id" : 0,
      "fields" : [ {
       "name" : "birth_month",
       "transform" : "month",
       "source-id" : 3,
       "field-id" : 1000
   } ]
   

3. 创建与分区对齐的物化视图：

   CREATE MATERIALIZED VIEW mydataset.myicebergmv
     PARTITION BY DATE_TRUNC(birth_month, MONTH)
   AS
     SELECT * FROM mydataset.myicebergtable;
   

限制

除了标准 Iceberg 表的限制之外，Iceberg 表的物化视图还存在以下限制：

• 您可以创建与基表分区一致的物化视图。不过，物化视图仅支持基于时间的分区转换，例如 YEAR、MONTH、DAY 和 HOUR。
• 物化视图的分区粒度不得小于基表分区的粒度。例如，如果您使用 birth_date 列每年对基表进行分区，则使用 PARTITION BY DATE_TRUNC(birth_date, MONTH) 创建物化视图将无法正常运行。
• 任何架构更改都会使物化视图失效。
• 支持分区演进。但是，更改基表的分区列而不重新创建物化视图可能会导致完全失效，无法通过刷新来解决。
• 基表中必须至少有一个快照。
• Iceberg 表必须是 BigLake 表，例如已授权的外部表。
• 如果已启用 VPC Service Controls，则必须将授权外部表的服务账号添加到入口规则中，否则 VPC Service Controls 会阻止对该实时查询的自动后台刷新。

Iceberg 表的 metadata.json 文件必须符合以下规范。如果没有这些规范，您的查询会扫描基表，而无法使用物化结果。

• 在表元数据中：

  • current-snapshot-id
  • current-schema-id
  • snapshots
  • snapshot-log

• 在快照中：

  • parent-snapshot-id（如果适用）
  • schema-id
  • operation（在 summary 字段中）

• 分区（对于分区物化视图）

分区物化视图

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

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

如果基表按提取时间分区，则物化视图可以按基表的 _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 聚簇表限制的约束。聚合输出列不能用作聚簇列。将聚簇列添加到物化视图可以改善包含这些列的过滤条件的查询性能。

引用逻辑视图

预览

此功能须遵守服务专用条款的“通用服务条款”部分中的“非正式版产品条款”。 非正式版功能“按原样”提供，可能只能获得有限的支持。 如需了解详情，请参阅发布阶段说明。

如需针对此功能请求反馈或支持，请发送电子邮件至 [e-mail]。

物化视图查询可以引用逻辑视图，但存在以下限制：

• 物化视图限制适用。
• 如果逻辑视图发生变化，则物化视图会失效，必须完全刷新。
• 不支持智能调优。

创建物化视图时的注意事项

要创建哪些物化视图

创建物化视图时，请确保物化视图定义反映了针对基表的查询模式。具体化视图在用于处理一系列查询（而不仅仅是某种特定的查询模式）时，会更高效。

例如，假设有一个针对某个表的查询，用户经常按 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';

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

将物化视图与 max_staleness 选项结合使用

max_staleness 物化视图视图选项可帮助您在处理大型、频繁更改的数据集时以可控的成本实现始终如一的高查询性能。借助 max_staleness 参数，您可以通过设置查询结果数据滞后可接受的时间间隔来降低查询的成本和延迟时间。此行为对于数据新鲜度不是必需的信息中心和报告很有用。

数据过时

当您查询设置了 max_staleness 选项的物化视图时，BigQuery 会根据 max_staleness 值以及上次刷新发生的时间返回结果。

如果最后一次刷新是在 max_staleness 间隔内，则 BigQuery 会直接从物化视图返回数据，而无需读取基表。例如，如果 max_staleness 间隔为 4 小时，并且上次刷新时间是 2 小时前，则会出现这种情况。

如果上次刷新发生在 max_staleness 间隔之外，BigQuery 会从物化视图中读取数据，将其与上次刷新以来对基表所做的更改合并，并返回合并后的结果。此组合结果可能仍然过时，具体取决于您的 max_staleness 间隔。例如，如果 max_staleness 间隔为 4 小时，并且上次刷新时间是 7 小时前，则会出现这种情况。

使用 max_staleness 选项创建

从下列选项中选择一项：

SQL

如需使用 max_staleness 选项创建物化视图，请在创建物化视图时向 DDL 语句添加 OPTIONS 子句：

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

   转到 BigQuery

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

   CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
     OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
       max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
   AS SELECT
     employee_id,
     DATE(transaction_time),
     COUNT(1) AS count
   FROM my_dataset.my_base_table
   GROUP BY 1, 2;

   替换以下内容：

   • project-id 是您的项目 ID。
   • my_dataset 是项目中数据集的 ID。
   • my_mv_table 是您要创建的物化视图的 ID。
   • my_base_table 是数据集中用作物化视图的基表的表 ID。

   • 点击 运行。

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

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"
  }
  "maxStaleness": "4:0:0"
}

替换以下内容：

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

应用 max_staleness 选项

您可以使用 ALTER MATERIALIZED VIEW 语句将此参数应用于现有物化视图。例如：

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120,
  max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);

使用 max_staleness 查询

您可以使用 max_staleness 选项查询物化视图，就像查询任何其他物化视图、逻辑视图或表一样。

例如：

SELECT * FROM  project-id.my_dataset.my_mv_table

如果数据不早于 max_staleness 参数，则此查询将返回上次刷新的数据。如果物化视图尚未在 max_staleness 间隔内刷新，则 BigQuery 会将最新可用刷新的结果与基表更改合并，以在 max_staleness 间隔内返回结果。

数据流和 max_staleness 结果

如果使用 max_staleness 选项将数据流式传输到物化视图的基表中，则物化视图的查询可能会排除在过时间隔开始之前流式传输到表中的记录。因此，如果物化视图包含来自多个表的数据及 max_staleness 选项，则可能并不表示这些表的时间点快照。

智能调整和 max_staleness 选项

无论 max_staleness 选项如何，智能调整都会自动重写查询以尽可能使用物化视图，即使查询未引用物化视图也是如此。物化视图上的 max_staleness 选项不会影响重写查询的结果。max_staleness 选项仅影响直接查询物化视图的查询。

管理过时和刷新频率

您应根据自己的需要设置 max_staleness。为避免从基表中读取数据，请配置刷新间隔，以便在过时间隔内进行刷新。您可以考虑平均刷新运行时以及增长幅度。

比方说，如果需要一个小时来刷新物化视图，并且您希望一小时的增长缓冲区，则应将刷新间隔设置为两个小时。此配置可确保在报告的过期时间最长 4 小时内进行刷新。

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness =
INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

非增量物化视图

非增量物化视图支持大多数 SQL 查询，包括 OUTER JOIN、UNION 和 HAVING 子句以及分析函数。如需确定您的查询中是否使用了物化视图，请使用试运行检查费用估算。在可以接受数据过时的场景中（例如批量数据处理或报告），非增量物化视图可提高查询性能并降低费用。您可以使用 max_staleness 选项构建任意的复杂物化视图，这些物化视图自动进行维护且内置了过时保证。

使用非增量物化视图

您可以使用 allow_non_incremental_definition 选项创建非增量物化视图。此选项必须与 max_staleness 选项同时使用。为确保定期刷新物化视图，您还应配置刷新政策。如果没有刷新政策，您必须手动刷新物化视图。

物化视图始终表示 max_staleness 间隔内的基表的状态。如果上次刷新过于过时，并且不表示 max_staleness 间隔内的基表，则查询会读取基表。如需详细了解可能的性能影响，请参阅数据过时。

使用 allow_non_incremental_definition 创建

如需使用 allow_non_incremental_definition 选项创建物化视图，请按照以下步骤操作。创建物化视图后，您无法修改 allow_non_incremental_definition 选项。例如，您无法将值 true 更改为 false，也不能从物化视图中移除 allow_non_incremental_definition 选项。

SQL

创建物化视图时，向 DDL 语句添加 OPTIONS 子句：

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

   转到 BigQuery

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

   CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
   OPTIONS (
     enable_refresh = true, refresh_interval_minutes = 60,
     max_staleness = INTERVAL "4" HOUR,
       allow_non_incremental_definition = true)
   AS SELECT
     s_store_sk,
     SUM(ss_net_paid) AS sum_sales,
     APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
   FROM my_project.my_dataset.store
   LEFT OUTER JOIN my_project.my_dataset.store_sales
     ON ss_store_sk = s_store_sk
   GROUP BY s_store_sk
   HAVING median < 40 OR median is NULL ;

   替换以下内容：

   • my_project 是您的项目 ID。
   • my_dataset 是项目中数据集的 ID。
   • my_mv_table 是您要创建的物化视图的 ID。
   • my_dataset.store 和 my_dataset.store_sales 是数据集中用作物化视图的基表的表的 ID。

3. 点击 运行。

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

API

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

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "my_project",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "`SELECT`
        s_store_sk,
        SUM(ss_net_paid) AS sum_sales,
        APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
      FROM my_project.my_dataset.store
      LEFT OUTER JOIN my_project.my_dataset.store_sales
        ON ss_store_sk = s_store_sk
      GROUP BY s_store_sk
      HAVING median < 40 OR median is NULL`",
    "allowNonIncrementalDefinition": true
  }
  "maxStaleness": "4:0:0"
}

替换以下内容：

• my_project 是您的项目 ID。
• my_dataset 是项目中数据集的 ID。
• my_mv_table 是您要创建的物化视图的 ID。
• my_dataset.store 和 my_dataset.store_sales 是数据集中用作物化视图的基表的表的 ID。

使用 allow_non_incremental_definition 查询

您可以查询非增量物化视图，就像查询任何其他物化视图、逻辑视图或表一样。

例如：

SELECT * FROM  my_project.my_dataset.my_mv_table

如果数据不早于 max_staleness 参数，则此查询将从上次刷新返回数据。如需详细了解数据过时和新鲜度，请参阅数据过时。

非增量物化视图的特定限制

以下限制仅适用于使用 allow_non_incremental_definition 选项的物化视图。除了对支持的查询语法施加的限制外，所有物化视图限制仍适用。

• 智能调优不会应用于包含 allow_non_incremental_definition 选项的物化视图。从包含 allow_non_incremental_definition 选项的物化视图获益的唯一方法是直接查询它们。
• 不包含 allow_non_incremental_definition 选项的物化视图可以逐步刷新其部分数据。包含 allow_non_incremental_definition 选项的物化视图必须完整地进行刷新。
• 使用 max_staleness 选项的物化视图会在查询执行期间验证列级安全约束条件是否存在。如需了解详情，请参阅列级访问权限控制

后续步骤

• 管理物化视图。
• 使用物化视图。