StarRocks中关于物化视图实用的语句

最新推荐文章于 2025-03-20 18:06:55 发布

jiujiu645343

最新推荐文章于 2025-03-20 18:06:55 发布

阅读量1.6k

点赞数 18

分类专栏： Starrocks 文章标签： sql 数据库数据仓库

本文链接：https://blog.csdn.net/jiujiu645343/article/details/135518222

版权

Starrocks 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了在StarRocks数据库中创建、修改物化视图（MaterializedView）的方法，包括CREATEMATERIALIZEDVIEW的语法、分区策略、刷新选项以及ALTERMATERIALIZEDVIEW的用法，如重命名、刷新模式调整和状态控制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

官网文档地址：StarRocks @ StarRocks_introhttps://link.zhihu.com/?target=https%3A//docs.starrocks.io/zh-cn/2.2/introduction/StarRocks_intro
StarRocks版本：3.1

一、Create materialized VIEW --创建物化视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]<mv_name>
[COMMENT ""]--- 注释，可选
-- distribution_desc --分桶方式，可选
[DISTRIBUTED BY HASH(<bucket_key>[,<bucket_key2> ...]) [BUCKETS <bucket_number>]]
-- refresh_desc
[REFRESH
-- refresh_moment ，刷新时刻，立即，调度默认IMMEDIATE立即刷新，可选
    [IMMEDIATE | DEFERRED]
-- refresh_scheme，刷新模式，手动，自动
    [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> --sql语句

CREATE TABLE `lineorder` (
`lo_orderkey` 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 ""
) 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`);

CREATE TABLE IF NOT EXISTS `customer` (
`c_custkey` int(11) NOT NULL COMMENT "",
`c_name` varchar(26) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`);

CREATE MATERIALIZED VIEW IF NOT EXISTS `test_mv`
COMMENT "测试视图"
DISTRIBUTED BY HASH(`d_date`, `d_month`, `d_month`) BUCKETS 3
REFRESH IMMEDIATE MANUAL --手动刷新
PARTITION BY (`d_datekey`) -- 基表分区字段
ORDER BY   `d_date`
PROPERTIES("replication_num"="3") --设置副本数为3
AS
SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    c.C_NAME AS C_NAME
   FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY

二、ALTER MATERIALIZED VIEW --修改物化视图

ALTER MATERIALIZED VIEW [db_name.]<mv_name>
    { RENAME [db_name.]<new_mv_name>
    | REFRESH <new_refresh_scheme_desc>
    | ACTIVE | INACTIVE
    | SWAP WITH [db_name.]<mv2_name>
    | SET ( "<key>" = "<value>"[,...]) }

1.重命名物化视图

ALTER MATERIALIZED VIEW lo_mv1 RENAME lo_mv1_new_name;

2.修改物化视图刷新模式及刷新间隔

ALTER MATERIALIZED VIEW lo_mv2 REFRESH ASYNC EVERY(INTERVAL 1 DAY); --更改物化视图刷新间隔，仅支持DAY、HOUR、MINUTE 以及 SECOND

ALTER MATERIALIZED VIEW lo_mv2 REFRESH MANUAL；--更改刷新模式为手动

3.修改物化视图状态为 Active

ALTER MATERIALIZED VIEW order_mv ACTIVE; --当出现 is_active=fasle 使用