User profiling with bit-sliced index - Hologres - Centro de documentación de Alibaba Cloud

El algoritmo roaring bitmap funciona bien para consultas de etiquetas de atributos, pero presenta limitaciones al analizar etiquetas de comportamiento numéricas —como GMV, vistas de página o montos de pedidos— en grupos grandes de usuarios. También tiene dificultades con etiquetas de alta cardinalidad, donde el almacenamiento crece significativamente después de la deduplicación. Hologres ofrece el algoritmo de índice bit-sliced (BSI) para resolver estas limitaciones: BSI precalcula y comprime los valores de las etiquetas numéricas, lo que permite ejecutar consultas de suma, distribución y top-K sin necesidad de unir tablas de detalle.

Cuándo usar BSI

Utilice BSI cuando los roaring bitmaps por sí solos no satisfagan sus necesidades:

Análisis de etiquetas de comportamiento numéricas: Las consultas sobre etiquetas numéricas como GMV, monto de pedido o duración de reproducción requieren unir tablas de detalle si solo se usan roaring bitmaps, lo que aumenta la latencia. BSI precalcula y comprime estos valores, eliminando la necesidad de la unión.
Consultas de etiquetas de alta cardinalidad: Cuando una etiqueta tiene muchos valores distintos tras la deduplicación, los roaring bitmaps consumen mucho almacenamiento y ralentizan las consultas. BSI almacena todos los valores de etiquetas de usuario en un máximo de 32 segmentos de bits (bit slices), manteniendo el almacenamiento compacto y las consultas rápidas.

Cómo funciona BSI

BSI codifica los valores de las etiquetas numéricas en binario y distribuye los identificadores de usuario (UID) en segmentos de bits. Cada segmento corresponde a una posición de bit en la representación binaria del valor de la etiqueta.

Con esta codificación:

Una operación de suma se convierte en una intersección de bitmaps en todos los segmentos.
Una operación top-K se convierte en una intersección de bitmaps que comienza desde los bits de mayor orden.

BSI se combina con operaciones de roaring bitmap (AND, OR, NOT) para permitir un análisis rápido de asociaciones entre etiquetas de atributos y etiquetas de comportamiento.

Para consultar la referencia completa de funciones, consulte Funciones BSI.

Notas de uso

Requisito de tipo UID: BSI requiere valores UID de tipo INT. Para UID no enteros, cree una tabla de codificación de diccionario (como dws_uid_dict) para asignar los UID a valores enteros encode_uid antes de construir los datos BSI.
Distribución de recursos: Sin segmentación (bucketing), las tablas BSI y roaring bitmap se ubican en nodos específicos, dejando inactivos otros recursos de la instancia. Para cargas de trabajo de producción con conjuntos de datos grandes, utilice el enfoque de segmentación descrito en Prácticas avanzadas: segmentación.
Agregación entre segmentos: La función bsi_add_agg agrega datos BSI en varios segmentos antes de aplicar bsi_topk o bsi_stat. Úsela siempre en consultas segmentadas.

Prácticas básicas

Este ejemplo utiliza dos tablas de origen:

dws_userbase: etiquetas de atributos de usuario (provincia, género)
usershop_behavior: etiquetas de comportamiento de usuario (GMV = Gross Merchandise Volume)

Configurar tablas

Tabla	Campos	Descripción
`dws_userbase`	`uid int, province text, gender text`	Tabla de origen de etiquetas de atributos (igual que en la solución de tabla ancha)
`dws_uid_dict`	`encode_uid serial, uid int`	Tabla de codificación de diccionario UID (igual que en la solución roaring bitmap)
`usershop_behavior`	`uid int, gmv int`	Tabla de origen de etiquetas de comportamiento
`rb_tag`	`tag_name text, tag_val text, bitmap roaringbitmap`	Tabla de etiquetas de atributos construida con roaring bitmap
`bsi_gmv`	`gmv_bsi bsi`	Tabla de métricas GMV construida con BSI

Cree las tablas con las siguientes sentencias de Lenguaje de Definición de Datos (DDL):

CREATE TABLE dws_userbase (
    uid int NOT NULL PRIMARY KEY,
    province text,
    gender text
    ... -- Other attribute columns.
)
WITH (
    distribution_key = 'uid'
);

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'
);

CREATE TABLE rb_tag (
    tag_name text,
    tag_val text,
    bitmap roaringbitmap
);

CREATE TABLE bsi_gmv (
    gmv_bsi bsi
);

Cargar datos

Paso 1. Construya roaring bitmaps para las etiquetas de atributos (provincia y género) a partir de dws_userbase y dws_uid_dict, e insértelos en rb_tag.

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;

INSERT INTO rb_tag
SELECT
    'gender',
    gender,
    rb_build_agg(b.encode_uid) AS bitmap
FROM
    dws_userbase a
    JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY
    gender;

Paso 2. Construya datos BSI para las etiquetas de comportamiento GMV a partir de usershop_behavior y dws_uid_dict, e insértelos en bsi_gmv.

INSERT INTO bsi_gmv
SELECT
    bsi_build(array_agg(b.encode_uid), array_agg(a.gmv)) AS bitmap
FROM
    usershop_behavior a
    JOIN dws_uid_dict b ON a.uid = b.uid;

Ejecutar análisis de perfiles

Utilice BSI junto con operaciones de roaring bitmap para analizar etiquetas de comportamiento en grupos de usuarios identificados. Las siguientes consultas se enfocan en usuarios masculinos de la provincia de Guangdong. Cada consulta muestra el enfoque BSI junto con el SQL estándar equivalente para fines comparativos.

