AUTO INCREMENT と グローバル辞書 を使用した COUNT(DISTINCT) と ジョイン の高速化
このトピックでは、AUTO INCREMENT 列とグローバル辞書を使用して COUNT(DISTINCT) 計算とジョインを高速化する方法について説明します。
ユースケース
-
シナリオ 1: 大量のデータ（小売や配送注文など）に対して正確な重複排除を行う必要があるとします。しかし、重複排除のための列が STRING 型であるため、カウント時にパフォーマンスが最適でない可能性があります。例えば、
ordersテーブルのorder_uuid列は STRING 型で、通常 32 から 36 バイトのサイズで、UUID()や類似の関数によって生成されます。この場合、order_uuidの STRING 列に対する COUNT(DISTINCT) クエリSELECT count(DISTINCT order_uuid) FROM orders WHERE create_date >= CURDATE();は満足のいくパフォーマンスを提供しないかもしれません。正確な重複排除のために INTEGER 列を使用すると、パフォーマンスが大幅に向上します。 -
シナリオ 2: ビットマップ関数を使用して多次元分析における正確な重複排除を高速化したい とします。
bitmap_count()関数は INTEGER 入力を必要としますが、重複排除する列が STRING 型の場合、bitmap_hash()関数を使用する必要があります。これにより、重複排除のカウントが近似的でやや低くなる可能性があり、クエリパフォーマンスが低下し、ストレージ要件が増加する可能性があります。これは、bitmap_hash() によって生成された INTEGER 値が、順次割り当てられた INTEGER 値と比較してより分散しているためです。 -
シナリオ 3: 注文の発注と支払いの間の短い時間間隔での注文数をクエリする必要があるとします。注文の発注時間と支払い時間は、異なるビジネスチームによって管理される異なるテーブルに保存されているかもしれません。これらのテーブルを注文 ID に基づいてジョインし、注文を重複排除する必要があるかもしれません。例えば:
SELECT count(distinct order_uuid)
FROM orders_t1 as t1 JOIN orders_t2 as t2
ON t1.order_uuid = t2.order_uuid
WHERE t2.payment_time - t1.create_time <= 3600
AND create_date >= CURDATE();しかし、ジョインに STRING 型の
order_uuid列を使用することは、INTEGER 列を使用するよりも効率が悪いです。
最適化アプローチ
上記のシナリオの問題に対処するための最適化アプローチは、注文データをターゲットテーブルにロードし、STRING 値と INTEGER 値の間にマッピングを確立することです。その後のクエリ分析は INTEGER 列に基づいて行われます。このアプローチは以下の段階に分けられます。
-
ステージ 1: グローバル辞書を作成し、STRING 値と INTEGER 値の間にマッピングを確立します。この辞書では、キー列は STRING 型で、値列は AUTO INCREMENT INTEGER 型です。データがロードされると、システムは各 STRING 値に一意の ID を自動的に生成し、STRING 値と INTEGER 値の間にマッピングを作成します。
-
ステージ 2: 注文データとグローバル辞書の間のマッピング関係をターゲットテーブルにロードします。
-
ステージ 3: その後のクエリ分析で、ターゲットテーブルの INTEGER 列を使用して正確な重複排除やジョインを行い、パフォーマンスを大幅に向上させます。
-
ステージ 4: さらなるパフォーマンスの最適化のために、INTEGER 列にビットマップ関数を使用して正確な重複排除を高速化することができます。
ソリューション
v3.2.5 以前では、ステージ 2 は次の 2 つの方法で実装できました。
- 外部テーブルまたは内部テーブルを中間テーブルとして使用し、ロード前に辞書テーブルとジョインして対応する辞書 ID を取得します。
- 主キーテーブルを使用してデータロードを行い、その後、ジョイン操作を伴う UPDATE 文を使用して辞書 ID を更新します。しかし、このデータロードプロセスは不便で、多くの制約があります。
v3.2.5 以降、StarRocks は dict_mapping() 関数を導入し、ターゲットテーブルの辞書 ID 列を dict_mapping() 式を使用して生成列として定義できるようになりました。その後のデータロードタスクは通常のデータロードのように処理され、辞書 ID を書き込むためにジョイン操作を伴う UPDATE 文を必要としません。データロード中に、システムは元のテーブルを辞書テーブルと自動的に関連付け、対応する辞書 ID を挿入します。これにより、テーブルタイプに関係なく、さまざまなロード 方法をサポートし、グローバル辞書テーブルを使用したデータロードプロセスが大幅に簡素化されます。
ビジネスシナリオ
以下の例では、id と order_uuid の 2 列を含む 2 つの例の CSV ファイル batch1.csv と batch2.csv を使用します。
-
batch1.csv1, a1
2, a2
3, a3
11, a1
11, a2
12, a1 -
batch2.csv1, a2
2, a2
3, a2
11, a2
12, a101
12, a102
13, a102
プロセス
ステージ 1
グローバル辞書テーブルを作成し、CSV ファイルから注文 ID 列の値をロードして STRING 値と INTEGER 値の間にマッピングを確立します。
-
グローバル辞書として機能する主キーテーブルを作成します。主キー
order_uuid（STRING 型）と値列order_id_int（AUTO INCREMENT INTEGER 型）を定義します。備考dict_mapping関数は、グローバル辞書テーブルが主キーテーブルであることを要求します。CREATE TABLE dict (
order_uuid STRING,
order_id_int BIGINT AUTO_INCREMENT -- 各 order_uuid 値に自動的に ID を割り当てます。
)
PRIMARY KEY (order_uuid)
DISTRIBUTED BY HASH (order_uuid)
PROPERTIES("replicated_storage" = "true"); -
Stream Load を使用して、2 つの CSV ファイルから
order_uuid列を辞書テーブルdictのorder_uuid列にバッチロードします。列モードで部分更新を使用したことを確認してください。curl --location-trusted -u root: \
-H "partial_update: true" \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load
curl --location-trusted -u root: \
-H "partial_update: true" \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load
NOTE
次のステージに進む前にデータソースに新しいデータが追加された場合、すべての新しいデータを辞書テーブルにロードして、マッピングが存在することを確認する必要があります。
ステージ 2
辞書 ID 列に dict_mapping 属性を持つターゲットテーブルを作成します。注文データがターゲットテーブルにロードされると、システムは自動的に辞書テーブルと関連付け、対応する辞書 ID を挿入します。
-
CSV ファイルのすべての列を含む
dest_tableテーブルを作成します。また、STRING 型のorder_uuid列とマッピングする INTEGER 列order_id_int（通常は BIGINT）を定義し、dict_mapping列属性を持たせる必要があります。将来のクエリ分析はこのorder_id_int列に基づいて行われます。-- ターゲットテーブルで、STRING 型の列 `order_uuid` とマッピングする BIGINT dict_mapping 列 `order_id_int` を定義します。
CREATE TABLE dest_table (
id BIGINT,
order_uuid STRING, -- この列は STRING 型の注文 ID を記録します。
batch INT comment '異なるバッチロードを区別するために使用されます',
order_id_int BIGINT AS dict_mapping('dict', order_uuid) -- 辞書 ID dict_mapping 列は `order_uuid` に対応します。
)
DUPLICATE KEY (id, order_uuid)
DISTRIBUTED BY HASH(id); -
Stream Load または他の利用可能な方法でターゲットテーブルにデータをロードします。
order_id_int列がdict_mapping属性を持っているため、システムはロード中にdictから辞書 ID を自動的に取得します。curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=1" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load
curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=2" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load
ステージ 3
クエリ分析中に、INTEGER 列 order_id_int で正確な重複排除やジョインを行うことができ、STRING 列 order_uuid を使用するよりもパフォーマンスが大幅に向上します。
-- BIGINT 型の order_id_int に基づく正確な重複排除。
SELECT id, COUNT(DISTINCT order_id_int) FROM dest_table GROUP BY id ORDER BY id;
-- STRING 型の order_uuid に基づく正確な重複排除。
SELECT id, COUNT(DISTINCT order_uuid) FROM dest_table GROUP BY id ORDER BY id;
また、ビットマップ関数を使用して正確な重複排除を高速化する こともできます。
ビットマップ関数を使用して正確な重複排除を高速化する
計算をさらに高速化するために、グローバル辞書を作成した後、辞書テーブルの INTEGER 列の値をビットマップ列に直接挿入することができます。その後、このビットマップ列にビットマップ関数を使用して正確な重複排除を行います。
方法 1
グローバル辞書を構築し、すでに dest_table に注文データをインポートしている場合は、次の手順に従います。
-
2 つの列を持つ集計テーブル
dest_table_bitmapを作成します。1 つはbitmap_union()関数を使用して集計するための BITMAP 型のorder_id_bitmap列、もう 1 つは INTEGER 型のid列です。このテーブルには元の STRING 列は含まれていません。そうでないと、各ビットマップには 1 つの値しか含まれず、加速効果が失われます。CREATE TABLE dest_table_bitmap (
id BIGINT,
order_id_bitmap BITMAP BITMAP_UNION
)
AGGREGATE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 6; -
dest_table_bitmapにデータを挿入します。dest_tableのid列のデータをid列に挿入し、辞書テーブルdictの INTEGER 列order_id_intのデータをto_bitmap()関数で処理してorder_id_bitmap列に挿入します。INSERT INTO dest_table_bitmap (id, order_id_bitmap)
SELECT id, to_bitmap(dict_mapping('dict', order_uuid))
FROM dest_table
WHERE dest_table.batch = 1; -- 異なるバッチを示します。
INSERT INTO dest_table_bitmap (id, order_id_bitmap)
SELECT id, to_bitmap(dict_mapping('dict', order_uuid))
FROM dest_table
WHERE dest_table.batch = 2; -
BITMAP 列に
BITMAP_UNION_COUNT()関数を使用して正確な重複排除を行います。SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
GROUP BY id ORDER BY id;
方法 2
グローバル辞書を作成した後、特定の注文データを保持する必要がなく、データを直接 dest_table_bitmap テーブルにロードしたい場合は、次の手順に従います。
-
2 つの列を持つ集計テーブル
dest_table_bitmapを作成します。1 つはbitmap_union()関数を使用して集計するための BITMAP 型のorder_id_bitmap列、もう 1 つは INTEGER 型のid列です。このテーブルには元の STRING 列は含まれていません。そうでないと、各ビットマップには 1 つの値しか含まれず、加速効果が失われます。CREATE TABLE dest_table_bitmap (
id BIGINT,
order_id_bitmap BITMAP BITMAP_UNION
)
AGGREGATE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 6; -
集計テーブルにデータを挿入します。CSV ファイル の
id列のデータをid列に挿入し、辞書テーブルdictの INTEGER 列order_id_intのデータをto_bitmap()関数で処理してorder_id_bitmap列に挿入します。curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," \
-H "columns: id, order_uuid, order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load
curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," \
-H "columns: id, order_uuid, order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load -
BITMAP 列に
BITMAP_UNION_COUNT()関数を使用して正確な重複排除を行います。SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
GROUP BY id ORDER BY id;