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

バイナリロギングの有効化

増分リフレッシュは、ベーステーブルの変更を追跡するためにバイナリロギングに依存します。クラスターレベルと各ベーステーブルで有効にしてください。

SET ADB_CONFIG BINLOG_ENABLE=true;
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;

バイナリロギングの有効化に失敗した場合は、「高速マテリアライズドビューを作成できない」をご参照ください。

単一テーブルの高速マテリアライズドビューの作成

次の例では、sales テーブルに sales_mv_incre という名前の高速マテリアライズドビューを作成し、自動リフレッシュ間隔を 3 分に設定します。

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_id;

複数テーブルの高速マテリアライズドビューの作成

V3.2.1.0 以降のクラスターでは、高速マテリアライズドビューで複数のテーブルを結合できます。次の例では、customer と sales を結合します。

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  customer.customer_id,
  SUM(sales.price) AS price
FROM customer
INNER JOIN (SELECT customer_id, price FROM sales) sales
  ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

CREATE MATERIALIZED VIEW の完全な構文については、「CREATE MATERIALIZED VIEW」をご参照ください。

SELECT 列のルール

SELECT リストの列は、次のルールに従う必要があります。

• GROUP BY と集計関数を使用する場合： SELECT リストにすべての GROUP BY 列を含めてください。

  例の表示

  正しい例 (すべての GROUP BY 列が含まれています) ：

  CREATE MATERIALIZED VIEW demo_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- GROUP BY 列
    sale_date,  -- GROUP BY 列
    max(quantity) AS max,  -- 式の列にはエイリアスが必要です
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;

  正しくない例 (GROUP BY 列 sale_date がありません) ：

  CREATE MATERIALIZED VIEW false_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;  -- sale_date は GROUP BY にありますが、SELECT にはありません

• 集計関数を使用し、GROUP BY を使用しない場合： SELECT リストには、集計列のみ、または定数と集計列のみを含めることができます。

  例の表示

  -- 集計列のみ
  CREATE MATERIALIZED VIEW demo_mv2
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;
  
  -- 定数と集計列 (定数がプライマリキーになります)
  CREATE MATERIALIZED VIEW demo_mv3
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    1 AS pk,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;

• 集計を使用しない場合：ベーステーブルのすべてのプライマリキー列を含めてください。

  例の表示

  -- 単一プライマリキー
  CREATE MATERIALIZED VIEW demo_mv4
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,   -- ベーステーブル sales のプライマリキー列
    quantity
  FROM sales;
  
  -- 複合プライマリキー: PRIMARY KEY(sale_id, sale_date)
  CREATE MATERIALIZED VIEW demo_mv5
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- 最初のプライマリキー列
    sale_date,  -- 2 番目のプライマリキー列
    quantity
  FROM sales1;

• UNION ALL クエリ：各ブランチは、ブランチごとに異なる定数値を持つ union_all_marker という名前の列を出力する必要があります。すべてのベーステーブルのプライマリキー列を含めます。マテリアライズドビューのプライマリキーには、ベーステーブルのプライマリキー列と union_all_marker の両方を含める必要があります。

  CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker))
  REFRESH FAST NEXT now() + INTERVAL 5 minute
  AS
  SELECT customer_id AS id, "customer" AS union_all_marker
  FROM customer
  UNION ALL
  SELECT sale_id AS id, "sales" AS union_all_marker
  FROM sales;

• 式の列： SELECT リスト内のすべての式の列には、SUM(price) AS total_price のようにエイリアスが必要です。

一般的な制限

これらの制限は、すべてのマテリアライズドビューに適用されます。

• マテリアライズドビューに対して INSERT、DELETE、または UPDATE を実行することはできません。

• マテリアライズドビューが参照している間は、ベーステーブルまたはその列を削除したり名前を変更したりすることはできません。まずマテリアライズドビューを削除してから、ベーステーブルを変更してください。

• クラスターあたりの最大マテリアライズドビュー数：

  • V3.1.4.7 以降：64

  • V3.1.4.7 未満：8

クォータを引き上げるには、。

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

予約済みノードを追加または削除すると、非同期ジョブは無効になります。完全リフレッシュは非同期ジョブであるため、ノードのスケーリング中は実行できません。高速リフレッシュは影響を受けません。

