04 Mengecilkan tabel

Lima perubahan konkret pada schema naive, dijalankan satu per satu, masing-masing dengan jumlah byte terkompresi yang membuktikan apakah itu membantu.

Hasil akhir

Dalam sekitar 25 menit Anda akan membawa bq.events_naive melalui lima perubahan, satu per satu, dan mengukur ukuran terkompresi setelah masing-masing. Di akhir Anda akan memiliki tabel yang menyimpan event yang sama dalam byte yang jauh lebih sedikit, dan Anda akan tahu persis bagian mana dari pengurangan itu yang didapat dari perubahan mana, karena Anda menyaksikan masing-masing terjadi.

Titik awal

bq.events_naive adalah titik di mana modul 03 meninggalkan Anda: migrasi yang berfungsi tapi biasa-biasa saja, setiap kolom Nullable, disortir berdasarkan microsecond timestamp mentah yang tidak berhubungan dengan cara siapa pun mengquery-nya. Diukur pada export penuh 4,295,584 baris, pada service ClickHouse Cloud:

348,759,309 Bterkompresi (332.60 MiB)

5,897,273,025 Btidak terkompresi (5,624.08 MiB)

Selisih antara terkompresi dan tidak terkompresi itu sudah menunjukkan kompresi default ClickHouse bekerja nyata pada data yang belum dituning siapa pun. Lima langkah di bawah mengalahkan angka itu, dengan sengaja, satu lever pada satu waktu.

Soal angka-angka di modul ini

Setiap langkah di bawah menunjukkan byte dan MiB persis yang dikembalikan oleh query pengukurannya, di service ClickHouse Cloud terhadap export penuh 4,295,584 baris, jadi Anda punya angka konkret untuk mengecek hasil run Anda sendiri, bukan cuma persentase yang harus dibalik jadi angka. Angka Cloud sedikit bervariasi dari run ke run dan service ke service -- kalau punya Anda mendekati tapi tidak identik, itu wajar. Jalankan setiap query sendiri. Itulah satu-satunya angka yang benar-benar menggambarkan service Anda.

Langkah 1: Ratakan dan sesuaikan ukuran tipe

Setiap kolom di bq.events_naive adalah Nullable, karena itulah yang dihasilkan export Parquet BigQuery dan belum ada yang membetulkannya. Beberapa juga berbentuk salah untuk apa yang disimpannya: event_date adalah String bukan Date, event_timestamp adalah Int64 mentah berisi microsecond bukan DateTime64, dan device, geo, serta traffic_source adalah tuple bersarang bukan kolom flat.

Tidak ada satu pun dari itu yang berbiaya tidak wajar untuk disimpan dengan benar. Nullable menambahkan mask yang harus disimpan ClickHouse per kolom, String untuk tanggal memakai byte lebih banyak dibanding Date, dan membaca field bersarang di setiap query adalah friksi tanpa keuntungan kompresi sama sekali. Langkah ini menghapus semuanya: ratakan setiap field bersarang menjadi kolomnya sendiri, buang Nullable demi default eksplisit ('' untuk string, 0 untuk angka), dan beri setiap kolom tipe yang benar-benar cocok untuk nilainya.

