# CREATE MATERIALIZED VIEW #### 功能描述 创建新的物化视图。物化视图语法仅8.2.1.220及以上集群支持。 物化视图（Materialized View）是数据库中一种特殊类型的数据库对象。它将复杂计算的查询结果预计算并存储在数据库中，实现加速查询的目的。 适用于频繁对大量数据进行复杂的查询和分析场景，使用物化视图可以预先计算和存储该场景的查询结果，显著提高查询性能。 #### 物化视图权限要求 1. 创建权限：创建物化视图时需要schema的CREATE权限和基表或列的SELECT权限。 2. 查询权限：查询物化视图需要物化视图的SELECT权限。 3. 刷新权限：刷新需要物化视图的INSERT和基表或列的SELECT权限。 4. 其他权限： 物化视图支持ANALYZE/VACUUM/ALTER/DROP等细粒度权限。 物化视图支持with grant option的权限传递操作。 5. 安全管理： 物化视图不支持更高级别的安全控制，如果基表存在行级访问控制、脱敏策略或owner为私有用户等限制SELECT权限的场景，则禁止创建物化视图； 如果已存在物化视图，基表增加RLS、脱敏策略或修改owner为私有用户，则物化视图可以执行查询，但无法刷新。 #### 注意事项 - 使用CREATE MATERIALIZED VIEW语句需确保[enable_matview](https://support.huaweicloud.com/devg-dws/dws_04_0931.html#section3)设置为on。 - 物化视图的基表可以是行存表、列存表、hstore表、冷热表（9.1.0.200及以上集群版本支持）、分区表或者指定某个分区、外表、其他物化视图。 - 不支持包含临时表（包括全局临时表、volatile临时表和普通临时表），不支持对自动分区表指定分区。 - 不支持物化视图定义中的CTE包含除SELECT语句以外的其他DML。 - 不支持基表上存在脱敏策略和行级访问控制或者基表属于私有用户。脱敏策略和行级访问控制可能会导致结果集错误，而私有用户可能导致刷新物化视图失败等问题。 - 物化视图不支持的存储类型：外表、时序表。 - 物化视图禁止INSERT/UPDATE/MERGE INTO/DELETE对数据进行修改。 - 物化视图执行一次将结果并保存，每次查询结果是一致的。BUILD IMMEDIATE或REFRESH后，物化视图可以查询出正确结果。 - 物化视图不能通过语法指定Node Group。支持物化视图的基表指定Node Group创建，物化视图会继承基表NodeGroup信息创建，需要多个基表的NodeGroup相同。 #### 语法格式 ``` CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ] [ BUILD { DEFERRED | IMMEDIATE } ] [ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] [ { ENABLE | DISABLE } QUERY REWRITE ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ] [ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ] AS query; ``` #### 参数说明 表1CREATE MATERIALIZED VIEW参数说明 | 参数 | 描述 | 取值范围/默认值 | |:---|:---|:---| | BUILD DEFERRED \| IMMEDIATE | IMMEDIATE，表示创建物化视图时即包含最新数据。 DEFERRED，表示创建物化视图时需要等到第一次refresh时才会包含数据。 | - | | REFRESH | 指定物化视图的刷新方式。 创建物化视图后，物化视图中的数据只反映创建时刻基表的状态。当基表中的数据发生变化时，需要通过刷新物化视图（REFRESH MATERIALIZED VIEW）更新物化视图中的数据。 | - 刷新方式：DEFAULT默认刷新，RESTRICT 强制刷新。仅创建后台异步刷新的物化视图可以指定。 - 刷新方向：级联嵌套使用物化视图时的刷新方向，FORWARD向上刷新，BACKWARD向下刷新，ALL 全部都刷。仅创建后台异步刷新的物化视图可以指定。具体参考[REFRESH MATERIALIZED VIEW](https://support.huaweicloud.com/sqlreference-dws/dws_06_0361.html)。 - 目前只支持COMPLETE全量刷新这一种刷新方式。执行物化视图定义的查询语句并更新物化视图。 - 刷新触发方式。 ON DEMAND：手动按需刷新。 START WITH (timestamptz) \| EVERY (interval)：定时自动刷新。START WITH指定首次刷新时间，EVERY 指定刷新间隔，根据指定的时间定时自动刷新，支持MONTH、DAY、HOUR、MINUTE及SECOND。 | | ENABLE \| DISABLE QUERY REWRITE | 是否开启查询重写。 查询重写是指在对基表进行查询时， 如果基表上创建有物化视图，数据库系统自动判断是否可以使用物化视图中的预计算结果处理查询。 如果可以使用某个物化视图，会直接从该物化视图读取预计算结果，起到加速查询的作用。 | 默认关闭。 指定ENABLE QUERY REWRITE时，需要设置GUC参数[mv_rewrite_rule](https://support.huaweicloud.com/devg-dws/dws_04_0936.html#section40)才能启用物化视图查询重写功能。 | | WITH | 创建物化视图指定的相关参数。 | 参见[表2]。 | | DISTRIBUTE BY | 指定表如何在节点之间分布或者复制。 | REPLICATION、ROUNDROBIN或HASH： - REPLICATION：表的每一行存储在所有数据节点（DN）中，即每个数据节点都有完整的表数据。 - ROUNDROBIN：表的每一行被轮番地发送给各个DN，因此数据会被均匀地分布在各个DN中。（ROUNDROBIN仅8.1.2及以上版本支持） - HASH：对指定的列进行Hash，通过映射，把数据分布到指定DN。 默认值：由参数default_distribution_mode决定。 说明： 当物化视图指定为hash分布时，可能存在数据倾斜。可使用普通表检查数据是否倾斜的方法对物化视图进行检查，详细内容请参考《数据仓库服务开发指南》的"查看数据倾斜状态"章节。若物化视图的数据存储存在倾斜时，可参考《数据仓库服务开发指南》的"数据倾斜调优"章节进行存储层的倾斜调优。 | | PARTITION BY | 创建分区物化视图语法仅9.1.0.200及以上集群版本支持。 注意： - 物化视图的分区键，必须是被查询表中某个分区表的分区键，用于建立按分区失效和刷新的对应关系。 - 物化视图的分区键，必须包含在查询的最外层SQL中，以便将查询数据按分区键刷入物化视图表。 - 物化视图只支持range分区。 - 被查询表可以是组合分区，如果被查询表有多个分区表且分区条件都一样，都会与物化视图自动建立映射关系。 - 不支持外表的分区映射，因为无法感知外表的数据变化。 - 不支持Outer join中会产生空值的列做分区键的情况。 - 不支持基表分区的merge和split。 | - 等比例关联，指定查询中某个分区表的分区键作为物化视图的分区键，并建立一比一的映射关系。 - 上卷式关联，通过date_trunc函数对查询中某个分区表的分区键进行上卷，并建立上卷式的映射关系。要求数据类型必须是date_trunc函数可接受的日期类型。 | | AS query | 基于query的结果创建物化视图。 | - | 表2WITH参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | ORIENTATION | 指定表数据的存储方式，即行存方式、列存方式，该参数设置成功后就不再支持修改。 | ROW或COLUMN： - ROW，表示表的数据将以行式存储。 行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。 - COLUMN，表示表的数据将以列式存储。 列存储适合于数据仓库业务，此类型的表上会做大量的汇聚计算，且涉及的列操作较少。 默认值：ROW | | enable_foreign_table_query_rewrite | 指定是否允许包含外表的物化视图进行查询重写，需要与ENABLE QUERY REWRITE一起使用。 外表数据有变化，物化视图无法感知。如果需要对包含外表的物化视图使用查询重写功能，需要指定此选项。 | - on，允许包含外表的物化视图进行查询重写。 - off，不允许包含外表的物化视图进行查询重写。 默认值：off | | bitmap_columns | bitmap index只适用于hstore_opt表，只有开启表级参数enable_hstore_opt且开启bitmap_columns='指定列'。该参数仅9.1.0.200及以上集群版本支持。 | - | | secondary_part_num | 指定列存表二级分区的数量，仅适用于HStore列存表。该参数仅9.1.0.200及以上集群版本支持。 | 1\~32。 默认值：8 | | enable_hstore_opt | enable_hstore_opt表级参数打开时会默认同时打开enable_hstore表级参数。该参数仅9.1.0.200及以上集群版本支持。 | true或者false。 默认值：false | | enable_turbo_store | 用于控制是否创建为turbo表（基于列存表实现）。该参数只对列存表有效。该参数仅9.1.0.200及以上集群版本支持。 | on或者off。 默认值：off | | mv_analyze_mode | 控制物化视图自动analyze的方式。该参数仅9.1.0.200及以上集群版本支持。 | - none，表示物化视图在刷新后不自动执行analyze。 - light，表示物化视图在刷新后执行light analyze。 默认值：light | | mv_pck_column | 物化视图的局部聚簇存储，列存表导入数据时按照指定的列（单列或多列），进行局部排序。该参数仅9.1.0.200及以上集群版本支持。 开启mv_pck_column='指定列'。 | - | | mv_support_function_type | 开启物化视图创建时查询语句中可以使用的函数属性。该参数仅9.1.0.200及以上集群版本支持。 | - stable，表示支持在查询语句中使用类型为STABLE和IMMUTABLE的函数。 - volatile，表示支持在查询语句中使用类型为VOLATILE，STABLE和IMMUTABLE的函数。 默认值：空 | | excluded_inactive_tables | 指定的基表发生数据变化不会失效该物化视图。该参数仅9.1.0.200及以上集群版本支持。 设置方式为：excluded_inactive_tables='schemaName1.tableName1,schemaName2.tableName2' | 默认值：空 | | force_rewrite_timeout | 在刷新后的指定时间间隔内，可以查询重写，无论此物化视图数据是否新鲜。该参数仅9.1.0.200及以上集群版本支持。 | 单位为秒，默认值：0。 | | TABLESPACE tablespace_name | 声明一个表空间。用于V3存储格式的创建，如果default_tablespace为空，将使用数据库的缺省表空间。该参数仅9.1.0.200及以上集群版本支持。 | - | #### 示例 开启物化视图功能。 ``` set enable_matview = on; ``` 创建基表，并向基表插入数据。 ``` DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a int, b int) DISTRIBUTE BY HASH(a); INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x; ``` 创建默认BUILD IMMEDIATE方式的物化视图： ``` CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t1; ``` 创建指定列存方式的物化视图。 ``` CREATE MATERIALIZED VIEW mv2 WITH(orientation = column) AS SELECT * FROM t1; ``` 创建手动按需刷新的物化视图。 ``` CREATE MATERIALIZED VIEW mv3 BUILD DEFERRED REFRESH ON DEMAND AS SELECT * FROM t1; ``` 创建指定刷新时间的物化视图。 ``` CREATE MATERIALIZED VIEW mv4 BUILD DEFERRED REFRESH START WITH(trunc(sysdate)) EVERY (interval '1 day') AS SELECT * FROM t1; ``` 创建带有bitmap index的物化视图（该用例仅实时数仓支持，详情参见[实时数仓](https://support.huaweicloud.com/devg-dws/dws_04_1024.html)）。 ``` DROP TABLE IF EXISTS base_table; CREATE TABLE base_table (col1 varchar(50), col2 int , col3 int) WITH (ORIENTATION=COLUMN); CREATE MATERIALIZED VIEW mv1 with (ORIENTATION = COLUMN, enable_hstore=true, enable_hstore_opt=on, bitmap_columns='col1') AS SELECT * FROM base_table; ``` 创建物化视图并指定列存表二级分区的数量（该用例仅实时数仓支持，详情参见[实时数仓](https://support.huaweicloud.com/devg-dws/dws_04_1024.html)）。 ``` CREATE MATERIALIZED VIEW mv WITH (ORIENTATION=COLUMN, ENABLE_HSTORE=ON, enable_hstore_opt=on, mv_pck_column='col3', secondary_part_column = 'col2', secondary_part_num = 8) AS SELECT * FROM base_table; ``` 创建物化视图并指定pck列进行排序（该用例仅实时数仓支持，详情参见[实时数仓](https://support.huaweicloud.com/devg-dws/dws_04_1024.html)）。 ``` CREATE MATERIALIZED VIEW mv2 WITH (ORIENTATION=COLUMN, ENABLE_HSTORE=ON, enable_hstore_opt=on, mv_pck_column='col3') AS SELECT * FROM base_table; ``` 创建物化视图并指定其analyze的方式。 ``` CREATE MATERIALIZED VIEW mv1 enable query rewrite with(excluded_inactive_tables='matview_basic."T1",matview_basic."a=b"',mv_analyze_mode='none') as SELECT * FROM base_table; ``` 创建V3物化视图： ``` DROP TABLE IF EXISTS dicttbl_low; CREATE TABLE dicttbl_low (col1 int, col2 int ) WITH (ORIENTATION=COLUMN); CREATE MATERIALIZED VIEW mv1 with (orientation=column, enable_hstore=true, compression=low, enable_hstore_opt=on, COLVERSION = 3.0) TABLESPACE cu_obs_tbs distribute by hash(col1) AS SELECT * FROM dicttbl_low; ``` 创建包含外表的物化视图进行查询重写。**注意**，该示例的base_table_ft为外表。 ``` CREATE MATERIALIZED VIEW mv1 with (enable_foreign_table_query_rewrite = true) as SELECT * FROM base_table_ft; ``` 创建物化视图指定查询语句中可以使用volatile函数。 ``` DROP TABLE IF EXISTS t_date; CREATE TABLE t_date (a int, b int ) WITH (ORIENTATION=COLUMN); CREATE MATERIALIZED VIEW mv_date with(mv_support_function_type = 'volatile') as select to_date(a) from t_date; ``` #### 相关链接 [ALTER MATERIALIZED VIEW](https://support.huaweicloud.com/sqlreference-dws/dws_06_0358.html)，[DROP MATERIALIZED VIEW](https://support.huaweicloud.com/sqlreference-dws/dws_06_0360.html)，[REFRESH MATERIALIZED VIEW](https://support.huaweicloud.com/sqlreference-dws/dws_06_0361.html)