ApsaraDB for ClickHouse：Mesin tabel

Mesin tabel mengacu pada jenis tabel. Di ApsaraDB for ClickHouse, mesin tabel menentukan cara penyimpanan dan pembacaan data, dukungan indeks, serta dukungan replikasi utama/sekunder. Tabel berikut menjelaskan mesin tabel yang didukung oleh ApsaraDB for ClickHouse.

Dalam contoh ini, kunci utama digunakan untuk menunjukkan perbedaan fitur. Dalam sintaks SQL dari ApsaraDB for ClickHouse, kunci utama digunakan untuk menghapus duplikat guna memastikan data unik. Pada mesin tabel MergeTree, kunci utama digunakan untuk mempercepat kueri. Bahkan setelah kompaksi selesai, baris data dengan kunci utama yang sama masih ada.

Contoh berikut menunjukkan cara menggunakan mesin tabel MergeTree:

1. Buat tabel bernama test_tbl. Kunci utamanya adalah (id, create_time). Data yang disimpan diurutkan berdasarkan kunci utama dan dipartisi berdasarkan nilai create_time.

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. Tulis data yang memiliki kunci utama duplikat.

   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(2, '2019-12-14', null);
   insert into test_tbl values(3, '2019-12-15', null);
   insert into test_tbl values(3, '2019-12-15', null);

3. Kueri data.

   select * from test_tbl;

   Hasil kueri berikut dikembalikan:

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Eksekusi pernyataan OPTIMIZE untuk memaksa kompaksi di latar belakang. Kompaksi dipaksa karena mesin tabel dalam keluarga MergeTree menggunakan struktur mirip log-structured merge-tree (LSM-tree), dan logika pemrosesan di lapisan penyimpanan tidak diimplementasikan hingga fase kompaksi dimulai.

   optimize table test_tbl final;

5. Kueri data lagi.

   select * from test_tbl;

   Hasil kueri berikut dikembalikan. Data dengan kunci utama duplikat masih ada.

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

Meskipun mesin tabel ReplacingMergeTree dapat menghapus duplikat dengan kunci utama yang sama, mesin ini memiliki batasan berikut. Oleh karena itu, mesin ini digunakan untuk memastikan bahwa duplikat akhirnya dihapus, tetapi tidak dapat menjamin kunci utama unik selama proses kueri.

• Dalam skenario terdistribusi, data dengan kunci utama yang sama mungkin didistribusikan ke node berbeda. Duplikat tidak dapat dihapus untuk data lintas shard yang berbeda.

• Sebelum eksekusi pernyataan OPTIMIZE selesai, duplikat dengan kunci utama yang sama mungkin belum dihapus. Misalnya, duplikat dihapus untuk beberapa data, tetapi tidak untuk data lainnya.

• Pernyataan OPTIMIZE dieksekusi di latar belakang, dan waktu eksekusinya tidak dapat diprediksi.

• Dalam skenario dengan volume data besar, diperlukan waktu lama untuk mengeksekusi pernyataan OPTIMIZE secara manual. Dalam hal ini, persyaratan untuk kueri real-time tidak dapat dipenuhi.

Contoh berikut menunjukkan cara menggunakan mesin tabel ReplacingMergeTree:

1. Buat tabel bernama test_tbl_replacing.

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. Tulis data yang memiliki kunci utama duplikat.

   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(2, '2019-12-14', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);

3. Kueri data.

   select * from test_tbl_replacing;

   Hasil kueri berikut dikembalikan:

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘ 

4. Eksekusi pernyataan OPTIMIZE untuk memaksa kompaksi di latar belakang. Kompaksi dipaksa karena mesin tabel dalam keluarga MergeTree menggunakan struktur mirip LSM tree dan logika pemrosesan di lapisan penyimpanan tidak diimplementasikan hingga fase kompaksi dimulai.

   optimize table test_tbl_replacing final;

5. Kueri data lagi.

   select * from test_tbl_replacing;

   Hasil kueri berikut dikembalikan. Data dengan kunci utama duplikat telah dihapus.

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

