Roaring Bitmap函数

仅3.1.6.4及以上版本支持roaringbitmap类型及Roaring Bitmap相关函数。

在AnalyticDB MySQL使用Roaring Bitmap函数，具体限制如下：

• 不支持直接SELECT某个roaringbitmap类型。如需查看roaringbitmap字段中的元素，请用unnest操作，如：

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

• roaringbitmap类型仅在OSS外表原生支持。AnalyticDB MySQL不支持直接创建含有roaringbitmap类型列的表。如果查询AnalyticDB MySQL内表时使用Roaring Bitmap函数，需要使用rb_build_varbinary将varbinary类型转换成roaringbitmap类型，如：

  // 定义adb内表
  CREATE TABLE test_rb_cstore (id int, rb varbinary);
  
  // 通过Roaring Bitmap函数计算
  SELECT rb_cardinality(rb_build_varbinary(rb)) FROM test_rb_cstore;

如下内容将为您介绍Roaring Bitmap函数完整的使用示例。

1. 创建一个含有roaringbitmap类型的外表。

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

   说明

   外表相关的参数说明，请参见不带分区的数据文件创建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. 使用Roaring Bitmap标量函数进行基数计算。

   SELECT id, rb_cardinality(rb) FROM test_rb;

   返回结果如下：

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

4. 使用Roaring Bitmap聚合函数进行聚合计算。

   SELECT rb_or_cardinality_agg(rb) FROM test_rb;

   返回结果如下：

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

实践过程中，需要将原始标签表转化成Roaring Bitmap标签表，然后进行Roaring Bitmap计算。流程如下图所示：

在设计Roaring Bitmap标签表时，可以利用分布式计算引擎的并发能力，建议引入一个分组字段（本例中使用user_group），将uid分组并行计算。分组的大小可以根据集群ACU总数（计算ACU数+存储ACU数）和实际业务情况进行取舍，遵循以下原则：

• 一般分组越多计算能力越强，但是分组过多又会导致每个Roaring Bitmap字段中的元素太少，不能充分利用Bitmap的计算特性。

• 实践过程中建议保证每个分组的Roaring Bitmap空间中数据小于1亿条。例如，原始的uid空间是100亿，可以使用100个分组，每个分组空间为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 |
   +------+----------+

场景二：Roaring Bitmap标签表交并差计算

从tag1的Roaring Bitmap标签表tag_tbl_1读取满足tag1 = 'x' or tag1 = 'y'条件的数据，从tag2的Roaring Bitmap标签表tag_tbl_2读取满足tag2 = 'b'条件的数据，对两个标签表的数据进行交并差计算，得到同时满足tag1 = 'x' or tag1 = 'y'且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 |
+------------+--------------------+

场景三：原始标签表和Roaring Bitmap标签表交并差计算

从tag1的Roaring Bitmap标签表tag_tbl_1读取满足tag1 = 'x' or tag1 = 'y'条件的数据，从原始标签表users读取满足tag2 = 'b'条件的数据，对两个标签表的数据进行交并差计算，得到同时满足tag1 = 'x' or tag1 = 'y'且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_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 |
+------------+--------------------+