hologres-bsi-profile-analysis

Prerequisites

This skill requires hologres-cli to be installed first:

pip install hologres-cli
export HOLOGRES_SKILL=hologres-bsi-profile-analysis

All SQL execution depends on hologres-cli commands (hologres sql run --write).

Hologres BSI profile analysis

Reference documentation: Tag computation in profile analysis using BSI

A Hologres user profile analysis solution based on BSI (Bit-sliced Index), supporting efficient combined computation of behavioral tags (GMV, PV, watch duration, etc.) and attribute tags (province, gender, etc.).

Solution architecture: Hologres 实例 + Roaring Bitmap 扩展 + BSI 扩展 + UID 字典编码 + 属性标签 Bitmap + 行为标签 BSI

⚠️ Overview of CLI / code automation limitations

The following table summarizes the automation feasibility of each step in this solution:

Step	Automatable?	Description
Install extension (`CREATE EXTENSION`）	⚠️ CLI requires `--write` flag	Requires DDL write permission:`hologres sql run --write "CREATE EXTENSION ..."`
Create table (`CREATE TABLE`）	⚠️ CLI requires `--write` flag	Requires DDL write permission:`hologres sql run --write "CREATE TABLE ..."`
UID dictionary encoding initialization	⚠️ CLI requires `--write` flag	Done via SQL INSERT; incremental maintenance is managed by the user
Source data preparation	❓ User must specify	The user must provide the names of the attribute tag source table and the behavioral tag source table. If not specified, the user must be asked to provide them
Data import (INSERT INTO rb_tag/bsi_gmv)	⚠️ CLI requires `--write` flag	Requires DML write permission:`hologres sql run --write "INSERT INTO ..."`
BSI/RB query analysis	✅ Fully automatable	All read-only queries can be executed via `hologres sql run "SELECT ..."` execution
Bucket count selection	❌ Not automatable	Depends on cluster scale and data volume, requiring domain knowledge and performance tuning
Incremental maintenance	❌ Managed by the user	Scheduled incremental import can be implemented via scheduling tools such as DataWorks

Background

Roaring Bitmap is widely used for attribute tag indexing in user profiling scenarios, but it has two key limitations:

Multi-tag combined query problem: Roaring Bitmap is only suitable for fixed categorical "attribute tags". For value-based "behavioral tags" (such as GMV, order amount, watch duration), it can only fall back to the detail table for join queries.
High-cardinality tag problem: When the number of distinct values (cardinality) of a tag is very large, Roaring Bitmap storage expands, and query performance degrades.

BSI solves the above two problems:

Precompute value-based behavioral tags and store them in BSI compressed format, so they can be directly analyzed jointly with the Roaring Bitmap of attribute tags without falling back to the detail table.
Through bit-sliced indexing, at most 32 bit slices can be generated to store all behavioral tag values within the INT range, achieving compressed storage and low-latency queries.

Prerequisites

The Hologres instance has roaringbitmap extension installed
The BSI extension is installed

-- 安装所需扩展
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
CREATE EXTENSION IF NOT EXISTS bsi;

⚠️ CLI write operations: Installing an extension requires the --write flag:
hologres sql run --write "CREATE EXTENSION IF NOT EXISTS roaringbitmap"
hologres sql run --write "CREATE EXTENSION IF NOT EXISTS bsi"

Quick Start

1. Create a table

-- 用户属性标签表
CREATE TABLE dws_userbase (
    uid int NOT NULL PRIMARY KEY,
    province text,
    gender text
) WITH (distribution_key = 'uid');

-- UID 字典编码表（Roaring Bitmap / BSI 必需）
CREATE TABLE dws_uid_dict (
    encode_uid serial,
    uid int PRIMARY KEY
);

-- 用户行为标签表
CREATE TABLE usershop_behavior (
    uid int NOT NULL,
    gmv int
) WITH (distribution_key = 'uid');

-- Roaring Bitmap 属性标签表（tag_name + tag_val 复合主键）
CREATE TABLE rb_tag (
    tag_name text NOT NULL,
    tag_val text NOT NULL,
    bitmap roaringbitmap,
    PRIMARY KEY (tag_name, tag_val)
);

-- BSI 行为标签表（GMV）
CREATE TABLE bsi_gmv (
    gmv_bsi bsi
);

⚠️ CLI write operations: Creating a table requires the --write flag:
hologres sql run --write "CREATE TABLE ..."

2. Data import

-- 构建属性标签 Roaring Bitmap
INSERT INTO rb_tag
SELECT 'province', province, rb_build_agg(b.encode_uid) AS bitmap
FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY province;

-- 构建行为标签 BSI（注意：bsi_build 接收 integer[] 和 bigint[] 数组，gmv 需转换为 bigint）
INSERT INTO bsi_gmv
SELECT bsi_build(array_agg(b.encode_uid), array_agg(a.gmv::bigint)) AS bitmap
FROM usershop_behavior a JOIN dws_uid_dict b ON a.uid = b.uid;

⚠️ CLI write operations: Data import requires the --write flag:
hologres sql run --write "INSERT INTO ..."

⚠️ UID dictionary maintenance：dws_uid_dict must be fully populated before BSI/Roaring Bitmap import.

Initialization: Completed via SQL INSERT

Incremental maintenance: Managed by the user (scheduled incremental import can be implemented via scheduling tools such as DataWorks)

3. Query examples

-- 查询“广东”+“男性”用户的 GMV 总值和人均 GMV
SELECT
    sum(kv[1]) AS total_gmv,
    sum(kv[1]) / sum(kv[2]) AS avg_gmv
FROM (
    SELECT bsi_sum(t1.gmv_bsi, t2.crowd) AS kv
    FROM bsi_gmv t1,
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a,
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
    ) t2
) t;

This query can be executed via CLI:

hologres sql run "SELECT sum(kv[1]) AS total_gmv, sum(kv[1])/sum(kv[2]) AS avg_gmv FROM (SELECT bsi_sum(t1.gmv_bsi, t2.crowd) AS kv FROM bsi_gmv t1, (SELECT rb_and(a.bitmap,b.bitmap) AS crowd FROM (SELECT bitmap FROM rb_tag WHERE tag_name='gender' AND tag_val='Male') a, (SELECT bitmap FROM rb_tag WHERE tag_name='province' AND tag_val='广东') b) t2) t"

Table design

Basic version (no bucketing)

Table name	Field	Description
`dws_userbase`	`(uid int, province text, gender text)`	User attribute tag source table
`dws_uid_dict`	`(encode_uid serial, uid int)`	UID dictionary encoding table
`usershop_behavior`	`(uid int, gmv int)`	User behavior tag source table
`rb_tag`	`(tag_name text, tag_val text, bitmap roaringbitmap)` PK=(tag_name, tag_val)	Attribute tag Roaring Bitmap table
`bsi_gmv`	`(gmv_bsi bsi)`	Behavior tag BSI table

Advanced version (bucketed parallel computation)

Without bucketing, all BSI/Roaring Bitmap data is concentrated on a few nodes. Bucketing can distribute the data across cluster nodes to enable parallel computation.

Table name	Field	Description
`dws_userbase`	`(uid int, province text, gender text)`	User attribute tag source table
`dws_uid_dict`	`(encode_uid serial, uid int)`	UID dictionary encoding table
`usershop_behavior`	`(uid int, category text, gmv int, ds date)`	User behavior tag source table (with category and date)
`rb_tag`	`(tag_name text, tag_val text, bucket int, bitmap roaringbitmap)` PK=(tag_name, tag_val, bucket)	Bucketed attribute tag Roaring Bitmap table
`bsi_gmv`	`(ds text, category text, bucket int, gmv_bsi bsi)`	Behavior tag BSI table with bucketing, category, and date

Key difference: add the bucket field and set distribution_key = 'bucket' to ensure even data distribution.

-- 分桶 Roaring Bitmap 属性标签表（复合主键）
CREATE TABLE rb_tag (
    tag_name text NOT NULL,
    tag_val text NOT NULL,
    bucket int NOT NULL,
    bitmap roaringbitmap,
    PRIMARY KEY (tag_name, tag_val, bucket)
) WITH (distribution_key = 'bucket');

-- 分桶 BSI 行为标签表
CREATE TABLE bsi_gmv (
    category text,
    bucket int,
    gmv_bsi bsi,
    ds date
) WITH (distribution_key = 'bucket');

Common query patterns

Pattern 1: Audience selection + behavior tag analysis

Sum and average: Query the total GMV and per-capita GMV of the selected audience.

-- 基础版：广东+男性用户的 GMV 总值和均值
SELECT
    sum(kv[1]) AS total_gmv,
    sum(kv[1]) / sum(kv[2]) AS avg_gmv
FROM (
    SELECT bsi_sum(t1.gmv_bsi, t2.crowd) AS kv
    FROM bsi_gmv t1,
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a,
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
    ) t2
) t;