Mesin tabel CollapsingMergeTree menghilangkan batasan pada fitur mesin tabel ReplacingMergeTree. Saat menggunakan mesin tabel CollapsingMergeTree, Anda harus menentukan kolom Sign dalam pernyataan CREATE TABLE. Baris dibagi menjadi dua kategori berdasarkan nilai kolom Sign: baris status dan baris pembatalan. Baris dengan nilai kolom Sign 1 disebut baris status, digunakan untuk menambahkan status. Baris dengan nilai kolom Sign -1 disebut baris pembatalan, digunakan untuk menghapus status.

Selama kompaksi di latar belakang, baris dengan kunci utama yang sama dan nilai Sign berlawanan diruntuhkan atau dihapus. Jika tidak ada kompaksi yang dilakukan, baris status dan baris pembatalan ada bersamaan. Untuk meruntuhkan atau menghapus baris dengan kunci utama yang sama, lakukan operasi berikut di lapisan bisnis:

• Catat nilai baris status asli, atau kueri database untuk mendapatkan nilai baris status asli sebelum menghapus status.

  Alasannya adalah Anda harus menulis baris pembatalan saat menghapus status. Baris pembatalan harus berisi data dengan kunci utama yang sama dengan baris status asli, kecuali kolom Sign.

• Saat mengeksekusi fungsi agregat seperti count() dan sum(col), redundansi data mungkin ada. Untuk mendapatkan hasil valid, modifikasi pernyataan SQL di lapisan bisnis. Ubah count() menjadi sum(Sign) dan sum(col) menjadi sum(col * Sign).

  Alasan berikut tersedia:

  • Waktu untuk melakukan kompaksi di latar belakang tidak dapat diprediksi. Saat memulai kueri, baris status dan baris pembatalan mungkin belum runtuh atau dihapus.

  • ApsaraDB for ClickHouse tidak dapat memastikan bahwa baris dengan kunci utama yang sama jatuh pada node yang sama. Data tidak dapat diruntuhkan atau dihapus lintas node.

Contoh berikut menunjukkan cara menggunakan mesin tabel CollapsingMergeTree:

1. Buat tabel bernama test_tbl_collapsing.

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Sisipkan baris status dengan nilai kolom Sign 1.

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

   Catatan

   Jika Anda menyisipkan baris pembatalan dan kemudian baris status, baris pembatalan dan baris status mungkin tidak berurutan. Dalam hal ini, bahkan jika kompaksi dipaksa dilakukan di latar belakang, data dengan kunci utama yang sama tidak dapat diruntuhkan atau dihapus.

3. Sisipkan baris pembatalan dengan nilai kolom Sign -1. Nilai dalam baris pembatalan sama dengan nilai dalam baris status yang disisipkan, kecuali nilai kolom Sign. Pada saat yang sama, sisipkan baris status baru dengan kunci utama yang sama dengan baris pembatalan.

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

4. Kueri data.

   SELECT * FROM test_tbl_collapsing;

   Hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐ 
   │ 4324182021466249494 │    5      │    146   │   1   │ 
   │ 4324182021466249494 │    5      │    146   │  -1   │ 
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

5. Jika ingin mengeksekusi fungsi agregat pada kolom tertentu, modifikasi pernyataan SQL untuk mendapatkan hasil valid. Dalam contoh ini, fungsi agregat sum(col) digunakan dan pernyataan SQL dimodifikasi menjadi pernyataan berikut:

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   Setelah data di-aggregasi, hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration──┐ 
   │ 4324182021466249494 │    6      │    185    │ 
   └─────────────────────┴───────────┴───────────┘

6. Eksekusi pernyataan OPTIMIZE untuk memaksa kompaksi di latar belakang. Kompaksi dipaksa karena mesin tabel dalam keluarga MergeTree menggunakan struktur mirip LSM tree dan logika pemrosesan di lapisan penyimpanan tidak diimplementasikan hingga fase kompaksi dimulai.

   optimize table test_tbl_collapsing final;