CREATE TABLE bq.tuned_types
(
  event_date       Date,
  event_time       DateTime64(6),
  event_name       String,
  user_pseudo_id   String,
  user_id          String,
  device_category  String,
  device_os        String,
  device_browser   String,
  geo_country      String,
  geo_city         String,
  geo_continent    String,
  traffic_medium   String,
  traffic_source   String,
  traffic_name     String,
  platform         String,
  stream_id        UInt32,
  ga_session_id    UInt64,
  page_location    String,
  page_title       String,
  engagement_msec  UInt32,
  item_ids         Array(String),
  item_names       Array(String),
  event_params     Map(String, String)
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_types
SELECT
  toDate(parseDateTimeBestEffort(n.event_date))                                   AS event_date,
  fromUnixTimestamp64Micro(n.event_timestamp, 'UTC')                              AS event_time,
  ifNull(n.event_name, '')                                                        AS event_name,
  ifNull(n.user_pseudo_id, '')                                                    AS user_pseudo_id,
  ifNull(n.user_id, '')                                                           AS user_id,
  ifNull(n.device.category, '')                                                   AS device_category,
  ifNull(n.device.operating_system, '')                                           AS device_os,
  ifNull(n.device.web_info.browser, '')                                           AS device_browser,
  ifNull(n.geo.country, '')                                                       AS geo_country,
  ifNull(n.geo.city, '')                                                          AS geo_city,
  ifNull(n.geo.continent, '')                                                     AS geo_continent,
  ifNull(n.traffic_source.medium, '')                                             AS traffic_medium,
  ifNull(n.traffic_source.source, '')                                             AS traffic_source,
  ifNull(n.traffic_source.name, '')                                               AS traffic_name,
  ifNull(n.platform, '')                                                          AS platform,
  toUInt32(ifNull(n.stream_id, 0))                                                AS stream_id,
  toUInt64(ifNull(arrayFirst(x -> x.key = 'ga_session_id', n.event_params).value.int_value, 0)) AS ga_session_id,
  ifNull(arrayFirst(x -> x.key = 'page_location', n.event_params).value.string_value, '')       AS page_location,
  ifNull(arrayFirst(x -> x.key = 'page_title',    n.event_params).value.string_value, '')       AS page_title,
  toUInt32(ifNull(arrayFirst(x -> x.key = 'engagement_time_msec', n.event_params).value.int_value, 0)) AS engagement_msec,
  arrayMap(i -> ifNull(i.item_id, ''),   n.items)                                 AS item_ids,
  arrayMap(i -> ifNull(i.item_name, ''), n.items)                                 AS item_names,
  mapFromArrays(
    arrayMap(x -> ifNull(x.key, ''), n.event_params),
    arrayMap(x -> coalesce(
      x.value.string_value,
      toString(x.value.int_value),
      toString(x.value.double_value),
      ''), n.event_params)
  )                                                                              AS event_params
FROM bq.events_naive AS n;

Baca ukuran barunya dengan cara yang sama seperti Anda membaca titik awalnya:

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_types';

303,794,384 Bterkompresi, Cloud (289.72 MiB)

~13%lebih kecil dari tabel naive

Meratakan dan menyesuaikan ukuran tipe saja, tanpa LowCardinality, tanpa codec, dan sort key yang masih sama, sudah memotong bagian yang cukup berarti dari tabelnya. Setiap langkah dari sini menumpuk di atas langkah ini.

Langkah 2: `LowCardinality` untuk kolom yang berulang-ulang

LowCardinality menyimpan setiap nilai berbeda satu kali, dalam sebuah dictionary, dan setiap baris sebagai integer kecil yang menunjuk ke dalamnya. Ia layak dipakai ketika sebuah kolom berasal dari vocabulary kecil dibanding jumlah baris. Cardinality dataset ini sendiri yang sudah diukur membuktikannya langsung: funnel.event_names menghitung 17 nilai event_name berbeda, funnel.countries menghitung 109 nilai geo_country berbeda, funnel.item_names menghitung 431 nama item berbeda, semuanya beberapa orde besar di bawah 4,295,584 baris.

Bungkus setiap kolom seperti itu. user_pseudo_id dan user_id adalah kasus sebaliknya (mendekati satu nilai berbeda per user) dan tetap String biasa.

CREATE TABLE bq.tuned_lowcard
(
  event_date       Date,
  event_time       DateTime64(6),
  event_name       LowCardinality(String),
  user_pseudo_id   String,
  user_id          String,
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64,
  page_location    String,
  page_title       LowCardinality(String),
  engagement_msec  UInt32,
  item_ids         Array(String),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String)
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_lowcard SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_lowcard';

183,994,516 Bterkompresi, Cloud (175.47 MiB)

~39%lebih kecil dari Langkah 1

Penurunan terbesar sejauh ini, dari membungkus tiga belas kolom yang sudah bertipe benar, berbentuk benar, dan menyimpan nilai yang benar. Tidak ada apa pun soal datanya yang berubah.

Langkah 3: Codec untuk kolom yang berubah secara terduga

Codec kompresi kolom berjalan sebelum compressor umum melihat byte-nya, membentuk ulang nilai menjadi sesuatu yang lebih bisa dikompres dulu. Delta menyimpan selisih dari nilai sebelumnya alih-alih nilai itu sendiri, yang menjadi angka kecil ketika sebuah kolom bergerak ke satu arah. Itu hanya membantu kolom yang monotonik, atau mendekati itu, dalam urutan penyimpanan: event_date dan event_time persis seperti itu, karena tabel ini masih disortir berdasarkan event_time.

CREATE TABLE bq.tuned_codecs
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY event_time;

INSERT INTO bq.tuned_codecs SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_codecs';

176,934,547 Bterkompresi, Cloud (168.74 MiB)

~4%lebih kecil dari Langkah 2

Potongan kecil yang nyata di atas apa yang sudah dihemat LowCardinality -- jauh lebih kecil dari penurunan sekitar 48%-di-atas-48% yang ditunjukkan run clickhouse local untuk langkah yang sama saat modul ini ditulis. Di Cloud, LowCardinality sudah menangkap sebagian besar manfaat yang ditambahkan codec ini: Delta pada dua kolom monotonik, ditambah codec ZSTD umum pada kolom string berentropi lebih tinggi, tetap melakukan kerja nyata, hanya saja ruang yang tersisa untuk itu lebih sedikit setelah LowCardinality berjalan. Tidak ada kolom lain di schema ini yang monotonik dalam sort order, itulah sebabnya Delta tidak muncul di tempat lain: menerapkannya pada kolom tanpa hubungan urutan antara baris yang berdekatan tidak mengecilkan apa pun.

Langkah 4: Pilih sort key, dan lihat apa yang terjadi

Sort key sebuah MergeTree menentukan urutan fisik baris ditulis. Sejauh ini tabel ini disortir berdasarkan event_time, yang praktis tapi arbitrer. Sisa workshop ini butuh baris disortir berdasarkan (event_date, event_name, user_pseudo_id) sebagai gantinya, karena lookup modul 05 memfilter pada user_pseudo_id dan sort key hanya bisa melakukan pruning pada kolom-kolom terdepannya.

CREATE TABLE bq.tuned_sorted
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY (event_date, event_name, user_pseudo_id);

INSERT INTO bq.tuned_sorted SELECT * FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'tuned_sorted';

194,809,477 Bterkompresi, Cloud (185.78 MiB)

~10%LEBIH BESAR dari Langkah 3

Itu bukan salah ketik, dan bukan kesalahan di DDL di atas: langkah ini membuat kompresi benar-benar lebih buruk, lebih banyak di Cloud dibanding sekitar 6% yang ditunjukkan run clickhouse local untuk langkah ini. event_time terkompresi dengan baik justru karena baris secara fisik diurutkan berdasarkan event_time, yang membuat selisih baris-ke-baris Delta kecil. Menyortir berdasarkan (event_date, event_name, user_pseudo_id) sebagai gantinya mengacak urutan waktu yang detail itu di dalam setiap grup, sehingga Delta pada event_time kini melihat lompatan yang lebih besar dan kurang terduga.

Trade-off ini adalah intinya, bukan langkah yang salah

Sort key tidak dipilih untuk kompresi. Ia dipilih untuk cara sebuah query membaca tabelnya, dan modul 05 seluruhnya tentang trade-off itu: sekitar 10% yang dikorbankan langkah ini di sini membeli lookup yang membaca ratusan kali lebih sedikit baris begitu Anda memfilter pada user_pseudo_id. Pertahankan sort key ini. Langkah berikutnya memulihkan kerugiannya dan lebih.

Langkah 5: De-duplikasi `event_params`

event_params masih menyimpan setiap key dari sumbernya, termasuk empat yang sudah diekstrak ke kolomnya sendiri dua langkah yang lalu: page_location, page_title, ga_session_id, dan engagement_time_msec. Setiap event membayar untuk menyimpan keempat value itu dua kali: sekali ditipekan, di kolom khusus, dan sekali lagi sebagai teks, di dalam map generik.

mapFilter menghapus key tertentu dari sebuah Map menggunakan predikat atas setiap pasangan key/value. Filter keempat key yang terduplikasi itu dari map residual, di atas semua yang sudah dibangun Langkah 4. Langkah ini menghasilkan bq.events_tuned, tabel yang dipakai sisa workshop ini:

CREATE TABLE bq.events_tuned
(
  event_date       Date          CODEC(Delta, ZSTD(1)),
  event_time       DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name       LowCardinality(String),
  user_pseudo_id   String        CODEC(ZSTD(1)),
  user_id          String        CODEC(ZSTD(1)),
  device_category  LowCardinality(String),
  device_os        LowCardinality(String),
  device_browser   LowCardinality(String),
  geo_country      LowCardinality(String),
  geo_city         LowCardinality(String),
  geo_continent    LowCardinality(String),
  traffic_medium   LowCardinality(String),
  traffic_source   LowCardinality(String),
  traffic_name     LowCardinality(String),
  platform         LowCardinality(String),
  stream_id        UInt32,
  ga_session_id    UInt64        CODEC(ZSTD(1)),
  page_location    String        CODEC(ZSTD(3)),
  page_title       LowCardinality(String),
  engagement_msec  UInt32        CODEC(ZSTD(1)),
  item_ids         Array(String) CODEC(ZSTD(1)),
  item_names       Array(LowCardinality(String)),
  event_params     Map(LowCardinality(String), String) CODEC(ZSTD(6))
)
ENGINE = MergeTree
ORDER BY (event_date, event_name, user_pseudo_id);

INSERT INTO bq.events_tuned
SELECT
  event_date, event_time, event_name, user_pseudo_id, user_id,
  device_category, device_os, device_browser,
  geo_country, geo_city, geo_continent,
  traffic_medium, traffic_source, traffic_name,
  platform, stream_id, ga_session_id, page_location, page_title, engagement_msec,
  item_ids, item_names,
  mapFilter(
    (k, v) -> k NOT IN ('page_location', 'page_title', 'ga_session_id', 'engagement_time_msec'),
    event_params
  ) AS event_params
FROM bq.tuned_types;

SELECT sum(data_compressed_bytes) AS compressed_bytes
FROM system.parts
WHERE active AND database = 'bq' AND table = 'events_tuned';

140,637,179 Bterkompresi, Cloud (134.12 MiB)

~28%lebih kecil dari Langkah 4

Lever tunggal terbesar di seluruh modul ini, lebih besar dari LowCardinality atau codec sendirian. event_params adalah kolom berentropi tertinggi di schema ini: URL free-text, kata kunci pencarian, identifier campaign. Menyimpan data berentropi tinggi yang sama dua kali, sekali di dalam map dan sekali lagi di kolom khusus, berarti membayar biaya kompresinya dua kali. Menghapus duplikatnya adalah apa yang sebenarnya direspons jumlah byte-nya.

Biaya map residual

Memfilter event_params mengembalikan value setiap key yang diekstrak secara gratis dari kolom khususnya, tapi tidak mempertahankan presence. Jika sebuah event sumber tidak pernah membawa param page_title sama sekali, kolom khusus page_title menyimpan '', nilai yang sama yang akan disimpannya jika event itu membawa page_title dan kebetulan kosong. Begitu key-nya hilang dari map, tidak ada apa pun di schema ini yang bisa membedakan kedua kasus itu. Itu biaya nyata dan disengaja dari desain ini, bukan kelalaian.

Konfirmasi jumlah barisnya cocok dengan yang Anda mulai, sekarang setelah Anda membangun ulang tabelnya dua kali lagi sejak modul 03:

SELECT count() FROM bq.events_tuned;

Harapkan 4,295,584, sama seperti bq.events_naive.

Apa yang didapat dari lima langkah

Dua angka di bawah adalah bookend sebenarnya, keduanya diukur di ClickHouse Cloud, terhadap export 4,295,584 baris yang sama, dari awal sampai akhir.

348,759,309 Bnaive (Cloud, modul 03)

140,637,179 Bevents_tuned (Cloud)

2.48xlebih kecil, diukur di Cloud

Dua langkah melakukan hampir semua kerjanya: LowCardinality dan de-duplikasi event_params. Tipe juga penting, tapi codec saja hampir tidak menggerakkan jumlah byte begitu LowCardinality sudah berjalan. Langkah sort key membuat kompresi jelas lebih buruk dengan sengaja, karena itu tidak pernah dipilih untuk kompresi: itu yang dibutuhkan modul 05 untuk mengubah full-table scan menjadi lookup yang membaca beberapa ribu baris alih-alih jutaan.

Selesai jika

bq.events_tuned menyimpan 4,295,584 baris, dan Anda bisa mengatakan dalam satu kalimat apa yang diubah masing-masing dari lima langkah, termasuk mengapa Langkah 4 membuat tabelnya lebih besar bukannya lebih kecil. Lanjut ke 05 Sort key jika Anda sudah siap.