CREATE MATERIALIZED VIEW

语法

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name [ COMMENT string ] [ WITH properties ] AS query

描述

该语法是使用SELECT查询结果创建物化视图。物化视图是一个数据库对象，它包含了一个查询的结果，例如：它可以是远程数据的本地副本，单表查询或者多表join后查询的结果的行或列、行和列的子集，也可以是使用聚合函数的汇总表。

物化视图通常基于对数据表进行聚合和连接的查询结果创建。物化视图支持“查询重写”，这是一种优化技术，它将以原始表编写的用户查询转换为包括一个或多个物化视图的等效请求。

语法支持的属性包括：

• storage_table：指定存储表表名。
• need_auto_refresh： 管理计算实例时，预先创建维护实例后，可通过设置need_auto_refresh为true，创建具备自动刷新能力的物化视图，它会自动创建并提交物化视图刷新任务，在此基础上，可对refresh_duration，start_refresh_ahead_of_expiry,refresh_priority等属性做进一步配置来调整自动刷新任务。
• mv_validity：物化视图生命周期。0表示永久有效，最短为1分钟。need_auto_refresh设置为false时，mv_validity默认值为0；设置为true时，默认值为24小时。
• refresh_duration：物化视图自动刷新任务的最长等待时间。默认为5分钟，取值范围为1分钟到24小时。若自动刷新任务的等待时间超过设定的最长等待时间，自动化任务界面对应的任务状态显示为"timeout"。
• start_refresh_ahead_of_expiry：基于mv _validity设置物化视图自动刷新任务的提交时间，表示达到物化生命周期的指定百分比时，提交自动刷新任务，默认值为0.2，最小值为0.05。
• refresh_priority：物化视图提交自动刷新任务的优先级。默认值为3，最大值为3，1表示最高优先级。高优先级的任务会有更大机会先被执行。

示例

• 在mv catalog和数据存储的catalog（示例中使用的数据存储的catalog为Hive）中创建相同的schema，并启用物化视图“查询重写”。

  hetuengine:tpcds_2gb> set session materialized_view_rewrite_enabled=true;
  hetuengine:tpcds_2gb> create schema mv.tpcds;
  CREATE SCHEMA
  hetuengine:tpcds_2gb> create schema hive.tpcds;
  CREATE SCHEMA

• 创建表。

  hetuengine:tpcds_2gb> create table t1 (id int, c1 varchar); 
  hetuengine:tpcds_2gb> Insert into t1 values (1,'abc'), (2,'abc2'), (3,'abc3'), (4,'abc4'), (5,'abc5'),(6, 'abc6');
  hetuengine:tpcds_2gb> create table tb_a(a int ,b varchar, c varchar);
  hetuengine:tpcds_2gb> create table tb_b(a int ,d varchar, e varchar);

• 在mv catalog的tpcds schema中创建名为“mv.tpcds.test”的视图。如果已存在具有此名称的物化视图，则将抛出错误信息。

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test as select c1 from t1 where id <7;
  CREATE MATERIALIZED VIEW

• 在mv catalog和tpcds schema中创建具有指定列名的物化视图“mv.tpcds.test”。

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test (a ,b) as select c1, id from t1 where id<7;
  CREATE MATERIALIZED VIEW

• 在mv catalog和tpcds schema中使用“if not exists”关键字创建物化视图。如果视图已存在，不会抛出错误信息。

  hetuengine:tpcds_2gb> create materialized view if not exists mv.tpcds.test as select c1, id from t1 where id<7;
  CREATE MATERIALIZED VIEW

• 在mv catalog和tpcds schema中创建具有指定属性的物化视图。

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test with (storage_table='mppdb.tpcds.test2',need_auto_refresh = true, mv_validity = '10m', start_refresh_ahead_of_expiry = 0.2, refresh_priority = 1, refresh_duration = '5m') as select c1, id from t1 where id<7;
  CREATE MATERIALIZED VIEW

• 创建带有注释的物化视图。

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test comment 'test_comment' as select c1, id from t1 where id<7;
  CREATE MATERIALIZED VIEW

注意事项

• 创建物化视图时，mv catalog应存在。
• 创建物化视图之后, 需要使用refresh materialized view xxx来填充物化视图的数据。
• 需要在System或者Session级别开启物化视图重写功能。
• 用于在mv catalog中创建视图的schema，需要在用于数据存储的catalog和mv catalog中提前创建好。
• 不要删除用于存储的catalog中存在的物化视图数据表。
• 创建物化视图时，建议查询中不要包含Order By。
• 创建物化视图时，查询语句不要包含子查询和子查询join，若包含子查询和子查询join需使用with子查询代替。

  例如：

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test1 as select t1.a, b, d from ((select a, b, c from tb_a) as t1 join (select a, d, e from tb_b) as t2 on t1.a=t2.a);

  上述场景可用with语句代替：

  hetuengine:tpcds_2gb> 
  create materialized view mv.tpcds.test1 as
  with t1 as (select a, b, c from tb_a),
  t2 as (select a, d, e from tb_b)select t1.a, b, d from t1 join t2 on t1.a = t2.a;

• 不支持查询部分物化视图的重写，这意味着当查询或子查询需要视图中的部分数据（物化视图的子集数据）时，查询将无法被转换为包含物化视图的等效请求。

  如使用“select id from test where id <100”创建物化视图t1，若用户需要查询“select id from test where id <50”，则不会发生重写，因为查询试图使用物化视图的部分数据。

• 创建物化视图时表名必须是全限定名（catalogName.schemaName.tableName）或者表名。

  例如：

  hetuengine:tpcds_2gb> create materialized view mv.tpcds.test as select c1 from t1 where id <7;

  其中表名“t1”也可替换为全限定名“hive.tpcds_2gb.t1”，但不能为“tpcds_2gb.t1”。

• 物化视图的“查询重写”不支持全表扫描，SQL查询没有使用Where子句，无法被查询重写。

  例如：表“hivetb1”的列定义包含了“id”、“name”、“age”三个列，如下SQL查询就无法被“查询重写”。

  Create MV SQL : select id,name,age from hivetb1;