数億件のデータレコードに対するニアリアルタイムUV計算 - Hologres

Hologresは、事前集計に基づくニアリアルタイムのユニークビジター（UV）計算ソリューションを提供します。このソリューションを使用すると、1秒あたりのクエリ数（QPS）が高いレベルで、数億件のデータレコードに対してUV計算を実行できます。このソリューションでは、roaring bitmapsと定期的なスケジューリング方法を使用してデータを事前集計し、カスタム期間のUV数をカウントできます。

説明

ビジネスで大量のデータが扱われ、高QPSと低レイテンシが求められる場合は、Hologresのroaring bitmapsと定期的なスケジューリング方法を使用してデータを事前集計できます。これにより、カスタム期間のUV数をカウントできます。

長所と短所
- 長所：このソリューションは優れた計算パフォーマンスを提供し、正確なカーディナリティ推定に基づいて、高QPSと低レイテンシでのUV計算をサポートします。また、カスタム期間を設定することもできます。
- 短所：事前計算を実行し、集計テーブルのデータを定期的に更新する必要があるため、メンテナンス作業の負荷が増加します。
シナリオ：このソリューションは、カスタムの長期にわたって高QPSで数億件のデータレコードに対してUV計算を実行するのに適しています。

roaring bitmapsの使用方法データ型と使用シナリオによって異なります。ビジネス要件に基づいて方法を選択できます。

方法 1：INT型フィールドに基づく長期のUV計算：この方法は、単一のタグに基づいてデータをフィルタリングし、結果データのINT型フィールドに対して正確なカーディナリティ推定を実行するシナリオに適しています。カーディナリティとは、個別値の数のことです。
方法 2：TEXT型フィールドに基づく長期のUV計算：この方法は、単一のタグに基づいてデータをフィルタリングし、結果データのTEXT型フィールドに対して正確なカーディナリティ推定を実行するシナリオに適しています。この方法では、マッピングテーブルが必要です。
方法 3（高度な方法）：バケットベースの長期UV計算：この方法は、主にプロファイル分析シナリオで使用されます。この方法では、複数のタグまたはプロパティに基づいて積集合、和集合、XOR演算を実行して、ユーザーカーディナリティを決定します。さらに、ユーザーデータはバケットに分散されて並列クエリをサポートします。これにより、ユーザーデータが高度に圧縮され、ユーザーデータに対するI/O操作が削減され、計算効率が向上します。

説明

Roaring bitmapsは、UV計算やその他の正確なカーディナリティ推定シナリオに使用できます。たとえば、ライブコマースのシナリオでは、ダッシュボードに製品数とブランド数を表示する必要があります。

Roaring bitmap関数の詳細については、「Roaring bitmap関数」をご参照ください。

方法 1：roaring bitmapsを使用したINT型フィールドに基づく長期のUV計算

シナリオ

この方法は、INT型のユーザーID（UID）に基づいて、カスタムの長期にわたって高QPSで数億件のデータレコードに対してUV計算を実行する必要があるシナリオに適しています。

手順

このセクションでは、この方法を実装するために使用される手順について説明します。

手順 1：すべてのビジネスディメンションの詳細データを格納するユーザーファクトテーブルを作成します。

手順 2：GROUP BY句で指定された基本ディメンションに基づいてファクトテーブルを集計し、集計されたUIDをroaring bitmapsとして集計結果テーブルに格納します。

手順 3：クエリディメンションに基づいて集計結果テーブルをクエリします。この手順では、重複排除のためにroaring bitmapフィールドに対してOR演算を実行します。これにより、UV数をカウントし、集計結果テーブルのレコード数をカウントして、ページビュー（PV）数を求めることができます。結果は数ミリ秒以内に返されます。

手順 1：基本データを準備する

