ApsaraDB for ClickHouse:Tablo motorları

MergeTree

MergeTree, yüksek yüklü ekleme iş yükleri için temel motordur. Veriler parçalar halinde eklenir ve ORDER BY ile tanımlanan sıralama düzenine göre arka planda birleştirilir.

Örnek: Bölümlendirilmiş MergeTree

1. create_time sütununa göre bölümlendirilmiş ve (id, create_time) üzerinde sıralanmış bir tablo oluşturun.

   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. Yinelenen birincil anahtarlara sahip satırlar ekleyin.

   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. Tabloyu sorgulayın.

   SELECT * FROM test_tbl;

   Sonuç — yinelenenler dahil tüm beş satır döndürülür:

   ┌─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. Sıkıştırmayı zorlayın.

   OPTIMIZE TABLE test_tbl FINAL;

5. Tekrar sorgulayın.

   SELECT * FROM test_tbl;

   Sonuç — yinelenen kayıtlar hala mevcuttur. MergeTree bunları kaldırmaz.

   ┌─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   │
   └────┴─────────────┴──────────┘

Daha fazla bilgi için bkz. MergeTree.

ReplacingMergeTree

ReplacingMergeTree, arka plan sıkıştırması sırasında aynı birincil anahtara sahip yinelenen satırları kaldırır. Sorgu zamanında benzersizlik garantisi vermez; yalnızca nihai tutarlılıkla deduplication kabul edilebilir olduğunda kullanın.

Sınırlamalar

• Dağıtılmış bir kümede, aynı birincil anahtara sahip satırlar farklı parçalara yerleşebilir. Deduplication yalnızca tek bir parça içinde gerçekleşir, parçalar arasında gerçekleşmez.

• Sıkıştırma tamamlanmadan önce bazı yinelenen kayıtlar hala mevcut olabilir. Sıkıştırma, öngörülemeyen bir programla arka planda çalışır.

• Büyük veri kümelerinde OPTIMIZE ... FINAL komutunu manuel olarak çalıştırmak uzun sürebilir ve bu da gerçek zamanlı deduplication işlemini pratik olmaktan çıkarır.

Örnek: ReplacingMergeTree ile deduplication

1. Bir tablo oluşturun.

   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. Yinelenen birincil anahtarlara sahip satırlar ekleyin.

   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. Sıkıştırma öncesi sorgulayın — yinelenen kayıtlar hala mevcuttur.

   SELECT * FROM test_tbl_replacing;

   ┌─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. Sıkıştırmayı zorlayın.

   OPTIMIZE TABLE test_tbl_replacing FINAL;

5. Tekrar sorgulayın — yinelenen kayıtlar kaldırıldı.

   SELECT * FROM test_tbl_replacing;

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

CollapsingMergeTree

CollapsingMergeTree, satırları yerinde güncellemek yerine bir Sign sütunu kullanarak satır durumu değişikliklerini izler; ClickHouse'un yalnızca ekleme yapılan depolama modelinde güncellemeler maliyetlidir. Her satır ya bir durum satırı (Sign = 1) ya da iptal satırı (Sign = -1) olarak işaretlenir. Sıkıştırma sırasında, aynı birincil anahtara sahip durum ve iptal satır çiftleri daraltılır ve kaldırılır.

Daraltma nasıl çalışır

Bir satırın durumunu güncellemek için:

1. Değiştirmek istediğiniz satırla aynı birincil anahtar değerlerine sahip Sign = -1 değerinde bir iptal satırı ekleyin.

2. Güncellenmiş değerlerle Sign = 1 değerinde yeni bir durum satırı ekleyin.

Bir satırın durumunu silmek için: Sign dışındaki tüm sütunlarda orijinal durum satırıyla eşleşen bir iptal satırı ekleyin.

Kullanım notları

