すべてのプロダクト
Search
ドキュメントセンター

AnalyticDB:Roaringビットマップ関数

最終更新日:Sep 04, 2024

とどろくビットマップは、効率的に圧縮されたビットマップであり、重複排除、タグベースのフィルタリング、および時系列データの計算のために、さまざまなプログラミング言語およびビッグデータプラットフォームで広く使用されています。 このトピックでは、roaring bitmap関数の使用方法について説明します。

サポートされているバージョン

  • V3.1.6.4以降のAnalyticDB For MySQLクラスターの場合、OSS (Object Storage Service) 外部テーブルでは、ROARING BITMAPタイプと轟音ビットマップ関数がサポートされます。

  • V3.2.1.0以降のAnalyticDB For MySQLクラスターの場合、OSS外部テーブルおよびAnalyticDB for MySQL内部テーブルで、ROARING BITMAPタイプおよびroaringビットマップ関数がサポートされます。

説明

AnalyticDB For MySQLクラスターのマイナーバージョンを表示する方法については、クラスターのマイナーバージョンを表示するにはどうすればよいですか。 クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。

制限事項

AnalyticDB for MySQLでとどろくビットマップ関数を使用する場合は、次の制限事項に注意してください。

  • SELECTステートメントを実行して、ROARING BITMAP型のフィールドのデータを照会することはできません。 ROARING BITMAP型のフィールド内の要素をクエリするには、UNNEST演算子を使用する必要があります。 例:

    SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3]));
  • V3.2.1.0より前のバージョンのAnalyticDB For MySQLクラスターの場合、ROARING BITMAPタイプはOSS外部テーブルでのみサポートされ、ROARING BITMAPタイプの列を含むAnalyticDB for MySQL内部テーブルは作成できません。 AnalyticDB for MySQL内部テーブルでroaring bitmap関数を使用する場合は、まずRB_BUILD_VARBINARY関数を使用してVARBINARYデータをROARING BITMAP型に変換します。 例:

    // Create an internal table.
    CREATE TABLE test_rb_cstore (id INT, rb VARBINARY);
    
    // Invoke roaring bitmap functions.
    SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;

関数

轟音ビットマップ関数には、スカラー関数と集計関数が含まれます。

スカラー関数

関数

入力データ型

出力データ型

説明

RB_BUILD

INT

BITMAPのルーティング

整数配列から轟音ビットマップを生成します。

RB_BUILD(ARRAY[1,2,3])

RB_BUILD_RANGE

INT、INT

BITMAPのルーティング

開始値 (含まれる) から終了値 (除外される) までの整数の範囲に基づいて、轟音ビットマップを生成します。

RB_BUILD_RANGE(0, 10000)

RB_BUILD_VARBINARY

VARBINARY

BITMAPのルーティング

VARBINARYデータから轟音ビットマップを生成します。

RB_BUILD_VARBINARY(RB_TO_VARBINARY (RB_BUILD(ARRAY[1,2,3])))

RB_カーディナリティ

BITMAPのルーティング

BIGINT

轟音ビットマップのカーディナリティを計算します。

RB_CARDINALITY(RB_BUILD(ARRAY[1,2,3]))

RB_CONTAINS

ローリングビットマップ、INT

BOOLEAN

轟音ビットマップに整数が含まれているかどうかをチェックします。

RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]), 3)

RB_AND

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

2つの轟音ビットマップの交点を取得します。

RB_AND(RB_BUILD(ARRAY[1,2,3]), RB_BUILD(ARRAY[2,3,4]))

RB_OR

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

2つの轟音ビットマップの和集合を取得します。

RB_OR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))

RB_XOR

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

2つの轟音ビットマップのXOR値を取得します。

RB_XOR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))

RB_AND_NULL2EMPTY

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

AND演算を実行します。 パラメーターの入力値がnullの場合、関数は別のパラメーターの入力値を出力として使用します。 パラメーターの入力値が空の中括弧のペア ({}) の場合、関数は空の中括弧のペア ({}) を出力として使用します。