Roaring bitmap拡張機能を作成します。
Roaring bitmapsを使用する前に、roaring bitmap拡張機能を作成する必要があります。拡張機能はデータベースレベルで作成されます。各データベースでは、拡張機能を1回だけ作成する必要があります。構文：
説明
publicスキーマにroaring bitmap拡張機能を読み込む必要があります。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

ユーザーファクトテーブルを作成します。

完全で詳細なユーザーデータを格納するユーザーファクトテーブルを作成します。ほとんどの場合、ファクトテーブルは、完全で詳細なユーザーデータを格納する大規模なテーブルです。データの書き込みと更新を容易にするために、日ごとにパーティション分割されたファクトテーブルを作成することをお勧めします。

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid int,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
)
PARTITION BY LIST (ymd) ;-- ファクトテーブルには大量のデータが含まれているため、日ごとにパーティションを設定します。
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
-- リアルタイムのクエリ要件に基づいて、テーブルの分散キーを指定します。データは、分散キーに基づいてシャードに分散されます。
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
-- WHERE句で使用できるフィールドを指定します。年、月、日付の情報を含むフィールドをクラスタリングキーとイベント時間列として設定することをお勧めします。
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

Roaring bitmapsの集計結果テーブルを作成します。
集計されたroaring bitmapsを格納する集計結果テーブルを作成します。
- 集計後、1日ごとに数百万件のデータレコードのみが集計結果テーブルに格納されます。データの書き戻しを容易にするために、パーティション分割されていない集計結果テーブルを作成することをお勧めします。月または四半期ごとにパーティション分割された集計結果テーブルを作成することもできます。日ごとにパーティションを設定すると、各パーティションのデータ量が少なくなり、パーティション分割によって多数の小さなファイルが生成されます。これにより、メモリ使用量が増加します。
- 国、州、市などのディメンションに基づいて集計結果テーブルを作成し、ディメンションフィールドを分散キーフィールドとして設定します。これにより、ディメンションベースのクエリが容易になります。GROUP BY句に3つ以上のフィールドが指定されている場合は、最も頻繁に使用されるフィールドを分散キーフィールドとして設定することをお勧めします。
- データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドを主キーフィールドとして設定します。
- データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として設定します。
サンプルコード：
```
BEGIN;
CREATE TABLE dws_app_rb(
  rb_uid roaringbitmap, -- UV計算。
  country text,
  prov text,
  city text,
  ymd text NOT NULL, -- 日付フィールド。
  pv integer, -- PV計算。
  PRIMARY key(country, prov, city, ymd)-- データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドを主キーフィールドとして設定します。
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
-- データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として設定します。
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
-- GROUP BY句で指定されたフィールドを分散キーフィールドとして設定します。
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;
```

手順 2：roaring bitmapsを使用して集計結果テーブルを作成する。

ファクトテーブルを作成したら、結果データを集計結果テーブルに書き込むことによって、集計結果テーブルを作成できます。サンプルコード：

-- 例：ファクトテーブルから指定された6か月間に生成されたデータをクエリし、クエリされたデータをroaring bitmapsに変換し、roaring bitmapsを集計結果テーブルに書き込みます。
INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd >= '20231201' AND ymd <='20240502'
GROUP BY country,prov,city,ymd;

ファクトテーブルが更新された場合は、ファクトテーブルの更新粒度に基づいて、集計結果テーブルの増分データまたは全データを更新できます。次の表に、増分更新と全更新の違いを示します。

集計結果テーブルの更新モード

説明

サンプルコード

増分更新

ファクトテーブルのデータが定期的に更新される場合（たとえば、前日のデータが格納されているパーティションのみが更新される場合）、INSERTステートメントを実行して、増分データを集計結果テーブルに書き込むことができます。

増分データのみを書き込みます：

INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd = '20240503'
GROUP BY country,prov,city,ymd;

全更新

ファクトテーブルのデータが定期的に更新されず、増分データをすばやく識別できない場合は、全データ書き戻し方法を使用して集計結果テーブルを更新できます。