-- 分桶版：广东+男性用户、3C 品类、昨日的 GMV 总值和均值
SELECT
    sum(kv[1]) AS total_gmv,
    sum(kv[1]) / sum(kv[2]) AS avg_gmv
FROM (
    SELECT bsi_sum(t1.gmv_bsi, t2.crowd) AS kv, t1.bucket
    FROM (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1
    JOIN
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a
     JOIN
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
     ON a.bucket = b.bucket
    ) t2
    ON t1.bucket = t2.bucket
) t;

Distribution statistics: Calculate the GMV distribution by specified boundary values.

-- 基础版：按边界值 [100, 300, 500] 统计 GMV 分布
SELECT bsi_stat('{100,300,500}', filter_bsi)
FROM (
    SELECT bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi
    FROM bsi_gmv t1,
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a,
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
    ) t2
) t;

-- 分桶版：聚合 BSI 后统计 GMV 分布
SELECT bsi_stat('{100,300,500}', bsi_add_agg(filter_bsi))
FROM (
    SELECT bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi, t1.bucket
    FROM (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1
    JOIN
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a
     JOIN
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
     ON a.bucket = b.bucket
    ) t2
    ON t1.bucket = t2.bucket
) t;

Top K: Query the Top K users by behavior tag value.

-- 基础版：GMV Top 10 用户
SELECT rb_to_array(bsi_topk(filter_bsi, 10))
FROM (
    SELECT bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi
    FROM bsi_gmv t1,
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a,
        (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
    ) t2
) t;

