语法参考

基于SQL查询创建物化视图：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
[(<col_name> [COMMENT <col_comment>], ...)]
[COMMENT '...']
[PARTITIONED BY col1, col2, ...]
[ CLUSTERED BY (col_name3, col_name4, ...)
[ SORTED BY (col_name [ ASC | DESC], ...)]
INTO num_buckets BUCKETS]
[TBLPROPERTIES ('key'='value', ...)]
AS <SELECT query>;

样例：

CREATE MATERIALIZED VIEW mv_partitioned
PARTITIONED BY (region, product)
AS SELECT region, product, SUM(amount) AS total
FROM sales GROUP BY region, product

CREATE MATERIALIZED VIEW mv_sorted_bucketed
CLUSTERED BY (id) SORTED BY (amount ASC) INTO 2 BUCKETS
AS SELECT * FROM sales

CREATE MATERIALIZED VIEW mv_sales_with_product AS
SELECT s.region, p.product_name, SUM(s.amount) AS total_amount
FROM sales s
INNER JOIN products p ON s.product_id = p.product_id
GROUP BY s.region, p.product_name

DROP MATERIALIZED VIEW [IF EXISTS] mv_revenue_per_customer;

样例：

DROP MATERIALIZED VIEW mv_sales

查询当前数据库中的所有物化视图

SHOW MATERIALIZED VIEWS;

查询指定数据库中的所有物化视图

SHOW MATERIALIZED VIEWS FROM example_db;

查询符合特定模式的物化视图

SHOW MATERIALIZED VIEWS LIKE 'mv_*';

从基表中重新计算物化视图的数据。

REFRESH MATERIALIZED VIEW mv_revenue_per_customer;

ALTER MATERIALIZED VIEW mv_name SET TBLPROPERTIES ('key1' = 'val1');

ALTER MATERIALIZED VIEW mv_name UNSET TBLPROPERTIES ('key1' = 'val1');

下列属性无法用Alter命令修改：

• mv.archived：视图是否归档
• mv.base.table.valid：视图是否可用于重写SQL
• mv.base.tables：物化视图基表
• mv.base.table.names：物化视图
• mv.select.statement.spark：本物化视图在spark引擎中对应的select语句
• mv.base.tables.last.update.time：物化视图基表最近刷新的时间
• mv.last.refresh.time：物化视图最近刷新的时间
• viewQuery：物化视图的查询语句
• spark.mv.type：物化视图类型

下列属性允许使用Alter命令修改

mv.refresh.policy：物化视图刷新策略

可使用下面语法修改刷新策略

ALTER MATERIALIZED VIEW mv_altr ADD SCHEDULE REFRESH EVERY 3 DAYS
ALTER MATERIALIZED VIEW mv_altr ALTER SCHEDULE REFRESH EVERY 2 DAYS
ALTER MATERIALIZED VIEW mv_altr DROP SCHEDULE;