• Durum ve iptal satırları sırasız eklenirse (örneğin, bir iptal satırı eklendikten sonra eşleşen durum satırı ayrı olarak gelirse) doğru şekilde daraltılmayabilir. Sırasız eklemeleri işlemek için VersionedCollapsingMergeTree kullanmayı düşünün.

• Daraltma yalnızca tek bir parça içinde gerçekleşir. Dağıtılmış bir kümede, farklı düğümlerdeki aynı birincil anahtara sahip satırlar daraltılmaz.

• Sıkıştırma tamamlanmadan önce durum ve iptal satırları birlikte bulunur. Sıkıştırma öncesi toplu iş sorguları çalıştırırken SQL kodunuzu aşağıdaki gibi ayarlayın:

  • COUNT() yerine SUM(Sign) kullanın

  • SUM(col) yerine SUM(col * Sign) kullanın

Örnek: CollapsingMergeTree durum güncellemeleri

1. Sign sütunu içeren bir tablo oluşturun.

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

2. İlk durum satırını ekleyin.

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

3. Durumu güncelleyin: Eski durum için bir iptal satırı ve yeni bir durum satırı ekleyin.

   Not

   İptal satırını ve yeni durum satırını aynı toplu işte ekleyin. İptal satırını ve ardından durum satırını ayrı işlemlerde eklerseniz sırasız gelebilirler. Bu durumda, sıkıştırma zorlansa bile aynı birincil anahtara sahip satırlar daraltılamaz veya silinemez.

   INSERT INTO test_tbl_collapsing VALUES
     (4324182021466249494, 5, 146, -1),  -- cancel old state
     (4324182021466249494, 6, 185, 1);   -- new state

4. Sıkıştırma öncesi sorgulayın — üç satır da mevcuttur.

   SELECT * FROM test_tbl_collapsing;

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

   Sıkıştırma öncesi doğru toplu sonuçlar almak için Sign farkında ifadeler kullanın:

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

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

5. Sıkıştırmayı zorlayın.

   OPTIMIZE TABLE test_tbl_collapsing FINAL;

6. Tekrar sorgulayın — yalnızca en son durum satırı kalır.

   SELECT * FROM test_tbl_collapsing;

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

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree, CollapsingMergeTree'i, ekleme sırasından bağımsız olarak durum ve iptal satırlarının doğru eşleştirilmesini sağlayan bir Version sütunuyla genişletir. Sıkıştırma sırasında, aynı birincil anahtara, aynı Version değerine ve zıt Sign değerlerine sahip satırlar daraltılır ve kaldırılır.

Satırların sırasız gelebileceği durumlarda (örneğin, olay odaklı işlem hatlarında iptal olaylarının referans aldıkları durum olaylarından önce işlenebildiği senaryolarda) bu motoru kullanın.

CollapsingMergeTree'de olduğu gibi, toplu iş sorgularında COUNT() yerine SUM(Sign) ve SUM(col) yerine SUM(col * Sign) kullanacak şekilde ayarlama yapın.

Örnek: Sırasız eklemelerle VersionedCollapsingMergeTree

1. Sign ve Version sütunları içeren bir tablo oluşturun.

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

2. Önce bir iptal satırı ekleyin (sırasız).

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

3. Eşleşen durum satırını ve yeni bir durum satırını ekleyin.

   INSERT INTO test_tbl_Versioned VALUES
     (4324182021466249494, 5, 146, 1, 1),   -- state row matching the cancel above
     (4324182021466249494, 6, 185, 1, 2);   -- new state

4. Sıkıştırma öncesi sorgulayın.

   SELECT * FROM test_tbl_Versioned;

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

   Sıkıştırma öncesi doğru sonuçlar almak için Sign farkında toplama işlemi kullanın:

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

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

5. Sıkıştırmayı zorlayın.

   OPTIMIZE TABLE test_tbl_Versioned FINAL;

6. Tekrar sorgulayın — Version sütunu, sırasız eklemeye rağmen iptal ve durum satırlarını doğru şekilde eşleştirdi.

   SELECT * FROM test_tbl_Versioned;

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