7. Kueri data lagi.

   SELECT * FROM test_tbl_collapsing;

   Hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐  
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

Contoh berikut menunjukkan cara menggunakan mesin tabel VersionedCollapsingMergeTree:

1. Buat tabel bernama test_tbl_Versioned.

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. Sisipkan baris pembatalan dengan nilai kolom Sign -1.

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. Sisipkan baris status dengan nilai kolom Sign 1 dan nilai kolom Version 1. Nilai di kolom lainnya sama dengan nilai dalam baris pembatalan yang disisipkan. Pada saat yang sama, sisipkan baris status baru dengan kunci utama yang sama dengan baris pembatalan.

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);

4. Kueri data.

   SELECT * FROM test_tbl_Versioned;

   Hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

5. Jika ingin mengeksekusi fungsi agregat pada kolom tertentu, modifikasi pernyataan SQL untuk mendapatkan hasil valid. Dalam contoh ini, fungsi agregat sum(col) digunakan dan pernyataan SQL dimodifikasi menjadi pernyataan berikut:

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   Setelah data di-aggregasi, hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration─┐ 
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

6. Eksekusi pernyataan OPTIMIZE untuk memaksa kompaksi di latar belakang. Kompaksi dipaksa karena mesin tabel dalam keluarga MergeTree menggunakan struktur mirip LSM tree dan logika pemrosesan di lapisan penyimpanan tidak diimplementasikan hingga fase kompaksi dimulai.

   optimize table test_tbl_Versioned final;

7. Jalankan kueri data kembali.

   SELECT * FROM test_tbl_Versioned;

   Hasil kueri berikut dikembalikan:

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

Sebelum menggunakan mesin tabel SummingMergeTree, perhatikan hal-hal berikut:

• Di ApsaraDB for ClickHouse, kolom kunci utama hanya di-aggregasi saat kompaksi dilakukan di latar belakang. Waktu kompaksi tidak dapat diprediksi, sehingga beberapa data mungkin sudah di-aggregasi, sementara yang lain belum. Oleh karena itu, klausa GROUP BY tetap diperlukan dalam pernyataan SQL untuk menjamin agregasi.

• Selama pra-agregasi, ApsaraDB for ClickHouse melakukan pra-agregasi pada semua kolom kecuali kolom kunci utama. Jika kolom-kolom tersebut dapat di-aggregasi, seperti tipe data NUMERIC, nilainya akan dijumlahkan. Namun, jika kolom-kolom tersebut tidak dapat di-aggregasi, seperti tipe data STRING, nilai acak akan digunakan.

• Kami merekomendasikan penggunaan mesin tabel SummingMergeTree bersamaan dengan mesin tabel MergeTree. Mesin tabel MergeTree menyimpan data lengkap, sedangkan mesin tabel SummingMergeTree menyimpan hasil pra-agregasi.

Contoh berikut menunjukkan cara menggunakan mesin tabel SummingMergeTree:

1. Buat tabel bernama test_tbl_summing.

   CREATE TABLE test_tbl_summing
   (
       key UInt32,
       value UInt32
   )
   ENGINE = SummingMergeTree()
   ORDER BY key;

2. Tulis data.

   INSERT INTO test_tbl_summing Values(1,1),(1,2),(2,1);

3. Kueri data.

   select * from test_tbl_summing;

   Hasil kueri berikut dikembalikan:

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘    

4. Eksekusi pernyataan OPTIMIZE untuk memaksa kompaksi di latar belakang. Kompaksi dipaksa karena mesin tabel dalam keluarga MergeTree menggunakan struktur mirip LSM tree, dan logika pemrosesan di lapisan penyimpanan tidak diimplementasikan hingga fase kompaksi dimulai.

   optimize table test_tbl_summing final;

5. Setelah kompaksi dipaksa di latar belakang, eksekusi pernyataan yang berisi klausa GROUP BY untuk mengagregasi data. Kemudian, kueri data lagi.

   SELECT key, sum(value) FROM test_tbl_summing GROUP BY key;

   Hasil kueri berikut dikembalikan. Data dengan kunci utama duplikat telah di-aggregasi.

   ┌─key─┬value─┐
   │  1  │  3   │
   │  2  │  1   │
   └─────┴──────┘

