CREATE MATERIALIZED VIEW
功能
创建物化视图。关于物化视图适用的场景请参考同步物化视图和异步物化视图。
创建物化视图是一个异步的操作。该命令执行成功即代表创建物化视图的任务提交成功。您可以通过 SHOW ALTER MATERIALIZED VIEW 命令查看当前数据库中同步物化视图的构建状态,或通过查询 Information Schema 中的 tasks
和 task_runs
来查看异步物化视图的构建状态。
注意
- 只有拥有基表所在数据库的 CREATE MATERIALIZED VIEW 权限的用户才可以创建物化视图。
- 自 v3.4.0 起,StarRocks 存算分离集群支持同步物化视图。
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
为物化视图的列名。
说明
- 该参数至少需包含一个单列。
- 使用聚合函数创建同步物化视图时,必须指定 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
不能同时用于为相同的基表创建同步物化视图,你可以为同一复杂表达式设置多个不同别名。 - 您可以通过执行
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(选填)
异步物化视图的分区表达式。目前仅支持在创建异步物化视图时使用一个分区表达式。
注意
自 v3.3.3 起,StarRocks 支持创建基于 List 分区策略的异步物化视图。
- 您可以基于使用 List 分区或表达式分区策略创建的表来创建 List 分区的物化视图。
- 目前,当使用 List 分区策略创建物化视图时,您只能指定一个分区键。如果基表有多个分区键,您只能选择其中一个分区键。
- 使用 List 分区策略的物化视图 的刷新行为和查询改写逻辑与使用 Range 分区策略的物化视图一致。
该参数支持如下值:
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
时,将不会拆分刷新任务。自 v3.3 起,默认值由-1
变为1
,表示 StarRocks 每次只刷新一个分区。 -
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 起支持。 -
excluded_refresh_tables
:在此项属性中列出的基表,其数据产生变化时不会触发该表的数据刷新到物化视图。通常需要与属性excluded_trigger_tables
搭配使用。形式:[db_name.]table_name
。默认值为空字符串。当值为空字符串时,任意的基表数据变化都将触发对应物化视图刷新。注意
Unique Key 约束和外键约束仅用于查询改写。导入数据时,不保证进行外键约束校验。您必须确保导入的数据满足约束条件。
-
resource_group
: 为物化视图刷新任务设置资源组。默认值为default_mv_wg
,即一个系统定义的,专门用于物化视图刷新的资源组。该资源组的cpu_core_limit
为1
,mem_limit
为0.8
。更多关于资源组信息,请参考资源隔离。 -
query_rewrite_consistency
: 指定当前异步物化视图的查询改写规则。该属性自 v3.2 起支持。有效值:disable
:禁用基于该异步物化视图进行自动查询改写。checked
(默认值):仅在物化视图满足时效性要求时启用自动查询改写,即:- 如果未指定
mv_rewrite_staleness_second
,则只有当物化视图的数据与所有基表中的数据一致时,才可以将其用于查询改写。 - 如果指定了
mv_rewrite_staleness_second
,则只有在其最后刷新在 staleness 时间间隔内时,才可以将物化视图用于查询改写。
- 如果未指定
loose
:直接启用自动查询改写,无需进行一致性检查。
-
storage_volume
:如果您使用存算分离集群,则需要指定创建物化视图的 Storage Volume 名称。该属性自 v3.1 版本起支持。如果未指定该属性,则使用默认 Storage Volume。示例:"storage_volume" = "def_volume"
。 -
force_external_table_query_rewrite
: 是否启用基于 External Catalog 的物化视图的查询改写。该属性自 v3.2 起支持。有效值:true
(自 v3.3 变为默认值):启用基于 External Catalog 的物化视图的查询改写。false
:禁用基于 External Catalog 的物化视图的查询改写。
由于无法保证基表和基于 External Catalog 的物化视图之间的数据强一致,因此默认情况下禁用此功能。启用此功能时,物化视图将根据
query_rewrite_consistency
中指定的规则改写查询。 -
enable_query_rewrite
:是否使用物化视图进行查询改写。当存在大量物化视图时,基于物化视图的查询改写可能会影响优化器的耗时。通过此属性,您可以控制是否允许使用物化视图进行查询改写。该功能自 v3.3.0 起支持。有效值:default
(默认):系统将不会针对物化视图执行语义检查,但只有 SPJG 类型的物化视图可以用于查询改写。请注意,如果启用了基于文本的查询改写,非 SPJG 类型的物化视图也可以用于查询改写。true
:系统将在创建或修改物化视图时执行语义检查。如果物化视图不符合查询改写的条件(即,物化视图的定义不是 SPJG 类型的查询),则会返回失败信息。false
:物化视图将不会用于查询改写。
-
[Preview]
transparent_mv_rewrite_mode
:为 直接针对物化视图的查询 指定透明改写模式。此功能从 v3.3.0 版本开始支持。有效值如下:false
(默认,与早期版本行为兼容):直接针对物化视图的查询不会被改写,仅返回物化视图中现有的数据。根据物化视图的刷新状态(数据一致性),其结果可能与直接执行物化视图定义查询的结果不同。true
:直接针对物化视图的查询将被改写,并返回最新数据,结果与物化视图定义查询的一致。请注意,当物化视图处于失效(Inactive)状态或不支持透明查询改写时,这些查询将路由至物化视图定义查询执行。transparent_or_error
:直接针对物化视图的查询将在符合条件时可以被改写。如果物化视图处于失效(Inactive)状态或不支持透明查询改写,将返回错误。transparent_or_default
:直接针对物化视图的查询将在符合条件时可以被改写。如果物化视图处于失效(Inactive)状态或不支持透明查询改写,将返回物化视图中现有的数据。
query_statement(必填)
创建异步物化视图的查询语句,其结果即为异步物化视图中的数据。从 v3.1.6 版本开始,StarRocks 支持使用 Common Table Expression (CTE) 创建异步物化视图。
查询异步物化视图
异步物化视图本质是一张实体表。您可以将其作为普通表进行任何除直接导入数据以外的操作。
支持数据类型
-
基于 StarRocks 内部数据目录(Default Catalog)创建的异步物化视图支持以下数据类型:
- 日期类型:DATE、DATETIME
- 字符串类型:CHAR、VARCHAR
- 数值类型:BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、DECIMAL、PERCENTILE
- 半结构化类型:ARRAY、JSON、MAP(自 v3.1 起)、STRUCT(自 v3.1 起)
- 其他类型:BITMAP、HLL
说明
自 v2.4.5 起支持 BITMAP、HLL 以及 PERCENTILE。
-
基于 StarRocks 外部数据目录(External Catalog)创建的异步物化视图支持以下数据类型:
-
Hive Catalog
- 数值类型:INT/INTEGER、BIGINT、DOUBLE、FLOAT、DECIMAL
- 日期类型:TIMESTAMP
- 字符串类型:STRING、VARCHAR、CHAR
- 半结构化类型:ARRAY
-
Hudi Catalog
- 数值类型:BOOLEAN、INT、LONG、FLOAT、DOUBLE、DECIMAL
- 日期类型:DATE、TimeMillis/TimeMicros、TimestampMillis/TimestampMicros
- 字符串类型:STRING
- 半结构化类型:ARRAY
-
Iceberg Catalog
- 数值类型:BOOLEAN、INT、LONG、FLOAT、DOUBLE、DECIMAL(P, S)
- 日期类型:DATE、TIME、TIMESTAMP
- 字符串类型:STRING、UUID、FIXED(L)、BINARY
- 半结构化类型:LIST
-
注意事项
-
当前版本暂时不支持同时创建多个物化视图。仅当当前创建任务完成时,方可执行下一个创建任务。
-
关于同步物化视图:
- 同步物化视图仅支持单列聚合函数,不支持形如
sum(a+b)
的查询语句。 - 同步物化视图仅支持对同一列数据使用一种聚合函数,不支持形如
select sum(a), min(a) from table
的查询语句。 - 同步物化视图中使用聚合函数需要与 GROUP BY 语句一起使用,且 SELECT 的列中至少包含一个分组列。
- 同步物化视图创建语句不支持 JOIN 以及 GROUP BY 的 HAVING 子句。
- 使用 ALTER TABLE DROP COLUMN 删除基表中特定列时,需要保证该基表所有同步物化视图中不包含被删除列,否则无法进行删除操作。如果必须删除该列,则需要将所有包含该列的同步物化视图删除,然后进行删除列操作。
- 为一张表创建过多的同步物化视图会影响 导入的效率。导入数据时,同步物化视图和基表数据将同步更新,如果一张基表包含 n 个物化视图,向基表导入数据时,其导入效率大约等同于导入 n 张表,数据导入的速度会变慢。
- 同步物化视图仅支持单列聚合函数,不支持形如
-
关于嵌套异步物化视图:
- 每个异步物化视图的刷新方式仅影响当前物化视图。
- 当前 StarRocks 不对嵌套层数进行限制。生产环境中建议嵌套层数不超过三层。
-
关于外部数据目录异步物化视图:
- 外部数据目录物化视图仅支持异步定时刷新和手动刷新。
- 物化视图中的数据不保证与外部数据目录的数据强一致。
- 目前暂不支持基于资源(Resource)构建物化视图。
- StarRocks 目前无法感知外部数据目录基表数据是否发生变动,所以每次刷新会默认刷新所有分区。您可以通过手动刷新方式指定刷新部分分区。
示例
同步物化视图示例
基表结构为:
mysql> desc duplicate_table;
+-------+--------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+------+---------+-------+
| k1 | INT | Yes | true | N/A | |
| k2 | INT | Yes | true | N/A | |
| k3 | BIGINT | Yes | true | N/A | |
| k4 | BIGINT | Yes | true | N/A | |
+-------+--------+------+------+---------+-------+
-
创建一个仅包含原始表 (k1,k2)列的物化视图。
create materialized view k1_k2 as
select k1, k2 from duplicate_table;物化视图的 schema 如下图,物化视图仅包含两列 k1、k2 且不带任何聚合。
+-----------------+-------+--------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------------+-------+--------+------+------+---------+-------+
| k1_k2 | k1 | INT | Yes | true | N/A | |
| | k2 | INT | Yes | true | N/A | |
+-----------------+-------+--------+------+------+---------+-------+ -
创建一个以 k2 为排序列的物化视图。
create materialized view k2_order as
select k2, k1 from duplicate_table order by k2;