CREATE MATERIALIZED VIEW

description

This statement is used to create materialized views.

The operation is asynchronous, progress of which can be viewed through SHOW ALTER TABLE MATERIALIZED VIEW after successful submission.

CREATE MATERIALIZED VIEW [MV name] as [query]
[PROPERTIES ("key" = "value")]

• MV name

  Name of materialized view, required item.

  Materialized view names of the same table cannot be duplicated.

• query

  Query statement used to build materialized view, and the result of query statement is materialized view data. Currently the following formats of query are supported:

  SELECT select_expr[, select_expr ...]
  FROM [Base view name]
  GROUP BY column_name[, column_name ...]
  ORDER BY column_name[, column_name ...]

  • select_expr: materialize all columns in the schema of the view.

    • Only single columns and aggregate columns not containing expression calculation are supported.
    • Currently, aggregate functions only support sum, min and max, and the parameters of aggregate functions can only be single columns not containing expression calculation.
    • It contains at least one single column.
    • All columns involved can only appear once.

  • base view name: original table name of materialized view, required item.

    • It must be a single table and not a subquery

  • group by: grouping column of materialized views, optional item.

    • If not filled in, the data will not be grouped.

  • order by: ordering column of material views, required item.

    • The order of declaration of ordering column must be consistent with that in select_expr.
    • If order by is not declared, then the ordering column will be automatically supplemented according to the rules. If the materialized view is an aggregation type, all grouping columns will be automatically supplemented as ordering columns. If the materialized view is not the aggregation type, then the first 36 bytes will be automatically supplemented as ordering columns. If the number of ordering columns automatically supplemented is less than 3, the first three will be taken as ordering columns.
    • If query contains grouping columns, then the ordering columns must be consistent with grouping columns.

• properties

  Declare some configurations of materialized view, optional.

  PROPERTIES ("key" = "value", "key" = "value" ...)

  The following configurations are supported:

  • timeout: timeout for materialized view construction.

Example

Assuming that the 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 schema of materialized view is shown in the following figure. The materialized view only contains two columns of k1 and 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 with k2 as the ordering column

create materialized view k2_order as
select k2, k1 from duplicate_table order by k2;

The schema of materialized view is shown in the following figure. Materialized view only contains two columns of k2, k1, in which K2 column is an ordering 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 aggregated with k1, k2 as grouping columns and k3 as SUM

create materialized view k1_k2_sumk3 as
select k1, k2, sum(k3) from duplicate_table group by k1, k2;

The schema of materialized view is shown in the following figure. The materialized view contains two columns of k1, k2 and sum(k3), in which k1 and k2 are grouping columns, and sum(k3) is the sum value of k3 columns grouped according to k1 and k2.

As there is no ordering column declared in materialized view, and the materialized view contains aggregation data, the grouping columns k1, k2 will be supplemented as ordering columns 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 with duplicate rows removed

create materialized view deduplicate as
select k1, k2, k3, k4 from duplicate_table group by k1, k2, k3, k4;

The schema of materialized view is shown in the following figure. The materialized view contains k1, k2, k3, k4 columns with 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-aggregation type materialized view with no ordering columns declared

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  |
+-------+--------------+------+-------+---------+-------+

When the materialized view contains k3, k4, k5, k6, k7 columns, and does not contain declared ordering columns, then create the statement as follows:

create materialized view mv_1 as
select k3, k4, k5, k6, k7 from all_type_table;

The ordering columns supplemented in the system are k3, k4, k5 and K5 by default. The sum of bytes of these three column types is 4(INT) + 8(BIGINT) + 16(DECIMAL) = 28 < 36. Therefore, these three columns are supplemented as ordering columns.

The schema of materialized view is as follows, the key field of k3, k4, k5 and K5 columns, the ordering columns, is true. The key field of K6 k6, k7 columns, the non-ordering columns, is 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  |
+----------------+-------+--------------+------+-------+---------+-------+

Keywords

CREATE, MATERIALIZED, VIEW