INSERT OVERWRITEステートメントを実行して、集計結果テーブルのすべてのデータを更新します。

-- INSERT OVERWRITEステートメントを実行して、集計結果テーブルのすべてのデータを更新します。
CALL hg_insert_overwrite('public.dws_app_rb' , $$SELECT
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1) FROM ods_app_detail 
 WHERE ymd >= '20231201' AND ymd <='20240503'
 GROUP BY country,prov,city,ymd$$);

手順 3：カスタムの長期におけるUV数をカウントする

基本ディメンションのカスタムの組み合わせに基づいて、カスタム期間のUV数とPV数をカウントできます。ほとんどの場合、クエリ結果はミリ秒単位で返されます。この方法は、従来のSQLステートメントを実行するよりも効率的で、高QPSもサポートしています。例：

特定の日のUV数とPV数をカウントします。

SELECT  
RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM    dws_app_rb
WHERE   ymd = '20240329'
GROUP BY country,prov,city;

特定の月のUV数とPV数をカウントします。

SELECT  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM dws_app_rb
WHERE   ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country,prov,city;

方法 2：Roaring Bitmap を使用した TEXT 型フィールドとマッピングテーブルに基づく長期間の UV 計算

実際のビジネスシナリオでは、ほとんどのテーブルの ID フィールドは TEXT 型です。ただし、Roaring Bitmap は TEXT 型をサポートしていません。したがって、SERIAL 型のフィールドを使用してマッピングテーブルを作成する必要があります。これにより、Roaring Bitmap を使用して効率的な UV 計算を実行できます。

シナリオ

この方法は、TEXT 型のフィールドに基づいて、数億件のデータレコードのカスタム期間および長期間の UV 計算を高 QPS で実行する必要があるシナリオに適しています。この方法では、TEXT 型のフィールドに対して正確な基数推定が実行されます。

手順

このセクションでは、この方法を実装するために使用される手順について説明します。

手順 1：すべてのビジネスディメンションの詳細データを格納するユーザーファクトテーブルを作成します。履歴ユーザーの UID とマッピングされた 32 ビット整数を格納する UID マッピングテーブルを作成します。

手順 2：ファクトテーブルと UID マッピングテーブルを結合し、最も細かい基本ディメンションに基づいてデータに対して GROUP BY 操作を実行し、前日のすべてのデータを最大のクエリディメンションに基づいて UID に集約します。次に、UID を Roaring Bitmap として集約結果テーブルに格納します。集約結果テーブルには、毎日数百万件のデータレコードが格納されます。

手順 3：クエリディメンションに基づいて集約結果テーブルをクエリします。この手順では、重複排除のために Roaring Bitmap フィールドに対して OR 演算を実行します。このようにして、UV の数をカウントし、集約結果テーブルのレコード数をカウントして PV の数を求めることができます。結果は数ミリ秒以内に返されます。

手順 1：基本データを準備する

Roaring Bitmap エクステンションを作成します。
Roaring Bitmap を使用する前に、Roaring Bitmap エクステンションを作成する必要があります。エクステンションはデータベースレベルで作成されます。各データベースについて、エクステンションを作成する必要があるのは 1 回だけです。構文：
説明
public スキーマに roaring bitmap エクステンションを読み込む必要があります。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

ユーザーファクトテーブルを作成します。

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid text,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
)
PARTITION BY LIST (ymd) ;-- ファクトテーブルには大量のデータが含まれているため、日ごとにパーティションを構成します。
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
-- リアルタイムのクエリ要件に基づいて、テーブルの分散キーを指定します。データは、分散キーに基づいてシャードに分散されます。
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
-- WHERE 句で使用できるフィールドを指定します。年、月、日付の情報を含むフィールドをクラスタリングキーとイベント時間列として構成することをお勧めします。
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

