マテリアライズドビューの増分更新の設定 - AnalyticDB - Alibaba Cloud ドキュメントセンター

増分リフレッシュでは、特定のアルゴリズムを使用して変更されたデータのみを計算し、具体化されたビューのデータを更新します。フルリフレッシュと比較して、増分リフレッシュはオーバーヘッドが低く、更新頻度が高くなります。変更されたデータのみを更新する場合は、マテリアライズドビューの増分更新を設定できます。このトピックでは、マテリアライズドビューの作成時に増分更新を設定する方法について説明します。

前提条件

AnalyticDB for MySQLクラスターのマテリアライズドビューの増分更新を設定する前に、クラスターのマイナーバージョンが次の要件を満たしていることを確認してください。

単一テーブルのマテリアライズドビューの増分更新: 3.1.9.0以降。
マルチテーブルのマテリアライズドビューの増分更新: 3.2.1.0以降。

説明

AnalyticDB for MySQLのマイナーバージョンを照会するには
Data Lakehouse Editionクラスターで、SELECT adb_version(); 文を実行します。クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
AnalyticDB For MySQL Data Warehouse Editionクラスターのマイナーバージョンを表示および更新する方法については、「クラスターのマイナーバージョンの更新」をご参照ください。

シナリオ

ほとんどの場合、マテリアライズドビューは、複雑なクエリを高速化したり、抽出、変換、読み込み (ETL) プロセスを簡素化したりするために使用されます。マテリアライズドビューは、事前に計算されたクエリ結果を格納し、ビジネス要件に基づいて自動的に更新できます。データをリフレッシュするには、フルリフレッシュと増分リフレッシュの2つの方法を使用できます。次のリストは、2つの方法の違いを示しています。

フルリフレッシュは、対応するSQL文を再実行して、すべての古いデータを更新されたデータで上書きします。
増分リフレッシュは、更新されたデータのみを計算し、具体化されたビューの特定のデータを更新するアルゴリズムを使用します。

増分リフレッシュは、フルリフレッシュと比較して、ほとんどの場合費用効果が高く、より頻繁なデータ更新を実装するために使用できます。ただし、増分リフレッシュは必ずしもフルリフレッシュよりも優れているとは限りません。さまざまなシナリオに適しています。

フルリフレッシュは、次のバッチ処理シナリオに適しています。

データは毎日または1時間に1回更新されます。このシナリオでは、完全更新で複雑なSQL文を処理できます。
クエリには、分レベルのレイテンシが許可されます。このシナリオでは、特定のクエリの完全な更新を12秒以内に完了できます。

増分リフレッシュは、次のリアルタイムシナリオに適しています。

データはリアルタイムで更新されます。
レポートまたはETL操作はリアルタイムで更新されます。
第2レベルのレイテンシが必要です。

マテリアライズドビューは、履歴時点からのクエリ結果を表示します。より低いリフレッシュ待ち時間は、ほぼリアルタイムでクエリ結果を提供する。特定のデータウェアハウスはビューをリアルタイムで更新できますが、メンテナンスコストが高く、ベーステーブルの書き込みパフォーマンスが低下します。 AnalyticDB for MySQLは完全非同期アルゴリズムを使用して、ベーステーブルの書き込みパフォーマンスを損なうことなくすべてのDML操作をサポートします。

増分リフレッシュは、増分データのみが計算されるため、フルリフレッシュよりも費用対効果が高くなります。同じ量のデータが計算される場合、より複雑なアルゴリズムを使用する増分リフレッシュは、フルリフレッシュよりもコストがかかります。したがって、増分リフレッシュはバッチ処理シナリオには適していません。増分更新では、SQL文の種類に制限があります。

特にマテリアライズドビューの増分リフレッシュ結果がベーステーブルのクエリ結果と同じである場合、増分リフレッシュは特定のストリームコンピューティング要件を満たすことができます。増分更新を使用することで、ストリーミングエンジンコンポーネントの保守コストを節約できます。