高速 (増分) リフレッシュマテリアライズドビューの制限

SQL の制限の完全なリストについては、「増分 MV のクエリ制約」をご参照ください。

リフレッシュトリガー：定時自動リフレッシュのみがサポートされています。間隔は 5 秒から 5 分の間でなければなりません。

マテリアライズドビューに最新の 1 年分のデータのみを保持するにはどうすればよいですか？

日付列をパーティションキー (PARTITION BY) として使用して、LIFECYCLE 値を設定し保持するパーティションの数を制限します。日付でパーティション化されたビューの場合は、LIFECYCLE 365 を設定すると、最新の 365 パーティション (1 年分) が保持されます。

例：sales テーブルに、sale_date でパーティション化された新しいレコードが毎日追加されます。

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT
  sale_date,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_date;

クエリ実行エラー：Can not create FAST materialized view, because demotable doesn't support getting incremental data

ベーステーブル demotable でバイナリロギングが有効になっていません。次のコマンドで有効にしてください。

ALTER TABLE demotable binlog=true;

XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now と表示された場合、ベーステーブルは XUANWU_V2 エンジンを使用しており、クラスターのカーネルバージョンは V3.2.6.0 未満です。XUANWU_V2 は、カーネルバージョン V3.2.6.0 からバイナリロギングをサポートします。クラスターを V3.2.6.0 以降にアップグレードし、再度 ALTER TABLE demotable binlog=true; を実行してベーステーブルでバイナリロギングを有効にしてください。

クエリ実行エラー：PRIMARY KEY id must output to MV

高速マテリアライズドビューが GROUP BY のない非集計クエリを使用しています。この場合、SELECT リストにはベーステーブルのすべてのプライマリキー列を含める必要があります。

正しくない例 (sales のプライマリキーである sale_id がありません) ：

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id, price
FROM sales;

プライマリキー列を追加してください。

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

クエリ実行エラー：MV PRIMARY KEY must be equal to base table PRIMARY KEY

高速マテリアライズドビューが GROUP BY のない非集計クエリを使用しており、マテリアライズドビューのプライマリキー定義に、ベーステーブルのプライマリキーの一部ではない列が含まれています。

正しくない例 (product_id は sales のプライマリキー列ではありません) ：

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id, product_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

プライマリキー定義からプライマリキー以外の列を削除してください。

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

クエリ実行エラー：FAST materialized view must define PRIMARY KEY

このエラーには 2 つの原因が考えられます。

• 有効なプライマリキーが定義されていません。 マテリアライズドビューの定義を更新して、次のルールを満たすようにしてください。

  • グループ化された集計クエリ (GROUP BY を使用) ：プライマリキーは GROUP BY 列でなければなりません (例：GROUP BY a, b の場合、PRIMARY KEY(a, b) を設定) 。

  • グループ化されていない集計クエリ (GROUP BY を使用しない) ：プライマリキーは定数でなければなりません。

  • 非集計クエリ：プライマリキーはベーステーブルのプライマリキーと完全に一致する必要があります (例：ベーステーブルに PRIMARY KEY(sale_id, sale_date) がある場合、ビューにも PRIMARY KEY(sale_id, sale_date) が必要) 。

• プライマリキー列に関数が適用されています。 クエリ内のプライマリキー列から関数を削除してください。

クエリ実行エラー：The join graph is not supported

結合列のデータ型が一致していません。たとえば、customer.id と sales.id の型が異なる場合、結合は失敗します。次のコマンドで型を揃えてください。

ALTER TABLE tablename MODIFY COLUMN columnname newtype;

詳細については、「列のデータ型の変更」をご参照ください。

クエリ実行エラー：Unable to use index join to refresh this fast MV

結合列にインデックスがありません。各結合列にインデックスを追加してください。

ALTER TABLE tablename ADD KEY idx_name(columnname);

詳細については、「インデックスの作成」をご参照ください。

クエリ実行エラー：Query exceeded reserved memory limit

クエリがノードごとのメモリ制限を超えました。SQL 診断機能を使用して、メモリ使用量の多いステージと演算子 (Aggregation、TopN、Window、Join が一般的な原因です) を特定し、それらの演算子を最適化してください。「メモリメトリック」および「ステージとタスクの詳細を使用したクエリの分析」もご参照ください。