Roaring Bitmap の集約結果テーブルを作成します。
集約された Roaring Bitmap を格納する集約結果テーブルを作成します。
- 集約後、集約結果テーブルには毎日数百万件のデータレコードのみが格納されます。データの書き戻しを容易にするために、パーティション分割されていない集約結果テーブルを作成することをお勧めします。月または四半期ごとにパーティション分割された集約結果テーブルを作成することもできます。日ごとにパーティションを構成すると、各パーティションのデータ量が少なくなり、パーティション分割によって多数の小さなファイルが生成されます。これにより、メモリ使用量が増加します。
- 国、都道府県、市区町村などのディメンションに基づいて集約結果テーブルを作成し、ディメンションフィールドを分散キーフィールドとして構成します。これにより、ディメンションベースのクエリが容易になります。GROUP BY 句に 3 つ以上のフィールドが指定されている場合は、最も頻繁に使用されるフィールドを分散キーフィールドとして構成することをお勧めします。
- データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドを主キーフィールドとして構成します。
- データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として構成します。
サンプルコード：
```
BEGIN;
CREATE TABLE dws_app_rb(
  rb_uid roaringbitmap, -- UV 計算。
  country text,
  prov text,
  city text,
  ymd text NOT NULL, -- 日付フィールド。
  pv integer, -- PV 計算。
  PRIMARY key(country, prov, city, ymd)-- データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドを主キーフィールドとして構成します。
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
-- データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として構成します。
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
-- GROUP BY 句に指定されているフィールドを分散キーフィールドとして構成します。
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;
```
UID マッピングテーブルを作成します。
Roaring Bitmap に格納される UID は 32 ビット整数である必要があり、連続した UID が推奨されます。ただし、ビジネスシステムまたはトラッキングポイントで収集される UID は通常 TEXT 型です。したがって、SERIAL 型の列を含む UID マッピングテーブルを作成する必要があります。この列は、自動インクリメントの 32 ビット整数で構成されます。このようにして、UID マッピングは自動的に管理され、安定した状態が維持されます。
```
BEGIN;
 CREATE TABLE uid_mapping (
     uid text NOT NULL,
     uid_int32 serial,
    PRIMARY KEY (uid)
 );
 -- UID に対応する 32 ビット整数をすばやく見つけるために、UID 列をクラスタリングキーと分散キーとして構成します。
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;
```

手順 2：Roaring Bitmap を作成し、集約結果テーブルにインポートする

UID マッピングテーブルにデータをインポートして更新します。
1. UID マッピングテーブルを完全に初期化します。
  すべての UID データを UID マッピングテーブルにインポートし、データを初期化します。日付フィールドに基づいてデータをフィルタリングし、取得したデータを UID マッピングテーブルにインポートすることもできます。この例では、指定された 6 か月間の UID データがインポートされます。
```
-- 指定された 6 か月間のデータを UID マッピングテーブルにインポートします。
INSERT INTO uid_mapping (uid) B
SELECT distinct (uid) FROM ods_app_detail 
WHERE B.ymd >= '20231201' AND B.ymd <='20240502';
```
2. UID マッピングテーブルのデータを確認します。
  UID マッピングテーブルにデータをインポートした後、UID マッピングテーブルのデータがファクトテーブルのデータと一致するかどうかを確認します。
```
-- データを確認します。
SELECT COUNT(*) FROM uid_mapping;
```
  SERIAL 型の列の値の連続性を確認します。特定のシナリオでは、TRUNCATE 操作と INSERT 操作を実行して、UID マッピングテーブルを複数回初期化します。ただし、TRUNCATE 操作を実行しても、SERIAL 型の列の値のシーケンスはリセットされません。その結果、SERIAL 型の値が無駄になります。データを複数回インポートした後、SERIAL 型の列の値が 32 ビットを超えます。
