创建具体化视图
本文档介绍了如何在 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 的点击次数:
在控制台中,转到 BigQuery 页面。
在查询编辑器中,输入以下语句:
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 );
点击
运行。
如需详细了解如何运行查询,请参阅运行交互式查询。
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
参数搭配使用:
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 参考文档。
成功创建具体化视图后,它会显示在 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';
具体化视图可能不如原始查询高效。为获得最佳结果,请使用一组具有代表性的查询(使用和不使用具体化视图)进行实验。