BITMAP Function
Last Updated：2021-04-13
This article mainly introduces the built-in functions related to BITMAP type.
-
Scalar function
1.to_bitmap
2.bitmap_hash
3.bitmap_count
4.bitmap_empty
5.bitmap_or
6.bitmap_and
7.bitmap_xor
8.bitmap_to_string
9.bitmap_from_string
10.bitmap_contains
11.bitmap_has_any -
Aggregate function
1.bitmap_union
2.bitmap_union_int
3.bitmap_union_count
4.bitmap_intersect
5.intersect_count
TO_BITMAP
Description
BITMAP to_bitmap(int i)- Function: convert an integer to a BITMAP type. It supports BIGINT type at most, and is mostly used for import, mapping the integer column in the source data to the BITMAP column in the Palo table.
- Returned value: BITMAP type
Example
mysql> select bitmap_to_string(to_bitmap("123"));
+------------------------------------+
| bitmap_to_string(to_bitmap('123')) |
+------------------------------------+
| 123 |
+------------------------------------+
mysql> select bitmap_to_string(to_bitmap(1));
+--------------------------------+
| bitmap_to_string(to_bitmap(1)) |
+--------------------------------+
| 1 |
+--------------------------------+As BITMAP is a binary type, it cannot be displayed on MySQL client. Therefore,
bitmap_to_stringis used for the visual display of results.
Keywords
to_bitmap, bitmapBITMAP_HASH
Description
BITMAP bitmap_hash(string s)- Function: Map a string to 32-bit integer by Hash algorithm, and then convert it to BITMAP type. It is mostly used for import, mapping the non-integer column in the source data to the BITMAP column in the Palo table. As it adopts Hash algorithm, it might have Hash conflicts. That means, different strings may produce the same BITMAP value. So it can only be used for approximate calculation.
- Returned value: BITMAP type
Example
mysql> select bitmap_to_string(bitmap_hash("abc"));
+--------------------------------------+
| bitmap_to_string(bitmap_hash('abc')) |
+--------------------------------------+
| 3409700625 |
+--------------------------------------+Keywords
bitmap_hash, bitmapBITMAP_COUNT
Description
bigint bitmap_count(bitmap s)-
- Count the number of 1 in a BITMAP.
- Returned value: bigint type.
Example
mysql> select bitmap_count(bitmap_from_string("1,2,3"));
+-------------------------------------------+
| bitmap_count(bitmap_from_string('1,2,3')) |
+-------------------------------------------+
| 3 |
+-------------------------------------------+
mysql> select bitmap_count(bitmap_from_string("1,2,3,4"));
+---------------------------------------------+
| bitmap_count(bitmap_from_string('1,2,3,4')) |
+---------------------------------------------+
| 4 |
+---------------------------------------------+Keywords
bitmap_count, bitmapBITMAP_EMPTY
Description
bitmap bitmap_empty()- Function: return an empty BITMAP. It is generally used for import, generating an empty bitmap.
- Returned value: bitmap type.
Example
mysql> select bitmap_to_string(bitmap_empty());
+----------------------------------+
| bitmap_to_string(bitmap_empty()) |
+----------------------------------+
| |
+----------------------------------+Keywords
bitmap_empty, bitmapBITMAP_OR
Description
bitmap bitmap_or(bitmap a, bitmap b)- Function: return the union set of two bitmaps.
- Returned value: bitmap type.
Example
mysql> select bitmap_to_string(bitmap_or(bitmap_from_string("1,2,3,4"), bitmap_from_string("4,5,6")));
+-----------------------------------------------------------------------------------------+
| bitmap_to_string(bitmap_or(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))) |
+-----------------------------------------------------------------------------------------+
| 1,2,3,4,5,6 |
+-----------------------------------------------------------------------------------------+Keywords
bitmap_or, bitmapBITMAP_AND
Description
bitmap bitmap_and(bitmap a, bitmap b)- Function: return the intersection set of two bitmaps.
- Returned value: bitmap type.
Example
mysql> select bitmap_to_string(bitmap_and(bitmap_from_string("1,2,3,4"), bitmap_from_string("4,5,6")));
+------------------------------------------------------------------------------------------+
| bitmap_to_string(bitmap_and(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))) |
+------------------------------------------------------------------------------------------+
| 4 |
+------------------------------------------------------------------------------------------+Keywords
bitmap_and, bitmapBITMAP_XOR
Description
bitmap bitmap_xor(bitmap a, bitmap b)- Function: return the Exclusive OR result of two bitmaps.
- Returned value: bitmap type.
Example
mysql> select bitmap_to_string(bitmap_xor(bitmap_from_string("1,2,3,4"), bitmap_from_string("4,5,6")));
+------------------------------------------------------------------------------------------+
| bitmap_to_string(bitmap_and(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))) |
+------------------------------------------------------------------------------------------+
| 1,2,3,5,6 |
+------------------------------------------------------------------------------------------+Keywords
bitmap_xor, bitmapBITMAP_TO_STRING
Description
string bitmap_to_string(bitmap a)- Function: return the Exclusive OR result of two bitmaps. For example, when the first bit and the third bid of a bitmap are 1, then return
1,3. - Returned value: string.
Example
mysql> select bitmap_to_string(bitmap_from_string("4,5,6"));
+-----------------------------------------------+
| bitmap_to_string(bitmap_from_string('4,5,6')) |
+-----------------------------------------------+
| 4,5,6 |
+-----------------------------------------------+Keywords
bitmap_to_string, bitmapBITMAP_FROM_STRING
Description
bitmap bitmap_from_string(string a)- Function: parse a string and return a bitmap. The string is a list of values separated by comma, for example:
1,200,301 - Returned value: bitmap type.
Example
mysql> select bitmap_to_string(bitmap_from_string("4, 5, 6"));
+-----------------------------------------------+
| bitmap_to_string(bitmap_from_string('4,5,6')) |
+-----------------------------------------------+
| 4,5,6 |
+-----------------------------------------------+Keywords
bitmap_from_string, bitmapBITMAP_CONTAINS
Description
boolean bitmap_contains(bitmap a, bigint b)- Function: judge whether a bitmap contains the value specified.
- Returned value: bool type.
Example
mysql> select bitmap_contains(bitmap_from_string("4, 5 ,6"), 4);
+---------------------------------------------------+
| bitmap_contains(bitmap_from_string('4, 5 ,6'), 4) |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
mysql> select bitmap_contains(bitmap_from_string("4, 5 ,6"), 7);
+---------------------------------------------------+
| bitmap_contains(bitmap_from_string('4, 5 ,6'), 7) |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+Keywords
bitmap_contains, bitmapBITMAP_HAS_ANY
Description
boolean bitmap_has_any(bitmap a, bitmap b)- Function: Judge whether two bitmaps intersect.
- Returned value: bool type.
Example
mysql> select bitmap_has_any(bitmap_from_string("1,2,3,4"), bitmap_from_string("4,5,6"));
+----------------------------------------------------------------------------+
| bitmap_has_any(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6')) |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
mysql> select bitmap_has_any(bitmap_from_string("1,2,3"), bitmap_from_string("4,5,6"));
+--------------------------------------------------------------------------+
| bitmap_has_any(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6')) |
+--------------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------------+Keywords
bitmap_has_any, bitmapBITMAP_UNION
Description
bitmap bitmap_union(bitmap a)- Function: an aggregate function, which returns the union set of a set of bitmap.
- Returned value: bitmap type.
Example
mysql> select k1, bitmap_to_string(bitmap_union(v1)) from tbl1 group by k1;
+------+--------------------------------------+
| k1 | bitmap_to_string(bitmap_union(`v1`)) |
+------+--------------------------------------+
| 2 | 2,3,4 |
| 1 | 1,2,3 |
+------+--------------------------------------+Keywords
bitmap_union, bitmapBITMAP_UNION_INT
Description
bigint bitmap_union_int(int a)- Function: an aggregate function, which uses bitmap data structure to calculate the deduplication value of integer columns, and is equivalent to
count(distinct a). It supports the types of parameters TINYINT, SMALLINT, INT, BIGINT. The function can use the bitmap data structure and get the RemoveDuplicate value with less system resources. - Returned value: bigint type.
Example
mysql> select bitmap_union_int(k1) from tbl1;
+------------------------+
| bitmap_union_int(`k1`) |
+------------------------+
| 2 |
+------------------------+Keywords
bitmap_union_int, bitmapBITMAP_UNION_COUNT
Description
bigint bitmap_union_count(bitmap a)- Function: an aggregate function, which returns the number of 1 in the union set of a set of bitmap, and is equivalent to
bitmap_count(bitmap_union(a)). It is recommended to directly use bitmap_union_count` , which is more efficient. - Returned value: bigint type.
Example
mysql> select k1, bitmap_union_count(v1) from tbl1 group by k1;
+------+--------------------------+
| k1 | bitmap_union_count(`v1`) |
+------+--------------------------+
| 2 | 3 |
| 1 | 3 |
+------+--------------------------+Keywords
bitmap_union, bitmapBITMAP_INTERSECT
Description
bitmap bitmap_intersect(bitmap a)- Function: an aggregate function, which return the intersection of a set of bitmap.
- Returned value: bitmap type.
The function of this aggregate function corresponds to bitmap_union, with a slight difference with bitmap_union usage scenario.
At present, when building a table in Palo, the bitmap_union aggregation method must be specified for the bitmap type column (note that this refers to the Aggregation Method, not the Aggregation Function). And the aggregation mode is of practical value only when queried by an equivalent aggregation function. Therefore, for columns of bitmap type, quires as follows are meaningful:
select k1, bitmap_union(v1) from tbl group by k1;And queries like the following are meaningless:
select k1, bitmap_intersect(v1) from tbl group by k1;See the example for the specific usage of bitmap_intersect .
Example
The table structure is as follows:
k1 INT
v1 BITMAP BITMAP_UNIONWhen querying, firstly aggregate data by using subqueries through bitmap_union , then obtain the intersection at outer layer through bitmap_intersect:
mysql> select bitmap_to_string(bitmap_intersect(x)) from (select k1, bitmap_union(v1) x from tbl1 group by k1) a;
+-----------------------------------------+
| bitmap_to_string(bitmap_intersect(`x`)) |
+-----------------------------------------+
| 2,3 |
+-----------------------------------------+Keywords
bitmap_intersect, bitmapINTERSECT_COUNT
Description
bigint intersect_count(bitmap a, column c, type cond1[, type cond2, ...])-
Function:
This function is commonly used in business scenarios such as calculating retention. The first parameter is the bitmap column for which the retention calculation is to be performed. The second parameter is the column name for which intersection calculation is required. The variable-length parameter after that is a set of values of the column corresponding to the second parameter.
The variable-length parameter after that is a set of values of the column corresponding to the second parameter.
bitmap_count+bitmap_intersect+bitmap_union+where - Returned value: bigint type.
Example
Calculate the user retention on 2020-10-01 and 2020-10-02. The table structure is as follows:
dt DATETIME
user_id BITMAPmysql> select intersect_count(user_id, dt, '2020-10-01', '2020-10-02'), intersect_count(user_id, dt, '2020-10-01') from tbl where dt in ('2020-10-01', '2020-10-02');
+--------------------------------------------------------------+------------------------------------------------+
| intersect_count(`user_id`, `dt`, '2020-10-01', '2020-10-02') | intersect_count(`user_id`, `dt`, '2020-10-01') |
+--------------------------------------------------------------+------------------------------------------------+
| 3 | 7 |
+--------------------------------------------------------------+------------------------------------------------+The above results show that, the number of visitors on 2020-10-01 was 7, and the number of visitors among the above visitors who visited again on 2020-10-02 was 3.
In which, intersect_count(user_id, dt, '2020-10-01', '2020-10-02') is equivalent to the following statements:
select bitmap_count(bitmap_intersect(b))
from
(
select dt, bitmap_union(user_id) b from tbl2
where dt in ('2020-10-01', '2020-10-02')
group by dt
) t2Keywords
intersect_count, bitmap