-- 分桶版：昨日 GMV Top 10 用户
SELECT bsi_topk(bsi_add_agg(filter_bsi), 10)
FROM (
    SELECT bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi, t1.bucket
    FROM (SELECT bsi_add_agg(gmv_bsi) AS gmv_bsi, bucket FROM bsi_gmv WHERE ds = CURRENT_DATE - interval '1 day' GROUP BY bucket) t1
    JOIN
    (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a
     JOIN
        (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '广东') b
     ON a.bucket = b.bucket
    ) t2
    ON t1.bucket = t2.bucket
) t;

Pattern 2: Audience selection based on behavior tags

Select users based on behavior tag thresholds.

-- 基础版：圈选 GMV > 1000 的用户
SELECT rb_to_array(bsi_gt(gmv_bsi, 1000)) AS crowd
FROM bsi_gmv;

-- 分桶版：圈选过去一个月内 3C 品类 GMV > 1000 的用户
SELECT rb_to_array(bsi_gt(bsi_add_agg(gmv_bsi), 1000)) AS crowd
FROM bsi_gmv
WHERE category = '3C'
  AND ds BETWEEN CURRENT_DATE - interval '30 day' AND CURRENT_DATE - interval '1 day';

Other comparison functions:

-- GMV >= 1000
SELECT rb_to_array(bsi_ge(gmv_bsi, 1000)) FROM bsi_gmv;

-- GMV < 500
SELECT rb_to_array(bsi_lt(gmv_bsi, 500)) FROM bsi_gmv;

-- GMV between 100 and 500 (inclusive)
SELECT rb_to_array(bsi_range(gmv_bsi, 100, 500)) FROM bsi_gmv;

-- GMV == 200
SELECT rb_to_array(bsi_eq(gmv_bsi, 200)) FROM bsi_gmv;

-- GMV != 0
SELECT rb_to_array(bsi_neq(gmv_bsi, 0)) FROM bsi_gmv;

