CREATE MATERIALIZED VIEW
功能
创建物化视图。关于物化视图适用的场景请参考同步物化视图和异步物化视图。
创建物化视图是一个异步的操作。该命令执行成功即代表创建物化视图的任务提交成功。您可以通过 SHOW ALTER MATERIALIZED VIEW 命令查看当前数据库中同步物化视图的构建状态,或通过查询 Information Schema 中的元数据表 tasks
和 task_runs
来查看异步物化视图的构建状态。
注意
只有拥有基表所在数据库的 CREATE MATERIALIZED VIEW 权限的用户才可以创建物化视图。
StarRocks 自 v2.4 起支持异步物化视图。异步物化视图与先前版本中的同步物化视图区别主要体现在以下方面:
单表聚合 | 多表关联 | 查询改写 | 刷新策略 | 基表 | |
---|---|---|---|---|---|
异步物化视图 | 是 | 是 | 是 |
| 支持多表构建。基表可以来自:
|
同步物化视图(Rollup) | 仅部分聚合函数 | 否 | 是 | 导入同步刷新 | 仅支持基于 Default Catalog 的单表构建 |
同步物化视图
语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]<mv_name>
[COMMENT ""]
[PROPERTIES ("key"="value", ...)]
AS
<query_statement>
参数
mv_name(必填)
物化视图的名称。命名要求如下:
- 必须由字母(a-z 或 A-Z)、数字(0-9)或下划线(_)组成,且只能以字母开头。
- 总长度不能超过 64 个字符。
- 视图名大小写敏感。
COMMENT(选填)
物化视图的注释。注意建立物化视图时 COMMENT
必须在 mv_name
之后,否则创建失败。
query_statement(必填)
创建物化视图的查询语句,其结果即为物化视图中的数据。语法如下:
SELECT select_expr[, select_expr ...]
[WHERE where_expr]
[GROUP BY column_name[, column_name ...]]
[ORDER BY column_name[, column_name ...]]
-
select_expr(必填)
构建同步物化视图的查询语句。
-
单列或聚合列:形如
SELECT a, b, c FROM table_a
,其中a
、b
和c
为基表的列名。如果您没有为物化视图指定列名,那么 StarRocks 自动为这些列命名。 -
表达式:形如
SELECT a+1 AS x, b+2 AS y, c*c AS z FROM table_a
,其中a+1
、b+2
和c*c
为包含基表列名的表达式,x
、y
和z
为物化视图的列名。
说明
- 该参数至少需包含一个单列。
- 同步物化视图仅支持在单列上使用聚合函数。不支持形如
sum(a+b)
形式的查询语句。 - 使用聚合函数创建同步物化视图时,必须指定 GROUP BY 子句,并在
select_expr
中指定至少一个 GROUP BY 列。 - 同步物化视图不支持 JOIN、以及 GROUP BY 的 HAVING 子句。
- 从 v3.1 开始,每个同步物化视图支持为基表的每一列使用多个聚合函数,支持形如
select b, sum(a), min(a) from table group by b
形式的查询语句。 - 从 v3.1 开始,同步物化视图支持 SELECT 和聚合函数的复杂表达式,即形如
select b, sum(a + 1) as sum_a1, min(cast (a as bigint)) as min_a from table group by b
或select abs(b) as col1, a + 1 as col2, cast(a as bigint) as col3 from table
的查询语句。同步物化视图的复杂表达式有以下限制:- 每个复杂表达式必须有一个列名,并且基表所有同步物化视图中的不同复杂表达式的别名必须不同。例如,查询语句
select b, sum(a + 1) as sum_a from table group by b
和select b, sum(a) as sum_a from table group by b
不能同时用于为相同的基表创建同步物化视图。 - 每个复杂表达式只能引 用一列。不支持形如
a + b as col1
形式的查询语句。 - 您可以通过执行
EXPLAIN <sql_statement>
来查看您的查询是否被使用复杂表达式创建的同步物化视图改写。更多信息请参见查询分析。
- 每个复杂表达式必须有一个列名,并且基表所有同步物化视图中的不同复杂表达式的别名必须不同。例如,查询语句
-
-
WHERE (选填)
自 v3.1.8 起,同步物化视图支持通过 WHERE 子句筛选数据。
-
GROUP BY(选填)
构建物化视图查询语句的分组列。如不指定该参数,则默认不对数据进行分组。
-
ORDER BY(选填)
构建物化视图查询语句的排序列。
-
排序列的声明顺序必须和
select_expr
中列声明顺序一致。 -
如果查询语句中包含分组列,则排序列必须和分组列一致。
-
如果不指定排序列,则系统根据以下规则自动补充排序列:
- 如果物化视图是聚合类型,则所有的分组列自动补充为排序列。
- 如果物化视图是非聚合类型,则系统根据前缀列自动选择排序列。
-
查询同步物化视图
因为同步物化视图本质上是基表的索引而不是物理表,所以您只能使用 Hint [_SYNC_MV_]
查询同步物化视图:
-- 请勿省略 Hint 中的括号[]。
SELECT * FROM <mv_name> [_SYNC_MV_];
注意
目前,StarRocks 会自动为同步物化视图中的列生成名称。您为同步物化视图中的列指定的 Alias 将无法生效。
同步物化视图查询自动改写
使用同步物化视图查询时,原始查询语句将会被自动改写并用于查询物化视图中保存的中间结果。
下表展示了原始查询聚合函数和构建同步物化视图用到的聚合函数的匹配关系。您可以根据业务场景选择对应的聚合函数构建同步物化视图。
原始查询聚合函数 | 物化视图构建聚合函数 |
---|---|
sum | sum |
min | min |
max | max |
count | count |
bitmap_union, bitmap_union_count, count(distinct) | bitmap_union |
hll_raw_agg, hll_union_agg, ndv, approx_count_distinct | hll_union |
异步物化视图
语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]<mv_name>
[COMMENT ""]
-- 必须至少指定 `distribution_desc` 和 `refresh_scheme` 其中之一。
-- distribution_desc
[DISTRIBUTED BY HASH(<bucket_key>[,<bucket_key2> ...]) [BUCKETS <bucket_number>]]
-- refresh_desc
[REFRESH
-- refresh_moment
[IMMEDIATE | DEFERRED]
-- refresh_scheme
[ASYNC | ASYNC [START (<start_time>)] EVERY (INTERVAL <refresh_interval>) | MANUAL]
]
-- partition_expression
[PARTITION BY
{<date_column> | date_trunc(fmt, <date_column>)}
]
-- order_by_expression
[ORDER BY (<sort_key>)]
[PROPERTIES ("key"="value", ...)]
AS
<query_statement>
参数
mv_name(必填)
物化视图的名称。命名要求如下:
- 必须由字母(a-z 或 A-Z)、数字(0-9)或下划线(_)组成,且只能以字母开头。
- 总长度不能超过 64 个字符。
- 视图名大小写敏感。
注意
同一张基表可以创建多个异步物化视图,但同一数据库内的异步物化视图名称不可重复。
COMMENT(选填)
物化视图的注释。注意建立物化视图时 COMMENT
必须在 mv_name
之后,否则创建失败。
distribution_desc(选填)
异步物化视图的分桶方式,包括哈希分桶和随机分桶(自 3.1 版本起)。如不指定该参数,StarRocks 使用随机分桶方式,并自动设置分桶数量。
说明
创建异步物化视图时必须至少指定
distribution_desc
和refresh_scheme
其中之一。
-
哈希分桶:
语法
DISTRIBUTED BY HASH (<bucket_key1>[,<bucket_key2> ...]) [BUCKETS <bucket_number>]
更多信息,请参见 分桶。
说明
自 2.5.7 版本起,StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS),您无需手动设置分桶数量。更多信息,请参见 确定分桶数量。
-
随机分桶:
如果您选择随机分桶方式,并且自动设置分桶数量,则无需指定
distribution_desc
。如果您需要手动设置分桶数,请使用以下语法:DISTRIBUTED BY RANDOM BUCKETS <bucket_number>
注意
采用随机分桶方式的异步物化视图不支持设置 Colocation Group。
更多信息,请参见 随机分桶。
refresh_moment(选填)
物化视图的刷新时刻。默认值:IMMEDIATE
。有效值:
IMMEDIATE
:异步物化视图创建成功后立即刷新。DEFERRED
:异步物化视图创建成功后不进行刷新。您可以通过手动调用或创建定时任务触发刷新。
refresh_scheme(选填)
说明
创建异步物化视图时必须至少指定
distribution_desc
和refresh_scheme
其中之一。
物化视图的刷新方式。该参数支持如下值:
ASYNC
: 自动刷新模式。每当基表数据发生变化时,物化视图会自动刷新。ASYNC [START (<start_time>)] EVERY(INTERVAL <interval>)
: 定时刷新模式。物化视图将按照定义的间隔定时刷新。您可以使用DAY
(天)、HOUR
(小时)、MINUTE
(分钟)和SECOND
(秒)作为单位指定间隔,格式为EVERY (interval n day/hour/minute/second)
。默认值为10 MINUTE
(10 分钟)。您还可以进一步指定刷新起始时间,格式为START('yyyy-MM-dd hh:mm:ss')
。如未指定起始时间,默认使用当前时间。示例:ASYNC START ('2023-09-12 16:30:25') EVERY (INTERVAL 5 MINUTE)
。MANUAL
: 手动刷新模式。除非手动触发刷新任务,否则物化视图不会刷新。
如果不指定该参数,则默认使用 MANUAL 方式。
partition_expression(选填)
异步物化视图的分区表达式。目前仅支持在创建异步物化视图时使用一个分区表达式。
注意
异步物化视图暂不支持使用 List 分区策略。
该参数支持如下值:
date_column
:用于分区的列的名称。形如PARTITION BY dt
,表示按照dt
列进行分区。date_trunc
函数:形如PARTITION BY date_trunc("MONTH", dt)
,表示将dt
列截断至以月为单位进行分区。date_trunc 函数支持截断的单位包括YEAR
、MONTH
、DAY
、HOUR
以及MINUTE
。str2date
函数:用于将基表的 STRING 类型分区键转化为物化视图的分区键所需的日期类型。PARTITION BY str2date(dt, "%Y%m%d")
表示dt
列是一个 STRING 类型日期,其日期格式为"%Y%m%d"
。str2date
函数支持多种日期格式。更多信息,参考str2date。自 v3.1.4 起支持。time_slice
函数:从 v3.1 开始,您可以进一步使用 time_slice 函数根据指定的时间粒度周期,将给定的时间转化到其所在的时间粒度周期的起始或结束时刻,例如PARTITION BY date_trunc("MONTH", time_slice(dt, INTERVAL 7 DAY))
,其中 time_slice 的时间 粒度必须比date_trunc
的时间粒度更细。你可以使用它们来指定一个比分区键更细时间粒度的 GROUP BY 列,例如,GROUP BY time_slice(dt, INTERVAL 1 MINUTE) PARTITION BY date_trunc('DAY', ts)
。
如不指定该参数,则默认物化视图为无分区。
order_by_expression(选填)
异步物化视图的排序键。如不指定该参数,StarRocks 从 SELECT 列中选择部分前缀作为排序键,例如:select a, b, c, d
中, 排序列可能为 a
和 b
。此参数自 StarRocks 3.0 起支持。
PROPERTIES(选填)
异步物化视图的属性。您可以使用 ALTER MATERIALIZED VIEW 修改已有异步物化视图的属性。
-
session.
: 如果您想要更改与物化视图相关的 Session 变量属性,必须在属性前添加session.
前缀,例如,session.query_timeout
。对于非 Session 属性,例如,mv_rewrite_staleness_second
,则无需指定前缀。 -
replication_num
:创建物化视图副本数量。 -
storage_medium
:存储介质类型。有效值:HDD
和SSD
。 -
storage_cooldown_time
: 当设置存储介质为 SSD 时,指定该分区在该时间点之后从 SSD 降冷到 HDD,设置的时间必须大于当前时间。如不指定该属性,默认不进行自动降冷。取值格式为:"yyyy-MM-dd HH:mm:ss"。 -
partition_ttl
: 物化视图分区的生存时间 (TTL)。数据在指定的时间范围内的分区将被保留,过期的分区将被自动删除。单位:YEAR
、MONTH
、DAY
、HOUR
和MINUTE
。例如,您可以将此属性设置为2 MONTH
(2个月)。建议您使用此属性,不推荐使用partition_ttl_number
。该属性自 v3.1.5 起支持。 -
partition_ttl_number
:需要保留的最近的物化视图分区数量。对于分区开始时间小于当前时间的分区,当数量超过该值之后,多余的分区将会被删除。StarRocks 将根据 FE 配置项dynamic_partition_check_interval_seconds
中的时间间隔定期检查物化视图分区,并自动删除过期分区。在动态分区场景下,提前创建的未来分区将不会被纳入 TTL 考虑。默认值:-1
。当值为-1
时,将保留物化视图所有分区。 -
partition_refresh_number
:单次刷新中,最多刷新的分区数量。如果需要刷新的分区数量超过该值,StarRocks 将拆分这次刷新任务,并分批完成。仅当前一批分区刷新成功时,StarRocks 会继续刷新下一批分区,直至所有分区刷新完成。如果其中有分区刷新失败,将不会产生后续的刷新任务。默认值:-1
。当值为-1
时,将不会拆分刷新任务。 -
excluded_trigger_tables
:在此项属性中列出的基表,其数据产生变化时不会触发对应物化视图自动刷新。该参数仅针对导入触发式刷新,通常需要与属性auto_refresh_partitions_limit
搭配使用。形式:[db_name.]table_name
。默认值为空字符串。当值为空字符串时,任意的基表数据变化都将触发对应物化视图刷新。 -
auto_refresh_partitions_limit
:当触发物化视图刷新时,需要刷新的最近的物化视图分区数量。您可以通过该属性限制刷新的范围,降低刷新代价,但因为仅有部分分区刷新, 有可能导致物化视图数据与基表无法保持一致。默认值:-1
。当参数值为-1
时,StarRocks 将刷新所有分区。当参数值为正整数 N 时,StarRocks 会将已存在的分区按时间先后排序,并刷新当前分区和 N-1 个历史分区。如果分区数不足 N,则刷新所有已存在的分区。如果物化视图存在提前创建的未来分区,将会刷新所有提前创建的分区。 -
mv_rewrite_staleness_second
:如果当前物化视图的上一次刷新在此属性指定的时间间隔内,则此物化视图可直接用于查询重写,无论基表数据是否更新。如果上一次刷新时间早于此属性指定的时间间隔,StarRocks 通过检查基表数据是否变更决定该物化视图能否用于查询重写。单位:秒。该属性自 v3.0 起支持。 -
colocate_with
:异步物化视图的 Colocation Group。更多信息请参阅 Colocate Join。该属性自 v3.0 起支持。 -
unique_constraints
和foreign_key_constraints
:创建 View Delta Join 查询改写的异步物化视图时的 Unique Key 约束和外键约束。更多信息请参阅 异步物化视图 - 基于 View Delta Join 场景改写查询。该属性自 v3.0 起支持。注意
Unique Key 约束和外键约束仅用于查询改写。导入数据时,不保证进行外键约束校验。您必须确保导入的数据满足约束条件。
-
resource_group
: 为物化视图刷新任务设置资源组。默认值为default_mv_wg
,即一个系统定义的,专门用于物化视图刷新的资源组。该资源组的cpu_core_limit
为1