Sintaksis mesin tabel AggregatingMergeTree cukup kompleks. Mesin ini harus digunakan bersamaan dengan tampilan materialisasi atau tipe data khusus AggregateFunction di ApsaraDB for ClickHouse.

Contoh berikut menunjukkan cara menggunakan mesin tabel AggregatingMergeTree:

• Gunakan mesin tabel AggregatingMergeTree bersamaan dengan tampilan materialisasi

  1. Buat tabel detail bernama visits.

     CREATE TABLE visits
     (
         UserID UInt64,
         CounterID UInt8,
         StartDate Date,
         Sign Int8
     )
     ENGINE = CollapsingMergeTree(Sign)
     ORDER BY UserID;

  2. Buat tampilan materialisasi bernama visits_agg_view untuk tabel visits, dan eksekusi fungsi sumState dan uniqState untuk pra-agregasi tabel visits.

     CREATE MATERIALIZED VIEW visits_agg_view
     ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
     AS SELECT
         CounterID,
         StartDate,
         sumState(Sign)    AS Visits,
         uniqState(UserID) AS Users
     FROM visits
     GROUP BY CounterID, StartDate;

  3. Tulis data ke tabel visits.

     INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
     INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

  4. Eksekusi fungsi agregat sumMerge dan uniqMerge untuk mengagregasi tampilan materialisasi. Kemudian, kueri data yang telah di-aggregasi.

     SELECT
         StartDate,
         sumMerge(Visits) AS Visits,
         uniqMerge(Users) AS Users
     FROM visits_agg_view
     GROUP BY StartDate
     ORDER BY StartDate

     Catatan

     Fungsi sum dan uniq tidak dapat digunakan lagi. Jika Anda menggunakan fungsi-fungsi tersebut, pesan kesalahan berikut akan dikembalikan saat mengeksekusi pernyataan SQL: Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...

     Hasil kueri berikut dikembalikan:

     ┌──StartDate──┬─Visits─┬─Users──┐
     │  2019-11-11 │   1    │   1    │
     │  2019-11-12 │   1    │   1    │
     └─────────────┴────────┴────────┘

• Gunakan mesin tabel AggregatingMergeTree bersamaan dengan tipe data khusus AggregateFunction

  1. Buat tabel detail bernama detail_table.

     CREATE TABLE detail_table
     (   CounterID UInt8,
         StartDate Date,
         UserID UInt64
     ) ENGINE = MergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  2. Tulis data ke detail_table.

     INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
     INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

  3. Buat tabel agregat bernama agg_table di mana tipe data kolom UserID adalah AggregateFunction.

     CREATE TABLE agg_table
     (   CounterID UInt8,
         StartDate Date,
         UserID AggregateFunction(uniq, UInt64)
     ) ENGINE = AggregatingMergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  4. Eksekusi fungsi agregat uniqState untuk menyisipkan data dari tabel detail ke tabel agregat.

     INSERT INTO agg_table
     select CounterID, StartDate, uniqState(UserID)
     from detail_table
     group by CounterID, StartDate;

     Catatan

     Anda tidak dapat mengeksekusi pernyataan INSERT INTO agg_table VALUES(1, '2019-11-12', 1); untuk menyisipkan data ke tabel agregat. Jika tidak, pesan kesalahan berikut akan dikembalikan: Cannot convert UInt64 to AggregateFunction(uniq, UInt64)...

  5. Eksekusi fungsi agregat uniqMerge untuk mengagregasi data dalam tabel agregat. Kemudian, kueri data yang telah di-aggregasi.

     SELECT uniqMerge(UserID) AS state 
     FROM agg_table 
     GROUP BY CounterID, StartDate;

     Hasil kueri berikut dikembalikan:

     ┌─state─┐
     │   1   │
     │   1   │
     └───────┘