```
-- SERIAL 型の列の値の連続性を確認します。
SELECT MAX(uid_int32),MIN(uid_int32) FROM uid_mapping;
```
3. UID マッピングテーブルを更新します。
  ファクトテーブルの UID が更新された場合は、できるだけ早く UID マッピングテーブルのデータを更新する必要があります。特定の日の UID のみがファクトテーブルで更新された場合は、INSERT ON CONFLICT ステートメントを実行して UID マッピングテーブルを更新できます。更新された UID を識別できない場合は、完全なデータの書き戻しを実行できますが、大量のデータが関係する場合は時間がかかります。
  この例では、INSERT ON CONFLICT DO NOTHING ステートメントを実行して、UID マッピングテーブルの前日のデータを更新します。DO NOTHING は、データが更新されるが、繰り返し書き込まれないようにします。
```
-- UID マッピングテーブルの前日のデータを更新します。
INSERT INTO uid_mapping (uid)
SELECT distinct (uid) FROM ods_app_detail 
WHERE ymd = '20240503' 
ON conflict do nothing;
```
集約結果テーブルにデータをインポートして更新します。
UID マッピングテーブルを更新した後、次の手順を実行してデータを集約し、集約結果を集約結果テーブルに挿入します。
1. ファクトテーブルと UID マッピングテーブルを結合して、uid_int32 列の集約条件と 32 ビット UID を取得します。
2. 集約条件に基づいてデータを集約し、集約されたデータを Roaring Bitmap として集約結果テーブルに挿入します。ビジネス要件に基づいて、日ごとまたは月ごとにデータを集約できます。
3. データを 1 回だけ集約し、集約結果を集約結果テーブルに格納する必要があります。集約結果テーブルのデータレコード数は UV の数と同じです。
4. 次のステートメントを実行して、集約結果テーブルにデータを挿入します。この例では、指定された 6 か月間のデータが集約結果テーブルに集約されます。
```
WITH aggregation_src AS (
    SELECT
       B.uid_int32,
        A.country,
        A.prov,
        A.city,
        A.ymd
    FROM
        ods_app_detail A
        INNER JOIN uid_mapping B ON A.uid = B.uid
    WHERE
        A.ymd >= '20231201' AND A.ymd <='20240502')
INSERT INTO dws_app_rb
SELECT
    RB_BUILD_AGG (uid_int32),
    country,
    prov,
    city,
    ymd,
    COUNT(1)
FROM
    aggregation_src
GROUP BY
    country,
    prov,
    city,
    ymd;
```
5. 集約結果テーブルを更新します。
  ファクトテーブルと UID マッピングテーブルが更新された場合は、集約結果テーブルも更新する必要があります。次のルールに基づいて、増分更新モードまたは完全更新モードを使用して集約結果テーブルを更新できます。
  - 増分更新：ファクトテーブルのデータが毎日などのように定期的に更新される場合は、INSERT ステートメントを実行して、最新のパーティションの増分データを集約結果テーブルに書き込むことができます。
  - 完全更新：ファクトテーブルのデータが定期的に更新されず、増分データを識別できない場合は、INSERT OVERWRITE ステートメントを実行して、完全なデータの書き戻し方法を使用して集約結果テーブルを更新できます。

手順 3：カスタム期間および長期間の UV の数をカウントする

基本ディメンションのカスタムの組み合わせに基づいて、カスタム期間の UV の数と PV の数をカウントできます。ほとんどの場合、クエリ結果はミリ秒で返されます。この方法は、従来の SQL ステートメントを実行するよりも効率的で、高 QPS もサポートしています。例：

特定の日の UV の数と PV の数をカウントします。

SELECT  country,
        prov,
        city,
        RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
        SUM(pv) AS pv
FROM    dws_app_rb
WHERE   ymd = '20240329'
GROUP BY country,prov,city;

特定の月の UV の数と PV の数をカウントします。

-- 特定の月の UV の数と PV の数をカウントします。
SELECT  country
        ,prov
        ,RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv
        ,SUM(pv) AS pv
