BSI - Hologres - 阿里云

Jika sejumlah besar tag atribut dan perilaku terdapat dalam skenario analisis profil pengguna, algoritma roaring bitmap memiliki keterbatasan signifikan. Hologres menyediakan algoritma indeks bit-sliced (BSI) untuk mengatasi keterbatasan ini sambil mempertahankan manfaat dari algoritma roaring bitmap. Topik ini menjelaskan praktik terbaik untuk menerapkan komputasi tag di Hologres menggunakan BSI.

Informasi latar belakang

Algoritma roaring bitmap digunakan dalam skenario analisis profil pengguna. Algoritma ini membuat indeks untuk tabel tag, mengkodekan ID pengguna, dan menyimpan ID pengguna yang telah dikodekan dalam format bitmap. Dengan cara ini, operasi relasional diubah menjadi operasi irisan, gabungan, dan selisih bitmap, sehingga mempercepat komputasi waktu nyata. Namun, algoritma roaring bitmap memiliki keterbatasan dalam skenario berikut:

Kueri gabungan multi-tag: Algoritma roaring bitmap lebih cocok untuk kueri berbasis tag atribut dan hanya dapat digunakan untuk tag tetap. Jika digunakan untuk kueri gabungan berbasis beberapa tag perilaku seperti jumlah tampilan halaman (PV), jumlah pesanan, atau durasi pemutaran, Anda harus melacak balik tabel rinci, meningkatkan biaya pengeboran data rinci.
Kueri berbasis tag dengan kardinalitas tinggi: Jika sejumlah besar data ada setelah deduplikasi berdasarkan tag, ruang penyimpanan untuk roaring bitmap meningkat secara signifikan, dan kinerja kueri menurun.

Berikut ini menjelaskan cara menggunakan algoritma BSI dan fungsi spesifik untuk mengatasi keterbatasan tersebut. Untuk informasi lebih lanjut tentang fungsi-fungsi ini, lihat fungsi BSI.

Dalam kueri gabungan multi-tag, data dengan tag perilaku numerik dihitung sebelumnya dan dikompresi menggunakan algoritma BSI untuk memastikan akurasi. Kueri gabungan pada tabel rinci tidak diperlukan, sehingga analisis asosiasi berdasarkan tag atribut dan perilaku dapat dilakukan secara efisien.
Dalam kueri berbasis tag perilaku dengan kardinalitas tinggi, algoritma BSI digunakan untuk menyimpan nilai tag perilaku semua pengguna yang nilai cid bertipe INT berada dalam rentang tertentu dengan mengonversi nilai tag menjadi maksimal 32-bit slice. Konversi biner dan operasi irisan, gabungan, serta selisih dari roaring bitmap dilakukan untuk komputasi data cepat, mencapai penyimpanan terkompresi dan kueri latensi rendah pada nilai tag perilaku dengan kardinalitas tinggi.

Solusi analisis profil

Dalam contoh ini, terdapat dua tabel tag pengguna. Tabel dws_userbase berisi tag atribut dasar pengguna seperti provinsi dan gender. Tabel usershop_behavior berisi tag perilaku pengguna seperti Gross Merchandise Volume (GMV).

Gambar berikut menunjukkan data mentah.
Gambar berikut menunjukkan bitmap atribut pengguna dasar dan uids di tabel rb_tag. Di tabel ini, kolom tag_name berisi tag seperti provinsi dan gender.
Gambar berikut menunjukkan bit slice yang dihasilkan berdasarkan nilai tag perilaku pengguna dan uids menggunakan algoritma BSI. Nilai tag dikonversi menjadi nilai biner, dan uid bitmap dari empat slice dicatat di tabel bsi_gmv.

Dalam solusi ini, uids dan nilai perilaku terkait dikompresi ke dalam BSI. Komputasi cepat berbasis tag dicapai menggunakan algoritma BSI dan operasi AND, OR, serta NOT pada roaring bitmap. Contoh:

Gunakan algoritma BSI untuk melakukan operasi sum pada nilai tag perilaku kelompok pengguna yang diidentifikasi. Operasi sum diubah menjadi operasi irisan bitmap pada setiap slice.
Gunakan algoritma BSI untuk mendapatkan nilai top K berdasarkan tag perilaku kelompok pengguna yang diidentifikasi. Pengurutan global diubah menjadi operasi irisan bitmap pada slice dari bit tinggi ke bit rendah.

Praktik dasar analisis profil

Tabel BSI

