ApsaraDB for SelectDB:Deduplikasi\ presisi\ dengan\ BITMAP

Langkah 1: Buat tabel

Buat tabel menggunakan model Aggregate Key dengan bidang Value bertipe bitmap dan kolom hid untuk bucketing hash.

CREATE TABLE `user_tag_bitmap` (
  `tag`     BIGINT(20)  NULL COMMENT "User tag",
  `hid`     SMALLINT(6) NULL COMMENT "Bucket ID",
  `user_id` BITMAP BITMAP_UNION NULL COMMENT "User bitmap"
) ENGINE=OLAP
AGGREGATE KEY(`tag`, `hid`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`hid`) BUCKETS 3;

Kolom hid membagi rentang user_id ke dalam kelompok, dengan setiap kelompok ditugaskan ke satu bucket. Hal ini menjamin nilai bitmap antar bucket bersifat orthogonal.

Langkah 2: Impor data

Gunakan pernyataan LOAD LABEL untuk memuat data. Ekspresi ceil(tmp_user_id/500) menghitung nilai hid untuk setiap baris, dan to_bitmap(tmp_user_id) mengonversi bilangan bulat mentah menjadi entri bitmap.

LOAD LABEL user_tag_bitmap_test
(
    DATA INFILE('hdfs://abc')
    INTO TABLE user_tag_bitmap
    COLUMNS TERMINATED BY ','
    (tmp_tag, tmp_user_id)
    SET (
        tag     = tmp_tag,
        hid     = ceil(tmp_user_id/500),
        user_id = to_bitmap(tmp_user_id)
    )
)

Format data sumber terdiri dari dua kolom yang dipisahkan koma—tag dan ID pengguna:

11111111,1
11111112,2
11111113,3
11111114,4

Selama impor, SelectDB memberikan nilai hid yang sama kepada ID pengguna dalam rentang [1, 5000000) dan menempatkannya dalam bucket yang sama. Nilai bitmap antar bucket tetap orthogonal, sehingga mengurangi biaya komputasi untuk operasi intersection dan union.

Langkah 3: Kueri dengan fungsi orthogonal bitmap

Setelah data diimpor, gunakan fungsi orthogonal bitmap yang dijelaskan pada bagian berikutnya. Untuk daftar lengkap fungsi bitmap standar, lihat Referensi SQL.

bitmap_orthogonal_intersect

Menghitung intersection nilai bitmap pada nilai filter yang ditentukan dan mengembalikan hasilnya sebagai bitmap.

Sintaks

bitmap_orthogonal_intersect(bitmap_column, column_to_filter, filter_values)

Parameter

Contoh

SELECT BITMAP_COUNT(bitmap_orthogonal_intersect(user_id, tag, 13080800, 11110200))
FROM user_tag_bitmap
WHERE tag IN (13080800, 11110200);

Setara dengan:

-- Mengembalikan jumlah pengguna yang muncul di SEMUA tag yang ditentukan
SELECT COUNT(DISTINCT user_id)
FROM user_tag_bitmap
WHERE tag IN (13080800, 11110200)
  AND <user_id appears in every tag>;

Saat eksekusi, Tahap 1 memfilter baris berdasarkan tag yang ditentukan, lalu menghitung intersection bitmap antar semua tag yang cocok per bucket. Tahap 2 menggabungkan iteratif bitmap intersection per-bucket menjadi hasil akhir.

orthogonal_bitmap_intersect_count

Menghitung intersection nilai bitmap dan mengembalikan jumlahnya secara langsung. Semantiknya sama dengan intersect_count, tetapi eksekusinya didistribusikan ke seluruh bucket.Sintaksnya sama dengan fungsi `intersect_count`, tetapi implementasinya berbeda.Sintaksnya sama dengan fungsi `bitmap_union_count`, tetapi implementasinya berbeda.

Sintaks

orthogonal_bitmap_intersect_count(bitmap_column, column_to_filter, filter_values)

Parameter

Contoh

SELECT orthogonal_bitmap_intersect_count(user_id, tag, 1150000, 1150001, 390006)
FROM user_tag_bitmap
WHERE tag IN (1150000, 1150001, 390006);

Saat eksekusi, Tahap 1 memfilter berdasarkan tag yang ditentukan, menghitung intersection bitmap antar semua tag yang cocok, dan menghitung hasilnya per bucket. Tahap 2 menjumlahkan semua hitungan per-bucket menjadi total akhir.

orthogonal_bitmap_union_count

Menghitung union nilai bitmap dan mengembalikan jumlahnya. Semantiknya sama dengan BITMAP_UNION_COUNT, tetapi eksekusinya didistribusikan ke seluruh bucket.

Sintaks

orthogonal_bitmap_union_count(bitmap_column)

Parameter

Contoh

SELECT orthogonal_bitmap_union_count(user_id)
FROM user_tag_bitmap
WHERE tag IN (1150000, 1150001, 390006);

Setara dengan:

SELECT COUNT(DISTINCT user_id)
FROM user_tag_bitmap
WHERE tag IN (1150000, 1150001, 390006);

Saat eksekusi, Tahap 1 menghitung union bitmap per bucket dan menghitung hasilnya. Tahap 2 menjumlahkan semua hitungan per-bucket menjadi total akhir.

orthogonal_bitmap_expr_calculate

Mengevaluasi ekspresi himpunan pada kolom bitmap dan mengembalikan hasilnya sebagai bitmap. Gunakan fungsi ini ketika Anda memerlukan bitmap itu sendiri untuk pemrosesan lebih lanjut, bukan hanya jumlahnya.

Sintaks

orthogonal_bitmap_expr_calculate(bitmap_column, filter_column, input_string)

Parameter

input_string mendukung operator berikut:

Contoh

SELECT orthogonal_bitmap_expr_calculate(
    user_id,
    tag,
    '(833736|999777)&(1308083|231207)&(1000|20000-30000)'
)
FROM user_tag_bitmap
WHERE tag IN (833736, 999777, 130808, 231207, 1000, 20000, 30000);

Saat eksekusi, Tahap 1 mengurai input_string untuk menentukan filter tag, memfilter data, dan menerapkan ekspresi bitmap pada baris yang difilter per bucket. Tahap 2 menghitung union semua hasil bitmap per-bucket dan mengembalikan bitmap akhir.

orthogonal_bitmap_expr_calculate_count

Mengevaluasi ekspresi himpunan pada kolom bitmap dan mengembalikan jumlahnya. Sintaks dan parameternya identik dengan orthogonal_bitmap_expr_calculate.

Sintaks

orthogonal_bitmap_expr_calculate_count(bitmap_column, filter_column, input_string)

Parameter

Contoh

SELECT orthogonal_bitmap_expr_calculate_count(
    user_id,
    tag,
    '(833736|999777)&(1308083|231207)&(1000|20000-30000)'
)
FROM user_tag_bitmap
WHERE tag IN (833736, 999777, 130808, 231207, 1000, 20000, 30000);

Saat eksekusi, Tahap 1 mengurai input_string, memfilter data, menerapkan ekspresi bitmap, dan menghitung hasilnya per bucket. Tahap 2 menghitung union semua hasil bitmap per-bucket dan mengembalikan jumlah akhir.