制限事項

増分リフレッシュは、自動リフレッシュ機能を使用してのみ実装できます。自動リフレッシュ間隔は5秒から5分です。
マテリアライズドビューは、ベーステーブルのクエリ結果と同じ結果を保証し、すべてのDML操作をサポートする必要があります。この場合、増分更新はすべてのクエリ本文には適していません。作成するマテリアライズド・ビューが増分更新を実装できない場合、エラーが発生します。
V3.2.3.0より前のAnalyticDB for MySQLクラスターで増分更新をサポートするマテリアライズドビューのベーステーブルとしてパーティションテーブルを使用することはできません。
増分更新をサポートするマテリアライズドビューのベーステーブルに対して、INSERT OVERWRITEおよびTRUNCATE操作を実行することはできません。そうしないと、エラーが発生します。
複数テーブルのマテリアライズドビューの増分更新を設定する場合は、次の項目に注意してください。
- マルチテーブルマテリアライズドビューは、INNER JOIN操作のみをサポートします。
- 既定では、最大5つのテーブルを結合して、マテリアライズドビューを作成できます。クラスターの仕様に基づいて5つ以上のテーブルを含むマテリアライズドビューを作成するには、
  チケットを起票します。
- 関係するテーブルの結合に使用されるフィールドは、テーブルの元のフィールドであり、インデックスとして機能する必要があります。フィールドは同じデータ型である必要があります。
- レプリケートされたテーブルに基づく複数テーブルのマテリアライズドビューの増分更新を構成することはできません。

必要な権限。

マテリアライズドビューを作成するには、データベースまたはテーブルに対するcreate権限が必要です。
マテリアライズドビューを更新するには、データベースまたはテーブルに対するINSERT権限が必要です。
関連するテーブル列またはマテリアライズドビューに含まれるすべてのテーブルに対するSELECT権限が必要です。
作成したマテリアライズドビューの自動更新を構成する場合は、オンプレミスサーバー (127.0.0.1) またはIPアドレス ('%') を使用してビューを更新する権限が必要です。
マテリアライズドビューの増分更新を設定する場合は、マテリアライズドビューに含まれるすべてのベーステーブルに対してALTER権限があることを確認します。

準備

AnalyticDB for MySQLクラスターの増分更新をサポートするマテリアライズドビューを作成する前に、次の操作を実行します。

バイナリロギング機能を有効にします。
重要
- AnalyticDB for MySQLクラスターのマイナーバージョンが3.2.0.0以降の場合、バイナリログ機能は自動的に有効になります。 AnalyticDB for MySQLクラスターのマイナーバージョンが3.2.0.0より前の場合、SETステートメントを実行してバイナリログ機能を有効にする必要があります。
- AnalyticDB for MySQLのマイナーバージョンを照会するには
  Data Lakehouse Editionクラスターで、SELECT adb_version(); 文を実行します。クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
- AnalyticDB For MySQL Data Warehouse Editionクラスターのマイナーバージョンを表示および更新する方法については、「クラスターのマイナーバージョンの更新」をご参照ください。
```
SET ADB_CONFIG BINLOG_ENABLE=true;
```
ベーステーブルのバイナリログ機能を有効にします。
```
ALTER TABLE <table_name> binlog=true;
```
重要
- バイナリログ機能が有効になっているテーブルに対してINSERT OVERWRITE INTOおよびTRUNCATE操作を実行できるのは、V3.2.0.0以降のAnalyticDB for MySQLクラスターでのみです。
- 増分更新をサポートするマテリアライズドビューを作成した後、ベーステーブルのバイナリログ機能を無効にすることはできません。
- 増分更新をサポートするマテリアライズドビューを削除した後、SET ADB_CONFIG BINLOG_ENABLE=false; およびALTER TABLE <table_name> binlog=false; ステートメントを実行して、AnalyticDB for MySQLクラスターおよびベーステーブルのバイナリログ機能を無効にします。