Analizar etiquetas de comportamiento para un grupo de usuarios

Consultar GMV total y GMV promedio

Enfoque BSI:

SELECT
    sum(kv[1]) AS total_gmv,          -- Total GMV.
    sum(kv[1])/sum(kv[2]) AS avg_gmv  -- Average 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,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

SQL estándar:

SELECT
    sum(b.gmv) AS total_gmv,
    avg(b.gmv) AS avg_gmv
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE
    a.province = 'Guangdong'
    AND a.gender = 'Male';

Consultar distribución de GMV

Enfoque BSI: use bsi_stat con valores límite {100, 300, 500} para calcular la distribución en varios rangos en una sola pasada:

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,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

SQL estándar: requiere una expresión CASE WHEN para cada rango:

SELECT
    CASE
        WHEN gmv >= 0 AND gmv <= 100 THEN '0-100'
        WHEN gmv > 100 AND gmv <= 300 THEN '100-300'
        WHEN gmv > 300 AND gmv <= 500 THEN '300-500'
        WHEN gmv > 500 THEN '>500'
    END AS gmv_range,
    COUNT(*) AS user_count
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE a.province = 'Guangdong'
    AND a.gender = 'Male'
GROUP BY gmv_range
ORDER BY gmv_range;

Consultar los valores K superiores de GMV

Enfoque BSI:

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,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

SQL estándar:

SELECT
    b.uid,
    b.gmv
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE
    a.province = 'Guangdong'
    AND a.gender = 'Male'
ORDER BY gmv DESC
LIMIT 10;

Filtrar usuarios por etiqueta de comportamiento

Identificar usuarios cuyo GMV supera 1,000

Enfoque BSI:

SELECT
    rb_to_array(bsi_gt(gmv_bsi, 1000)) AS crowd
FROM
    bsi_gmv;

SQL estándar:

SELECT
    array_agg(uid)
FROM
    usershop_behavior
WHERE
    gmv > 800;

Prácticas avanzadas: segmentación

Sin segmentación, las tablas BSI y roaring bitmap se ubican en nodos específicos, dejando subutilizado el resto de la instancia. Dividir estas tablas en 65,536 segmentos distribuye los datos en todos los nodos, aumentando la concurrencia de consultas y la eficiencia de los recursos.

La fórmula de asignación de segmentos es encode_uid / 65536 AS bucket. Tanto rb_tag como bsi_gmv usan bucket como clave de distribución para que los segmentos correspondientes de cada tabla siempre se ubiquen juntos en el mismo nodo.

Configurar tablas

En comparación con la configuración básica, la configuración avanzada agrega una columna bucket a rb_tag y bsi_gmv, y añade las columnas category y ds a bsi_gmv para consultas particionadas por tiempo y nivel de categoría.

Tabla	Campos	Descripción
`dws_userbase`	`uid int, province text, gender text`	Igual que en prácticas básicas
`dws_uid_dict`	`encode_uid serial, uid int`	Igual que en prácticas básicas
`usershop_behavior`	`uid int, category text, gmv int, ds date`	Añade `category` y `ds` respecto a la versión básica
`rb_tag`	`tag_name text, tag_val text, bucket int, bitmap roaringbitmap`	Añade `bucket`; la clave de distribución es `bucket`
`bsi_gmv`	`ds date, category text, bucket int, gmv_bsi bsi`	Añade `category`, `ds`, `bucket`; la clave de distribución es `bucket`

CREATE TABLE rb_tag (
    tag_name text,
    tag_val text,
    bucket int,
    bitmap roaringbitmap
)
WITH (
    distribution_key = 'bucket'  -- Use the bucket ID as the distribution key.
);

CREATE TABLE bsi_gmv (
    category text,
    bucket int,
    gmv_bsi bsi,
    ds date
)
WITH (
    distribution_key = 'bucket'  -- Use the bucket ID as the distribution key.
);

Cargar datos

Paso 1. Construya roaring bitmaps para etiquetas de atributos, particionados en segmentos.

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 rb_tag
SELECT
    'gender',
    gender,
    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
    gender,
    "bucket";

Paso 2. Construya datos BSI para GMV, particionados en segmentos. Ejecute este paso diariamente para cargar los datos del día anterior.

INSERT INTO bsi_gmv
SELECT
    a.category,
    b.encode_uid / 65536 AS "bucket",
    bsi_build(array_agg(b.encode_uid), array_agg(a.gmv)) 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;

Ejecutar análisis de perfiles

Todas las consultas siguientes unen bsi_gmv y rb_tag mediante bucket para que el motor procese los segmentos coincidentes juntos en cada nodo.

Analizar etiquetas de comportamiento para un grupo de usuarios

Consultar GMV total y GMV promedio para usuarios masculinos de Guangdong en la categoría 3C (día anterior)

SELECT
    sum(kv[1]) AS total_gmv,          -- Total GMV.
    sum(kv[1])/sum(kv[2]) AS avg_gmv  -- Average 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  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

Consultar distribución de GMV para usuarios masculinos de Guangdong en la categoría 3C (día anterior)

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  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

Consultar los 10 valores superiores de GMV para usuarios masculinos de Guangdong (día anterior, todas las categorías)

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  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

Filtrar usuarios por etiqueta de comportamiento

Identificar usuarios cuyo GMV supera 1,000 en la categoría 3C durante los últimos 30 días

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';

Pasos siguientes

Funciones BSI — Referencia completa de todas las funciones BSI, incluyendo sintaxis, argumentos y tipos de retorno.