BSI function quick reference

For the complete function reference, see references/bsi-functions.md。

Function	Input	Output	Description
`bsi_build`	`integer[], bigint[]`	`bsi`	Create a BSI from a key-value array
`bsi_add_value`	`bsi, integer, bigint`	`bsi`	Add a key-value pair to a BSI
`bsi_iterate`	`bsi`	`set of integer[]`	Expand a BSI into key-value pairs
`bsi_show`	`bsi/bytea, integer`	`text`	Show the first N key-value pairs
`bsi_ebm`	`bsi/bytea`	`roaringbitmap`	Get all keys as a Roaring Bitmap
`bsi_eq`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value == N
`bsi_neq`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value != N
`bsi_gt`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value > N
`bsi_ge`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value >= N
`bsi_lt`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value < N
`bsi_le`	`bsi, bigint [, bytea]`	`roaringbitmap`	Keys with value <= N
`bsi_range`	`bsi, bigint, bigint [, bytea]`	`roaringbitmap`	Keys with values in the range [N, M]
`bsi_filter`	`bsi/bytea, bytea`	`bsi`	Filter a BSI by Roaring Bitmap intersection
`bsi_sum`	`bsi/bytea [, bytea]`	`bigint[]`	Returns `[总和, 基数]`
`bsi_stat`	`bigint[], bsi/bytea [, bytea]`	`text`	Calculate the distribution by boundary values.The first parameter is the boundary array, such as `'{100,300,500}'`
`bsi_topk`	`bsi/bytea, [bytea,] integer`	`roaringbitmap`	Get the Top K keys by value
`bsi_transpose`	`bsi/bytea [, bytea]`	`roaringbitmap`	Deduplicated values as Roaring Bitmap
`bsi_transpose_with_count`	`bsi/bytea [, bytea]`	`bsi`	Deduplicated values and their counts as BSI
`bsi_add`	`bsi, bsi`	`bsi`	Add two BSI values of the same key
`bsi_add_agg`	`bsi`	`bsi`	Cross-row sum aggregation
`bsi_merge`	`bsi, bsi`	`bsi`	Merge two BSIs (keys must not overlap)
`bsi_merge_agg`	`bsi`	`bsi`	Merge aggregation (keys must not overlap)
`bsi_compare`	`text, bsi, [bytea,] bigint, bigint`	`roaringbitmap`	Comparison filtering (LT/LE/GT/GE/EQ/NEQ/RANGE)

Optional bytea parameter: Many functions support an optional Roaring Bitmap (bytea) parameter, intersecting first before computing. This avoids calling bsi_filter。

Data import modes

Basic import

-- 属性标签导入 Roaring Bitmap
INSERT INTO rb_tag
SELECT 'province', province, rb_build_agg(b.encode_uid) AS bitmap
FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY province;

-- 行为标签导入 BSI（bsi_build 需要 integer[] + bigint[] 数组参数）
INSERT INTO bsi_gmv
SELECT bsi_build(array_agg(b.encode_uid), array_agg(a.gmv::bigint)) AS bitmap
FROM usershop_behavior a JOIN dws_uid_dict b ON a.uid = b.uid;

Bucketed import

-- 带分桶的属性标签导入
INSERT INTO rb_tag
SELECT 'province', province, encode_uid / 65536 AS bucket,
    rb_build_agg(b.encode_uid) AS bitmap
FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY province, bucket;

-- 带分桶的行为标签导入
INSERT INTO bsi_gmv
SELECT a.category, b.encode_uid / 65536 AS bucket,
    bsi_build(array_agg(b.encode_uid), array_agg(a.gmv::bigint)) AS bitmap, a.ds
FROM usershop_behavior a JOIN dws_uid_dict b ON a.uid = b.uid
WHERE ds = CURRENT_DATE - interval '1 day'
GROUP BY category, bucket, ds;

Implementation notes

⚠️ Operations requiring the `--write` flag (via CLI)

The following operations modify data; when executed via hologres sql run , they require the --write flag:

