Synchronous materialized views
同期素材化ビュー
このトピックでは、同期素材化ビュー (Rollup) の作成、使用、管理方法について説明します。
同期素材化ビューでは、ベーステーブルの変更は同期素材化ビューにも同時に更新されます。同期素材化ビューの更新は自動的にトリガされます。同期素材化ビューはメンテナンスや更新が非常に低コストで行えるため、リアルタイムで単一テーブルの集計クエリを透明に高速化するのに適しています。
StarRocksでは、同期素材化ビューはデフォルトカタログの単一のベーステーブル上のみ作成できます。同期素材化ビューは非同期素材化ビューのような物理テーブルではなく、クエリの高速化のための特別なインデックスです。
StarRocksでは、バージョン2.4以降で多数のテーブルと集計演算子をサポートする非同期素材化ビューが提供されています。非同期素材化ビューの使用方法については、非同期素材化ビューを参照してください。
次の表は、StarRocks v2.5、v2.4の非同期素材化ビュー（ASYNC MV）と同期素材化ビュー（SYNC MV）の各機能の対比を示しています。
単一テーブルの集計 | 複数テーブルの結 合 | クエリの書き換え | リフレッシュ戦略 | ベーステーブル | |
|---|---|---|---|---|---|
ASYNC MV | はい | はい | はい |
| デフォルトカタログ 外部カタログ（v2.5） 既存の素材化ビュー（v2.5） 既存のビュー（v3.1） |
SYNC MV (Rollup) | 集計関数の制約 の制約付き | いいえ | はい | データロード中の同期リフレッシュ | デフォルトカタログの単一テーブル |
基本的な概念
- ベーステーブルベーステーブルは、素材化ビューの駆動テーブルです。StarRocksの同期素材化ビューでは、ベーステーブルはデフォルトカタログの単一のネイティブテーブルである必要があります。StarRocksは、重複キーや集計テーブル、一意キーテーブルに対して同期素材化ビューを作成することができます。
- リフレッシュ同期素材化ビューは、ベーステーブルのデータが変更されるたびに自動的に更新されます。リフレッシュを手動でトリガする必要はありません。
- クエリの書き換えクエリの書き換えとは、素材化ビューが構築されたベーステーブル上でクエリを実行する際に、システムが事前計算済みの素材化ビューの結果をクエリに再利用できるかどうかを自動的に判断し、クエリに対して時間とリソースを消費する演算処理や結合処理を回避するために関連する素材化ビューからデータを直接読み込むことを意味します。同期素材化ビューは、いくつかの集計演算子に基づいてクエリの書き換えをサポートしています。詳細については、集計関数の対応を参照してください。
準備
同期素材化ビューを作成する前に、データウェアハウスが同期素材化ビューを介したクエリの高速化に適しているかどうかを確認します。たとえば、特定のサブクエリ文を再利用するクエリがあるかどうかを確認します。
次の例は、各トランザクションのトランザクションID record_id、セールスパーソンID seller_id、ストアID store_id、日付 sale_date、販売金額 sale_amt を含む sales_records テーブルをベースにしています。以下の手順に従ってテーブルを作成し、データを挿入します。
CREATE TABLE sales_records(
record_id INT,
seller_id INT,
store_id INT,
sale_date DATE,
sale_amt BIGINT
) DISTRIBUTED BY HASH(record_id);
INSERT INTO sales_records
VALUES
(001,01,1,"2022-03-13",8573),
(002,02,2,"2022-03-14",6948),
(003,01,1,"2022-03-14",4319),
(004,03,3,"2022-03-15",8734),
(005,03,3,"2022-03-16",4212),
(006,02,2,"2022-03-17",9515);
この例のビジネスシナリオでは、さまざまな店舗の販売金額を頻繁に分析する必要 があります。結果として、各クエリで SUM() 関数が使用され、膨大な計算リソースを消費します。クエリの時間を記録し、EXPLAINコマンドを使用してクエリプロファイルを表示することができます。
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.02 sec)
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: sales_records |
| tabletRatio=10/10 |
| tabletList=12049,12053,12057,12061,12065,12069,12073,12077,12081,12085 |
| cardinality=1 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
クエリの所要時間は約0.02秒であり、クエリプロファイルの rollup フィールドの値が sales_records であり、これはベーステーブルであるため、同期素材化ビューはクエリの高速化に使用されていません。
同期素材化ビューの作成
CREATE MATERIALIZED VIEWを使用して、特定のクエリ文に基づいて同期素材化ビューを作成できます。
テーブル sales_records と上記のクエリ文に基づいて、以下の例では同期素材化ビュー store_amt を作成して各店舗の販売金額の合計を分析します。
CREATE MATERIALIZED VIEW store_amt AS
SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
注意
- 同期素材化ビューで集計関数を使用する場合、SELECTリストでGROUP BY句を使用し、少なくとも1つのGROUP BYカラムを指定する必要があります。
- 同期素材化ビューでは、1つのカラムに複数の集計関数を使用することはサポートされていません。
sum(a+b)のようなクエリ文はサポートされていません。- 同期素材化ビューでは、1つのカラムに複数の集計関数を使用することはサポートされていません。
select sum(a), min(a) from tableのようなクエリ文はサポートされていません。- 結合やWHERE句は、同期素材化ビューの作成時にはサポー トされていません。
- ベーステーブルの特定の列をALTER TABLE DROP COLUMNで削除する場合、ベーステーブルのすべての同期素材化ビューが削除する列を含まないことを確認する必要があります。同期素材化ビューで使用される列を削除するには、まずベーステーブルに含まれるすべての同期素材化ビューを削除し、その後に列を削除する必要があります。
- テーブルに対して多くの同期素材化ビューを作成すると、データのロード効率に影響を与えます。ベーステーブルに
n個の同期素材化ビューが含まれる場合、ベーステーブルへのデータのロード効率は、n個のテーブルへのデータのロード効率とほぼ同じです。- 現在、StarRocksは同時に複数の同期素材化ビューの作成をサポートしていません。新しい同期素材化ビューは、前の作業が完了した後にのみ作成できます。
同期素材化ビューの構築状態の確認
同期素材化ビューの作成は非同期の操作です。CREATE MATERIALIZED VIEWを成功させると、同期素材化ビューの構築タスクが正常に送信されたことを示します。SHOW ALTER MATERIALIZED VIEWを使用して、データベース内の同期素材化ビューのビルド状態を表示できます。
MySQL > SHOW ALTER MATERIALIZED VIEW\G
*************************** 1. row ***************************
JobId: 12090
TableName: sales_records
CreateTime: 2022-08-25 19:41:10
FinishedTime: 2022-08-25 19:41:39
BaseIndexName: sales_records
RollupIndexName: store_amt
RollupId: 12091
TransactionId: 10
State: FINISHED
Msg:
Progress: NULL
Timeout: 86400
1 row in set (0.00 sec)
RollupIndexName セクションは、同期素材化ビューの名前を示し、State セクションはビルドが完了したかどうかを示します。
同期素材化ビューを直接クエリする
同期素材化ビューは物理テーブルではなくベーステーブルのインデックスであるため、同期素材化ビューをクエリするには[SYNC_MV]ヒントを使用する必要があります。
-- ヒントの中のブラケット [] を省略しないでください。
MySQL > SELECT * FROM store_amt [_SYNC_MV_];
+----------+----------+
| store_id | sale_amt |
+----------+----------+
| 2 | 6948 |
| 3 | 8734 |
| 1 | 4319 |
| 2 | 9515 |
| 3 | 4212 |
| 1 | 8573 |
+----------+----------+
注意
現在、StarRocksは、同期素材化ビューで列の別名を指定していても、自動的に列の名前を生成します。
同期素材化ビューを使用したクエリの書き換えと高速化
作成した同期素材化ビューには、クエリ文に基づいた完全な事前計算済みの結果が含まれています。その後のクエリでは、そのデータが使用されます。準備で行ったように、同じクエリを実行してクエリ時間をテストできます。
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.01 sec)
クエリ時間が0.01秒に短縮されたことがわかります。
クエリが同期素材化ビューにヒットするか確認する
再度EXPLAINコマンドを実行して、クエリが同期素材化ビューにヒットするかどうかを確認します。
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: store_amt |
| tabletRatio=10/10 |
| tabletList=12092,12096,12100,12104,12108,12112,12116,12120,12124,12128 |
| cardinality=6 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
クエリプロファイルの rollup セクションの値が store_amt に変わっているこ とがわかります。これは作成した同期素材化ビューを示しており、このクエリが同期素材化ビューにヒットしていることを意味します。
同期素材化ビューの表示
DESC <tbl_name> ALLを実行して、テーブルのスキーマとそれに属する同期素材化ビューの構造を確認できます。
MySQL > DESC sales_records ALL;
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | |
| | | seller_id | INT | Yes | true | NULL | |
| | | store_id | INT | Yes | true | NULL | |
| | | sale_date | DATE | Yes | false | NULL | NONE |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE |
| | | | | | | | |
| store_amt | AGG_KEYS | store_id | INT | Yes | true | NULL | |
| | | sale_amt | BIGINT | Yes | false | NULL | SUM |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
8 rows in set (0.00 sec)
同期素材化ビューの削除
同期素材化ビューを削除する場合の次のケースがあります。
- 誤った素材化ビューを作成したので、ビルドが完了する前に削除する必要がある場合。
- 多数の素材化ビューを作成したため、ロード性能が大幅に低下し、いくつかの素材化ビューが重複している場合。
- 関連するクエリの頻度が低く、比較的高いクエリのレイテンシを許容できる場合。
完了していない同期素材化ビューの削除
作成中の同期素材化ビューをキャンセルして削除するには、進行中の作成タスクをキャンセルして同期素材化ビューの作成タスクをキャンセルする必要があります。まず、同期素材化ビューの構築状態の確認で、素材化ビューの作成タスクのジョブID JobID を取得します。ジョブIDを取得したら、CANCEL ALTERコマンドを使用して作成タスクをキャンセルします。
CANCEL ALTER TABLE ROLLUP FROM sales_records (12090);
既存の同期素材化ビューの削除
DROP MATERIALIZED VIEWコマンドを使用して、既存の同期素材化ビューを削除できます。
DROP MATERIALIZED VIEW store_amt;
ベストプラクティス
正確な重複カウント
以下の例は、広告事業の分析テーブル advertiser_view_record を基にしています。このテーブルでは、広告が視聴された日付 click_time、広告の名前 advertiser、広告のチャネル channel、視聴したユーザーのID user_id が記録されます。
CREATE TABLE advertiser_view_record(
click_time DATE,
advertiser VARCHAR(10),
channel VARCHAR(10),
user_id INT
) distributed BY hash(click_time);
分析は主 に広告のUVに焦点を当てています。
SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;
正確な重複カウントを高速化するためには、このテーブルに基づいた同期素材化ビューを作成し、bitmap_union 関数を使用してデータを事前集約できます。
CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
同期素材化ビューが作成された後、その後のクエリ でのサブクエリ count(distinct user_id) は自動的に bitmap_union_count (to_bitmap(user_id)) に書き換えられますので、同期素材化ビューにヒットするようになります。
近似の重複カウント
再び、上記のテーブル advertiser_view_record を使用して近似の重複カウントを高速化するには、このテーブルに基づいた同期素 材化ビューを作成し、hll_union() 関数を使用してデータを事前集約できます。
CREATE MATERIALIZED VIEW advertiser_uv2 AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
追加のソートキーの設定
ベーステーブル tableA には、k1、k2、k3 の列が含まれており、ソートキーが指定されているのは k1 と k2 のみです。k3=x のサブクエリを含むクエリを高速化する必要がある場合、k3 を最初の列として持つ同期素材化ビューを作成できます。
CREATE MATERIALIZED VIEW k3_as_key AS
SELECT k3, k2, k1
FROM tableA
集計関数の対応
同期素材化ビューを使用してクエリを実行すると、元のクエリ文が自動的に書き換えられ、同期素材化ビューに格納されている中間結果をクエリするために使用されます。次の表は、元のクエリでの集計関数と同期素材化ビューの構築 に使用される集計関数の対応を示しています。ビジネスシナリオに応じて対応する集計関数を選択して同期素材化ビューを作成できます。
元のクエリでの集計関数 | 同期素材化ビューでの集計関数 |
|---|---|
sum | sum |
min | min |
max | max |
count | count |
bitmap_union、bitmap_union_count、count(distinct) | bitmap_union |
hll_raw_agg、hll_union_agg、ndv、approx_count_distinct | hll_union |