Asynchronous materialized view

Construction and maintenance of materialized views

Create materialized views

Prepare two tables and data

  1. use tpch;
  3. CREATE TABLE IF NOT EXISTS orders  (
  4.     o_orderkey       integer not null,
  5.     o_custkey        integer not null,
  6.     o_orderstatus    char(1) not null,
  7.     o_totalprice     decimalv3(15,2) not null,
  8.     o_orderdate      date not null,
  9.     o_orderpriority  char(15) not null,
  10.     o_clerk          char(15) not null,
  11.     o_shippriority   integer not null,
  12.     o_comment        varchar(79) not null
  13.     )
  14.     DUPLICATE KEY(o_orderkey, o_custkey)
  15.     PARTITION BY RANGE(o_orderdate)(
  16.     FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
  17.     DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
  18.     PROPERTIES ("replication_num" = "1");
  20. insert into orders values
  21.    (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'),
  22.    (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'),
  23.    (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy');
  25. CREATE TABLE IF NOT EXISTS lineitem (
  26.     l_orderkey    integer not null,
  27.     l_partkey     integer not null,
  28.     l_suppkey     integer not null,
  29.     l_linenumber  integer not null,
  30.     l_quantity    decimalv3(15,2) not null,
  31.     l_extendedprice  decimalv3(15,2) not null,
  32.     l_discount    decimalv3(15,2) not null,
  33.     l_tax         decimalv3(15,2) not null,
  34.     l_returnflag  char(1) not null,
  35.     l_linestatus  char(1) not null,
  36.     l_shipdate    date not null,
  37.     l_commitdate  date not null,
  38.     l_receiptdate date not null,
  39.     l_shipinstruct char(25) not null,
  40.     l_shipmode     char(10) not null,
  41.     l_comment      varchar(44) not null
  42.     )
  43.     DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
  44.     PARTITION BY RANGE(l_shipdate)
  45.     (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
  46.     DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
  47.     PROPERTIES ("replication_num" = "1");
  49. insert into lineitem values
  50.  (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
  51.  (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
  52.  (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19', '2023-10-19', 'c', 'd', 'xxxxxxxxx');

Create materialized views

  1. CREATE MATERIALIZED VIEW mv1 
  2.         BUILD DEFERRED REFRESH AUTO ON MANUAL
  3.         partition by(l_shipdate)
  4.         DISTRIBUTED BY RANDOM BUCKETS 2
  5.         PROPERTIES ('replication_num' = '1') 
  6.         AS 
  7.         select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as sum_total
  8.             from lineitem
  9.             left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate = o_orderdate
  10.             group by
  11.             l_shipdate,
  12.             o_orderdate,
  13.             l_partkey,
  14.             l_suppkey;

Specific syntax can be viewed CREATE MATERIALIZED VIEW

View materialized view meta information

  1. select * from mv_infos("database"="tpch") where Name="mv1";

The unique features of materialized views can be viewed through mv_infos()

Properties related to table, still viewed through SHOW TABLES

Refresh materialized view

The materialized view supports different refresh strategies, such as scheduled refresh and manual refresh. It also supports different refresh granularity, such as full refresh, incremental refresh of partition granularity, etc. Here we take manually refreshing partial partitions of the materialized view as an example.

First, check the list of materialized view partitions

  1. SHOW PARTITIONS FROM mv1;

Refresh partition named p_20231017_20231018

  1. REFRESH MATERIALIZED VIEW mv1 partitions(p_20231017_20231018);

Specific syntax can be viewed REFRESH MATERIALIZED VIEW

task management

Each materialized view defaults to a job responsible for refreshing data, which is used to describe the refresh strategy and other information of the materialized view. Each time a refresh is triggered, a task is generated, Task is used to describe specific refresh information, such as the time used for refreshing, which partitions were refreshed, etc

View jobs in materialized views

  1. select * from jobs("type"="mv") order by CreateTime;

Specific syntax can be viewed jobs(“type”=”mv”)

Pause materialized view job scheduled scheduling

  1. PAUSE MATERIALIZED VIEW JOB ON mv1;

Can pause the scheduled scheduling of materialized views

Specific syntax can be viewed PAUSE MATERIALIZED VIEW JOB

RESUME materialized view job scheduling

  1. RESUME MATERIALIZED VIEW JOB ON mv1;

Can RESUME scheduled scheduling of materialized views

Specific syntax can be viewed RESUME MATERIALIZED VIEW JOB

Viewing tasks in materialized views

  1. select * from tasks("type"="mv");

Specific syntax can be viewed tasks(“type”=”mv”)

Cancel the task of objectifying the view

  1. CANCEL MATERIALIZED VIEW TASK realTaskId on mv1;

Can cancel the operation of this task

Specific syntax can be viewed CANCEL MATERIALIZED VIEW TASK

Modifying materialized views

Modify the properties of materialized views

  1. ALTER MATERIALIZED VIEW mv1 set("grace_period"="3333");

Modify the name of the materialized view, the refresh method of the materialized view, and the unique properties of the materialized view can be viewed through ALTER MATERIALIZED VIEW

The materialized view itself is also a Table, so Table related properties, such as the number of copies, are still modified through the syntax related to ALTER TABLE.

Delete materialized view

  1. DROP MATERIALIZED VIEW mv1;

The materialized view has a dedicated deletion syntax and cannot be deleted through the drop table,

Specific syntax can be viewed DROP MATERIALIZED VIEW

The use of materialized views

can be viewed Query async materialized view

Notice

  • Asynchronous materialized views are only supported for use in the Nereids optimizer, Nereids optimizer
  • Currently, determining the synchronization between materialized views and base tables is only supported for OlapTable. For other types of external tables, they are directly considered to be synchronized. For instance, if the base tables of a materialized view are all external tables, they are assumed to be synchronized. When querying mv_infos(), the SyncWithBaseTables flag will always return 1 (true) for these external tables. When refreshing a materialized view, it is necessary to manually refresh specific partitions or specify complete to refresh all partitions.