You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2020/04/01 13:22:25 UTC
[incubator-doris] branch master updated: Sql reference of
materialized view (#3208)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 29b37da Sql reference of materialized view (#3208)
29b37da is described below
commit 29b37dad49012c62b7fd31a8699b163fc6f95eb6
Author: EmmyMiao87 <52...@qq.com>
AuthorDate: Wed Apr 1 21:22:19 2020 +0800
Sql reference of materialized view (#3208)
* Sql reference of materialized view
Sql reference of Create and drop materialized view in English and Chinese.
* Change discription
---
.../alter-table/alter-table-schema-change.md | 10 +-
.../materialized-view/index.rst | 9 +
.../Data Definition/CREATE MATERIALIZED VIEW.md | 230 ++++++++++++++++++++
.../Data Definition/DROP MATERIALIZED VIEW.md | 101 +++++++++
.../Data Definition/CREATE MATERIALIZED VIEW.md | 231 +++++++++++++++++++++
.../Data Definition/DROP MATERIALIZED VIEW.md | 103 +++++++++
.../doris/analysis/CreateMaterializedViewStmt.java | 2 +-
7 files changed, 680 insertions(+), 6 deletions(-)
diff --git a/docs/documentation/cn/administrator-guide/alter-table/alter-table-schema-change.md b/docs/documentation/cn/administrator-guide/alter-table/alter-table-schema-change.md
index 401afea..b4c681c 100644
--- a/docs/documentation/cn/administrator-guide/alter-table/alter-table-schema-change.md
+++ b/docs/documentation/cn/administrator-guide/alter-table/alter-table-schema-change.md
@@ -17,9 +17,9 @@ specific language governing permissions and limitations
under the License.
-->
-# Scheam Change
+# Schema Change
-用户可以通过 Scheam Change 操作来修改已存在表的 Schema。目前 Doris 支持以下几种修改:
+用户可以通过 Schema Change 操作来修改已存在表的 Schema。目前 Doris 支持以下几种修改:
* 增加、删除列
* 修改列类型
@@ -27,7 +27,7 @@ under the License.
* 增加、修改 Bloom Filter
* 增加、删除 bitmap index
-本文档主要介绍如何创建 Scheam Change 作业，以及进行 Scheam Change 的一些注意事项和常见问题。
+本文档主要介绍如何创建 Schema Change 作业，以及进行 Schema Change 的一些注意事项和常见问题。
## 名词解释
@@ -64,9 +64,9 @@ under the License.
## 创建作业
-创建 Scheam Change 的具体语法可以查看帮助 `HELP ALTER TABLE` 中 Scheam Change 部分的说明。
+创建 Schema Change 的具体语法可以查看帮助 `HELP ALTER TABLE` 中 Schema Change 部分的说明。
-Scheam Change 的创建是一个异步过程，作业提交成功后，用户需要通过 `SHOW ALTER TABLE COLUMN` 命令来查看作业进度。
+Schema Change 的创建是一个异步过程，作业提交成功后，用户需要通过 `SHOW ALTER TABLE COLUMN` 命令来查看作业进度。
## 查看作业
diff --git a/docs/documentation/cn/administrator-guide/materialized-view/index.rst b/docs/documentation/cn/administrator-guide/materialized-view/index.rst
new file mode 100644
index 0000000..280d782
--- /dev/null
+++ b/docs/documentation/cn/administrator-guide/materialized-view/index.rst
@@ -0,0 +1,9 @@
+=============
+物化视图
+=============
+
+.. toctree::
+ :maxdepth: 2
+ :glob:
+
+ *
diff --git a/docs/documentation/cn/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md b/docs/documentation/cn/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md
new file mode 100644
index 0000000..bd98402
--- /dev/null
+++ b/docs/documentation/cn/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md
@@ -0,0 +1,230 @@
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# CREATE MATERIALIZED VIEW
+
+## description
+
+ 该语句用于创建物化视图。
+
+说明:
+ 异步语法，调用成功后仅表示创建物化视图的任务提交成功，用户需要先通过 ``` show alter table rollup ``` 来查看物化视图的创建进度。
+ 在显示 FINISHED 后既可通过 ``` desc [table_name] all ``` 命令来查看物化视图的 schema 了。
+
+语法:
+
+ ```
+
+ CREATE MATERIALIZED VIEW [MV name] as [query]
+ [PROPERTIES ("key" = "value")]
+
+ ```
+
+1. MV name
+
+ 物化视图的名称，必填项。
+
+ 相同表的物化视图名称不可重复。
+
+2. query
+
+ 用于构建物化视图的查询语句，查询语句的结果既物化视图的数据。目前支持的 query 格式为:
+
+ ```
+
+ SELECT select_expr[, select_expr ...]
+ FROM [Base view name]
+ GROUP BY column_name[, column_name ...]
+ ORDER BY column_name[, column_name ...]
+
+ 语法和查询语句语法一致。
+
+ ```
+
+ select_expr: 物化视图的 schema 中所有的列。
+ + 仅支持不带表达式计算的单列，聚合列。
+ + 其中聚合函数目前仅支持 SUM, MIN, MAX 三种，且聚合函数的参数只能是不带表达式计算的单列。
+ + 至少包含一个单列。
+ + 所有涉及到的列，均只能出现一次。
+
+ base view name: 物化视图的原始表名，必填项。
+ + 必须是单表，且非子查询
+
+ group by: 物化视图的分组列，选填项。
+ + 不填则数据不进行分组。
+
+ order by: 物化视图的排序列，选填项。
+ + 排序列的声明顺序必须和 select_expr 中列声明顺序一致。
+ + 如果不声明 order by，则根据规则自动补充排序列。
+ 如果物化视图是聚合类型，则所有的分组列自动补充为排序列。
+ 如果物化视图是非聚合类型，则前 36 个字节自动补充为排序列。如果自动补充的排序个数小于3个，则前三个作为排序列。
+ + 如果 query 中包含分组列的话，则排序列必须和分组列一致。
+
+3. properties
+
+ 声明物化视图的一些配置，选填项。
+
+ ```
+
+ PROPERTIES ("key" = "value", "key" = "value" ...)
+
+ ```
+
+ 以下几个配置，均可声明在此处：
+
+ short_key: 排序列的个数。
+ timeout: 物化视图构建的超时时间。
+
+## example
+
+Base 表结构为
+
+```
+mysql> desc duplicate_table;
++-------+--------+------+------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+--------+------+------+---------+-------+
+| k1 | INT | Yes | true | N/A | |
+| k2 | INT | Yes | true | N/A | |
+| k3 | BIGINT | Yes | true | N/A | |
+| k4 | BIGINT | Yes | true | N/A | |
++-------+--------+------+------+---------+-------+
+```
+
+1. 创建一个仅包含原始表 （k1, k2）列的物化视图
+
+ ```
+ create materialized view k1_k2 as
+ select k1, k2 from duplicate_table;
+ ```
+
+ 物化视图的 schema 如下图，物化视图仅包含两列 k1, k2 且不带任何聚合
+
+ ```
+ +-----------------+-------+--------+------+------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+------+---------+-------+
+ | k1_k2 | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ +-----------------+-------+--------+------+------+---------+-------+
+ ```
+
+2. 创建一个以 k2 为排序列的物化视图
+
+ ```
+ create materialized view k2_order as
+ select k2, k1 from duplicate_table order by k2;
+ ```
+
+ 物化视图的 schema 如下图，物化视图仅包含两列 k2, k1，其中 k2 列为排序列，不带任何聚合。
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | k2_order | k2 | INT | Yes | true | N/A | |
+ | | k1 | INT | Yes | false | N/A | NONE |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ ```
+
+3. 创建一个以 k1, k2 分组，k3 列为 SUM 聚合的物化视图
+
+ ```
+ create materialized view k1_k2_sumk3 as
+ select k1, k2, sum(k3) from duplicate_table group by k1, k2;
+ ```
+
+ 物化视图的 schema 如下图，物化视图包含两列 k1, k2，sum(k3) 其中 k1, k2 为分组列，sum(k3) 为根据 k1, k2 分组后的 k3 列的求和值。
+
+ 由于物化视图没有声明排序列，且物化视图带聚合数据，系统默认补充分组列 k1, k2 为排序列。
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | k1_k2_sumk3 | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ | | k3 | BIGINT | Yes | false | N/A | SUM |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ ```
+
+4. 创建一个去除重复行的物化视图
+
+ ```
+ create materialized view deduplicate as
+ select k1, k2, k3, k4 from duplicate_table group by k1, k2, k3, k4;
+ ```
+
+ 物化视图 schema 如下图，物化视图包含 k1, k2, k3, k4列，且不存在重复行。
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | deduplicate | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ | | k3 | BIGINT | Yes | true | N/A | |
+ | | k4 | BIGINT | Yes | true | N/A | |
+ +-----------------+-------+--------+------+-------+---------+-------+
+
+ ```
+
+5. 创建一个不声明排序列的非聚合型物化视图
+
+ all_type_table 的 schema 如下：
+
+ ```
+ +-------+--------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+--------------+------+-------+---------+-------+
+ | k1 | TINYINT | Yes | true | N/A | |
+ | k2 | SMALLINT | Yes | true | N/A | |
+ | k3 | INT | Yes | true | N/A | |
+ | k4 | BIGINT | Yes | true | N/A | |
+ | k5 | DECIMAL(9,0) | Yes | true | N/A | |
+ | k6 | DOUBLE | Yes | false | N/A | NONE |
+ | k7 | VARCHAR(20) | Yes | false | N/A | NONE |
+ +-------+--------------+------+-------+---------+-------+
+ ```
+
+ 物化视图包含 k3, k4, k5, k6, k7 列，且不声明排序列，则创建语句如下：
+
+ ```
+ create materialized view mv_1 as
+ select k3, k4, k5, k6, k7 from all_type_table;
+ ```
+
+ 系统默认补充的排序列为 k3, k4, k5 三列。这三列类型的字节数之和为 4(INT) + 8(BIGINT) + 16(DECIMAL) = 28 < 36。所以补充的是这三列作为排序列。
+ 物化视图的 schema 如下，可以看到其中 k3, k4, k5 列的 key 字段为 true，也就是排序列。k6, k7 列的 key 字段为 false，也就是非排序列。
+
+ ```
+ +----------------+-------+--------------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +----------------+-------+--------------+------+-------+---------+-------+
+ | mv_1 | k3 | INT | Yes | true | N/A | |
+ | | k4 | BIGINT | Yes | true | N/A | |
+ | | k5 | DECIMAL(9,0) | Yes | true | N/A | |
+ | | k6 | DOUBLE | Yes | false | N/A | NONE |
+ | | k7 | VARCHAR(20) | Yes | false | N/A | NONE |
+ +----------------+-------+--------------+------+-------+---------+-------+
+ ```
+
+
+## keyword
+ CREATE, MATERIALIZED, VIEW
diff --git a/docs/documentation/cn/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md b/docs/documentation/cn/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md
new file mode 100644
index 0000000..35078eb
--- /dev/null
+++ b/docs/documentation/cn/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md
@@ -0,0 +1,101 @@
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# DROP MATERIALIZED VIEW
+
+## description
+ 该语句用于删除物化视图。同步语法
+
+语法:
+
+ ```
+ DROP MATERIALIZED VIEW [IF EXISTS] mv_name ON table_name
+ ```
+
+1. IF EXISTS
+ 如果物化视图不存在，不要抛出错误。如果不声明此关键字，物化视图不存在则报错。
+
+2. mv_name
+ 待删除的物化视图的名称。必填项。
+
+3. table_name
+ 待删除的物化视图所属的表名。必填项。
+
+## example
+
+表结构为
+
+```
+mysql> desc all_type_table all;
++----------------+-------+----------+------+-------+---------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++----------------+-------+----------+------+-------+---------+-------+
+| all_type_table | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | NONE |
+| | k3 | INT | Yes | false | N/A | NONE |
+| | k4 | BIGINT | Yes | false | N/A | NONE |
+| | k5 | LARGEINT | Yes | false | N/A | NONE |
+| | k6 | FLOAT | Yes | false | N/A | NONE |
+| | k7 | DOUBLE | Yes | false | N/A | NONE |
+| | | | | | | |
+| k1_sumk2 | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | SUM |
++----------------+-------+----------+------+-------+---------+-------+
+```
+
+1. 删除表 all_type_table 的名为 k1_sumk2 的物化视图
+
+ ```
+ drop materialized view k1_sumk2 on all_type_table;
+ ```
+ 物化视图被删除后的表结构
+
+ ```
+ +----------------+-------+----------+------+-------+---------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++----------------+-------+----------+------+-------+---------+-------+
+| all_type_table | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | NONE |
+| | k3 | INT | Yes | false | N/A | NONE |
+| | k4 | BIGINT | Yes | false | N/A | NONE |
+| | k5 | LARGEINT | Yes | false | N/A | NONE |
+| | k6 | FLOAT | Yes | false | N/A | NONE |
+| | k7 | DOUBLE | Yes | false | N/A | NONE |
++----------------+-------+----------+------+-------+---------+-------+
+ ```
+
+2. 删除表 all_type_table 中一个不存在的物化视图
+
+ ```
+ drop materialized view k1_k2 on all_type_table;
+ ERROR 1064 (HY000): errCode = 2, detailMessage = Materialized view [k1_k2] does not exist in table [all_type_table]
+ ```
+ 删除请求直接报错
+
+3. 删除表 all_type_table 中的物化视图 k1_k2，不存在不报错。
+
+ ```
+ drop materialized view if exists k1_k2 on all_type_table;
+Query OK, 0 rows affected (0.00 sec)
+ ```
+
+ 存在则删除，不存在则不报错。
+
+## keyword
+ DROP, MATERIALIZED, VIEW
diff --git a/docs/documentation/en/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md b/docs/documentation/en/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md
new file mode 100644
index 0000000..9d844b7
--- /dev/null
+++ b/docs/documentation/en/sql-reference/sql-statements/Data Definition/CREATE MATERIALIZED VIEW.md
@@ -0,0 +1,231 @@
+<!-
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+->
+
+# CREATE MATERIALIZED VIEW
+
+## description
+
+This statement is used to create a materialized view.
+
+ Asynchronous syntax. After the call is successful, it only indicates that the task to create the materialized view is successfully submitted. The user needs to check the progress of the materialized view by using ```show alter table rollup```.
+
+ After the progress is FINISHED, you can use the ```desc [table_name] all``` command to view the schema of the materialized view.
+
+syntax:
+
+ ```
+
+ CREATE MATERIALIZED VIEW [MG name] as [query]
+ [PROPERTIES ("key" = "value")]
+
+ ```
+
+1. MV name
+
+ Name of the materialized view. Required.
+
+ Materialized view names in the same table cannot be duplicated.
+
+2. query
+
+ The query used to construct the materialized view. The result of the query is the data of the materialized view. The query format currently supported is:
+
+ ```
+ SELECT select_expr [, select_expr ...]
+ FROM [Base view name]
+ GROUP BY column_name [, column_name ...]
+ ORDER BY column_name [, column_name ...]
+
+ The syntax is the same as the query syntax.
+ ```
+
+ select_expr: All columns in the materialized view's schema.
+
+ + Only single columns and aggregate columns without expression calculation are supported.
+ + The aggregate function currently only supports SUM, MIN, MAX, and the parameters of the aggregate function can only be a single column without expression calculation.
+ + Contains at least one single column.
+ + All involved columns can only appear once.
+
+ base view name: The original table name of the materialized view. Required.
+
+ + Must be a single table and not a subquery
+
+ group by: Grouped column of materialized view, optional.
+
+ + If not filled, the data will not be grouped.
+
+ order by: Sort order of materialized view, optional.
+
+ + The order of the column sort must be the same as the column declaration order in select_expr.
+ + If order by is not specified, sort columns are automatically supplemented according to the rules.
+
+ + If the materialized view is an aggregate type, all grouping columns are automatically supplemented with sort columns.
+ + If the materialized view is a non-aggregated type, the first 36 bytes are automatically supplemented as a sorted column. If the number of sorts for automatic replenishment is less than three, the first three are sorted.
+ + If the query contains a grouping column, the sort order must be the same as the grouping column.
+
+3. properties
+
+ Declare some configuration of materialized view, optional.
+
+ ```
+ PROPERTIES ("key" = "value", "key" = "value" ...)
+
+ ```
+
+ The following configurations can be declared here:
+
+ + short_key: the number of columns.
+ + timeout: timeout for materialized view construction.
+
+## example
+
+Base table structure is
+
+```
+mysql> desc duplicate_table;
++-------+--------+------+------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+--------+------+------+---------+-------+
+| k1 | INT | Yes | true | N/A | |
+| k2 | INT | Yes | true | N/A | |
+| k3 | BIGINT | Yes | true | N/A | |
+| k4 | BIGINT | Yes | true | N/A | |
++-------+--------+------+------+---------+-------+
+```
+
+1. Create a materialized view containing only the columns of the original table (k1, k2)
+
+ ```
+ create materialized view k1_k2 as
+select k1, k2 from duplicate_table;
+ ```
+
+ The materialized view's schema is shown below. The materialized view contains only two columns k1, k2 without any aggregation.
+
+ ```
+ +-----------------+-------+--------+------+------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+------+---------+-------+
+ | k1_k2 | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ +-----------------+-------+--------+------+------+---------+-------+
+ ```
+
+2. Create a materialized view sorted by k2
+
+ ```
+ create materialized view k2_order as
+select k2, k1 from duplicate_table order by k2;
+```
+
+ The materialized view's schema is shown below. The materialized view contains only two columns k2, k1, where column k2 is a sorted column without any aggregation.
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | k2_order | k2 | INT | Yes | true | N/A | |
+ | | k1 | INT | Yes | false | N/A | NONE |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ ```
+
+3. Create a materialized view grouped by k1, k2 with k3 as the SUM aggregate
+
+ ```
+ create materialized view k1_k2_sumk3 as
+select k1, k2, sum (k3) from duplicate_table group by k1, k2;
+ ```
+
+ The materialized view's schema is shown below. The materialized view contains two columns k1, k2 and sum (k3), where k1, k2 are grouped columns, and sum (k3) is the sum of the k3 columns grouped according to k1, k2.
+
+ Because the materialized view does not declare a sort column, and the materialized view has aggregate data, the system supplements the grouping columns k1 and k2 by default.
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | k1_k2_sumk3 | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ | | k3 | BIGINT | Yes | false | N/A | SUM |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ ```
+
+4. Create a materialized view to remove duplicate rows
+
+ ```
+ create materialized view deduplicate as
+select k1, k2, k3, k4 from duplicate_table group by k1, k2, k3, k4;
+ ```
+
+ The materialized view schema is shown below. The materialized view contains k1, k2, k3, and k4 columns, and there are no duplicate rows.
+
+ ```
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ | deduplicate | k1 | INT | Yes | true | N/A | |
+ | | k2 | INT | Yes | true | N/A | |
+ | | k3 | BIGINT | Yes | true | N/A | |
+ | | k4 | BIGINT | Yes | true | N/A | |
+ +-----------------+-------+--------+------+-------+---------+-------+
+ ```
+
+5. Create a non-aggregated materialized view that does not declare a sort column
+
+ The schema of all_type_table is as follows:
+
+ ```
+ +-------+--------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+--------------+------+-------+---------+-------+
+ | k1 | TINYINT | Yes | true | N/A | |
+ | k2 | SMALLINT | Yes | true | N/A | |
+ | k3 | INT | Yes | true | N/A | |
+ | k4 | BIGINT | Yes | true | N/A | |
+ | k5 | DECIMAL(9,0) | Yes | true | N/A | |
+ | k6 | DOUBLE | Yes | false | N/A | NONE |
+ | k7 | VARCHAR(20) | Yes | false | N/A | NONE |
+ +-------+--------------+------+-------+---------+-------+
+ ```
+
+ The materialized view contains k3, k4, k5, k6, k7 columns, and no sort column is declared. The creation statement is as follows:
+
+ ```
+ create materialized view mv_1 as
+select k3, k4, k5, k6, k7 from all_type_table;
+ ```
+
+ The system's default supplementary sort columns are k3, k4, and k5. The sum of the number of bytes for these three column types is 4 (INT) + 8 (BIGINT) + 16 (DECIMAL) = 28 <36. So these three columns are added as sort columns.
+
+ The materialized view's schema is as follows. You can see that the key fields of the k3, k4, and k5 columns are true, which is the sort order. The key field of the k6, k7 columns is false, that is, non-sorted.
+
+ ```
+ +----------------+-------+--------------+------+-------+---------+-------+
+ | IndexName | Field | Type | Null | Key | Default | Extra |
+ +----------------+-------+--------------+------+-------+---------+-------+
+ | mv_1 | k3 | INT | Yes | true | N/A | |
+ | | k4 | BIGINT | Yes | true | N/A | |
+ | | k5 | DECIMAL(9,0) | Yes | true | N/A | |
+ | | k6 | DOUBLE | Yes | false | N/A | NONE |
+ | | k7 | VARCHAR(20) | Yes | false | N/A | NONE |
+ +----------------+-------+--------------+------+-------+---------+-------+
+ ```
+
+## keyword
+ CREATE, MATERIALIZED, VIEW
diff --git a/docs/documentation/en/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md b/docs/documentation/en/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md
new file mode 100644
index 0000000..cd356a5
--- /dev/null
+++ b/docs/documentation/en/sql-reference/sql-statements/Data Definition/DROP MATERIALIZED VIEW.md
@@ -0,0 +1,103 @@
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# DROP MATERIALIZED VIEW
+
+## description
+ This statement is used to delete a materialized view. Synchronization syntax
+
+syntax:
+
+ ```
+ DROP MATERIALIZED VIEW [IF EXISTS] mv_name ON table_name
+ ```
+
+1. IF EXISTS
+ If the materialized view does not exist, doris will not throw an error. If this keyword is not declared, an error will be reported if the materialized view does not exist.
+Ranch
+
+2. mv_name
+ The name of the materialized view to be deleted. Required.
+
+3. Table_name
+ Name of the table to which the materialized view to be deleted belongs. Required.
+
+## example
+
+Table structure is
+
+```
+mysql> desc all_type_table all;
++----------------+-------+----------+------+-------+---------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++----------------+-------+----------+------+-------+---------+-------+
+| all_type_table | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | NONE |
+| | k3 | INT | Yes | false | N/A | NONE |
+| | k4 | BIGINT | Yes | false | N/A | NONE |
+| | k5 | LARGEINT | Yes | false | N/A | NONE |
+| | k6 | FLOAT | Yes | false | N/A | NONE |
+| | k7 | DOUBLE | Yes | false | N/A | NONE |
+| | | | | | | |
+| k1_sumk2 | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | SUM |
++----------------+-------+----------+------+-------+---------+-------+
+```
+
+1. Drop the materialized view named k1_sumk2 of the table all_type_table
+
+ ```
+ drop materialized view k1_sumk2 on all_type_table;
+ ```
+ Table structure after materialized view is deleted as following:
+
+ ```
++----------------+-------+----------+------+-------+---------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++----------------+-------+----------+------+-------+---------+-------+
+| all_type_table | k1 | TINYINT | Yes | true | N/A | |
+| | k2 | SMALLINT | Yes | false | N/A | NONE |
+| | k3 | INT | Yes | false | N/A | NONE |
+| | k4 | BIGINT | Yes | false | N/A | NONE |
+| | k5 | LARGEINT | Yes | false | N/A | NONE |
+| | k6 | FLOAT | Yes | false | N/A | NONE |
+| | k7 | DOUBLE | Yes | false | N/A | NONE |
++----------------+-------+----------+------+-------+---------+-------+
+ ```
+
+2. Delete a non-existing materialized view in the table all_type_table
+
+ ```
+ drop materialized view k1_k2 on all_type_table;
+ERROR 1064 (HY000): errCode = 2, detailMessage = Materialized view [k1_k2] does not exist in table [all_type_table]
+ ```
+
+ The delete request directly reports an error
+
+3. Delete the materialized view k1_k2 in the table all_type_table. Materialized view does not exist and no error is reported.
+
+ ```
+ drop materialized view if exists k1_k2 on all_type_table;
+Query OK, 0 rows affected (0.00 sec)
+ ```
+
+ If it exists, it will be deleted; If it does not exist, no error will be reported.
+
+## keyword
+ DROP, MATERILIAZED, VIEW
diff --git a/fe/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java b/fe/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
index d73bbd1..d01310f 100644
--- a/fe/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
+++ b/fe/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
@@ -46,7 +46,7 @@ import java.util.Set;
* FROM [Base view name]
* GROUP BY column_name[, column_name ...]
* ORDER BY column_name[, column_name ...])
- * [PROPERTIES ("key": "value")]
+ * [PROPERTIES ("key" = "value")]
*/
public class CreateMaterializedViewStmt extends DdlStmt {
private String mvName;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org