マテリアライズドビューを作成するときの増分更新の設定

CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
REFRESH FAST [ON DEMAND] [START WITH date] <NEXT date>
AS
<QUERY BODY>

パラメーター

パラメーター	必須	説明
`または交換`	選択可能	作成するマテリアライズドビューの名前が既存のマテリアライズドビューの名前と同じかどうかに基づいて、マテリアライズドビューを作成するために使用されるルール。既存のマテリアライズドビューが同じ名前を使用していない場合、AnalyticDB for MySQLはマテリアライズドビューを作成します。既存のマテリアライズドビューが同じ名前を使用する場合、AnalyticDB for MySQLは既存のマテリアライズドビューを削除し、別のマテリアライズドビューを作成します。
`mv_name`	必須	マテリアライズド・ビューの名前。
`[MV定義]`	選択可能	マテリアライズドビューのテーブル関連のプロパティ。マテリアライズド・ビューは、標準テーブル・スキーマを使用してデータを格納する。このパラメーターを使用して、マテリアライズドビューの主キー、パーティションキー、およびインデックスを指定できます。増分更新をサポートするマテリアライズドビューの主キーが自動的に生成されます。主キーを指定することもできます。主キーは、次のルールに基づいて生成されます。ステートメントにGROUP BY句が含まれている場合、主キーはGROUP BY列です。たとえば、`GROUP BY a,b`が指定されている場合、主キーはa列とb列です。ステートメントに集計操作が含まれているがGROUP BY句が含まれていない場合、グローバルな一意性を確保するために主キーを定数に設定する必要があります。ステートメントに集計操作またはGROUP BY句が含まれていない場合は、主キーをベーステーブルの主キーに設定する必要があります。説明マテリアライズドビューを作成するときに、クエリ結果に含まれない列を指定することはできません。このルールは、テーブルを作成するときに使用されるルールと同じです。
`リフレッシュ高速`	必須	増分リフレッシュ方法。
`需要`	選択可能	リフレッシュのオンデマンドトリガモード。増分リフレッシュは、指定された`START WITH date`および`NEXT date`パラメーターに基づいて自動的にトリガーされます。
`START WITH date`	選択可能	マテリアライズドビューを自動的に更新する最初の時点。このパラメーターを指定しない場合は、現在の時点が使用されます。
`次の日付`	必須	マテリアライズドビューを自動的に更新する次の時点。
`クエリ体`	必須	マテリアライズドビューのクエリ本体。増分更新をサポートするマテリアライズドビューを作成する場合、クエリ本文で不定値を生成する可能性のある式を条件として指定することはできません。例: `NOW()` および`RAND()` 。 COUNT() 、SUM() 、MAX() 、MIN() 、AVG() 、APPROX_DISTINCT() 、およびCOUNT(DISTINCT) 関数は、集約演算のためにサポートされる。説明 V3.2.2.1以降のAnalyticDB for MySQLクラスターのみがMAX() 、MIN() 、AVG() 、APPROX_DISTINCT() 、およびCOUNT(DISTINCT) が機能する。増分更新をサポートするマテリアライズドビューのクエリ本文でMAX() 、MIN() 、APPROX_DISTINCT() 、またはCOUNT(DISTINCT) 関数を使用する場合、マテリアライズドビューのベーステーブルに対してINSERT操作のみを実行できます。ベーステーブルに対して、DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATEなどのデータ削除につながる可能性のある操作を実行することはできません。そうしないと、エラーが発生します。 COUNT(DISTINCT) 関数はINTEGER型のみをサポートします。 AVG() 関数はDECIMAL型をサポートしていません。集計操作はHAVINGキーワードをサポートしていません。 COUNT(DISTINCT) 以外の集計操作は、DISTINCTキーワードをサポートしていません。

単一テーブルのマテリアライズドビューの増分更新の設定