RB_AND_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))

RB_OR_NULL2EMPTY

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

OR演算を実行します。 入力値がnullの場合、関数は空の中かっこ ({}) のペアを入力として使用します。

RB_OR_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))

RB_ANDNOT_NULL2EMPTY

BITMAPのルーティング、BITMAPのルーティング

BITMAPのルーティング

ANDNOT操作を実行します。 入力値がnullの場合、関数は空の中かっこ ({}) のペアを入力として使用します。

RB_ANDNOT_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))

RB_AND_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してAND演算を実行してカーディナリティを計算します。

RB_AND_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))

RB_AND_NULL2EMPTY_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してAND演算を実行してカーディナリティを計算します。 入力値がnullの場合、関数は空の中かっこ ({}) のペアを入力として使用します。

RB_AND_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))

RB_OR_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してOR演算を実行して濃度を計算します。

RB_OR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))

RB_OR_NULL2EMPTY_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してOR演算を実行して濃度を計算します。 入力値がnullの場合、関数は空の中かっこ ({}) のペアを入力として使用します。

RB_OR_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))

RB_XOR_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してXOR演算を実行することによって濃度を計算します。

RB_XOR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))

RB_ANDNOT_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してANDNOT演算を実行してカーディナリティを計算します。

RB_ANDNOT_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))

RB_ANDNOT_NULL2EMPTY_CARDINALITY

BITMAPのルーティング、BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してANDNOT演算を実行してカーディナリティを計算します。 入力値がnullの場合、関数は空の中かっこ ({}) のペアを入力として使用します。

RB_ANDNOT_NULL2EMPTY_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))

RB_IS_EMPTY

BITMAPのルーティング

BOOLEAN

轟音ビットマップが空かどうかをチェックします。

RB_IS_EMPTY(RB_BUILD(ARRAY[]))

RB_CLEAR

ROARING BITMAP,BIGINT,BIGINT

BITMAPのルーティング

轟音ビットマップの範囲の終わりを除く指定された範囲をクリアします。

RB_CLEAR(RB_BUILD('{1,2,3}'), 2, 3)

RB_CONTAINS

BITMAPのルーティング、BITMAPのルーティング

BOOLEAN

最初の轟音ビットマップに2番目の轟音ビットマップが含まれるかどうかをチェックします。

RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3]))

RB_FLIP

ルーティングBITMAP, INTEGER, INTEGER

BITMAPのルーティング

指定されたオフセットを轟音ビットマップで反転します。

RB_FLIP(RB_BUILD(ARRAY[1,2,3,4,5]), 2, 5)

RB_MINIMUM

BITMAPのルーティング

INTEGER

轟音ビットマップの最小オフセットを返します。 轟音ビットマップが空の場合、エラーが返されます。

RB_MINIMUM(RB_BUILD(ARRAY[1,2,3]))

RB_MAXIMUM

BITMAPのルーティング

INTEGER

轟音ビットマップで最大のオフセットを返します。 轟音ビットマップが空の場合、エラーが返されます。

RB_MAXIMUM(RB_BUILD(ARRAY[1,2,3]))

RB_RANK

ルーティングBITMAP,INTEGER

INTEGER

轟音ビットマップ内の特定のオフセット以下の要素の数を返します。

RB_RANK(RB_BUILD(ARRAY[1,2,3]),2)

RB_TO_ARRAY

BITMAPのルーティング

INTEGER

轟音ビットマップが作成される整数配列を返します。

RB_TO_ARRAY(RB_BUILD(ARRAY[1,2,3]))

RB_TO_VARBINAR

BITMAPのルーティング

VARBINARY

轟音ビットマップが作成されるVARBINARY配列を返します。

RB_TO_VARBINARY(RB_BUILD(ARRAY[1,2,3]))

RB_RANGE_CARDINALITY

ルーティングBITMAP, INTEGER, INTEGER

INTEGER

開始位置 (含まれている) から終了位置 (含まれていない) までの範囲のカーディナリティを返します。 開始位置は1である。

