CREATE MATERIALIZED VIEW

示例准备

下文的物化视图示例均利用本章节的基表。为了更方便地体验下文的示例，您可以先参考本章节的SQL语句创建基表。

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎为XUANWU引擎。
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎为XUANWU引擎。
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

全量刷新物化视图

• 创建物化视图myview1，每5分钟刷新一次。

  CREATE MATERIALIZED VIEW myview1
  REFRESH   --等同于REFRESH COMPLETE
   NEXT now() + INTERVAL 5 minute
  AS
  SELECT count(*) as cnt FROM customer;

• 创建物化视图myview2，每天凌晨2点刷新。

  CREATE MATERIALIZED VIEW myview2
  REFRESH COMPLETE
   START WITH DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
   NEXT DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
  AS
  SELECT count(*) as cnt FROM customer;

• 创建物化视图myview3，每周一凌晨2点刷新。

  CREATE MATERIALIZED VIEW myview3
  REFRESH COMPLETE ON DEMAND
   START WITH DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
   NEXT DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
  AS
  SELECT count(*) as cnt FROM customer;

• 创建物化视图myview4，每个月第一天凌晨2点刷新。

  CREATE MATERIALIZED VIEW myview4
  REFRESH   --等同于REFRESH COMPLETE 
   NEXT DATE_FORMAT(last_day(now()) + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
  AS
  SELECT count(*) as cnt FROM customer;

• 创建物化视图myview5，只刷新一次。

  CREATE MATERIALIZED VIEW myview5
  REFRESH   --等同于REFRESH COMPLETE
   START WITH now() + INTERVAL 1 day
  AS 
  SELECT count(*) as cnt FROM customer;

• 创建物化视图myview6，不自动刷新，完全依靠手动刷新。

  CREATE MATERIALIZED VIEW myview6 (
    PRIMARY KEY (customer_id)
  ) DISTRIBUTED BY HASH (customer_id)
  AS
  SELECT customer_id FROM customer;

• 创建物化视图myview7，基表被INSERT OVERWRITE覆写后触发物化视图自动刷新。

  CREATE MATERIALIZED VIEW myview7
  REFRESH COMPLETE ON OVERWRITE
  AS
  SELECT count(*) as cnt FROM customer;

增量刷新单表物化视图

创建增量刷新的物化视图前，请先开启集群的Binlog特性和基表的Binlog功能。

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE customer binlog=true;

ALTER TABLE sales binlog=true;

• 创建无聚合操作的增量刷新的单表物化视图fast_mv1，每10秒钟刷新一次，

  CREATE MATERIALIZED VIEW fast_mv1
  REFRESH FAST NEXT now() + INTERVAL 10 second
  AS
  SELECT sale_id, sale_date, price
  FROM sales
  WHERE price > 10;

• 创建分组聚合操作的增量刷新的单表物化视图fast_mv2，每5秒钟刷新一次。

  CREATE MATERIALIZED VIEW fast_mv2
  REFRESH FAST NEXT now() + INTERVAL 5 second
  AS
  SELECT
     customer_id, sale_date,                 -- 系统会自动输出GROUP BY列作为物化视图主键。
     COUNT(sale_id) AS cnt_sale_id,          -- 聚合输出列。
     SUM(price * quantity) AS total_revenue, -- 聚合输出列。
     customer_id / 100 AS new_customer_id    -- 非聚合输出列可以使用任意表达式。
  FROM sales
  WHERE ifnull(price, 1) > 0                 -- 条件可以使用任何表达式。
  GROUP BY customer_id, sale_date;

• 创建无分组聚合操作的增量刷新的单表物化视图fast_mv3，每分钟刷新一次。

  CREATE MATERIALIZED VIEW fast_mv3
  REFRESH FAST NEXT now() + INTERVAL 1 minute
  AS
  SELECT count(*) AS cnt   -- 系统会自动生成常量主键，确保全局只有一条记录在物化视图中。
  FROM sales;

增量刷新多表物化视图

• 创建无聚合操作的增量刷新的多表物化视图fast_mv4，每5秒钟刷新一次，

  CREATE MATERIALIZED VIEW fast_mv4
  REFRESH FAST NEXT now() + INTERVAL 5 second
  AS
  SELECT 
      c.customer_id,
      c.customer_name,
      s.sale_id,
      (s.price * s.quantity) AS revenue
  FROM 
      sales s
  JOIN 
      customer c ON s.customer_id = c.customer_id;

• 创建分组聚合操作的增量刷新的多表物化视图fast_mv5，每10秒钟刷新一次。

  CREATE MATERIALIZED VIEW fast_mv5
  REFRESH FAST NEXT now() + INTERVAL 10 second
  AS
  SELECT 
      s.sale_id,
      c.customer_name,
      COUNT(*) AS cnt,           
      SUM(s.price * s.quantity) AS revenue                
  FROM 
      sales s
  JOIN 
      (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
  GROUP BY 
      s.sale_id, c.customer_name;

显式定义主键、分布键、分区键、索引等

• 创建物化视图myview8，不对全部列建立索引，仅对指定列customer_name建立索引。

  CREATE MATERIALIZED VIEW myview8 (
    INDEX (sale_date),
    PRIMARY KEY (sale_id)
  ) DISTRIBUTED BY HASH (sale_id)
  REFRESH
   NEXT now() + INTERVAL 1 DAY
  AS
  SELECT * FROM sales;

• 创建物化视图myview9，定义主键、分布键、分区键、聚集索引、指定列索引和注释。

  CREATE MATERIALIZED VIEW myview9 (
    quantity INT,    --即使不显式列出普通列，物化视图也会包含查询中输出的所有列。     
    price DECIMAL(10, 2),
    KEY INDEX_ID(customer_id) COMMENT 'customer',
    CLUSTERED KEY INDEX(sale_id),
    PRIMARY KEY(sale_id,sale_date)
  ) 
  DISTRIBUTED BY HASH(sale_id)
  PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
  COMMENT 'MATERIALIZED VIEW c'
  AS 
  SELECT * FROM sales;

弹性物化视图

• 创建弹性物化视图myview10，使用Job型资源组serverless创建和刷新物化视图，每天刷新1次。

  CREATE MATERIALIZED VIEW myview10
  MV_PROPERTIES='{
    "mv_resource_group":"serverless"
  }'
  REFRESH COMPLETE ON DEMAND
   START WITH now()
   NEXT now() + INTERVAL 1 DAY
  AS
  SELECT * FROM sales;

• 创建弹性物化视图myview11，使用Job型资源组serverless创建和刷新物化视图，且可占用serverless资源组12 ACU的资源。

  CREATE MATERIALIZED VIEW myview11
  MV_PROPERTIES='{
    "mv_resource_group":"serverless",
    "mv_refresh_hints":{"elastic_job_max_acu":"12"}
  }'
  REFRESH COMPLETE ON DEMAND
   START WITH now()
   NEXT now() + INTERVAL 1 DAY
  AS
  SELECT * FROM sales;