Operation	CLI command	Remarks
Install extension	`hologres sql run --write "CREATE EXTENSION IF NOT EXISTS bsi"`	DDL write
Create table	`hologres sql run --write "CREATE TABLE ..."`	DDL write
Data import (INSERT)	`hologres sql run --write "INSERT INTO ..."`	DML write

✅ Operations that can be executed read-only (via CLI)

All BSI/Roaring Bitmap query functions are read-only and fully automatable:

# 求和与基数
hologres sql run "SELECT bsi_sum(gmv_bsi) FROM bsi_gmv"

# Top K 查询
hologres sql run "SELECT rb_to_array(bsi_topk(gmv_bsi, 10)) FROM bsi_gmv"

# 分布统计
hologres sql run "SELECT bsi_stat('{100,300,500}', gmv_bsi) FROM bsi_gmv"

# 人群过滤
hologres sql run "SELECT rb_to_array(bsi_gt(gmv_bsi, 1000)) FROM bsi_gmv"

# 比较查询（均为只读）
hologres sql run "SELECT rb_to_array(bsi_ge(gmv_bsi, 1000)) FROM bsi_gmv"
hologres sql run "SELECT rb_to_array(bsi_lt(gmv_bsi, 500)) FROM bsi_gmv"
hologres sql run "SELECT rb_to_array(bsi_range(gmv_bsi, 100, 500)) FROM bsi_gmv"
hologres sql run "SELECT rb_to_array(bsi_eq(gmv_bsi, 200)) FROM bsi_gmv"
hologres sql run "SELECT rb_to_array(bsi_neq(gmv_bsi, 0)) FROM bsi_gmv"

❌ Operations that cannot be automated via CLI or code

The following steps require manual setup or are beyond the CLI's scope:

Step	Reason	Solution
UID dictionary encoding (`dws_uid_dict`）	must be populated before BSI/RB import	Initialization is done via SQL INSERT; incremental maintenance is managed by the user
Source data preparation	The user must specify the attribute tag source table and behavior tag source table	The user must specify the attribute tag source table and behavior tag source table; if not specified, the user must be asked to provide them
Bucket count selection	Depends on cluster scale and data volume; requires domain knowledge	Using `encode_uid / 65536` as the bucketing key is recommended; specifics require tuning based on the business
Incremental maintenance	Managed by the user	Scheduled incremental imports can be implemented via scheduling tools such as DataWorks

Best practices

Always use UID dictionary encoding: BSI and Roaring Bitmap require consecutive integer keys. Before importing, first build dws_uid_dict。
Use bucketing for large data volumes: Without bucketing, data is concentrated on a few nodes. Use encode_uid / 65536 as the bucketing key to achieve even distribution.
Set distribution_key = 'bucket': Ensure data sharding aligns with bucket boundaries to achieve local computation.
Use bsi_add_aggfor bucketed aggregation: aggregate BSI across buckets before analysis.
Prefer using the optional bytea parameter: No need to bsi_filter + bsi_sum call separately; directly using bsi_sum(bsi, crowd_bytea) has better performance.
Use bsi_stat for distribution statistics: More efficient than multiple CASE WHEN queries on the detail table.
Use bsi_topk for sorting: Converting global sorting into bit intersection operations is much faster than ORDER BY ... LIMIT .
BSI value range is limited to INT: BSI stores values in 32-bit slices, and values must be within the integer range.

Reference Documentation

Document	Description
references/bsi-functions.md	Complete BSI function reference, including syntax and examples
references/table-design.md	Table design patterns and DDL for the basic and bucketed versions

About this skill

Prerequisites

⚠️ Overview of CLI / code automation limitations

Prerequisites

Quick Start

1. Create a table

2. Data import

3. Query examples

Table design

Basic version (no bucketing)

Advanced version (bucketed parallel computation)

Common query patterns

Pattern 1: Audience selection + behavior tag analysis

Pattern 2: Audience selection based on behavior tags

BSI function quick reference

Data import modes

Basic import

Bucketed import

Implementation notes

⚠️ Operations requiring the --write flag (via CLI)

✅ Operations that can be executed read-only (via CLI)

❌ Operations that cannot be automated via CLI or code

Best practices

Reference Documentation

⚠️ Operations requiring the `--write` flag (via CLI)