重要

AnalyticDB for MySQL V3.1.10.0以降のみがこの機能をサポートしています。

RB_RANGE_CARDINALITY(RB_BUILD(ARRAY [1,2,3]),2,3)

RB_SELECT

ルーティングBITMAP, INTEGER, INTEGER

BITMAPのルーティング

開始位置 (含まれている) から終了位置 (含まれていない) までの範囲の轟音ビットマップのオフセットを返します。

重要

AnalyticDB for MySQL V3.1.10.0以降のみがこの機能をサポートしています。

RB_SELECT(RB_BUILD(ARRAY [1,3,4,5,7,9])),2, 3)

集計関数

関数

入力データ型

出力データ型

説明

RB_BUILD_AGG

INTEGER

BITMAPのルーティング

オフセットのグループから轟音ビットマップを作成します。

RB_CARDINALITY(RB_BUILD_AGG (1))

RB_OR_AGG

BITMAPのルーティング

BITMAPのルーティング

OR集計操作を実行します。

RB_CARDINALITY(RB_OR_AGG(RB_BUILD(array[1,2,3])))

RB_AND_AGG

BITMAPのルーティング

BITMAPのルーティング

AND集計操作を実行します。

RB_CARDINALITY(RB_AND_AGG(RB_BUILD(ARRAY[1,2,3])))

RB_XOR_AGG

BITMAPのルーティング

BITMAPのルーティング

XOR集計演算を実行します。

RB_CARDINALITY(RB_XOR_AGG(RB_BUILD(ARRAY[1,2,3])))

RB_OR_CARDINALITY_AGG

BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してOR集計演算を実行して濃度を計算します。

RB_OR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))

RB_AND_CARDINALITY_AGG

BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してAND集計演算を実行して濃度を計算します。

RB_AND_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))

RB_XOR_CARDINALITY_AGG

BITMAPのルーティング

INTEGER

2つの轟音ビットマップに対してXOR集計演算を実行して濃度を計算します。

RB_XOR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))

次の例では、内部テーブルと外部テーブルで轟音ビットマップ関数を使用する方法について説明します。

内部テーブル

  1. 轟音ビットマップをサポートする内部テーブルを作成します。

    CREATE TABLE `test_rb` (
      `id` INT,
      `rb` ROARINGBITMAP
    );
  2. テーブルにデータを書き込みます。

    INSERT INTO test_rb VALUES (1, '[1, 2, 3]');
    INSERT INTO test_rb VALUES (2, '[2, 3, 4, 5, 6]');
  3. 轟音ビットマップのカーディナリティを取得します。

    SELECT id, RB_CARDINALITY(rb) FROM test_rb;

    サンプル結果:

    +------+--------------------+
    | id   | rb_cardinality(rb) |
    +------+--------------------+
    |    2 |                  5 |
    |    1 |                  3 |
    +------+--------------------+
  4. 轟音ビットマップで集計を実行します。

    SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

    サンプル結果:

    +---------------------------+
    | rb_or_cardinality_agg(rb) |
    +---------------------------+
    |                         6 |
    +---------------------------+