FROM    public.dws_app_rb
WHERE   ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country,prov,city;

方法 3（高度な方法）: roaring bitmaps を使用した拡張期間のバケットベースの UV 計算

実際のシナリオでは、カーディナリティをカウントするために複数の大きなテーブルを結合する必要がある場合があります。たとえば、プロファイル分析では、タグテーブルとプロパティテーブルを結合して、積集合、和集合、XOR 演算を実行することでユーザーのカーディナリティをカウントしたり、行動テーブルとプロパティテーブルを結合して行動のカーディナリティをカウントしたりする必要がある場合があります。Hologres の roaring bitmaps をバケットと共に使用して、効率的なカーディナリティ計算を実行できます。バケットを使用して bitmaps を複数のセグメントに分割し、セグメントを分散して並列計算を可能にすることができます。このようにして、ユーザーデータは高度に圧縮され、I/O 操作が削減され、計算効率が向上します。

シナリオ

この方法は、カスタムの長期にわたる数億件のデータレコードに対する UV 計算に適しています。ほとんどの場合、正確な重複除外が実行される列の整数の長さは長く、32 ビットを超える場合や、性別列のように列のカーディナリティが低い場合があります。この方法は、複数のタグに基づくユーザーカーディナリティ計算によく使用されます。

仕組み

データは分割され、異なるバケットに格納されます。このようにして、データは異なるシャードに分散されます。クエリのパフォーマンスは、バケット化の方法とバケットの数によって影響を受けます。

バケット化の方法: 一般的で効率的な方法は、最上位ビットと最下位ビットを計算することです。最下位ビットは bitmaps として格納され、最上位ビットはバケット ID として格納されます。
バケットの数: 通常、インスタンスの単一テーブルグループのシャード数は 256 を超えません。32 ビット整数の場合、int_value>>24 の最上位 8 ビットはバケット ID として格納され、最下位 24 ビットは bitmaps として格納されます。このようにして、データはシャードに均等に分散され、順番に格納されるため、並列処理が容易になります。
推奨されるバケット化の式:
- 方法 1: テーブルグループに n 個のシャードが含まれている場合、バケットの数は n です。 n 個のバケットに必要なビット数は、log2(n) の切り上げ結果です。シャード数が 256 以下の場合は、256 バケットを推奨します。シャード数が 256 を超える場合は、式に基づいて結果を計算します。
- 方法 2: UID が小さい数値の場合、方法 1 を使用して計算された最上位ビットが 0 になる場合があります。これは、すべてのデータが同じバケットに格納されていることを意味します。この場合は、(i>>16)%256 を使用してバケット ID を計算することをお勧めします。バケットの数は変更されず、1 つのバケットに 65,536 個の値が格納されます。これは、ストレージ効率に悪影響を与えません。

手順

以下のセクションでは、この方法の使用方法について説明します。

説明

この例では、UID フィールドは INT 型です。UID フィールドが TEXT 型の場合、方法 2 に基づいて UID マッピングテーブルを作成して、roaring bitmaps を構築できます。バケット化の方法は同じです。

手順 1: 基本データを準備する

roaring bitmap エクステンションを作成します。
roaring bitmaps を使用する前に、roaring bitmap エクステンションを作成する必要があります。エクステンションはデータベースレベルで作成されます。各データベースでは、エクステンションを 1 回だけ作成する必要があります。構文:
説明
public スキーマに roaring bitmap エクステンションを読み込む必要があります。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

ユーザー行動テーブルとプロパティテーブルを作成します。

バケットは通常、複数のテーブルを結合することによるプロファイル分析に使用されます。この例では、2 つのテーブルが必要です。1 つのユーザー行動テーブルは、完全で詳細なユーザー行動を格納するために使用され、1 つのプロパティテーブルは、性別や年齢などのユーザープロパティを格納するために使用されます。2 つのテーブルを結合して、カスタムディメンションに基づいてユーザーカーディナリティをカウントできます。