Nama tabel	Bidang	Deskripsi
dws_userbase	(uid int, province text, gender text)	Tabel yang berisi tag atribut asli pengguna, sama seperti tabel dalam solusi tabel lebar.
dws_uid_dict	(encode_uid serial, uid int)	Tabel pengkodean kamus uid, sama seperti tabel dalam solusi roaring bitmap.
usershop_behavior	(uid int, gmv int)	Tabel tag perilaku asli yang berisi tag perilaku pengguna, seperti GMV.
rb_tag	(tag_name text, tag_val text, bitmap roaringbitmap)	Tabel tag atribut yang didasarkan pada algoritma roaring bitmap.
bsi_gmv	(gmv_bsi bsi)	Tabel metrik GMV rinci yang didasarkan pada algoritma BSI.

Pernyataan bahasa definisi data (DDL) yang digunakan untuk membuat tabel-tabel di atas:

Buat tabel dws_userbase.

CREATE TABLE dws_userbase (
    uid int NOT NULL PRIMARY KEY,
    province text,
    gender text
    ... -- Kolom atribut lainnya.
)
WITH (
    distribution_key = 'uid'
);

Buat tabel dws_uid_dict.

CREATE TABLE dws_uid_dict (
    encode_uid serial,
    uid int PRIMARY KEY
);

Buat tabel usershop_behavior.

CREATE TABLE usershop_behavior (
    uid int NOT NULL,
    gmv int
)
WITH (
    distribution_key = 'uid'
);

Buat tabel rb_tag.

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

Buat tabel bsi_gmv.

CREATE TABLE bsi_gmv (
    gmv_bsi bsi
);

Impor data

Hasilkan roaring bitmap nilai tag atribut berdasarkan tabel dws_userbase dan dws_uid_dict, lalu simpan roaring bitmap dalam bucket yang berbeda.

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;

Hasilkan data BSI nilai tag perilaku berdasarkan tabel usershop_behavior dan dws_uid_dict, lalu simpan data tabel dalam bucket yang berbeda.

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
;

Analisis profil

Anda dapat menggunakan algoritma BSI untuk melakukan analisis asosiasi berdasarkan tag atribut dan perilaku pengguna. Sebagai contoh, Anda dapat memperoleh wawasan tentang tag perilaku kelompok pengguna yang diidentifikasi atau memfilter kelompok pengguna berdasarkan tag perilaku.

Identifikasi kelompok pengguna dan analisis tag perilaku

Kueri total GMV dan rata-rata GMV pengguna pria dari provinsi Guangdong.

Gunakan algoritma BSI dan roaring bitmap.

SELECT 
    sum(kv[1]) AS total_gmv, -- Total GMV.
    sum(kv[1])/sum(kv[2]) AS avg_gmv -- Rata-rata 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, -- Pengguna laki-laki.
            (SELECT bitmap FROM rb_tag WHERE tag_name='province' AND tag_val ='Guangdong') b -- Pengguna dari provinsi Guangdong.
        ) t2 
    ) t;

Gunakan tabel dws_userbase dan usershop_behavior.

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

Kueri distribusi jumlah konsumsi pengguna pria dari provinsi Guangdong.

Gunakan algoritma BSI dan roaring bitmap: Gunakan fungsi bsi_stat untuk mendefinisikan array nilai batas, mempercepat kueri dalam beberapa rentang.

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, -- Pengguna laki-laki.
            (SELECT bitmap FROM rb_tag WHERE tag_name='province' AND tag_val = 'Guangdong') b -- Pengguna dari provinsi Guangdong.
        ) t2
    ) t;

Gunakan tabel dws_userbase dan usershop_behavior: Anda hanya dapat menggunakan sintaks CASE WHEN.

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;

Kueri jumlah konsumsi top K pengguna pria dari provinsi Guangdong pada hari sebelumnya.

Gunakan algoritma BSI dan roaring bitmap.

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, -- Pengguna laki-laki.
            (SELECT bitmap FROM rb_tag WHERE tag_name='province' AND tag_val ='Guangdong') b -- Pengguna dari provinsi Guangdong.
        ) t2
    ) t;

Gunakan tabel dws_userbase dan usershop_behavior untuk menanyakan data.

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;

Identifikasi kelompok pengguna berdasarkan tag perilaku

Identifikasi pengguna yang jumlah konsumsinya lebih besar dari 1.000.
- Gunakan algoritma BSI dan roaring bitmap.
```
SELECT
    rb_to_array(bsi_gt(gmv_bsi, 1000)) AS crowd
FROM
    bsi_gmv;
```
- Gunakan tabel dws_userbase dan usershop_behavior.
```
SELECT
    array_agg(uid)
FROM
    usershop_behavior
WHERE
    gmv > 800;
```