外部テーブル

  1. 轟音ビットマップをサポートする外部テーブルを作成します。

    CREATE TABLE `test_rb` (
      `id` INT,
      `rb` ROARINGBITMAP
      ) engine = 'oss'
    TABLE_PROPERTIES = '{
    "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
    "accessid":"LTAIF****5FsE"",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketName/roaringbitmap/test_for_user/",
    "format":"parquet"
    }';
    説明

    外部テーブルのパラメーターについては、「外部テーブルを使用してdata Warehouse Editionにデータをインポートする」トピックの「パーティション分割されていないオブジェクトのOSS外部テーブルの作成」をご参照ください。

  2. テーブルにデータを書き込みます。

    重要

    INSERT INTOステートメントを使用したデータの書き込みは、パフォーマンスの高い方法ではありません。 大量のデータを書き込む場合は、抽出、変換、読み込み (ETL) ツールを使用して、Parquet形式のデータファイルを生成し、OSSディレクトリにアップロードすることをお勧めします。

    INSERT INTO test_rb SELECT 1, rb_build(ARRAY[1,2,3]);
    INSERT INTO test_rb SELECT 2, rb_build(ARRAY[2,3,4,5]);
  3. 轟音ビットマップのカーディナリティを取得します。

    SELECT id, RB_CARDINALITY(rb) FROM test_rb;

    サンプル結果:

    +------+--------------------+
    | id   | rb_cardinality(rb) |
    +------+--------------------+
    |    2 |                  4 |
    |    1 |                  3 |
    +------+--------------------+
  4. 轟音ビットマップで集計を実行します。

    SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

    サンプル結果:

    +---------------------------+
    | rb_or_cardinality_agg(rb) |
    +---------------------------+
    |                         5 |
    +---------------------------+

例: 対象顧客の特定

この例では、タグテーブルが轟音ビットマップテーブルに変換され、次の図に示すように、轟音ビットマップに基づいて分析が実行されます。 1

ステップ1: タグテーブルを準備する

  1. テスト用にusers_baseというタグテーブルを作成します。

    CREATE TABLE users_base(
       uid INT,
       tag1 STRING, // Valid values of tag1: x, y, and z 
       tag2 STRING, // Valid values of tag2: a and b 
       tag3 INT // Valid values of tag3: 1 to 10 
    );
  2. データを挿入して100万行のタグデータを生成します。

    SUBMIT JOB
    INSERT OVERWRITE users_base
    SELECT CAST(ROW_NUMBER() OVER (ORDER BY c1) AS INT) AS uid, SUBSTRING('xyz', FLOOR(RAND() * 3) + 1, 1) AS tag1, SUBSTRING('ab', FLOOR(RAND() * 2) + 1, 1) AS tag2, CAST(FLOOR(RAND() * 10) + 1 AS INT) as tag3 FROM  
    (
    SELECT A.c1 FROM
    UNNEST(RB_BUILD_RANGE(0, 10000)) AS A(c1)
      JOIN
      (SELECT c1 FROM
    UNNEST(RB_BUILD_RANGE(0, 10000)) AS B(c1)
    ));
  3. users_baseテーブルから10行のデータを照会します。

    SELECT * FROM users_base LIMIT 10;

    サンプル結果:

    +--------+------+------+------+
    | uid    | tag1 | tag2 | tag3 |
    +--------+------+------+------+
    |  74526 | y    | b    |    3 |
    |  75611 | z    | b    |   10 |
    |  80850 | x    | b    |    5 |
    |  81656 | z    | b    |    7 |
    | 163845 | x    | b    |    2 |
    | 167007 | y    | b    |    4 |
    | 170541 | y    | b    |    9 |
    | 213108 | x    | a    |   10 |
    |  66056 | y    | b    |    4 |
    |  67761 | z    | a    |    2 |
    +--------+------+------+------+

ステップ2: タグテーブルのグループ化

AnalyticDB for MySQLの分散コンピューティングエンジンの同時実行機能を利用するには、uidでデータをグループ化できる列を追加することをお勧めします。 この例では、user-groupという名前の列がグループ化のために作成されます。 グループサイズは、クラスター内のAnalyticDBコンピューティングユニット (ACU) の総数によって異なります。

  • 多数のグループは、高いコンピューティング効率をもたらす。 しかし、各とどろくビットマップが少数の要素しか含まない場合、システムはとどろくビットマップを十分に利用することができない。

  • 実際のシナリオでは、各グループ内のレコード数を100万未満に制限することをお勧めします。 たとえば、uid値の総数が100億の場合、それぞれ100万行のデータを持つ100グループを作成できます。

この例では、uid値は16のグループに分類されます。 user_group列には、uid値を16で割った値の剰余が格納されます。 オフセット列は、除算の商を保持する。 オフセット列は、轟音ビットマップの計算に使用される。 uidは、次の式に基づいて計算できます。uid = 16 × offset + user_group

