AUTO INCREMENT とグローバル辞書を使用した COUNT(DISTINCT) とジョインの高速化
このトピックでは、AUTO INCREMENT 列とグローバル辞書を使用して、COUNT(DISTINCT) 計算とジョインを高速化する方法について説明します。
ユースケース
-
シナリオ 1: 大量のデータ（小売や配送の注文など）に対して正確な重複排除を行う必要があるとします。しかし、重複排除のための列が STRING 型であるため、カウント中にパフォーマンスが最適でない可能性があります。例えば、
ordersテーブルのorder_uuid列は、注文 ID を表す STRING 型で、通常 32 から 36 バイトのサイズで、UUID()や類似の関数によって生成されます。この場合、STRING 列order_uuidに対する 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() 式を使用して生成列として定義できるようにしました。後続のデータロードタスクは、ジョイン操作を伴う UPDATE ステートメントを使用せずに、通常のデータロードのように処理されます。データロード中に、システムは元のテーブルを辞書テーブルと自動的に関連付け、対応する辞書 ID を挿入し、テーブルタイプに関係なく、さまざまなロード方法をサポートし、グローバル辞書テーブルを使用したデータロードプロセスを大幅に簡素化します。
ビジネスシナリオ
以下の例では、batch1.csv と batch2.csv の 2 つの例の CSV ファイルを使用します。それぞれのファイルには、id と order_uuid の 2 つの列が含まれています。
-
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
注意
次のステージに進む前にデータソースに新しいデータが追加された場合、すべての新しいデータを辞書テーブルにロードして、マッピングが存在することを確認する必要があります。
ステージ 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を作成します:bitmap_union()関数を使用して集計するための BITMAP 型order_id_bitmap列と 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を作成します:bitmap_union()関数を使用して集計するための BITMAP 型order_id_bitmap列と 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;