AnalyticDB:マテリアライズドビューの作成

例の準備

以下のマテリアライズドビューの例では、このセクションで定義されているベーステーブルを使用します。例を試すには、まずここに記載されている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;

• 毎日02:00: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;

• 毎週月曜日02:00: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日の02:00: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;

• 1回のみリフレッシュされる 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 という名前のマテリアライズドビューを作成します。手動リフレッシュ時間は不要です。

  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;

• 10秒間隔で高速リフレッシュをサポートする fast_mv1 という名前の単一テーブルのマテリアライズドビューを作成します。マテリアライズドビュークエリに集計関数は含まれません。

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

• 5秒間隔で高速リフレッシュをサポートする fast_mv2 という名前の単一テーブルのマテリアライズドビューを作成します。マテリアライズドビュークエリにGROUP BY句を含む集計関数が含まれます。

  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                 -- WHERE条件を式を使用して定義できます。
  GROUP BY customer_id, sale_date;

• 1分間隔で高速リフレッシュをサポートする fast_mv3 という名前の単一テーブルのマテリアライズドビューを作成します。マテリアライズドビュークエリにGROUP BY句を含まない集計関数が含まれます。

  CREATE MATERIALIZED VIEW fast_mv3
  REFRESH FAST NEXT now() + INTERVAL 1 minute
  AS
  SELECT count(*) AS cnt   -- システムは、マテリアライズドビューに1つのレコードのみが含まれるように、定数をプライマリキーとして生成します。
  FROM sales;

高速リフレッシュをサポートする複数テーブルのマテリアライズドビューの作成

• 5秒間隔で高速リフレッシュをサポートし、集約を使用しない fast_mv4 という名前の複数テーブルのマテリアライズドビューを作成します。

  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;

• 10秒間隔で高速リフレッシュをサポートし、グループ化集約を使用する fast_mv5 という名前の複数テーブルのマテリアライズドビューを作成します。

  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 という名前のエラスティックマテリアライズドビューを作成します。

  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;

• サーバーレスジョブリソースグループを使用し、最大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;