このグループ化方法は参照専用です。 ビジネス要件に基づいてグループを設計する必要があります。

  1. グループ化用の列を含むusersという名前のタグテーブルを作成します。

    CREATE TABLE users(
       uid INT,
       tag1 STRING,
       tag2 STRING,
       tag3 INT,
       user_group INT, // The grouping field.
       offset INT // The offset field.
    );
  2. users_baseテーブルのデータをusersテーブルに書き込みます。

    SUBMIT JOB INSERT OVERWRITE users SELECT uid, tag1, tag2, tag3, CAST(uid%16 AS INT), CAST(FLOOR(uid/16) AS INT) FROM users_base;
  3. usersテーブルから10行のデータを照会します。

    SELECT * FROM users LIMIT 10;

    サンプル結果:

    +---------+------+------+------+------------+--------+
    | uid     | tag1 | tag2 | tag3 | user_group | offset |
    +---------+------+------+------+------------+--------+
    |  377194 | z    | b    |   10 |         10 |  23574 |
    |  309440 | x    | a    |    1 |          0 |  19340 |
    |  601745 | z    | a    |    7 |          1 |  37609 |
    |  753751 | z    | b    |    3 |          7 |  47109 |
    |  988186 | y    | a    |   10 |         10 |  61761 |
    |  883822 | x    | a    |    9 |         14 |  55238 |
    |  325065 | x    | b    |    6 |          9 |  20316 |
    | 1042875 | z    | a    |   10 |         11 |  65179 |
    |  928606 | y    | b    |    5 |         14 |  58037 |
    |  990858 | z    | a    |    8 |         10 |  61928 |
    +---------+------+------+------+------------+--------+

ステップ3: とどろくビットマップを作成する

内部テーブル

  1. タグ1用にtag_tbl_1という轟音ビットマップテーブルを作成します。

    CREATE TABLE `tag_tbl_1` (
      `tag1` STRING,
      `rb` ROARINGBITMAP,
      `user_group` INT
    );
  2. usersテーブルのデータをtag_tbl_1テーブルに書き込みます。

    INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;
  3. tag_tbl_1のデータを照会します。

    SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

    サンプル結果:

    +------+------------+--------------------+
    | tag1 | user_group | rb_cardinality(rb) |
    +------+------------+--------------------+
    | y    |         13 |             563654 |
    | x    |         11 |             565013 |
    | z    |          2 |             564428 |
    | x    |          4 |             564377 |
    ...                                 
    | z    |          5 |             564333 |
    | x    |          8 |             564808 |
    | x    |          0 |             564228 |
    | y    |          3 |             563325 |
    +------+------------+--------------------+
  4. タグ2用にtag_tbl_2という轟音ビットマップテーブルを作成します。

    CREATE TABLE `tag_tbl_2` (
      `tag2` STRING,
      `rb` ROARINGBITMAP,
      `user_group` INT
    );
  5. usersテーブルのデータをtag_tbl_2テーブルに書き込みます。

    INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;
  6. tag_tbl_2のデータを照会します。

    SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

    サンプル結果:

    +------+------------+--------------------+
    | tag2 | user_group | rb_cardinality(rb) |
    +------+------------+--------------------+
    | a    |          9 |            3123039 |
    | a    |          5 |            3123973 |
    | a    |         12 |            3122414 |
    | a    |          7 |            3127218 |
    | a    |         15 |            3125403 |
    ...                                  
    | a    |         10 |            3122698 |
    | b    |          4 |            3126091 |
    | b    |          3 |            3124626 |
    | b    |          9 |            3126961 |
    | b    |         14 |            3125351 |
    +------+------------+--------------------+