ベーステーブルを作成します。

CREATE TABLE tbl0
(a bigint,
 b tinyint,
 c boolean,
 d decimal(15, 2),
 PRIMARY KEY(a))
DISTRIBUTED BY HASH (a);

バイナリロギング機能を有効にします。

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE tbl0 binlog=true;

増分更新をサポートするマテリアライズドビューを作成します。

1つのテーブルに基づいて10秒間隔で増分更新をサポートする単一テーブルのマテリアライズドビューを作成します。マテリアライズド・ビューには集計操作は含まれません。
```
CREATE MATERIALIZED VIEW mv0
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT a, b, c   # The system uses the primary key of the base table as the primary key of the materialized view. 
FROM tbl0
WHERE d > 1000;
```

1つのテーブルに基づいて5秒間隔で増分更新をサポートする単一テーブルのマテリアライズドビューを作成します。マテリアライズドビューには、GROUP BY列と集計操作が含まれます。

CREATE MATERIALIZED VIEW mv1
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   b, c,                # The system uses the GROUP BY columns as the primary key of the materialized view. 
   COUNT(a) AS cnt_a,   # You can perform an aggregate operation on a column. 
   sum(d) AS sum_d,     # You can perform an aggregate operation on a column. 
   b / 100 AS new_b     # You can use an expression to perform a non-aggregate operation on a column. 
FROM tbl0
WHERE ifnull(d, 1) > 0  # You can use expressions in the WHERE clause. 
GROUP BY b, c;

1つのテーブルに基づいて1分間隔で増分更新をサポートするマテリアライズドビューを作成します。マテリアライズド・ビューには、GROUP BY列のない集計操作が含まれます。

CREATE MATERIALIZED VIEW mv2
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt   # The system generates a constant as the primary key to ensure that only one record is contained in the materialized view. 
FROM tbl0;

マルチテーブルマテリアライズドビューの増分更新の設定

ベーステーブルを作成し、バイナリログ機能を有効にします。

CREATE TABLE tbl0 (
  a0 bigint,
  b0 varchar,
  c0 int,
  d0 varchar,
  e0 boolean,
  PRIMARY KEY(a0)
)
DISTRIBUTED BY HASH (a0) BINLOG=true;

CREATE TABLE tbl1 (
  a1 bigint,
  b1 varchar,
  c1 int,
  d1 varchar,
  e1 boolean,
  PRIMARY KEY(a1)
)
DISTRIBUTED BY HASH (a1) BINLOG=true;

CREATE TABLE tbl2 (
  a2 bigint,
  b2 varchar,
  c2 int,
  d2 varchar,
  e2 boolean,
  PRIMARY KEY(a2)
)
DISTRIBUTED BY HASH (a2) BINLOG=true;

増分更新をサポートするマテリアライズドビューを作成します。
- 複数のテーブルに基づいて5秒間隔で増分更新をサポートする複数テーブルのマテリアライズドビューを作成します。マテリアライズド・ビューには集計操作は含まれません。
```
CREATE MATERIALIZED VIEW mv3
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT a0, a1, a2, (c0 + c1 + c2) AS c
FROM tbl0
JOIN tbl1 ON b1 = b0
JOIN tbl2 ON b2 = b1;
```
- 複数のテーブルに基づいて10秒間隔で増分更新をサポートする複数テーブルのマテリアライズドビューを作成します。マテリアライズドビューには、GROUP BY列と集計操作が含まれます。
```
CREATE MATERIALIZED VIEW mv4
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT 
d0, d1, d2,                
COUNT(*) AS cnt,           
sum(c1) AS sum_c1,         
sum(c2) AS sum_c2          
FROM tbl0
JOIN (SELECT b1, c1, d1 FROM tbl1) ON b1 = b0
JOIN (SELECT * FROM tbl2 WHERE e2 = true) ON b2 = b1
GROUP BY d0, d1, d2;
```