AnalyticDB：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;

  手動重新整理物化視圖：

  REFRESH MATERIALIZED VIEW myview6;

• 建立物化視圖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，定義分布鍵和分區鍵。

CREATE MATERIALIZED VIEW myview8 (
  quantity INT,    --即使不顯式列出普通列，物化視圖也會包含查詢中輸出的所有列。     
  price DECIMAL(10, 2),
  sale_date TIMESTAMP
) 
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
AS 
SELECT * FROM sales;

顯式定義主鍵、分布鍵、索引等

• 建立物化視圖myview9，不對全部列建立索引，僅對指定列customer_name建立索引。

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

• 建立物化視圖myview10，定義主鍵、分布鍵、叢集索引、指定列索引和注釋。

  CREATE MATERIALIZED VIEW myview10 (
    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)
  COMMENT 'MATERIALIZED VIEW c'
  AS 
  SELECT * FROM sales;

彈性物化視圖

• 建立彈性物化視圖myview11，使用Job型資源群組serverless建立和重新整理物化視圖，每天重新整理1次。

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

• 建立彈性物化視圖myview12，使用Job型資源群組serverless建立和重新整理物化視圖，且可佔用serverless資源群組12 ACU的資源。

  CREATE MATERIALIZED VIEW myview12
  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;