外部テーブル

  1. タグ1用にtag_tbl_1という轟音ビットマップテーブルを作成します。

    CREATE TABLE `tag_tbl_1` (
      `tag1` STRING,
      `rb` ROARINGBITMAP,
      `user_group` INT
     ) engine = 'oss'
    TABLE_PROPERTIES = '{
    "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
    "accessid":"LTAIF****5FsE"",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketName/roaringbitmap/tag_tbl_1/",
    "format":"parquet"
    }';
  2. usersテーブルのデータをtag_tbl_1テーブルに書き込みます。

    INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;
  3. tag_tbl_1のデータを照会します。

    SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

    サンプル結果:

    +------+------------+--------------------+
    | tag1 | user_group | rb_cardinality(rb) |
    +------+------------+--------------------+
    | z    |          7 |            2082608 |
    | x    |         10 |            2082953 |
    | y    |          7 |            2084730 |
    | x    |         14 |            2084856 |
    ...                                  
    | z    |         15 |            2084535 |
    | z    |          5 |            2083204 |
    | x    |         11 |            2085239 |
    | z    |          1 |            2084879 |
    +------+------------+--------------------+
  4. タグ2用にtag_tbl_2という轟音ビットマップテーブルを作成します。

    CREATE TABLE `tag_tbl_2` (
      `tag2` STRING,
      `rb` ROARINGBITMAP,
      `user_group` INT
     ) engine = 'oss'
    TABLE_PROPERTIES = '{
    "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
    "accessid":"LTAIF****5FsE"",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketName/roaringbitmap/tag_tbl_2/",
    "format":"parquet"
    }';
  5. usersテーブルのデータをtag_tbl_2テーブルに書き込みます。

    INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;
  6. tag_tbl_2のデータを照会します。

    SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

    サンプル結果:

    +------+------------+--------------------+
    | tag2 | user_group | rb_cardinality(rb) |
    +------+------------+--------------------+
    | b    |         11 |            3121361 |
    | a    |          6 |            3124750 |
    | a    |          1 |            3125433 |
    ...                                    
    | b    |          2 |            3126523 |
    | b    |         12 |            3123452 |
    | a    |          4 |            3126111 |
    | a    |         13 |            3123316 |
    | a    |          2 |            3123477 |
    +------+------------+--------------------+

ステップ4: 轟音ビットマップに基づいて分析を実行する

シナリオ1: フィルタリング分析

