Gunakan fungsi bitmap Roaring untuk mengidentifikasi pelanggan target - AnalyticDB

Bitmap Roaring adalah bitmap terkompresi yang efisien dan banyak digunakan di berbagai bahasa pemrograman serta platform data besar untuk deduplikasi, penyaringan berbasis tag, dan komputasi data deret waktu. Topik ini menjelaskan cara menggunakan fungsi bitmap Roaring.

Versi yang didukung

Untuk kluster AnalyticDB for MySQL versi V3.1.6.4 atau lebih baru, tipe ROARING BITMAP dan fungsi bitmap Roaring didukung di tabel eksternal Object Storage Service (OSS).
Untuk kluster AnalyticDB for MySQL versi V3.2.1.0 atau lebih baru, tipe ROARING BITMAP dan fungsi bitmap Roaring didukung di tabel eksternal OSS dan tabel internal AnalyticDB for MySQL.

Catatan

Untuk melihat dan memperbarui versi minor dari kluster AnalyticDB for MySQL, masuk ke Konsol AnalyticDB for MySQL dan pergi ke bagian Configuration Information pada halaman Cluster Information.

Batasan

Saat menggunakan fungsi bitmap Roaring di AnalyticDB for MySQL, perhatikan batasan berikut:

Anda tidak dapat mengeksekusi pernyataan SELECT untuk menanyakan data dalam bidang bertipe ROARING BITMAP. Anda harus menggunakan operator UNNEST untuk menanyakan elemen dalam bidang bertipe ROARING BITMAP. Contoh pernyataan:
```
SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3]));
```
Untuk kluster AnalyticDB for MySQL versi sebelum V3.2.1.0, tipe ROARING BITMAP hanya didukung di tabel eksternal OSS, dan tabel internal AnalyticDB for MySQL yang berisi kolom bertipe ROARING BITMAP tidak dapat dibuat. Jika Anda ingin menggunakan fungsi bitmap Roaring pada AnalyticDB for MySQL, pertama-tama gunakan fungsi RB_BUILD_VARBINARY untuk mengonversi data VARBINARY menjadi tipe ROARING BITMAP. Contoh pernyataan:
```
// Buat tabel internal.
CREATE TABLE test_rb_cstore (id INT, rb VARBINARY);

// Panggil fungsi bitmap Roaring.
SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;
```

Fungsi

Fungsi bitmap Roaring mencakup fungsi skalar dan fungsi agregat.

Fungsi skalar

