E-MapReduce:Precise deduplication with RoaringBitmap

Step 1: Register the UDFs

Register the bitmap-related UDFs. This example uses temporary UDFs. You can register temporary or permanent UDFs based on your needs.

CREATE TEMPORARY FUNCTION to_bitmap AS 'org.apache.paimon.spark.bitmap.ToBitmapUDF' USING JAR 'oss://bucket/path/to/paimon-spark-bitmap-1.0-SNAPSHOT.jar';
CREATE TEMPORARY FUNCTION bitmap_count AS 'org.apache.paimon.spark.bitmap.BitmapCountUDF' USING JAR 'oss://bucket/path/to/paimon-spark-bitmap-1.0-SNAPSHOT.jar';
CREATE TEMPORARY FUNCTION bitmap_contains AS 'org.apache.paimon.spark.bitmap.BitmapContainsUDF' USING JAR 'oss://bucket/path/to/paimon-spark-bitmap-1.0-SNAPSHOT.jar';
CREATE TEMPORARY FUNCTION bitmap_to_string AS 'org.apache.paimon.spark.bitmap.BitmapToStringUDF' USING JAR 'oss://bucket/path/to/paimon-spark-bitmap-1.0-SNAPSHOT.jar';

Step 2: Create a Paimon aggregation table

Create a Paimon primary-key table that uses the rbm32 aggregate function.

CREATE TABLE user_page_visits (
    user_id INT,
    page_visits BINARY
) USING paimon
TBLPROPERTIES (
    'primary-key' = 'user_id',
    'merge-engine' = 'aggregation',
    'fields.page_visits.aggregate-function' = 'rbm32'
);

Key parameters:

• 'primary-key' = 'user_id': specifies user_id as the primary key.

• 'merge-engine' = 'aggregation': enables the Aggregation Merge Engine.

• 'fields.page_visits.aggregate-function' = 'rbm32': applies the rbm32 aggregate function to the page_visits column for INT values.

Step 3: Write data

Use to_bitmap to convert page IDs to bitmap bytes before writing them to the table.

-- Initial write
INSERT INTO user_page_visits VALUES
    (1, to_bitmap(100, 101, 102)),   -- User 1 visited pages 100, 101, 102
    (2, to_bitmap(101, 103)),        -- User 2 visited pages 101, 103
    (3, to_bitmap(102, 104));        -- User 3 visited pages 102, 104

-- Incremental write (Paimon automatically OR-merges bitmaps for the same user_id)
INSERT INTO user_page_visits VALUES
    (1, to_bitmap(103, 105)),        -- User 1 also visited pages 103, 105
    (2, to_bitmap(104, 106));        -- User 2 also visited pages 104, 106

Step 4: Query the deduplication results

Query how many distinct pages each user has visited.

SELECT user_id, bitmap_count(page_visits) AS unique_pages
FROM user_page_visits;

Expected results:

You can also run the following queries for more operations.

-- Check whether a user has visited a specific page
SELECT user_id, bitmap_contains(page_visits, 101) AS visited_page_101
FROM user_page_visits;

-- View the values in the bitmap for debugging
SELECT user_id, bitmap_to_string(page_visits) AS pages
FROM user_page_visits;

Step 1: Create a table

Create a Paimon primary-key table that uses the rbm64 aggregate function. Because device IDs exceed the INT range, rbm64 is used.

CREATE TABLE app_device_visits (
    app_id INT,
    device_bitmap BINARY
) USING paimon
TBLPROPERTIES (
    'primary-key' = 'app_id',
    'merge-engine' = 'aggregation',
    'fields.device_bitmap.aggregate-function' = 'rbm64'
);

Step 2: Write data

When you pass BIGINT values to to_bitmap, it automatically produces the 64-bit format.

-- Initial write
INSERT INTO app_device_visits VALUES
    (1001, to_bitmap(CAST(8000000001 AS BIGINT), CAST(8000000002 AS BIGINT), CAST(8000000003 AS BIGINT))),
    (1002, to_bitmap(CAST(8000000002 AS BIGINT), CAST(8000000004 AS BIGINT)));

-- Incremental write (automatically merged)
INSERT INTO app_device_visits VALUES
    (1001, to_bitmap(CAST(8000000004 AS BIGINT), CAST(8000000006 AS BIGINT)));

Step 3: Query the deduplication results

SELECT app_id,
       bitmap_count(device_bitmap) AS unique_devices,
       bitmap_contains(device_bitmap, CAST(8000000002 AS BIGINT)) AS has_device
FROM app_device_visits;

Expected results:

You can also view the values in the bitmap.

SELECT app_id, bitmap_to_string(device_bitmap) AS devices
FROM app_device_visits;