本文介绍如何创建物化视图。

权限要求

  • 需要有database / table级别的CREATE权限。
  • 需要有database / table级别的INSERT权限。
  • 需要有物化视图所涉及的所有表的相关列(或整个表)的SELECT权限。
  • 如果要启用自动刷新功能,需要创建者在 127.0.0.1 上有刷新视图的权限。因为系统会代理创建者身份,从服务器本地(127.0.0.1)登录来执行自动刷新。这样当创建者没有权限之后,系统也无法刷新,避免越权问题。如果用户权限是在所有IP上 ('%'),那么就不会产生这个问题,因为所有IP也包括了 127.0.0.1。

语法

CREATE MATERIALIZED VIEW mv_name
[MV DEFINITION]
[REFRESH [FAST|COMPLETE] [ON <DEMAND|OVERWRITE>] [START WITH date] [NEXT date]]
[<ENABLE|DISABLE> QUERY REWRITE]
AS 
<QUERY BODY> 

关键字说明

MV DEFINITION
该关键字用于定义物化视图的特性。

物化视图使用一张普通的表结构来存储数据,所以可以定义一些表相关的特性,比如指定主键,分区键,索引等,和表定义语法一致。但不能定义查询中没有输出的列。

REFRESH

该关键字用于指定物化视图的刷新模式。

物化视图刷新分为全量刷新(COMPLETE)和增量刷新(FAST)两种刷新模式。如果未指定刷新模式,默认为全量刷新。

说明 目前仅支持默认的全量刷新模式。
全量刷新模式下可以指定以下两种触发方式:
  • ON DEMAND:表示按需触发刷新(默认),即需要的时候用户手动触发刷新或者配置自动刷新。
  • ON OVERWRITE:表示当物化视图引用的基表被INSERT OVERWRITE覆写后触发刷新,适合T+1场景。

自动刷新

配置自动刷新可以使用START WITH和NEXT这两个关键字。START WITH指定的是首次刷新时间,NEXT计算的是下次刷新时间。时间只精确到秒,毫秒部分会被截断。START WITH如果没有指定的话则默认为当前时刻。

要了解更多信息,可参见刷新物化视图

QUERY REWRITE

开启或关闭查询改写功能。

开启后业务查询可被自动改写到物化视图上,物化视图可以被当作缓存使用。

  • DISABLE QUERY REWRITE:在这张物化视图上关闭查询改写(默认)。
  • ENABLE QUERY REWRITE:在这张物化视图上开启查询改写。
说明 查询改写功能目前处于实验性阶段。

要了解更多信息,可参见查询物化视图

QUERY BODY
物化视图的查询主体。
  • 查询中输出的表达式列(如:sum(price))需要定义别名,建议使用具有实际意义的别名(sum(price) as total_price)。
  • 物化视图查询所涉及的基表不可被删除。涉及的基表的列不可被删除,修改。
  • 查询可以使用 WITH 语法,查询主体可以基于逻辑视图或者物化视图。

示例

# 每五分钟刷新一次
CREATE MATERIALIZED VIEW myview
REFRESH NEXT now() + interval 5 minute
AS
SELECT count(*) as cnt FROM base

# 每周一晚上2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
 NEXT DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# 每天晚上夜里2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
 NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# 每个月第一天凌晨2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# 只刷一次
CREATE MATERIALIZED VIEW myview
REFRESH START WITH now() + interval 1 day
AS 
SELECT count(*) as cnt FROM base

# 不自动刷新,完全依靠用户自主手动刷新
CREATE MATERIALIZED VIEW myview (
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name FROM base

# 指定列建立索引,默认全部列建立索引
CREATE MATERIALIZED VIEW myview (
  INDEX (name),
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name, age FROM base

使用限制

  • 无法对物化视图进行INSERT、DELETE、UDPATE操作。
  • 物化视图引用的基表无法被删除或重命名。引用的基表里的列,无法被删除或重命名。如果需要修改基表需要先删除物化视图。

使用建议

  • 创建物化视图时建议和普通表的设计一致。最好指定分区键和主键,后续查询性能会更好。
  • 规格较小的集群,尽量避免所有物化视图在同一时刻刷新,以免影响集群稳定性。
  • 物化视图和普通表一样,默认全列建立索引。为了减少开销,如果不需要全列建立索引,可以单独指定INDEX关键字对特定列建立索引。