CREATE MATERIALIZED VIEW
适用于:Databricks SQL
_具体化视图_是预计算结果可用于查询的视图,并且可以更新以反映输入中的更改。 每次刷新具体化视图时,都会重新计算查询结果,以反映上游数据集中的变化。 所有具体化视图都由 DLT 管道支持。 你可以手动或按计划刷新具体化视图。
若要详细了解如何执行手动刷新,请参阅“REFRESH(具体化视图或流式处理表)”。
若要详细了解如何按计划刷新,请参阅“示例”或“ALTER MATERIALIZED VIEW”。
语法
{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
view_name
[ column_list ]
[ view_clauses ]
AS query
column_list
( { column_name column_type column_properties } [, ...]
[ , table_constraint ] [...])
column_properties
{ NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]
view_clauses
{ PARTITIONED BY (col [, ...]) |
COMMENT view_comment |
TBLPROPERTIES clause |
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] |
WITH { ROW FILTER clause } } [...]
parameters
REPLACE
如果已指定,请替换视图及其内容(如果已存在)。
IF NOT EXISTS
如果视图不存在,则创建它。 如果已存在具有此名称的视图,则忽略
CREATE MATERIALIZED VIEW
语句。最多可以指定
IF NOT EXISTS
或OR REPLACE
中的一项。-
新创建的视图的名称。 完全限定的视图名称必须是独一无二的。
column_list
(可选)在视图的查询结果中标记列。 如果提供列列表,则列别名的数量必须与查询中的表达式数量一致。 如果未指定列列表,则别名派生自视图主体。
-
列名必须具有唯一性,并映射到查询的输出列。
column_type
指定列的数据类型。 并非 Azure Databricks 支持的所有数据类型都受具体化视图支持。
column_comment
描述列名称的可选
STRING
文本。 此选项必须与column_type
一起指定。 如果未指定列类型,则会跳过列注释。column_constraint
向具体化视图中的列添加信息性主键或信息性外键约束。 如果未指定列类型,则会跳过列约束。
-
重要
此功能目前以公共预览版提供。
添加列掩码函数以对敏感数据进行匿名化处理。 该列的所有未来查询将会收到对该列计算该函数(而不是该列的原始值)的结果。 这对于细粒度的访问控制目的非常有用,在这种情况下,该函数可以检查调用用户的身份和/或组成员身份,以便决定是否编辑该值。 如果未指定列类型,则会跳过列掩码。
-
table_constraint
向具体化视图中的表添加信息性主键或信息性外键约束。 如果未指定列类型,则会跳过表约束。
view_clauses
为新具体化视图指定分区、注释、用户定义的属性和刷新计划(可选)。 每个子子句只能指定一次。
-
表中用于对表进行分区的列可选列表。
COMMENT view_comment
用于描述表的
STRING
文本。-
可以选择设置一个或多个用户定义的属性。
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]
如果提供计划,则它会流式处理表或具体化视图,以使用给定的 quartz cron 计划刷新其数据。 仅接受 time_zone_values。 不支持
AT TIME ZONE LOCAL
。 如果AT TIME ZONE
不存在,则使用会话时区。 如果AT TIME ZONE
不存在并且未设置会话时区,则会引发错误。SCHEDULE
在语义上等效于SCHEDULE REFRESH
。WITH ROW FILTER 子句
重要
此功能目前以公共预览版提供。
向表中添加行筛选器函数。 该表中的所有未来查询都将接收函数计算结果为布尔值 TRUE 的行的子集。 这对于细粒度的访问控制目的非常有用,在这种情况下,该函数可以检查调用用户的身份和/或组成员身份,以便决定是否筛选特定行。
-
AS 查询
从基表或其他视图中构造视图的查询。
所需的权限
创建具体化视图 (MV) 的用户是 MV 所有者,需要具有以下权限:
- 对 MV 引用的基表的
SELECT
特权。 - 对父目录具有
USE CATALOG
权限,对父架构具有USE SCHEMA
权限。 - 对 MV 架构的
CREATE
特权。
为了让用户能够刷新 MV,他们需要:
- 对父目录具有
USE CATALOG
权限,对父架构具有USE SCHEMA
权限。 - MV 所有权或对 MV 的
REFRESH
特权。 - MV 所有者必须对 MV 引用的基表具有
SELECT
特权。
为了让用户能够查询 MV,他们需要:
- 对父目录具有
USE CATALOG
权限,对父架构具有USE SCHEMA
权限。 - 对具体化视图的
SELECT
特权。
行筛选器和列掩码
重要
此功能目前以公共预览版提供。
行筛选器允许指定在表扫描提取行时作为筛选器应用的函数。 这些筛选器可确保后续查询仅返回筛选器谓词的计算结果为 true 的行。
每当表扫描提取行时,列掩码就会让你将列值掩码。 将来所有涉及该列的查询都会收到对该列计算函数后的结果,并替换列的原始值。
有关如何使用行筛选器和列掩码的详细信息,请参阅使用行筛选器和列掩码筛选敏感表数据。
管理行筛选器和列掩码
应通过CREATE
语句添加具体化视图上的行筛选器和列掩码。
行为
- 以定义者身份刷新:当
REFRESH MATERIALIZED VIEW
语句刷新具体化视图时,行筛选器函数将以定义者的权限(以表所有者的身份)运行。 这意味着表刷新使用创建具体化视图的用户的安全上下文。 - 查询:虽然大多数筛选器都以定义者的权限运行,但检查用户上下文(例如
CURRENT_USER
和IS_MEMBER
)的函数并非如此。 这些函数作为调用程序运行。 此方法基于当前用户的上下文强制实施特定于用户的数据安全和访问控制。 - 在包含行筛选器和列掩码的源表上创建具体化视图时,具体化视图的刷新始终是完全刷新。 完全刷新使用最新定义重新处理源中的所有可用数据。 这可确保使用最新的数据和定义来评估和应用源表上的安全策略。
可观察性
使用DESCRIBE EXTENDED
、INFORMATION_SCHEMA
或目录资源管理器检查应用于给定具体化视图的现有行筛选器和列掩码。 此功能允许用户审核和查看有关具体化视图的数据访问和保护措施。
限制
- 当在可以为 NULL 的列上具有
sum
聚合的具体化视图从该列中移除了最后一个非 NULL 值 - 因此该列中只剩下NULL
个值时,具体化视图的结果聚合值将返回零而不是NULL
。 - 列引用不需要别名。 非列引用表达式需要别名,如以下示例所示:
- 允许:
SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
- 不允许:
SELECT col1, SUM(col2) FROM t GROUP BY col1
- 允许:
- 必须手动指定
NOT NULL
和PRIMARY KEY
,才能成为有效的语句。 - 具体化视图不支持标识列或代理项键。
- 具体化视图不支持
OPTIMIZE
和VACUUM
命令。 维护是自动进行的。 - 具体化视图不支持定义数据质量约束的预期。
示例
-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create and schedule a materialized view to be refreshed daily at midnight.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
COMMENT 'Daily sales numbers'
SCHEDULE CRON '0 0 0 * * ? *'
AS SELECT date AS date, sum(sales) AS sumOfSales
FROM table1
GROUP BY date;
-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
member_id int NOT NULL,
full_name string,
movie_title string,
CONSTRAINT movie_pk PRIMARY KEY(member_id)
)
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
PARTITIONED BY (member_id)
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
id int,
name string,
region string,
ssn string MASK catalog.schema.ssn_mask_fn
)
WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
AS SELECT id, name, region, ssn
FROM employees;