创建物化视图。创建物化视图是一个异步的操作。CREATE MATERIALIZED VIEW 命令执行成功即代表创建物化视图的任务提交成功。您可以通过 SHOW ALTER 命令查看当前数据库中物化视图的构建状态。关于物化视图适用的场景请参考 物化视图。
注意
只有拥有基表所在数据库
CREATE_PRIV权限的用户才可以创建物化视图。
StarRocks 自 v2.4 起支持多表物化视图。多表物化视图与先前版本中的单表物化视图区别主要体现在以下方面：
| 支持异步及手动刷新 | 支持聚合列 | 支持修改分区分片 | JOIN、WHERE、GROUP BY 子句 | |
|---|---|---|---|---|
| 单表物化视图 | 否（v2.3 及之前版本物化视图仅支持同步刷新方式） | 否 | 否 | 否 |
| 多表物化视图 | 是 | 是 | 是 | 是 |
StarRocks 2.5 版本中，多表异步刷新物化视图支持查询改写、嵌套物化视图以及基于 Hive catalog、Hudi catalog 以及 Iceberg catalog 构建物化视图。
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]mv_name
[distribution_desc]
[REFRESH refresh_scheme_desc]
[partition_expression]
[COMMENT ""]
[PROPERTIES ("key"="value", ...)]
AS (query);mv_name（必填）
物化视图的名称。命名要求如下：
- 必须由字母（a-z 或 A-Z）、数字（0-9）或下划线（_）组成，且只能以字母开头。
- 总长度不能超过 64 个字符。
注意
同一张基表可以创建多个物化视图，但同一数据库内的物化视图名称不可重复。
query（必填）
创建物化视图的查询语句，其结果即为物化视图中的数据。语法如下：
SELECT select_expr[, select_expr ...]
[GROUP BY column_name[, column_name ...]]
[ORDER BY column_name[, column_name ...]]-
select_expr（必填）
查询语句中所有的列，也即物化视图的 schema 中所有的列。该参数支持如下值：
- 单列或聚合列：形如
SELECT a, b, c FROM table_a（适用于创建单表物化视图）或SELECT table_a.a, table_a.b, table_b.d,（仅适用于 2.4 版本以上创建多表物化视图），其中a、b、c和d为基表的列名。如果您没有为物化视图指定列名，那么物化视图的列名也为a、b、c和d。 - 表达式：形如
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（选填）
物化视图的分组列。如不指定该参数，则默认不对数据进行分组。
-
ORDER BY（选填）
物化视图的排序列。
- 排序列的声明顺序必须和
select_expr中列声明顺序一致。 - 如果不指定排序列，则系统根据规则自动补充排序列。 如果物化视图是聚合类型，则所有的分组列自动补充为排序列。 如果物化视图是非聚合类型，则前 36 个字节自动补充为排序列。如果自动补充的排序个数小于 3 个，则前三个作为排序列。
- 如果查询语句中包含分组列，则排序列必须和分组列一致。
- 排序列的声明顺序必须和
distribution_desc（建立异步多表物化视图时为必填）
物化视图的分桶方式，形如 DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]。
refresh_scheme_desc（选填）
物化视图的刷新方式。该参数支持如下值：
ASYNC：异步的刷新方式。如需设置为定时刷新，您需要指定刷新开始时间和刷新间隔START('yyyy-MM-dd hh:mm:ss') EVERY (interval n day/hour/minute/second)。刷新间隔仅支持：DAY、HOUR、MINUTE以及SECOND。如不指定刷新间隔，物化视图将采用导入触发刷新方式。MANUAL：手动的刷新方式。
如果不指定该参数，则默认使用 MANUAL 方式。
partition_expression（选填）
物化视图的分区表达式。目前仅支持在创建物化视图时使用一个分区表达式。该参数支持如下值：
- 列：形如
PARTITION BY dt，表示按照dt列进行分区。 - date_trunc 函数：形如 PARTITION BY date_trunc("MONTH", 'dt')，表示将
dt列截断至以月为单位进行分区。date_trunc 函数支持截断的单位包括YEAR、MONTH、DAY、HOUR以及MINUTE。 - 如不指定该参数，则默认物化视图为无分区。
COMMENT（选填）
物化视图的注释。
PROPERTIES（选填）
物化视图的属性。
short_key：排序列的个数。timeout：构建物化视图的超时时间。单位为秒。replication_num：创建物化视图副本数量。storage_medium：存储介质类型。partition_ttl_number：需要保留的最近的物化视图分区数量。分区数量超过该值后，过期分区将被删除。StarRocks 将根据 FE 配置项dynamic_partition_enable中的时间间隔定期检查物化视图分区，并自动删除过期分区。默认值：-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时，将刷新所有分区。
使用物化视图搜索时，原始查询语句将会被自动改写并用于查询物化视图中保存的中间结果。下表展示了原始查询聚合函数和构建物化视图用到的聚合函数的匹配关系。您可以根据您的业务场景选择对应的聚合函数构建物化视图。
| 原始查询聚合函数 | 物化视图构建聚合函数 |
|---|---|
| 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 |
-
StarRocks 2.4 之前版本的物化视图仅支持单列聚合函数，不支持形如
sum(a+b)的查询语句。 -
StarRocks 2.4 之前版本的物化视图创建语句不支持 JOIN、WHERE、GROUP BY 等子句。
-
当前版本暂时不支持同时创建多个物化视图。仅当当前创建任务完成时，方可执行下一个创建任务。
-
一个物化视图仅支持对同一列数据使用一种聚合函数，不支持形如
select sum(a), min(a) from table的查询语句。 -
使用 ALTER TABLE DROP COLUMN 删除基表中特定列时，需要保证该基表所有物化视图中都包含被删除列，否则无法进行删除操作。如果必须删除该列，则需要将所有未包含该列的物化视图删除，然后进行删除列操作。
-
为一张表创建过多的物化视图会影响导入的效率。导入数据时，物化视图和基表数据将同步更新，如果一张基表包含 n 个物化视图，向基表导入数据时，其导入效率大约等同于导入 n 张表，数据导入的速度会变慢。
-
关于嵌套物化视图：
- 每个物化视图的刷新方式仅影响当前物化视图。
- 当前 StarRocks 不对嵌套层数进行限制。生产环境中建议嵌套层数不超过三层。
-
关于外部数据目录物化视图：
- 外部数据目录物化视图仅支持异步定时刷新和手动刷新。
- 物化视图中的数据不保证与外部数据目录的数据强一致。
- 目前暂不支持基于资源（Resource）构建物化视图。
- StarRocks 目前无法感知外部数据目录基表数据是否发生变动，所以每次刷新会默认刷新所有分区。您可以通过手动刷新方式指定刷新部分分区。
以下示例基于下列基表。
CREATE TABLE `lineorder` (
`lo_orderkey` int(11) NOT NULL COMMENT "",
`lo_linenumber` int(11) NOT NULL COMMENT "",
`lo_custkey` int(11) NOT NULL COMMENT "",
`lo_partkey` int(11) NOT NULL COMMENT "",
`lo_suppkey` int(11) NOT NULL COMMENT "",
`lo_orderdate` int(11) NOT NULL COMMENT "",
`lo_orderpriority` varchar(16) NOT NULL COMMENT "",
`lo_shippriority` int(11) NOT NULL COMMENT "",
`lo_quantity` int(11) NOT NULL COMMENT "",
`lo_extendedprice` int(11) NOT NULL COMMENT "",
`lo_ordtotalprice` int(11) NOT NULL COMMENT "",
`lo_discount` int(11) NOT NULL COMMENT "",
`lo_revenue` int(11) NOT NULL COMMENT "",
`lo_supplycost` int(11) NOT NULL COMMENT "",
`lo_tax` int(11) NOT NULL COMMENT "",
`lo_commitdate` int(11) NOT NULL COMMENT "",
`lo_shipmode` varchar(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
PARTITION BY RANGE(`lo_orderdate`)
(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
PARTITION p7 VALUES [("19980101"), ("19990101")))
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48;
CREATE TABLE IF NOT EXISTS `customer` (
`c_custkey` int(11) NOT NULL COMMENT "",
`c_name` varchar(26) NOT NULL COMMENT "",
`c_address` varchar(41) NOT NULL COMMENT "",
`c_city` varchar(11) NOT NULL COMMENT "",
`c_nation` varchar(16) NOT NULL COMMENT "",
`c_region` varchar(13) NOT NULL COMMENT "",
`c_phone` varchar(16) NOT NULL COMMENT "",
`c_mktsegment` varchar(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12;
CREATE TABLE IF NOT EXISTS `dates` (
`d_datekey` int(11) NOT NULL COMMENT "",
`d_date` varchar(20) NOT NULL COMMENT "",
`d_dayofweek` varchar(10) NOT NULL COMMENT "",
`d_month` varchar(11) NOT NULL COMMENT "",
`d_year` int(11) NOT NULL COMMENT "",
`d_yearmonthnum` int(11) NOT NULL COMMENT "",
`d_yearmonth` varchar(9) NOT NULL COMMENT "",
`d_daynuminweek` int(11) NOT NULL COMMENT "",
`d_daynuminmonth` int(11) NOT NULL COMMENT "",
`d_daynuminyear` int(11) NOT NULL COMMENT "",
`d_monthnuminyear` int(11) NOT NULL COMMENT "",
`d_weeknuminyear` int(11) NOT NULL COMMENT "",
`d_sellingseason` varchar(14) NOT NULL COMMENT "",
`d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
`d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
`d_holidayfl` int(11) NOT NULL COMMENT "",
`d_weekdayfl` int(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`d_datekey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1;
CREATE TABLE IF NOT EXISTS `supplier` (
`s_suppkey` int(11) NOT NULL COMMENT "",
`s_name` varchar(26) NOT NULL COMMENT "",
`s_address` varchar(26) NOT NULL COMMENT "",
`s_city` varchar(11) NOT NULL COMMENT "",
`s_nation` varchar(16) NOT NULL COMMENT "",
`s_region` varchar(13) NOT NULL COMMENT "",
`s_phone` varchar(16) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`s_suppkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12;
CREATE TABLE IF NOT EXISTS `part` (
`p_partkey` int(11) NOT NULL COMMENT "",
`p_name` varchar(23) NOT NULL COMMENT "",
`p_mfgr` varchar(7) NOT NULL COMMENT "",
`p_category` varchar(8) NOT NULL COMMENT "",
`p_brand` varchar(10) NOT NULL COMMENT "",
`p_color` varchar(12) NOT NULL COMMENT "",
`p_type` varchar(26) NOT NULL COMMENT "",
`p_size` int(11) NOT NULL COMMENT "",
`p_container` varchar(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`p_partkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12;
create table orders (
dt date NOT NULL,
order_id bigint NOT NULL,
user_id int NOT NULL,
merchant_id int NOT NULL,
good_id int NOT NULL,
good_name string NOT NULL,
price int NOT NULL,
cnt int NOT NULL,
revenue int NOT NULL,
state tinyint NOT NULL
)
PRIMARY KEY (dt, order_id)
PARTITION BY RANGE(`dt`)
( PARTITION p20210820 VALUES [('2021-08-20'), ('2021-08-21')),
PARTITION p20210821 VALUES [('2021-08-21'), ('2021-08-22')) )
DISTRIBUTED BY HASH(order_id) BUCKETS 4
PROPERTIES (
"replication_num" = "1",
"enable_persistent_index" = "true"
);示例一：从源表创建非分区物化视图
CREATE MATERIALIZED VIEW lo_mv1
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 10
REFRESH ASYNC
AS
select
lo_orderkey,
lo_custkey,
sum(lo_quantity) as total_quantity,
sum(lo_revenue) as total_revenue,
count(lo_shipmode) as shipmode_count
from lineorder
group by lo_orderkey, lo_custkey
order by lo_orderkey;示例二：从源表创建分区物化视图
CREATE MATERIALIZED VIEW lo_mv2
PARTITION BY `lo_orderdate`
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 10
REFRESH ASYNC START('2023-07-01 10:00:00') EVERY (interval 1 day)
AS
select
lo_orderkey,
lo_orderdate,
lo_custkey,
sum(lo_quantity) as total_quantity,
sum(lo_revenue) as total_revenue,
count(lo_shipmode) as shipmode_count
from lineorder
group by lo_orderkey, lo_orderdate, lo_custkey
order by lo_orderkey;
# 使用 date_trunc 函数将 `dt` 列截断至以月为单位进行分区。
CREATE MATERIALIZED VIEW order_mv1
PARTITION BY date_trunc('month', `dt`)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 10
REFRESH ASYNC START('2023-07-01 10:00:00') EVERY (interval 1 day)
AS
select
dt,
order_id,
user_id,
sum(cnt) as total_cnt,
sum(revenue) as total_revenue,
count(state) as state_count
from orders
group by dt, order_id, user_id;示例三：创建多表物化视图
CREATE MATERIALIZED VIEW flat_lineorder
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
REFRESH MANUAL
AS
SELECT
l.LO_ORDERKEY AS LO_ORDERKEY,
l.LO_LINENUMBER AS LO_LINENUMBER,
l.LO_CUSTKEY AS LO_CUSTKEY,
l.LO_PARTKEY AS LO_PARTKEY,
l.LO_SUPPKEY AS LO_SUPPKEY,
l.LO_ORDERDATE AS LO_ORDERDATE,
l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
l.LO_QUANTITY AS LO_QUANTITY,
l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
l.LO_DISCOUNT AS LO_DISCOUNT,
l.LO_REVENUE AS LO_REVENUE,
l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
l.LO_TAX AS LO_TAX,
l.LO_COMMITDATE AS LO_COMMITDATE,
l.LO_SHIPMODE AS LO_SHIPMODE,
c.C_NAME AS C_NAME,
c.C_ADDRESS AS C_ADDRESS,
c.C_CITY AS C_CITY,
c.C_NATION AS C_NATION,
c.C_REGION AS C_REGION,
c.C_PHONE AS C_PHONE,
c.C_MKTSEGMENT AS C_MKTSEGMENT,
s.S_NAME AS S_NAME,
s.S_ADDRESS AS S_ADDRESS,
s.S_CITY AS S_CITY,
s.S_NATION AS S_NATION,
s.S_REGION AS S_REGION,
s.S_PHONE AS S_PHONE,
p.P_NAME AS P_NAME,
p.P_MFGR AS P_MFGR,
p.P_CATEGORY AS P_CATEGORY,
p.P_BRAND AS P_BRAND,
p.P_COLOR AS P_COLOR,
p.P_TYPE AS P_TYPE,
p.P_SIZE AS P_SIZE,
p.P_CONTAINER AS P_CONTAINER FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;示例四：创建单表物化视图
基表结构为：
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;
物化视图的 schema 如下图，物化视图仅包含两列 k2、k1，其中 k2 列为排序列，不带任何聚合。
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k2_order | k2 | INT | Yes | true | N/A | | | | k1 | INT | Yes | false | N/A | NONE | +-----------------+-------+--------+------+-------+---------+-------+
-
创建一个以 k1，k2 分组，k3 列为 SUM 聚合的物化视图。
create materialized view k1_k2_sumk3 as select k1, k2, sum(k3) from duplicate_table group by k1, k2;
物化视图的 schema 如下图，物化视图包含两列 k1、k2，sum(k3) 其中 k1、k2 为分组列，sum(k3) 为根据 k1、k2 分组后的 k3 列的求和值。
由于物化视图没有声明排序列，且物化视图带聚合数据，系统默认补充分组列 k1、k2 为排序列。
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k1_k2_sumk3 | k1 | INT | Yes | true | N/A | | | | k2 | INT | Yes | true | N/A | | | | k3 | BIGINT | Yes | false | N/A | SUM | +-----------------+-------+--------+------+-------+---------+-------+
-
创建一个去除重复行的物化视图。
create materialized view deduplicate as select k1, k2, k3, k4 from duplicate_table group by k1, k2, k3, k4;
物化视图 schema 如下图，物化视图包含 k1、k2、k3、k4 列，且不存在重复行。
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | deduplicate | k1 | INT | Yes | true | N/A | | | | k2 | INT | Yes | true | N/A | | | | k3 | BIGINT | Yes | true | N/A | | | | k4 | BIGINT | Yes | true | N/A | | +-----------------+-------+--------+------+-------+---------+-------+
-
创建一个不声明排序列的非聚合型物化视图。
all_type_table 的 schema 如下：
+-------+--------------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-------+---------+-------+ | k1 | TINYINT | Yes | true | N/A | | | k2 | SMALLINT | Yes | true | N/A | | | k3 | INT | Yes | true | N/A | | | k4 | BIGINT | Yes | true | N/A | | | k5 | DECIMAL(9,0) | Yes | true | N/A | | | k6 | DOUBLE | Yes | false | N/A | NONE | | k7 | VARCHAR(20) | Yes | false | N/A | NONE | +-------+--------------+------+-------+---------+-------+
物化视图包含 k3、k4、k5、k6、k7 列，且不声明排序列，则创建语句如下：
create materialized view mv_1 as select k3, k4, k5, k6, k7 from all_type_table;
系统默认补充的排序列为 k3、k4、k5 三列。这三列类型的字节数之和为 4(INT) + 8(BIGINT) + 16(DECIMAL) = 28 < 36。所以补充的是这三列作为排序列。 物化视图的 schema 如下，可以看到其中 k3、k4、k5 列的 key 字段为 true，也就是排序列。k6、k7 列的 key 字段为 false，也就是非排序列。
+----------------+-------+--------------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +----------------+-------+--------------+------+-------+---------+-------+ | mv_1 | k3 | INT | Yes | true | N/A | | | | k4 | BIGINT | Yes | true | N/A | | | | k5 | DECIMAL(9,0) | Yes | true | N/A | | | | k6 | DOUBLE | Yes | false | N/A | NONE | | | k7 | VARCHAR(20) | Yes | false | N/A | NONE | +----------------+-------+--------------+------+-------+---------+-------+
如要创建逻辑视图，请参见 CREATE VIEW。