文档

ROARING BITMAP函数

更新时间:

ROARING BITMAP是一种高效的Bitmap压缩算法,目前已被广泛应用在各种语言和各种大数据平台,常用于去重、标签筛选、时间序列等计算中。本文将为您介绍如何使用ROARING BITMAP函数。

版本要求

  • 3.1.6.4及以上版本,支持在OSS外表中使用ROARING BITMAP类型及相关函数。

  • 3.2.1.0及以上版本,支持在内表中使用ROARING BITMAP类型及相关函数。

说明

如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

使用限制

AnalyticDB for MySQL使用ROARING BITMAP函数,具体限制如下:

  • 不支持直接SELECT某个ROARING BITMAP类型。如需查看ROARING BITMAP字段中的元素,请用Unnest操作,如:

    SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3]));
  • 3.2.1.0以下版本,ROARING BITMAP类型仅在OSS外表原生支持,不支持直接创建含有ROARING BITMAP类型列的表。如果查询AnalyticDB for MySQL内表时使用ROARING BITMAP函数,需要使用rb_build_varbinary将VARBINARY类型转换成ROARING BITMAP类型,如:

    // 定义内表
    CREATE TABLE test_rb_cstore (id INT, rb VARBINARY);
    
    // 通过ROARING BITMAP函数计算
    SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;

函数列表

ROARING BITMAP函数包括标量函数和聚合函数。

标量函数

函数名

输入数据类型

输出数据类型

描述

示例

RB_BUILD

INT

ROARING BITMAP

通过INT数组生成一个ROARING BITMAP。

RB_BUILD(ARRAY[1,2,3])

RB_BUILD_RANGE

INT,INT

ROARING BITMAP

通过开始(包含)、结束(不包含)的INT范围生成一个ROARING BITMAP。

RB_BUILD_RANGE(0, 10000)

RB_BUILD_VARBINARY

VARBINARY

ROARING BITMAP

通过二进制类型生成一个ROARING BITMAP。

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

RB_CARDINALITY

ROARING BITMAP

BIGINT

计算ROARING BITMAP基数。

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

RB_CONTAINS

ROARING BITMAP, INT

BOOLEAN

判断ROARING BITMAP是否包含INT。

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

RB_AND

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

两个ROARING BITMAP求交集。

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

RB_OR

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

两个ROARING BITMAP求并集。

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

RB_XOR

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

两个ROARING BITMAP求异或。

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

RB_AND_NULL2EMPTY

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

And计算。当输入的某一个参数为Null时,输出为另一个参数。当某一参数输入为{}时,输出为{}

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

RB_OR_NULL2EMPTY

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

Or计算。当输入为Null时,ROARING BITMAP会按空({})来处理。

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

RB_ANDNOT_NULL2EMPTY

ROARING BITMAP, ROARING BITMAP

ROARING BITMAP

AndNot计算。当输入为Null时,ROARING BITMAP会按空({})来处理。

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

RB_AND_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

And计算并返回基数。

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

RB_AND_NULL2EMPTY_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

And计算并返回基数。当输入为Null时,ROARING BITMAP会按空({})来处理。

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

RB_OR_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

Or计算并返回基数。

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

RB_OR_NULL2EMPTY_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

Or计算并返回基数。当输入为Null时,ROARING BITMAP会按空({})来处理。

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

RB_XOR_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

Xor计算并返回基数。

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

RB_ANDNOT_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

AndNot计算并返回基数。

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

RB_ANDNOT_NULL2EMPTY_CARDINALITY

ROARING BITMAP, ROARING BITMAP

INTEGER

AndNot计算并返回基数。当输入为Null时,ROARING BITMAP会按空({})来处理。

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

RB_IS_EMPTY

ROARING BITMAP

BOOLEAN

判断是否为空的ROARING BITMAP。

RB_IS_EMPTY(RB_BUILD(ARRAY[]))

RB_CLEAR

ROARING BITMAP,BIGINT,BIGINT

ROARING BITMAP

清除指定范围(不包括 range_end)。

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

RB_CONTAINS

ROARING BITMAP, ROARING BITMAP

BOOLEAN

判断第一个ROARING BITMAP是否包含第二个ROARING BITMAP。

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

RB_FLIP

ROARING BITMAP, INTEGER, INTEGER

ROARING BITMAP

翻转ROARING BITMAP中特定的Offset段。

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

RB_MINIMUM

ROARING BITMAP

INTEGER

返回ROARING BITMAP中最小的Offset,如果ROARING BITMAP为空则返回异常。

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

RB_MAXIMUM

ROARING BITMAP

INTEGER

返回ROARING BITMAP中最大的Offset,如果ROARING BITMAP为空则返回异常。

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

RB_RANK

ROARING BITMAP,INTEGER

INTEGER

