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
);

完全リフレッシュのマテリアライズドビュー

• 5 分ごとにリフレッシュされるマテリアライズドビュー myview1 を作成します。

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

• 毎日午前 2:00 にリフレッシュされるマテリアライズドビュー myview2 を作成します。

  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;

• 毎週月曜日の午前 2:00 にリフレッシュされるマテリアライズドビュー myview3 を作成します。

  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;

• 毎月 1 日の午前 2:00 にリフレッシュされるマテリアライズドビュー myview4 を作成します。

  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;

高速リフレッシュの単一テーブルマテリアライズドビュー

高速リフレッシュを使用するマテリアライズドビューを作成する前に、クラスターとベーステーブルのバイナリロギング機能を有効にする必要があります。

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE customer binlog=true;

ALTER TABLE sales binlog=true;

高速リフレッシュの複数テーブルマテリアライズドビュー

• 高速リフレッシュを使用し、集計なしの複数テーブルマテリアライズドビュー `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 '顧客',
    CLUSTERED KEY INDEX(sale_id),
    PRIMARY KEY(sale_id,sale_date)
  ) 
  DISTRIBUTED BY HASH(sale_id)
  COMMENT 'マテリアライズドビュー c'
  AS 
  SELECT * FROM sales;

エラスティックマテリアライズドビュー

• Job タイプのリソースグループ `serverless` を使用して作成およびリフレッシュし、1 日 1 回の更新頻度を持つエラスティックマテリアライズドビュー myview11 を作成します。

  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;

• Job タイプのリソースグループ `serverless` を作成とリフレッシュに使用し、このグループから 12 ACU のリソースを使用できるエラスティックマテリアライズドビュー myview12 を作成します。

  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;