Fungsi	Tipe data input	Tipe data output	Deskripsi	Contoh
RB_BUILD	INT	ROARING BITMAP	Menghasilkan bitmap Roaring dari array integer.	`RB_BUILD(ARRAY[1,2,3])`
RB_BUILD_RANGE	INT,INT	ROARING BITMAP	Menghasilkan bitmap Roaring berdasarkan rentang integer dari nilai awal (termasuk) hingga nilai akhir (tidak termasuk).	`RB_BUILD_RANGE(0, 10000)`
RB_BUILD_VARBINARY	VARBINARY	ROARING BITMAP	Menghasilkan bitmap Roaring dari data VARBINARY.	`RB_BUILD_VARBINARY(RB_TO_VARBINARY (RB_BUILD(ARRAY[1,2,3])))`
RB_CARDINALITY	ROARING BITMAP	BIGINT	Menghitung kardinalitas bitmap Roaring.	`RB_CARDINALITY(RB_BUILD(ARRAY[1,2,3]))`
RB_CONTAINS	ROARING BITMAP, INT	BOOLEAN	Memeriksa apakah bitmap Roaring berisi integer.	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]), 3)`
RB_AND	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Mendapatkan irisan dari dua bitmap Roaring.	`RB_AND(RB_BUILD(ARRAY[1,2,3]), RB_BUILD(ARRAY[2,3,4]))`
RB_OR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Mendapatkan gabungan dari dua bitmap Roaring.	`RB_OR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_XOR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Mendapatkan nilai XOR dari dua bitmap Roaring.	`RB_XOR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_AND_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi AND. Jika nilai input parameter adalah null, fungsi menggunakan nilai input parameter lainnya sebagai output. Jika nilai input parameter adalah sepasang kurung kosong (`{}`), fungsi menggunakan sepasang kurung kosong (`{}`) sebagai output.	`RB_AND_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi OR. Jika nilai input adalah null, fungsi menggunakan sepasang kurung kosong ({}) sebagai input.	`RB_OR_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi ANDNOT. Jika nilai input adalah null, fungsi menggunakan sepasang kurung kosong ({}) sebagai input.	`RB_ANDNOT_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi AND pada dua bitmap Roaring.	`RB_AND_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi AND pada dua bitmap Roaring. Jika nilai input adalah null, fungsi menggunakan sepasang kurung kosong ({}) sebagai input.	`RB_AND_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi OR pada dua bitmap Roaring.	`RB_OR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi OR pada dua bitmap Roaring. Jika nilai input adalah null, fungsi menggunakan sepasang kurung kosong ({}) sebagai input.	`RB_OR_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_XOR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi XOR pada dua bitmap Roaring.	`RB_XOR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi ANDNOT pada dua bitmap Roaring.	`RB_ANDNOT_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi ANDNOT pada dua bitmap Roaring. Jika nilai input adalah null, fungsi menggunakan sepasang kurung kosong ({}) sebagai input.	`RB_ANDNOT_NULL2EMPTY_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_IS_EMPTY	ROARING BITMAP	BOOLEAN	Memeriksa apakah bitmap Roaring kosong.	`RB_IS_EMPTY(RB_BUILD(ARRAY[]))`
RB_CLEAR	ROARING BITMAP,BIGINT,BIGINT	ROARING BITMAP	Menghapus rentang tertentu tidak termasuk akhir rentang dalam bitmap Roaring.	`RB_CLEAR(RB_BUILD('{1,2,3}'), 2, 3)`
RB_CONTAINS	ROARING BITMAP, ROARING BITMAP	BOOLEAN	Memeriksa apakah bitmap Roaring pertama berisi bitmap Roaring kedua.	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3]))`
RB_FLIP	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	Membalik offset tertentu dalam bitmap Roaring.	`RB_FLIP(RB_BUILD(ARRAY[1,2,3,4,5]), 2, 5)`
RB_MINIMUM	ROARING BITMAP	INTEGER	Mengembalikan offset terkecil dalam bitmap Roaring. Jika bitmap Roaring kosong, kesalahan akan dikembalikan.	`RB_MINIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_MAXIMUM	ROARING BITMAP	INTEGER	Mengembalikan offset terbesar dalam bitmap Roaring. Jika bitmap Roaring kosong, kesalahan akan dikembalikan.	`RB_MAXIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_RANK	ROARING BITMAP,INTEGER	INTEGER	Mengembalikan jumlah elemen yang kurang dari atau sama dengan offset tertentu dalam bitmap Roaring.	`RB_RANK(RB_BUILD(ARRAY[1,2,3]),2)`
RB_TO_ARRAY	ROARING BITMAP	INTEGER	Mengembalikan array integer dari mana bitmap Roaring dibuat.	`RB_TO_ARRAY(RB_BUILD(ARRAY[1,2,3]))`
RB_TO_VARBINAR	ROARING BITMAP	VARBINARY	Mengembalikan array VARBINARY dari mana bitmap Roaring dibuat.	`RB_TO_VARBINARY(RB_BUILD(ARRAY[1,2,3]))`
RB_RANGE_CARDINALITY	ROARING BITMAP, INTEGER, INTEGER	INTEGER	Mengembalikan kardinalitas rentang dari posisi awal (termasuk) hingga posisi akhir (tidak termasuk). Posisi awal adalah 1. Penting Hanya AnalyticDB for MySQL V3.1.10.0 atau lebih baru yang mendukung fungsi ini.	`RB_RANGE_CARDINALITY(RB_BUILD(ARRAY [1,2,3]),2,3)`
RB_SELECT	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	Mengembalikan offset bitmap Roaring dalam rentang dari posisi awal (termasuk) hingga posisi akhir (tidak termasuk). Penting Hanya AnalyticDB for MySQL V3.1.10.0 atau lebih baru yang mendukung fungsi ini.	`RB_SELECT(RB_BUILD(ARRAY [1,3,4,5,7,9]),2, 3)`