この例では、tag1 In (x, y) を満たすtag2の各値に対するユーザの数が取得される。

  1. tag1 in (x, y) を満たすユーザーを照会します。

    SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS rb FROM tag_tbl_2 AS t2
    JOIN (
    SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
    AS t1
    ON t1.user_group = t2.user_group;

    サンプル結果:

    +------+------------+---------+
    | tag2 | user_group | rb      |
    +------+------------+---------+
    | b    |          3 | 1041828 |
    | a    |          15| 1039859 |
    | a    |          9 | 1039140 |
    | b    |          1 | 1041524 |
    | a    |          4 | 1041599 |
    | b    |          1 | 1041381 |
    | b    |          10| 1041026 |
    | b    |          6 | 1042289 |
    +------+------------+---------+
  2. tag1 in (x, y) を満たすtag2の各値のユーザー数を取得します。

    SELECT tag2, SUM(cnt) FROM ( 
    SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS cnt FROM tag_tbl_2 AS t2
    JOIN (
    SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
    AS t1
    ON t1.user_group = t2.user_group
    ) GROUP BY tag2;

    サンプル結果:

    +------+----------+
    | tag2 | sum(cnt) |
    +------+----------+
    | a    | 33327868 |
    | b    | 33335220 |
    +------+----------+

シナリオ2: 轟音ビットマップの交差、和集合、およびXORを計算する

tag_tbl_1テーブルからtag1 = 'x' またはtag1 = 'y' を満たすデータとtag_tbl_2テーブルからtag2 = 'b' を満たすデータを取得し、両方の条件を満たすデータを取得します。

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb2
        FROM
          tag_tbl_2
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

サンプル結果:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|         10 |            2083679 |
|          3 |            2082370 |
|          9 |            2082847 |
|          2 |            2086511 |
...                              
|          1 |            2082291 |
|          4 |            2083290 |
|         14 |            2083581 |
|         15 |            2084110 |
+------------+--------------------+

シナリオ3: 轟音ビットマップと元のタグテーブルの交差、和集合、およびXORを計算する

tag1 = 'x' またはtag1 = 'y' を満たすtag_tbl_1テーブルからデータを取得し、tag2 = 'b' を満たすusersテーブルからデータを取得し、両方の条件を満たすデータを取得します。

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_BUILD_AGG(offset) AS rb2
        FROM
          users
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

サンプル結果:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|          3 |            2082370 |
|          1 |            2082291 |
|          0 |            2082383 |
|          4 |            2083290 |
|         11 |            2081662 |
|         13 |            2085280 |
...                              
|         14 |            2083581 |
|         15 |            2084110 |
|          9 |            2082847 |
|          8 |            2084860 |
|          5 |            2083056 |
|          7 |            2083275 |
+------------+--------------------+

シナリオ4: OSS外部テーブルを使用したOSSへの結果のエクスポート

  1. tag_tbl_3という名前のタグテーブルを作成し、計算結果をエクスポートします。

    CREATE TABLE `tag_tbl_3` (
      `user_group` INT,
      `rb` ROARINGBITMAP
      )engine = 'oss'
    TABLE_PROPERTIES = '{
    "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
    "accessid":"LTAIF****5FsE"",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketName/roaringbitmap/tag_tbl_3/",
    "format":"parquet"
    }';
  2. シナリオ2の結果をtag_tbl_3にエクスポートします。

    INSERT OVERWRITE tag_tbl_3
        SELECT
          t1.user_group AS user_group,
          RB_AND(rb1, rb2) AS rb
        FROM
          (
            SELECT
              user_group,
              RB_OR_AGG(rb) AS rb1
            FROM
              tag_tbl_1
            WHERE
              tag1 = 'x'
              OR tag1 = 'y'
            GROUP BY
              user_group
          ) AS t1
          JOIN (
            SELECT
              user_group,
              RB_OR_AGG(rb) AS rb2
            FROM
              tag_tbl_2
            WHERE
              tag2 = 'b'
            GROUP BY
              user_group
          ) AS t2 ON t1.user_group = t2.user_group;
    説明

    ステートメントが実行されると、ファイルはParquetファイルとしてoss:// testBucketName/roaringbitmap/tag_tbl_3/ ディレクトリに保存されます。

シナリオ5: クエリの高速化

tag_tbl_1テーブルから内部テーブルにデータをインポートして、クエリパフォーマンスを向上させることができます。

  1. tag_tbl_1_cstoreという名前の内部テーブルを作成し、rbフィールドのデータ型をVARBINARYに設定します。

    CREATE TABLE `tag_tbl_1_cstore` (
     `tag1` VARCHAR,
     `rb` VARBINARY,
     `user_group` INT
    );
  2. tag_tbl_1テーブルのデータをOSSからtag_tbl_1_cstoreにインポートします。

    INSERT INTO tag_tbl_1_cstore SELECT tag1, RB_TO_VARBINARY(rb), user_group FROM tag_tbl_1;
  3. tag_tbl_1_cstoreテーブルのデータを照会します。

    SELECT tag1, user_group, RB_CARDINALITY(RB_OR_AGG(RB_BUILD_VARBINARY(rb))) FROM tag_tbl_1_cstore GROUP BY tag1, user_group;

    サンプル結果:

    +------+------------+---------------------------------------------------+
    | tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) |
    +------+------------+---------------------------------------------------+
    | y    |          3 |                                           2082919 |
    | x    |          9 |                                           2083085 |
    | x    |          3 |                                           2082140 |
    | y    |         11 |                                           2082268 |
    | z    |          4 |                                           2082451 |
    ...                                                                    
    | z    |          2 |                                           2081560 |
    | y    |          6 |                                           2082194 |
    | z    |          7 |                                           2082608 |
    +------+------------+---------------------------------------------------+