Praktik lanjutan analisis profil: bucketing

Dalam contoh sebelumnya, tabel dws_userbase dikompresi menjadi tabel roaring bitmap berdasarkan kolom provinsi dan gender, sedangkan tabel usershop_behavior dikompresi menjadi tabel BSI. Tabel roaring bitmap dan BSI yang terkompresi hanya didistribusikan pada node tertentu dari sebuah instance. Akibatnya, sumber daya komputasi dan penyimpanan tidak terdistribusi secara merata, dan sumber daya instance tidak sepenuhnya digunakan. Untuk menyelesaikan masalah ini, Anda dapat membagi tabel roaring bitmap dan BSI menjadi beberapa segmen dan mendistribusikan segmen-segmen tersebut pada instance untuk meningkatkan konkurensi eksekusi. Dalam contoh ini, tabel bitmap dan BSI dibagi menjadi 65.536 segmen.

Tabel BSI

Nama tabel	Bidang	Deskripsi
dws_userbase	(uid int, province text, gender text)	Tabel yang berisi tag atribut asli pengguna, sama seperti tabel dalam praktik dasar analisis profil.
dws_uid_dict	(encode_uid serial, uid int)	Tabel pengkodean kamus uid, sama seperti tabel dalam praktik dasar analisis profil.
usershop_behavior	(uid int, category text, gmv int, ds date)	Tabel tag perilaku asli. Dibandingkan dengan tabel dalam praktik dasar analisis profil, bidang category dan ds ditambahkan ke tabel ini.
rb_tag	(tag_name text, tag_val text, bucket int, bitmap roaringbitmap)	Tabel tag atribut yang didasarkan pada algoritma roaring bitmap. Dibandingkan dengan tabel dalam praktik dasar analisis profil, bidang bucket ditambahkan ke tabel ini.
bsi_gmv	(ds text, category text, bucket int, gmv_bsi bsi)	Tabel metrik GMV rinci yang didasarkan pada algoritma BSI. Dibandingkan dengan tabel dalam praktik dasar analisis profil, bidang category, ds, dan bucket ditambahkan ke tabel ini.

Pernyataan DDL yang digunakan untuk membuat tabel-tabel di atas:

Buat tabel rb_tag dan simpan data dalam bucket yang berbeda.

CREATE TABLE rb_tag (
    tag_name text,
    tag_val text,
    bucket int,
    bitmap roaringbitmap
)
WITH (
    distribution_key = 'bucket ' -- Gunakan ID bucket sebagai kunci distribusi.
);

Buat tabel bsi_gmv dan simpan data dalam bucket yang berbeda.

CREATE TABLE bsi_gmv (
    category text,
    bucket int,
    gmv_bsi bsi,
    ds date
)
WITH (
    distribution_key = 'bucket' -- Gunakan ID bucket sebagai kunci distribusi.
);

Impor data

Hasilkan roaring bitmap nilai tag atribut berdasarkan tabel dws_userbase dan dws_uid_dict, lalu simpan roaring bitmap dalam bucket yang berbeda.

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

Hasilkan data BSI nilai tag perilaku berdasarkan tabel usershop_behavior dan dws_uid_dict, lalu simpan data tabel dalam bucket yang berbeda.

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;

Analisis profil

Anda dapat menggunakan algoritma BSI untuk melakukan analisis asosiasi berdasarkan tag atribut dan perilaku pengguna. Sebagai contoh, Anda dapat memperoleh wawasan tentang tag perilaku kelompok pengguna yang diidentifikasi, memfilter kelompok pengguna berdasarkan tag perilaku, dan mendistribusikan data pengguna ke bucket yang berbeda untuk mempercepat komputasi data.

Identifikasi kelompok pengguna dan analisis tag perilaku

Kueri total GMV dan rata-rata GMV pengguna pria dari provinsi Guangdong dalam kategori 3C pada hari sebelumnya.

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

Kueri distribusi jumlah konsumsi pengguna pria dari provinsi Guangdong dalam kategori 3C pada hari sebelumnya.

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

Kueri jumlah konsumsi top K pengguna pria dari provinsi Guangdong pada hari sebelumnya.

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

Identifikasi kelompok pengguna berdasarkan tag perilaku

Identifikasi pengguna yang jumlah konsumsinya lebih besar dari 1.000 dalam kategori 3C pada bulan sebelumnya.

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