ods_user_behaviour_detail という名前のユーザー行動テーブルを作成します。

-- 詳細な行動データを格納するユーザー行動テーブルを作成します。
BEGIN;
CREATE TABLE IF NOT EXISTS ods_user_behaviour_detail (
     uid int,
     operator text,
     channel text,
     shop_id text,
     time text,
     ymd text NOT NULL
);
CALL set_table_property('ods_user_behaviour_detail', 'orientation', 'column');
-- リアルタイムクエリの要件に基づいて、テーブルの分散キーを指定します。データは、分散キーに基づいてシャードに分散されます。
CALL set_table_property('ods_user_behaviour_detail', 'distribution_key', 'uid');
-- WHERE 句で使用できるフィールドを指定します。年、月、日情報を含むフィールドをクラスタリングキーとイベント時間列として構成することをお勧めします。
CALL set_table_property('ods_user_behaviour_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_user_behaviour_detail', 'event_time_column', 'ymd');
COMMIT;

dim_userbase という名前のユーザープロパティテーブルを作成します。

-- ユーザープロパティテーブルを作成します。
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase (
     uid int, 
     age text,
     gender text,
     country text,
     prov text,
     city text
     );
CALL set_table_property('dim_userbase', 'orientation', 'column');
CALL set_table_property('dim_userbase', 'distribution_key', 'uid');
COMMIT;

roaring bitmaps を使用して集計結果テーブルを作成します。

集計された roaring bitmaps を格納する集計結果テーブルを作成します。

集計後、毎日数百万件のデータレコードのみが集計結果テーブルに格納されます。データの書き戻しを容易にするために、パーティション化されていない集計結果テーブルを作成することをお勧めします。月または四半期ごとにパーティション化された集計結果テーブルを作成することもできます。日ごとにパーティションを構成すると、各パーティションのデータ量が少なくなり、パーティション化によって多数の小さなファイルが生成されます。これにより、メモリ使用量が増加します。
国、都道府県、市などのディメンションに基づいて集計結果テーブルを作成し、ディメンションフィールドを分散キーフィールドとして構成します。これにより、ディメンションベースのクエリが容易になります。GROUP BY 句に 3 つ以上のフィールドが指定されている場合は、最も頻繁に使用されるフィールドを分散キーフィールドとして構成することをお勧めします。
データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドをプライマリキーフィールドとして構成します。
データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として構成します。

dws_user_behaviour_rb という名前の集計結果テーブルを作成します。

-- ユーザー行動テーブルの集計結果テーブルを作成します。
BEGIN;
CREATE TABLE dws_user_behaviour_rb(
  rb_uid roaringbitmap, -- UV 計算。
  bucket int NOT NULL, -- バケット化フィールド。
  operator text,
  channel text,
  shop_id text,
  time text,
  ymd text NOT NULL,
  PRIMARY key(operator,channel,shop_id,time, ymd,bucket)-- データが繰り返し挿入されないように、クエリディメンションフィールドと日付フィールドをプライマリキーフィールドとして指定します。
);
CALL set_table_property('dws_user_behaviour_rb', 'orientation', 'column');
-- データのフィルタリングを容易にするために、日付フィールドをクラスタリングキーとイベント時間列として構成します。
CALL set_table_property('dws_user_behaviour_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_user_behaviour_rb', 'event_time_column', 'ymd');
-- バケット化フィールドを分散キーとして構成します。
CALL set_table_property('dws_user_behaviour_rb', 'distribution_key', 'bucket');
END;

dim_userbase_rb という名前の集計結果テーブルを作成します。

-- ユーザープロパティテーブルの集計結果テーブルを作成します。
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase_rb (
     rb_uid roaringbitmap, -- UV 計算。
     bucket int NOT NULL, -- バケット化フィールド。
     age text,
     gender text,
     country text,
     prov text,
     city text,
     PRIMARY key(age,gender,country, prov,city,bucket)-- データが繰り返し挿入されないように、クエリディメンションフィールドをプライマリキーフィールドとして指定します。
     );