Fungsi agregat

Fungsi	Tipe data input	Tipe data output	Deskripsi	Contoh
RB_BUILD_AGG	INTEGER	ROARING BITMAP	Membuat bitmap Roaring dari sekelompok offset.	`RB_CARDINALITY(RB_BUILD_AGG(1))`
RB_OR_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan operasi agregat OR.	`RB_CARDINALITY(RB_OR_AGG(RB_BUILD(array[1,2,3])))`
RB_AND_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan operasi agregat AND.	`RB_CARDINALITY(RB_AND_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_XOR_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan operasi agregat XOR.	`RB_CARDINALITY(RB_XOR_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_OR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi agregat OR pada dua bitmap Roaring.	`RB_OR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_AND_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi agregat AND pada dua bitmap Roaring.	`RB_AND_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_XOR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menghitung kardinalitas dengan melakukan operasi agregat XOR pada dua bitmap Roaring.	`RB_XOR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`

Contoh

Contoh berikut menjelaskan cara menggunakan fungsi bitmap Roaring di tabel internal dan tabel eksternal.

Tabel internal

Buat tabel internal yang mendukung bitmap Roaring.

CREATE TABLE `test_rb` (
  `id` INT,
  `rb` ROARINGBITMAP
);

Tulis data ke tabel.

INSERT INTO test_rb VALUES (1, '[1, 2, 3]');
INSERT INTO test_rb VALUES (2, '[2, 3, 4, 5, 6]');

Dapatkan kardinalitas bitmap Roaring.

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

Hasil contoh:

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  5 |
|    1 |                  3 |
+------+--------------------+

Lakukan agregasi pada bitmap Roaring.

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

Hasil contoh:

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         6 |
+---------------------------+

Tabel eksternal

Buat tabel eksternal yang mendukung bitmap Roaring.

CREATE TABLE `test_rb` (
  `id` INT,
  `rb` ROARINGBITMAP
  ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/test_for_user/",
"format":"parquet"
}';

Catatan

Untuk informasi tentang parameter tabel eksternal, lihat bagian "Buat tabel eksternal OSS untuk objek non-partisi" dari topik Gunakan tabel eksternal untuk mengimpor data ke Data Warehouse Edition.

Tulis data ke tabel.
Penting
Menulis data menggunakan pernyataan INSERT INTO bukan metode yang performant. Jika Anda ingin menulis sejumlah besar data, kami sarankan Anda menggunakan alat extract, transform, load (ETL) untuk menghasilkan file data dalam format Parquet dan mengunggahnya ke direktori OSS.
```
INSERT INTO test_rb SELECT 1, rb_build(ARRAY[1,2,3]);
INSERT INTO test_rb SELECT 2, rb_build(ARRAY[2,3,4,5]);
```

Dapatkan kardinalitas bitmap Roaring.

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

Hasil contoh:

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  4 |
|    1 |                  3 |
+------+--------------------+

Lakukan agregasi pada bitmap Roaring.

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

Hasil contoh:

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         5 |
+---------------------------+

Contoh: Identifikasi pelanggan target

Pada contoh ini, tabel tag dikonversi menjadi tabel bitmap Roaring, dan analisis dilakukan berdasarkan bitmap Roaring, seperti yang ditunjukkan pada gambar berikut.

Langkah 1: Siapkan tabel tag

Buat tabel tag bernama users_base untuk pengujian.

CREATE TABLE users_base(
   uid INT,
   tag1 STRING, // Nilai valid tag1: x, y, dan z 
   tag2 STRING, // Nilai valid tag2: a dan b 
   tag3 INT // Nilai valid tag3: 1 hingga 10 
);

Sisipkan data untuk menghasilkan 100 juta baris data tag.

SUBMIT JOB
INSERT OVERWRITE users_base
SELECT CAST(ROW_NUMBER() OVER (ORDER BY c1) AS INT) AS uid, SUBSTRING('xyz', FLOOR(RAND() * 3) + 1, 1) AS tag1, SUBSTRING('ab', FLOOR(RAND() * 2) + 1, 1) AS tag2, CAST(FLOOR(RAND() * 10) + 1 AS INT) as tag3 FROM  
(
SELECT A.c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS A(c1)
  JOIN
  (SELECT c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS B(c1)
));

Tanyakan 10 baris data dari tabel users_base.

SELECT * FROM users_base LIMIT 10;

Hasil contoh:

+--------+------+------+------+
| uid    | tag1 | tag2 | tag3 |
+--------+------+------+------+
|  74526 | y    | b    |    3 |
|  75611 | z    | b    |   10 |
|  80850 | x    | b    |    5 |
|  81656 | z    | b    |    7 |
| 163845 | x    | b    |    2 |
| 167007 | y    | b    |    4 |
| 170541 | y    | b    |    9 |
| 213108 | x    | a    |   10 |
|  66056 | y    | b    |    4 |
|  67761 | z    | a    |    2 |
+--------+------+------+------+

Langkah 2: Kelompokkan tabel tag

Untuk memanfaatkan kemampuan konkurensi mesin komputasi terdistribusi AnalyticDB for MySQL, kami sarankan Anda menambahkan kolom yang dapat mengelompokkan data berdasarkan uid. Pada contoh ini, kolom bernama user-group dibuat untuk pengelompokan. Ukuran grup bervariasi berdasarkan jumlah total unit komputasi AnalyticDB (ACU) di kluster Anda.

Sejumlah besar grup membawa efisiensi komputasi tinggi. Namun, jika setiap bitmap Roaring hanya berisi sejumlah kecil elemen, sistem tidak dapat sepenuhnya memanfaatkan bitmap Roaring.
Dalam skenario nyata, kami sarankan Anda membatasi jumlah rekaman dalam setiap grup kurang dari 100 juta. Misalnya, jika jumlah total nilai uid adalah 10 miliar, Anda dapat membuat 100 grup yang masing-masing memiliki 100 juta baris data.

Pada contoh ini, nilai uid jatuh ke dalam 16 grup. Kolom user_group menyimpan sisa pembagian nilai uid dibagi 16. Kolom offset menyimpan hasil bagi pembagian. Kolom offset digunakan untuk komputasi bitmap Roaring. Nilai uid dapat dihitung berdasarkan rumus berikut: uid = 16 × offset + user_group.

Metode pengelompokan ini hanya untuk referensi. Anda perlu merancang pengelompokan berdasarkan kebutuhan bisnis Anda.

Buat tabel tag bernama users yang berisi kolom untuk pengelompokan.

CREATE TABLE users(
   uid INT,
   tag1 STRING,
   tag2 STRING,
   tag3 INT,
   user_group INT, // Bidang pengelompokan.
   offset INT // Bidang offset.
);

Tulis data tabel users_base ke tabel users.

SUBMIT JOB INSERT OVERWRITE users SELECT uid, tag1, tag2, tag3, CAST(uid%16 AS INT), CAST(FLOOR(uid/16) AS INT) FROM users_base;

Tanyakan 10 baris data dari tabel users.

SELECT * FROM users LIMIT 10;

Hasil contoh:

+---------+------+------+------+------------+--------+
| uid     | tag1 | tag2 | tag3 | user_group | offset |
+---------+------+------+------+------------+--------+
|  377194 | z    | b    |   10 |         10 |  23574 |
|  309440 | x    | a    |    1 |          0 |  19340 |
|  601745 | z    | a    |    7 |          1 |  37609 |
|  753751 | z    | b    |    3 |          7 |  47109 |
|  988186 | y    | a    |   10 |         10 |  61761 |
|  883822 | x    | a    |    9 |         14 |  55238 |
|  325065 | x    | b    |    6 |          9 |  20316 |
| 1042875 | z    | a    |   10 |         11 |  65179 |
|  928606 | y    | b    |    5 |         14 |  58037 |
|  990858 | z    | a    |    8 |         10 |  61928 |
+---------+------+------+------+------------+--------+

Langkah 3: Bangun bitmap Roaring

Tabel internal

Buat tabel bitmap Roaring bernama tag_tbl_1 untuk tag 1.

CREATE TABLE `tag_tbl_1` (
  `tag1` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

Tulis data tabel users ke tabel tag_tbl_1.

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

Tanyakan data tag_tbl_1.

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

Hasil contoh:

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| y    |         13 |             563654 |
| x    |         11 |             565013 |
| z    |          2 |             564428 |
| x    |          4 |             564377 |
...                                 
| z    |          5 |             564333 |
| x    |          8 |             564808 |
| x    |          0 |             564228 |
| y    |          3 |             563325 |
+------+------------+--------------------+

Buat tabel bitmap Roaring bernama tag_tbl_2 untuk tag 2.

CREATE TABLE `tag_tbl_2` (
  `tag2` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

Tulis data tabel users ke tabel tag_tbl_2.

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

Tanyakan data tag_tbl_2.

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

Hasil contoh:

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| a    |          9 |            3123039 |
| a    |          5 |            3123973 |
| a    |         12 |            3122414 |
| a    |          7 |            3127218 |
| a    |         15 |            3125403 |
...                                  
| a    |         10 |            3122698 |
| b    |          4 |            3126091 |
| b    |          3 |            3124626 |
| b    |          9 |            3126961 |
| b    |         14 |            3125351 |
+------+------------+--------------------+

Tabel eksternal

Buat tabel bitmap Roaring bernama tag_tbl_1 untuk tag 1.

CREATE TABLE `tag_tbl_1` (
  `tag1` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
 ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_1/",
"format":"parquet"
}';

Tulis data tabel users ke tabel tag_tbl_1.

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

Tanyakan data tag_tbl_1.

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

Hasil contoh:

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| z    |          7 |            2082608 |
| x    |         10 |            2082953 |
| y    |          7 |            2084730 |
| x    |         14 |            2084856 |
...                                  
| z    |         15 |            2084535 |
| z    |          5 |            2083204 |
| x    |         11 |            2085239 |
| z    |          1 |            2084879 |
+------+------------+--------------------+

Buat tabel bitmap Roaring bernama tag_tbl_2 untuk tag 2.

CREATE TABLE `tag_tbl_2` (
  `tag2` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
 ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_2/",
"format":"parquet"
}';

Tulis data tabel users ke tabel tag_tbl_2.

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

Tanyakan data tag_tbl_2.

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

Hasil contoh:

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| b    |         11 |            3121361 |
| a    |          6 |            3124750 |
| a    |          1 |            3125433 |
...                                    
| b    |          2 |            3126523 |
| b    |         12 |            3123452 |
| a    |          4 |            3126111 |
| a    |         13 |            3123316 |
| a    |          2 |            3123477 |
+------+------------+--------------------+

Langkah 4: Lakukan analisis berdasarkan bitmap Roaring

Skenario 1: Analisis penyaringan

Pada contoh ini, jumlah pengguna untuk setiap nilai tag2 yang memenuhi tag1 in (x, y) diperoleh.

Tanyakan pengguna yang memenuhi tag1 in (x, y).

SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS rb FROM tag_tbl_2 AS t2
JOIN (
SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
AS t1
ON t1.user_group = t2.user_group;

Hasil contoh:

+------+------------+---------+
| tag2 | user_group | rb      |
+------+------------+---------+
| b    |          3 | 1041828 |
| a    |          15| 1039859 |
| a    |          9 | 1039140 |
| b    |          1 | 1041524 |
| a    |          4 | 1041599 |
| b    |          1 | 1041381 |
| b    |          10| 1041026 |
| b    |          6 | 1042289 |
+------+------------+---------+

Dapatkan jumlah pengguna untuk setiap nilai tag2 yang memenuhi tag1 in (x, y).

SELECT tag2, SUM(cnt) FROM ( 
SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS cnt FROM tag_tbl_2 AS t2
JOIN (
SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
AS t1
ON t1.user_group = t2.user_group
) GROUP BY tag2;

Hasil contoh:

+------+----------+
| tag2 | sum(cnt) |
+------+----------+
| a    | 33327868 |
| b    | 33335220 |
+------+----------+

Skenario 2: Hitung irisan, gabungan, dan XOR bitmap Roaring

Ambil data dari tabel tag_tbl_1 yang memenuhi tag1 = 'x' or tag1 = 'y' dan data dari tabel tag_tbl_2 yang memenuhi tag2 = 'b', dan dapatkan data yang memenuhi kedua kondisi tersebut.

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb2
        FROM
          tag_tbl_2
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

Hasil contoh:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|         10 |            2083679 |
|          3 |            2082370 |
|          9 |            2082847 |
|          2 |            2086511 |
...                              
|          1 |            2082291 |
|          4 |            2083290 |
|         14 |            2083581 |
|         15 |            2084110 |
+------------+--------------------+

Skenario 3: Hitung irisan, gabungan, dan XOR bitmap Roaring serta tabel tag asli

Ambil data dari tabel tag_tbl_1 yang memenuhi tag1 = 'x' or tag1 = 'y' dan data dari tabel users yang memenuhi tag2 = 'b', dan dapatkan data yang memenuhi kedua kondisi tersebut.

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_BUILD_AGG(offset) AS rb2
        FROM
          users
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

Hasil contoh:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|          3 |            2082370 |
|          1 |            2082291 |
|          0 |            2082383 |
|          4 |            2083290 |
|         11 |            2081662 |
|         13 |            2085280 |
...                              
|         14 |            2083581 |
|         15 |            2084110 |
|          9 |            2082847 |
|          8 |            2084860 |
|          5 |            2083056 |
|          7 |            2083275 |
+------------+--------------------+

Skenario 4: Ekspor hasil ke OSS menggunakan tabel eksternal OSS

Buat tabel tag bernama tag_tbl_3 ke mana hasil perhitungan diekspor.

CREATE TABLE `tag_tbl_3` (
  `user_group` INT,
  `rb` ROARINGBITMAP
  )engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_3/",
"format":"parquet"
}';

Ekspor hasil pada Skenario 2 ke tag_tbl_3.

INSERT OVERWRITE tag_tbl_3
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb2
        FROM
          tag_tbl_2
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group;

Catatan

Setelah pernyataan dieksekusi, file disimpan sebagai file Parquet di direktori oss://testBucketName/roaringbitmap/tag_tbl_3/.

Skenario 5: Percepat kueri pada tabel eksternal

Anda dapat mengimpor data dari tabel tag_tbl_1 ke tabel internal untuk meningkatkan performa kueri.

Buat tabel internal bernama tag_tbl_1_cstore dan atur tipe data bidang rb menjadi VARBINARY.
```
CREATE TABLE `tag_tbl_1_cstore` (
 `tag1` VARCHAR,
 `rb` VARBINARY,
 `user_group` INT
);
```

Impor data tabel tag_tbl_1 dari OSS ke tag_tbl_1_cstore.

INSERT INTO tag_tbl_1_cstore SELECT tag1, RB_TO_VARBINARY(rb), user_group FROM tag_tbl_1;

Tanyakan data tabel tag_tbl_1_cstore.

SELECT tag1, user_group, RB_CARDINALITY(RB_OR_AGG(RB_BUILD_VARBINARY(rb))) FROM tag_tbl_1_cstore GROUP BY tag1, user_group;

Hasil contoh:

+------+------------+---------------------------------------------------+
| tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) |
+------+------------+---------------------------------------------------+
| y    |          3 |                                           2082919 |
| x    |          9 |                                           2083085 |
| x    |          3 |                                           2082140 |
| y    |         11 |                                           2082268 |
| z    |          4 |                                           2082451 |
...                                                                    
| z    |          2 |                                           2081560 |
| y    |          6 |                                           2082194 |
| z    |          7 |                                           2082608 |
+------+------------+---------------------------------------------------+