Hologres Bsi Profile Analysis — Ho记录res Bsi 性能分析 Analysis

Prerequisites

This 技能 requires ho记录res-命令行工具 to be 安装ed first:

pip 安装 ho记录res-命令行工具 导出 HO记录RES_技能=ho记录res-bsi-性能分析-analysis

All SQL execution depends on ho记录res-命令行工具 commands (ho记录res sql 运行 --write).

Ho记录res BSI 画像分析

参考文档: 使用BSI进行画像分析中的标签计算

基于 BSI（Bit-sliced 索引，位切片索引）的 Ho记录res 用户画像分析方案，支持对行为标签（GMV、PV、观看时长等）与属性标签（省份、性别等）进行高效联合计算。

方案架构: Ho记录res 实例 + Roaring Bitmap 扩展 + BSI 扩展 + UID 字典编码 + 属性标签 Bitmap + 行为标签 BSI

⚠️ 命令行工具 / 代码自动化限制总览

以下表格总结了本方案中各步骤的自动化可行性：

步骤 可自动化？ 说明 安装扩展（创建 扩展） ⚠️ 命令行工具 需 --write 标志 需要 DDL 写权限：ho记录res sql 运行 --write "创建 扩展 ..." 建表（创建 TABLE） ⚠️ 命令行工具 需 --write 标志 需要 DDL 写权限：ho记录res sql 运行 --write "创建 TABLE ..." UID 字典编码初始化 ⚠️ 命令行工具 需 --write 标志 通过 SQL INSERT 完成；增量维护由用户自行管理 源数据准备 ❓ 需用户指定 用户必须提供属性标签源表和行为标签源表名称。若未指定，需要求用户提供 数据导入（INSERT INTO rb_tag/bsi_gmv） ⚠️ 命令行工具 需 --write 标志 需要 DML 写权限：ho记录res sql 运行 --write "INSERT INTO ..." BSI/RB 查询分析 ✅ 完全可自动化 所有只读查询均可通过 ho记录res sql 运行 "SELECT ..." 执行 分桶数选择 ❌ 不可自动化 取决于集群规模和数据量，需要领域知识和性能调优 增量维护 ❌ 由用户自行管理 可通过 DataWorks 等调度工具实现定时增量导入 背景

Roaring Bitmap 在用户画像场景中广泛用于属性标签索引，但存在两个关键局限：

多标签联合查询问题：Roaring Bitmap 仅适用于固定的分类型"属性标签"。对于量值类"行为标签"（如 GMV、订单金额、观看时长），只能回溯明细表进行关联查询。 高基数标签问题：当某标签去重值数量（基数）很大时，Roaring Bitmap 存储会膨胀，查询性能下降。

BSI 解决了上述两个问题：

对量值类行为标签进行预计算，以 BSI 压缩格式存储，可与属性标签的 Roaring Bitmap 直接联合分析，无需回溯明细表。 通过位切片索引，最多生成 32 个位切片即可存储 INT 范围内的所有行为标签值，实现压缩存储和低延迟查询。 前提条件 Ho记录res 实例已安装 roaringbitmap 扩展 已安装 BSI 扩展 -- 安装所需扩展 创建 扩展 IF NOT EXISTS roaringbitmap; 创建 扩展 IF NOT EXISTS bsi;

⚠️ 命令行工具 写操作：安装扩展需要 --write 标志：

ho记录res sql 运行 --write "创建 扩展 IF NOT EXISTS roaringbitmap" ho记录res sql 运行 --write "创建 扩展 IF NOT EXISTS bsi"

快速开始

• 建表

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

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

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

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

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

⚠️ 命令行工具 写操作：建表需要 --write 标志：

ho记录res sql 运行 --write "创建 TABLE ..."

• 数据导入

-- 构建属性标签 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;

⚠️ 命令行工具 写操作：数据导入需要 --write 标志：

ho记录res sql 运行 --write "INSERT INTO ..."

⚠️ UID 字典维护：dws_uid_dict 必须在 BSI/Roaring Bitmap 导入前完成填充。

初始化：通过 SQL INSERT 完成 增量维护：由用户自行管理（可通过 DataWorks 等调度工具实现定时增量导入）

• 查询示例

-- 查询“广东”+“男性”用户的 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 查询 can be 执行d via 命令行工具:

ho记录res sql 运行 "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"

表设计 基础版（无分桶） 表名 字段 说明 dws_userbase (uid int, province text, gender text) 用户属性标签源表 dws_uid_dict (encode_uid serial, uid int) UID 字典编码表 usershop_behavior (uid int, gmv int) 用户行为标签源表 rb_tag (tag_name text, tag_val text, bitmap roaringbitmap) PK=(tag_name, tag_val) 属性标签 Roaring Bitmap 表 bsi_gmv (gmv_bsi bsi) 行为标签 BSI 表 进阶版（分桶并行计算）

不分桶时，所有 BSI/Roaring Bitmap 数据集中在少数节点上。分桶可将数据分布到集群各节点，实现并行计算。

表名 字段 说明 dws_userbase (uid int, province text, gender text) 用户属性标签源表 dws_uid_dict (encode_uid serial, uid int) UID 字典编码表 usershop_behavior (uid int, category text, gmv int, ds date) 用户行为标签源表（含分类、日期） rb_tag (tag_name text, tag_val text, bucket int, bitmap roaringbitmap) PK=(tag_name, tag_val, bucket) 带分桶的属性标签 Roaring Bitmap 表 bsi_gmv (ds text, category text, bucket int, gmv_bsi bsi) 带分桶、分类、日期的行为标签 BSI 表

关键区别：增加 bucket 字段，并设置 distribution_key = 'bucket' 确保数据均匀分布。

-- 分桶 Roaring Bitmap 属性标签表（复合主键） 创建 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 行为标签表 创建 TABLE bsi_