#mysql #roaring #macros #bitmap #udf #macro

macro mysql_roaring_macros

Roaring bitmaps as MySQL/MariaDB User Defined Functions

1 unstable release

0.1.0 Apr 9, 2023

#17 in #roaring

Download history 5/week @ 2023-11-10 6/week @ 2023-11-17 7/week @ 2023-11-24 7/week @ 2023-12-01 3/week @ 2023-12-08 5/week @ 2023-12-15 6/week @ 2023-12-22 2/week @ 2023-12-29 4/week @ 2024-01-05 4/week @ 2024-01-12 3/week @ 2024-01-19 5/week @ 2024-01-26 3/week @ 2024-02-02 6/week @ 2024-02-09 25/week @ 2024-02-16 35/week @ 2024-02-23

69 downloads per month
Used in mysql_roaring

Custom license

43KB
742 lines

Roaring bitmaps as MySQL/MariaDB User Defined Functions

This development is completed, but only partially tested. Please use it at your own risk.

Storage

  • You can use LONGBLOB data type to store bitmap in MySQL/MariaDB column.
  • Please remember that if you're using database replication, you should probably exclude tables with bitmaps from it or switch to statement-based replication. Otherwise, the size of your binary log will significantly increase, and the performance of the database will be slightly slower.

UDF functions

prefixes

  • roaring32 - for INT (maybe unsigned)
  • roaring64 - for BIGINT (maybe unsigned)
  • nullsafe - SQL functions return NULL if at least one argument is NULL. With the 'nullsafe' prefix, the function will return an empty result of the corresponding type, instead of NULL.
    • empty bitmap BLOB
    • 0 for integer
    • '[]' for json

functions

  • roaring[32|64]_[nullsafe_]create(value0 INT,[value1 INT[, ...]]) -> BLOB - Create bitmap from multiple integer arguments.
  • roaring[32|64]_[nullsafe_][remove|insert](map BLOB, value0 INT[,value1 INT[, ...]]) -> BLOB - Add or remove multiple integers from bitmap.
  • roaring[32|64]_[nullsafe_]contains (map BLOB, value INT) -> BOOL - Check whether integer is in a bitmap.
  • roaring[32|64]_[nullsafe_]count (map BLOB) -> INT - How many integers are in a bitmap.
  • roaring[32|64]_[nullsafe_]json (map BLOB) -> TEXT - Get all integers from bitmap as JSON array.
  • roaring[32|64]_[nullsafe_][and|or|xor] ([map0 BLOB[, map1 BLOB[, ...]]]) -> BLOB - Perform one of bitmap operations (and, or, xor) on multiple bitmap arguments and return resulting bitmap.
  • roaring[32|64]_[nullsafe_][and|or|xor]_count ([map0 BLOB[, map1 BLOB[, ...]]]) -> BLOB - Perform one of bitmap operations (and, or, xor) on multiple bitmap arguments and return number of integers in a resulting bitmap.
  • roaring[32|64]_[nullsafe_]group_create (value INT) -> BLOB - Aggregation. Create bitmap from integers is group.
  • roaring[32|64]_[nullsafe_]group_[and|or|xor] (map BLOB) -> BLOB - Aggregation. Perform one of bitmap operations (and, or, xor) on bitmaps in group and return resulting bitmap.
  • roaring[32|64]_[nullsafe_]group_[and|or|xor]_count (map BLOB) -> BLOB - Aggregation. Perform one of bitmap operations (and, or, xor) on bitmaps in group and return number of integers in a resulting bitmap.

full list of functions

libmysql_roaring.md

SQL queries for all functions loading

libmysql_roaring.sql

Build and installation

Standalone MySQL/MariaDB

  • Get Rust programing language tools https://www.rust-lang.org/tools/install
  • Get this git repository git clone --depth=1 https://github.com/tarasbogach/mysql_roaring.git
  • Go to project folder cd mysql_roaring
  • Build this project cargo build --release
  • Now you should have compiled shared library ./target/release/libmysql_roaring.so
  • Find your MySQL/MariaDB plugin folder echo "SHOW VARIABLES LIKE 'plugin_dir';" | mysql, and copy shared library to it sudo cp ./target/release/libmysql_roaring.so /usr/lib/mysql/plugin/
  • Run queries from libmysql_roaring.sql cat libmysql_roaring.sql | mysql

Docker

You will need to add build stage to your Dockerfile. And copy .so and .sql files from it to your MySQL/MariaDB image.

FROM rust:latest AS build

ENV CARGO_REGISTRIES_CRATES_IO_PROTOCOL=sparse

WORKDIR /build

COPY ./mysql_roaring /build

RUN --mount=type=cache,target=/usr/local/cargo/registry \
    --mount=type=cache,target=/build/target \
    cargo build --release \
    && mkdir /output \
    && cp target/release/libmysql_roaring.so /output \
    && cp libmysql_roaring.sql /output

FROM mariadb

COPY --from=build /output/libmysql_roaring.so /usr/lib/mysql/plugin/
COPY --from=build /output/libmysql_roaring.sql /docker-entrypoint-initdb.d/

Examples

32bit integer

CREATE DATABASE IF NOT EXISTS example;

CREATE TABLE IF NOT EXISTS example.bitmaps (id INT8 UNSIGNED PRIMARY KEY, map LONGBLOB);

TRUNCATE TABLE example.bitmaps;

INSERT INTO  example.bitmaps
SELECT seq % 10 as id, roaring32_group_create(CAST(RAND() * POW(2, 31) AS INTEGER)) as map
FROM mysql.seq_1_to_1000000 GROUP BY seq % 10;

SELECT roaring32_count(map) FROM example.bitmaps;

SELECT roaring32_group_or_count(map) FROM example.bitmaps;

64bit integer

Please note that the 64-bit version may be significantly (6.5x in some my cases) slower than the 32-bit version.

CREATE DATABASE IF NOT EXISTS example;

CREATE TABLE IF NOT EXISTS example.bitmaps (id INT8 UNSIGNED PRIMARY KEY, map LONGBLOB);

TRUNCATE TABLE example.bitmaps;

INSERT INTO  example.bitmaps
SELECT seq % 10 as id, roaring64_group_create(CAST(RAND() * POW(2, 63) AS INTEGER)) as map
FROM mysql.seq_1_to_1000000 GROUP BY seq % 10;

SELECT roaring64_count(map) FROM example.bitmaps;

SELECT roaring64_group_or_count(map) FROM example.bitmaps;

Dependencies

~84KB