返回ROARING BITMAP中小于等于指定Offset的基数。

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

RB_TO_ARRAY

ROARING BITMAP

INTEGER

返回ROARING BITMAP对应整型数组。

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

RB_TO_VARBINAR

ROARING BITMAP

VARBINARY

返回ROARING BITMAP对应BOOLEAN类型。

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

RB_RANGE_CARDINALITY

ROARING BITMAP, INTEGER, INTEGER

INTEGER

返回从起始位置(包含)到结束位置(不包含)范围的基数,位置从1开始计数。

重要

仅3.1.10.0及以上版本支持该函数。

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

RB_SELECT

ROARING BITMAP, INTEGER, INTEGER

ROARING BITMAP

返回从起始位置(包含)到结束位置(不包含)范围之间Bitmap的Offsets。

重要

仅3.1.10.0及以上版本支持该函数。

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

聚合函数

函数名

输入数据类型

输出数据类型

描述

示例

RB_BUILD_AGG

INTEGER

ROARING BITMAP

将Offset聚合成Bitmap。

RB_CARDINALITY(RB_BUILD_AGG(1))

RB_OR_AGG

ROARING BITMAP

ROARING BITMAP

Or聚合计算。

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

RB_AND_AGG

ROARING BITMAP

ROARING BITMAP

And聚合计算。

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

RB_XOR_AGG

ROARING BITMAP

ROARING BITMAP

Xor聚合计算。

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

RB_OR_CARDINALITY_AGG

ROARING BITMAP

INTEGER

Or聚合计算并返回其基数。

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

RB_AND_CARDINALITY_AGG

ROARING BITMAP

INTEGER

And聚合计算并返回其基数。

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

RB_XOR_CARDINALITY_AGG

ROARING BITMAP

INTEGER

Xor聚合计算并返回其基数。

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

示例

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

内表

  1. 创建一个含有ROARING BITMAP类型的内表。

    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. 使用ROARING BITMAP标量函数进行基数计算。

    SELECT id, RB_CARDINALITY(rb) FROM test_rb;

    返回结果如下:

    +------+--------------------+
    | id   | rb_cardinality(rb) |
    +------+--------------------+
    |    2 |                  5 |
    |    1 |                  3 |
    +------+--------------------+
  4. 使用ROARING BITMAP聚合函数进行聚合计算。

    SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

    返回结果如下:

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

外表

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

    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计算。流程如下图所示:1

步骤一:准备原始标签表

  1. 创建测试用的原始标签表users_base。

    CREATE TABLE users_base(
       uid INT,
       tag1 STRING, // tag1取值范围是:x,y,z。
       tag2 STRING, // tag2取值范围是:a,b。
       tag3 INT // tag3取值范围是:1~10。
    );
  2. 生成1亿行随机数据,假设生成的数据为用户标签数据。

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

步骤二:原始标签表分组

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

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

  • 实践过程中建议保证每个分组的ROARING BITMAP空间中数据小于1亿条。例如,原始的uid空间是100亿,可以使用100个分组,每个分组空间为1亿。

本示例使用16个分组,根据uid%16 分组并记录在user_group字段,根据uid/16计算偏移并记录在offset字段,uid = 16 * offset + user_goup。后续使用offset来计算ROARING BITMAP。

该分组方式只是一个例子,实际业务中请根据需求设计分组函数。

  1. 创建添加分组字段后的标签表users。

    CREATE TABLE users(
       uid INT,
       tag1 STRING,
       tag2 STRING,
       tag3 INT,
       user_group INT, // 分组字段
       offset INT //偏移字段
    );
  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 |
    +---------+------+------+------+------------+--------+

步骤三:构建ROARING BITMAP标签表

内表

  1. 创建tag1的ROARING BITMAP标签表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. 创建tag2的ROARING BITMAP标签表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. 创建tag1的ROARING BITMAP标签表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. 创建tag2的ROARING BITMAP标签表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 |
    +------+------------+--------------------+

步骤四:使用ROARING BITMAP标签表计算分析

场景一:过滤筛选分析

场景一以分析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 |
+------------+--------------------+

场景四:将满足场景二的ROARING BITMAP导出到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. 将满足场景二的计算结果导出到标签表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;
    说明

    SQL语句执行结束后,文件会以parquet格式存放在oss://testBucketName/roaringbitmap/tag_tbl_3/路径中。

场景五:加速查询(适用于外表)

将tag1标签表的数据导入内表实现加速查询效果。

  1. 创建一个内表tag_tbl_1_cstore,并将rb字段定义为VARBINARY类型。

    CREATE TABLE `tag_tbl_1_cstore` (
     `tag1` VARCHAR,
     `rb` VARBINARY,
     `user_group` INT
    );
  2. 将tag1的标签表的数据从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 |
    +------+------------+---------------------------------------------------+