SummingMergeTree

SummingMergeTree'yi MergeTree ile birlikte kullanmanızı öneririz. Ham detay verilerini bir MergeTree tablosunda saklayın ve önceden toplanmış sonuçlar için SummingMergeTree kullanın. Bu yaklaşım, birincil anahtarınız yanlış oluşturulmuşsa veri kaybını önler.

SummingMergeTree, sayısal sütunları toplayarak aynı birincil anahtara sahip satırları tek bir satırda önceden toplar. Bu işlem depolama alanını azaltır ve toplu iş sorgularını hızlandırır.

Kullanım notları

• Ön toplama, ekleme sırasında değil, arka plan sıkıştırması sırasında gerçekleşir. Bazı veriler henüz toplanmamış olabilir, bu nedenle sorgularınızda her zaman SUM() ile birlikte bir GROUP BY yan tümcesi ekleyin.

• Yalnızca sayısal sütunlar toplanır. Birincil anahtarın parçası olmayan dize ve diğer sayısal olmayan sütunlar, toplamadan sonra rastgele bir değer alır.

Örnek: SummingMergeTree ön toplama

1. Bir tablo oluşturun.

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

2. Satırlar ekleyin — bazılarının anahtarı aynıdır.

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

3. Sıkıştırma öncesi sorgulayın — satırlar henüz toplanmadı.

   SELECT * FROM test_tbl_summing;

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

4. Sıkıştırmayı zorlayın.

   OPTIMIZE TABLE test_tbl_summing FINAL;

5. Doğru sonuçlar almak için GROUP BY ve SUM() ile sorgulayın.

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

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

AggregatingMergeTree

AggregatingMergeTree, yalnızca SUM değil, herhangi bir toplama işlevini destekleyen daha esnek bir ön toplama motorudur. Özel AggregateFunction veri türünü ve iki aşamalı bir sorgu deseni kullanır:

• Yazma aşaması: Ara toplama durumlarını depolamak için -State sonekli işlevleri (örneğin, sumState, uniqState) kullanın.

• Sorgu aşaması: Toplamayı nihailendirmek için -Merge sonekli işlevleri (örneğin, sumMerge, uniqMerge) kullanın.

AggregatingMergeTree iki şekilde kullanılır: Yazma aşamasını otomatikleştiren bir somutlaştırılmış görünümle veya doğrudan AggregateFunction sütunlarıyla.

Örnek 1: Somutlaştırılmış görünümle AggregatingMergeTree

1. Bir detay tablosu oluşturun.

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

2. Detay tablosunu -State işlevlerini kullanarak önceden toplayan bir somutlaştırılmış görünüm oluşturun.

   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. Detay tablosuna veri ekleyin. Somutlaştırılmış görünüm otomatik olarak güncellenir.

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

4. Somutlaştırılmış görünümü -Merge işlevlerini kullanarak sorgulayın.

   Not

   sumMerge ve uniqMerge kullanın — düz sum ve uniq işlevleri AggregateFunction sütunlarını işleyemez ve hata döndürür.

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

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

Örnek 2: AggregateFunction sütunlarıyla AggregatingMergeTree

1. Bir detay tablosu oluşturun.

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

2. Veri ekleyin.

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

3. Bir AggregateFunction sütunu içeren bir toplama tablosu oluşturun.

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

4. uniqState kullanarak toplanmış verileri ekleyin.

   Not

   -State işlevleriyle bir SELECT kullanarak ekleyin. Ham değerlerle doğrudan INSERT INTO ... VALUES kullanmak tür hatasıyla başarısız olur.

   INSERT INTO agg_table
   SELECT CounterID, StartDate, uniqState(UserID)
   FROM detail_table
   GROUP BY CounterID, StartDate;

5. Toplama tablosunu uniqMerge kullanarak sorgulayın.

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

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