CALL set_table_property('dim_userbase_rb', 'orientation', 'column');
CALL set_table_property('dim_userbase_rb', 'distribution_key', 'bucket');-- ローカル結合を可能にするために、バケット化フィールドを分散キーとして指定します。
COMMIT;

手順 2: roaring bitmaps を構築し、集計結果テーブルにインポートする

ファクトテーブルを作成した後、ファクトテーブルから集計結果テーブルにデータを書き込み、UID をバケット化フィールドに格納する必要があります。ビジネスのデータ量とデータ分散特性に基づいてバケットを構成します。この例では、UID データは 256 個のバケットに均等に分散されます。このようにして、同じバケット内の UID データは同じシャードに分散されます。これにより、並列計算が可能になり、高パフォーマンスのクエリが実現します。サンプルコード:

ユーザー行動テーブルから集計結果テーブルにデータをインポートします。

-- ファクトテーブルから集計結果テーブルにデータを書き込みます。
INSERT INTO dws_user_behaviour_rb
SELECT  
RB_BUILD_AGG(uid),
uid >> 24 AS bucket,-- 24 ビット右にシフトします。このようにして、最上位 8 ビットはバケット ID として使用され、最下位 24 ビットは bitmaps として格納されます。
operator,
channel,
shop_id,
time,
ymd
FROM ods_user_behaviour_detail
WHERE 
 ymd >= '20231201' AND ymd <='20240503';

プロパティテーブルから集計結果テーブルにデータを書き込みます。

-- プロパティテーブルから集計結果テーブルにデータを書き込みます。
INSERT INTO dim_userbase_rb
SELECT 
RB_BUILD_AGG(uid),
uid >> 24 AS bucket,-- 24 ビット右にシフトします。このようにして、最上位 8 ビットはバケット ID として使用され、最下位 24 ビットは bitmaps として格納されます。
age,
gender,
country,
prov,
city
FROM dim_userbase
GROUP BY age,gender,country,prov,city,bucket;

ファクトテーブルが更新された場合は、ファクトテーブルの更新粒度に基づいて、集計結果テーブルの増分データまたは完全データを更新できます。

増分更新: 前日のデータが格納されているパーティションのみが更新される場合など、ファクトテーブルでデータが定期的に更新される場合は、INSERT ステートメントを実行して増分データを集計結果テーブルに書き込むことができます。
完全更新: ファクトテーブルでデータが定期的に更新されず、増分データをすばやく識別できない場合は、完全データ書き戻し方式を使用して集計結果テーブルを更新できます。

手順 3: カスタムの長期にわたる UV 数をカウントする

基本ディメンションのカスタムの組み合わせに基づいて、カスタム期間の UV 数と PV 数をカウントできます。この方法は、従来の SQL ステートメントを実行するよりも効率的です。サンプルコード:

-- gender = 'man'&country = 'Beijing'&operator = 'Purchase'&shop_id = '1' という条件を満たすユーザーの数をクエリします。
SELECT
    SUM(RB_CARDINALITY (rb_and (t1.rb_uid, t2.rb_uid)))
FROM (
    SELECT
        rb_or_agg (rb_uid) AS rb_uid,
        bucket
    FROM
        dws_user_behaviour_rb
    WHERE
        OPERATOR = 'Purchase'
        AND shop_id = '1'
        AND ymd = '20240501'
    GROUP BY
        bucket) t1
    JOIN (
        SELECT
            rb_or_agg (rb_uid) AS rb_uid,
            bucket
        FROM
            dim_userbase_rb
        WHERE
            gender = 'man'
            AND country = 'Beijing'
        GROUP BY
            bucket) t2 ON t1.bucket = t2.bucket;