User profiling with bit-sliced index - Hologres: Central de Documentação do Alibaba Cloud

Quando usar o BSI

Use o BSI quando os roaring bitmaps sozinhos não atenderem às suas necessidades:

Análise de tags de comportamento numéricas: Consultas em tags numéricas, como GMV, valor do pedido ou duração de reprodução, exigem junções com tabelas de detalhes ao usar apenas roaring bitmaps, o que aumenta a latência da consulta. O BSI pré-computa e compacta esses valores, eliminando a necessidade de junção.
Consultas de tags de alta cardinalidade: Quando uma tag possui muitos valores distintos após a deduplicação, os roaring bitmaps consomem muito armazenamento e tornam as consultas mais lentas. O BSI armazena todos os valores de tags dos usuários em no máximo 32 fatias de bits, mantendo o armazenamento compacto e as consultas rápidas.

Como o BSI funciona

O BSI codifica valores de tags numéricas em binário e distribui os IDs de usuário (UIDs) entre fatias de bits. Cada fatia corresponde a uma posição de bit na representação binária do valor da tag.

Com essa codificação:

Uma operação de soma torna-se uma interseção de bitmaps em todas as fatias.
Uma operação top-K torna-se uma interseção de bitmaps começando pelos bits de maior ordem.

O BSI combina-se com operações de roaring bitmap (AND, OR, NOT) para permitir análises de associação rápidas entre tags de atributos e tags de comportamento.

Para obter a referência completa de funções, consulte Funções BSI.

Observações de uso

Requisito de tipo de UID: O BSI exige valores de UID do tipo INT. Para UIDs não inteiros, crie uma tabela de codificação de dicionário (como dws_uid_dict) para mapear UIDs para valores inteiros encode_uid antes de criar os dados BSI.
Distribuição de recursos: Sem bucketing, as tabelas BSI e roaring bitmap ficam em nós específicos, deixando outros recursos da instância ociosos. Para cargas de trabalho de produção com grandes conjuntos de dados, use a abordagem de bucketing descrita em Práticas avançadas: bucketing.
Agregação entre buckets: A função bsi_add_agg agrega dados BSI em vários buckets antes de aplicar bsi_topk ou bsi_stat. Use-a sempre em consultas com bucketing.

Práticas básicas

Este exemplo usa duas tabelas source:

dws_userbase: tags de atributos do usuário (província, gênero)
usershop_behavior: tags de comportamento do usuário (GMV = Volume Bruto de Mercadorias)

Configurar tabelas

Tabela	Campos	Descrição
`dws_userbase`	`uid int, province text, gender text`	Tabela source de tags de atributos (igual à solução de tabela wide)
`dws_uid_dict`	`encode_uid serial, uid int`	Tabela de codificação de dicionário de UID (igual à solução de roaring bitmap)
`usershop_behavior`	`uid int, gmv int`	Tabela source de tags de comportamento
`rb_tag`	`tag_name text, tag_val text, bitmap roaringbitmap`	Tabela de tags de atributos criada com roaring bitmap
`bsi_gmv`	`gmv_bsi bsi`	Tabela de métricas GMV criada com BSI

Crie as tabelas com as seguintes instruções de Linguagem de Definição de Dados (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
);

Carregar dados

Passo 1. Crie roaring bitmaps para tags de atributos (província e gênero) a partir de dws_userbase e dws_uid_dict, e insira-os em 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;

Passo 2. Crie dados BSI para tags de comportamento GMV a partir de usershop_behavior e dws_uid_dict, e insira-os em 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;

Executar análise de perfil

Use o BSI com operações de roaring bitmap para analisar tags de comportamento de grupos de usuários identificados. As consultas a seguir têm como alvo usuários do sexo masculino da província de Guangdong. Cada consulta mostra a abordagem BSI juntamente com o SQL simples equivalente para comparação.

Analisar tags de comportamento para um grupo de usuários

Consultar GMV total e GMV médio

Abordagem 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 simples:

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 distribuição de GMV

Abordagem BSI — use bsi_stat com valores de limite {100, 300, 500} para calcular a distribuição em vários intervalos em uma única passagem:

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 simples — requer uma expressão CASE WHEN para cada intervalo:

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 os K maiores valores de GMV

Abordagem 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 simples:

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 usuários por tag de comportamento

Identificar usuários cujo GMV excede 1.000

Abordagem BSI:

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

SQL simples:

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

Práticas avançadas: bucketing

Sem bucketing, as tabelas BSI e roaring bitmap ficam concentradas em nós específicos, deixando o restante da instância subutilizado. Dividir essas tabelas em 65.536 segmentos distribui os dados por todos os nós, aumentando a concorrência de consultas e a eficiência dos recursos.

A fórmula de atribuição de bucket é encode_uid / 65536 AS bucket. Tanto rb_tag quanto bsi_gmv usam bucket como chave de distribuição para garantir que segmentos correspondentes de cada tabela fiquem sempre colocalizados no mesmo nó.

Configurar tabelas

Em comparação com a configuração básica, a configuração avançada adiciona uma coluna bucket a rb_tag e bsi_gmv, além de adicionar colunas category e ds a bsi_gmv para consultas particionadas por tempo e nível de categoria.

Tabela	Campos	Descrição
`dws_userbase`	`uid int, province text, gender text`	Igual às práticas básicas
`dws_uid_dict`	`encode_uid serial, uid int`	Igual às práticas básicas
`usershop_behavior`	`uid int, category text, gmv int, ds date`	Adiciona `category` e `ds` em relação ao básico
`rb_tag`	`tag_name text, tag_val text, bucket int, bitmap roaringbitmap`	Adiciona `bucket`; a chave de distribuição é `bucket`
`bsi_gmv`	`ds date, category text, bucket int, gmv_bsi bsi`	Adiciona `category`, `ds`, `bucket`; a chave de distribuição é `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.
);

Carregar dados

Passo 1. Crie roaring bitmaps para tags de atributos, particionados em buckets.

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

Passo 2. Crie dados BSI para GMV, particionados em buckets. Execute este passo diariamente para carregar os dados do dia 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;

Executar análise de perfil

Todas as consultas abaixo fazem JOIN entre bsi_gmv e rb_tag em bucket para que o mecanismo processe segmentos correspondentes juntos em cada nó.

Analisar tags de comportamento para um grupo de usuários

Consultar GMV total e GMV médio para usuários do sexo masculino de Guangdong na categoria 3C (dia 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 distribuição de GMV para usuários do sexo masculino de Guangdong na categoria 3C (dia 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 os 10 maiores valores de GMV para usuários do sexo masculino de Guangdong (dia anterior, todas as categorias)

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 usuários por tag de comportamento

Identificar usuários cujo GMV excede 1.000 na categoria 3C nos últimos 30 dias

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

Próximos passos

Funções BSI — Referência completa para todas as funções BSI, incluindo sintaxe, argumentos e tipos de retorno.