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

/*+ 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_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 を作成します。1 分ごとにリフレッシュされます。

  CREATE MATERIALIZED VIEW fast_mv3
  REFRESH FAST NEXT now() + INTERVAL 1 minute
  AS
  SELECT count(*) AS cnt   -- システムは自動的に定数のプライマリキーを生成し、マテリアライズドビューにレコードが 1 つだけ存在するようにします。
  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 を作成し、すべての列ではなく、指定された列 sale_date にのみインデックスを作成します。

  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;