AnalyticDB for MySQL:Roaring bitmap functions

Only AnalyticDB for MySQL clusters of V3.1.6.4 or later support roaring bitmaps and roaring bitmap functions.

When you use roaring bitmap functions in AnalyticDB for MySQL, take note of the following limits:

• You must use the UNNEST operator to query the elements in a roaring bitmap. Sample statement:

  SELECT * FROM unnest(rb_build(array[1,2,3]));

• Roaring bitmaps are supported only in Object Storage Service (OSS) external tables and cannot be created in AnalyticDB for MySQL internal tables. If you want to use roaring bitmap functions on AnalyticDB for MySQL internal tables, first use the rb_build_varbinary function to convert VARBINARY data into the roaringbitmap type. Sample statement:

  // Create an AnalyticDB for MySQL 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;

The following examples describe how to use roaring bitmap functions:

1. Create an external table that supports roaring bitmaps.

   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"
   }';

   Note

   For information about the parameters of an external table, see the "Create an OSS external table for a non-partitioned object" section of the Use external tables to import data to Data Warehouse Edition topic.

2. Write data to the table.

   Important

   Writing data by using INSERT INTO statements is not a performant method. If you want to write a large amount of data, we recommend that you use an extract-transform-load (ETL) tool to generate data files in the parquet format and upload them to an OSS directory.

   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. Obtain the cardinality of the roaring bitmaps.

   SELECT id, rb_cardinality(rb) FROM test_rb;

   Sample result:

   +------+--------------------+
   | id   | rb_cardinality(rb) |
   +------+--------------------+
   |    2 |                  4 |
   |    1 |                  3 |
   +------+--------------------+

4. Perform aggregation on the roaring bitmaps.

   SELECT rb_or_cardinality_agg(rb) FROM test_rb;

   Sample result:

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

In this example, a tag table is converted into a roaring bitmap table, and analysis is performed based on the roaring bitmaps, as shown in the following figure.

To utilize the concurrency capability of the distributed computing engines of AnalyticDB for MySQL, we recommend that you add columns that can group the data by uid. In this example, a column named user-group is created for grouping. The group sizes vary based on the total number of computing and storage AnalyticDB compute units (ACUs) in your cluster.

• A large number of groups bring high computing efficiency. However, if each roaring bitmap contains only a small number of elements, the system cannot take full advantage of roaring bitmaps.

• In actual scenarios, we recommend that you limit the number of records within each group to less than 100 million. For example, if the total number of uid values is 10 billion, you can create 100 groups that each have 100 million rows of data.

Scenario 1: Filtering analysis

In this example, the number of users for each value of tag2 that meet tag1 in (x, y) is obtained.

1. Query the users that meet 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;

   Sample result:

   +------+------------+---------+
   | 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. Obtain the number of users for each value of tag2 that meet tag1 in (x, y).

   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;

   Sample result:

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

Scenario 2: Calculate the intersection, union, and XOR of roaring bitmaps

Retrieve data from the tag_tbl_1 table that meets tag1 = 'x' or tag1 = 'y' and data from the tag_tbl_2 table that meets tag2 = 'b', and obtain the data that meets both conditions.

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);

Sample result:

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

Scenario 3: Calculate the intersection, union, and XOR of roaring bitmaps and original tag tables

Retrieve data from the tag_tbl_1 table that meets tag1 = 'x' or tag1 = 'y' and data from the users table that meets tag2 = 'b', and obtain the data that meets both conditions.

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);

Sample result:

+------------+--------------------+
| 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 |
+------------+--------------------+