Operasi ClickHouse

Dokumen ini menjelaskan konsep ClickHouse yang akan Anda temui di lab migrasi ini — apa itu, mengapa ada, dan bagaimana bedanya dari konstruksi Snowflake yang Anda pakai di Bagian 1.

1. Engine Tabel

ClickHouse bukan database bermesin tunggal. Setiap tabel yang Anda buat wajib mendeklarasikan engine-nya, yang menentukan bagaimana data disimpan di disk, bagaimana duplikat ditangani, dan kapabilitas apa yang tersedia. Memilih engine yang salah adalah kesalahan paling umum dalam desain skema ClickHouse.

MergeTree

Engine dasar untuk hampir semua tabel produksi.

CREATE TABLE analytics.dim_taxi_zones (
    zone_id      UInt16,
    borough      String,
    service_zone String
) ENGINE = MergeTree()
ORDER BY zone_id;

Apa yang ia lakukan: ClickHouse menyimpan data dalam parts — potongan tersortir dan terkompresi di disk. Ketika Anda menyisipkan data, parts baru ditulis. Di latar belakang, ClickHouse terus-menerus me-merge parts kecil menjadi lebih besar, menjaga data tetap tersortir menurut kunci ORDER BY. Dari situlah namanya berasal.

Kapan dipakai: Tabel apa pun di mana Anda tidak perlu deduplikasi dan INSERT-nya append-only atau bulk load (tabel dimensi, tabel event mentah, tabel log).

Properti kunci: Tidak ada penegakan primary key. Dua baris dengan nilai ORDER BY identik keduanya disimpan. Jika Anda butuh deduplikasi, pakai ReplacingMergeTree.

ReplacingMergeTree(version_col)

Engine deduplikasi. Ia memperluas MergeTree dengan satu aturan: selama merge latar belakang, jika dua baris berbagi kunci ORDER BY yang sama, simpan hanya yang bernilai version_col tertinggi.

CREATE TABLE analytics.fact_trips (
    trip_id     String,
    pickup_at   DateTime,
    total_amount Float64,
    updated_at  DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (pickup_at, trip_id);

Penting — konsistensi eventual: Deduplikasi hanya terjadi selama merge latar belakang. Pada saat tertentu, tabel Anda bisa memuat baris duplikat. Ini disebut eventual consistency. Untuk mendapatkan hasil yang terdeduplikasi sepenuhnya saat query, tambahkan FINAL ke SELECT Anda:

-- Without FINAL: may return duplicates if merges haven't run
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc';

-- With FINAL: forces deduplication at query time (slower, always correct)
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc';

Bagaimana dbt memakainya: Adapter dbt-clickhouse memakai strategi inkremental delete_insert sebagai mekanisme utamanya — ia secara eksplisit menghapus baris dengan kunci yang cocok dan menyisipkan yang baru, yang selalu benar. ReplacingMergeTree berperan sebagai jaring pengaman yang membersihkan duplikat yang lolos (misalnya dari INSERT parsial yang gagal).

Padanan di Snowflake: Tidak ada padanan langsung. Di Snowflake Anda memakai MERGE INTO ... WHEN MATCHED THEN UPDATE. ClickHouse tidak punya pernyataan MERGE — ReplacingMergeTree plus FINAL mencapai hasil logis yang sama.

Refreshable Materialized View

ClickHouse mendukung dua jenis materialized view.

MV berbasis trigger (tradisional): Dieksekusi pada setiap INSERT, hanya memproses batch yang baru disisipkan.

-- Trigger-based: only sees the rows inserted in the current batch
CREATE MATERIALIZED VIEW analytics.mv_realtime_counts
TO analytics.counts_table AS
SELECT pickup_date, count() AS trips
FROM default.trips_raw
GROUP BY pickup_date;

MV refreshable (terjadwal): Menjalankan ulang query penuh sesuai jadwal, seperti cron job.

-- Refreshable: runs the full SELECT every 3 minutes
CREATE MATERIALIZED VIEW analytics.mv_hourly_revenue
REFRESH EVERY 180 SECOND AS
SELECT
    toStartOfHour(pickup_at) AS hour_bucket,
    pickup_borough,
    sum(total_amount)        AS revenue
FROM analytics.fact_trips FINAL
GROUP BY hour_bucket, pickup_borough;

Kapan memakai yang mana:

• Berbasis trigger: agregasi real-time pada stream INSERT di mana Anda hanya perlu memproses data baru
• Refreshable: agregasi yang mem-query fact_trips FINAL (harus melihat tabel penuh untuk mendeduplikasi), atau dashboard yang bisa menoleransi kebasian beberapa menit sebagai imbalan logika yang lebih sederhana

Mengubah interval refresh:

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. Kunci Sortir (ORDER BY)

Di Snowflake, Anda memakai CLUSTER BY sebagai petunjuk bagi optimizer. Di ClickHouse, ORDER BY adalah primary index — ia menentukan urutan sortir fisik data di disk dan menggerakkan semua range scan.

Cara kerjanya

ClickHouse menyimpan primary index yang sparse: satu entri index per ~8.192 baris (satu granule data). Ketika Anda memfilter berdasarkan kolom ORDER BY, ClickHouse melewati granule utuh tanpa membacanya. Inilah sebabnya ClickHouse bisa memindai miliaran baris per detik — sebagian besar data tidak pernah meninggalkan disk.

Urutan kardinalitas penting

Selalu letakkan kolom berkardinalitas rendah lebih dulu, kolom berkardinalitas tinggi paling akhir. Ini memberi index daya melewati blok yang maksimal untuk kasus yang umum.

-- Good: low cardinality (borough, ~6 values) first, then high cardinality (trip_id)
ORDER BY (pickup_borough, toStartOfMonth(pickup_at), trip_id)

-- Bad: high cardinality first — the index can't skip anything useful
ORDER BY (trip_id, pickup_borough, pickup_at)

Snowflake CLUSTER BY vs ClickHouse ORDER BY

Contoh: menyamakan dengan cluster key Snowflake yang sudah ada

-- Snowflake
CLUSTER BY (DATE_TRUNC('month', PICKUP_AT), PICKUP_LOCATION_ID)

-- ClickHouse equivalent
ORDER BY (toStartOfMonth(pickup_at), pickup_location_id, trip_id)
-- Note: trip_id added as tiebreaker to ensure unique sort order

Skip index (disebut singkat)

Untuk kolom yang tidak ada di kunci ORDER BY, ClickHouse mendukung skip index (bloom filter, minmax, set) yang menyimpan metadata tingkat kolom per granule. Berguna untuk memfilter kolom berkardinalitas rendah yang muncul setelah kolom berkardinalitas tinggi di kunci sortir.

-- Add a bloom filter skip index on payment_type
ALTER TABLE analytics.fact_trips
ADD INDEX idx_payment_type payment_type TYPE bloom_filter GRANULARITY 4;

3. Penanganan JSON

Tipe kolom VARIANT milik Snowflake mendukung notasi colon-path untuk menelusuri JSON bersarang. ClickHouse memakai fungsi JSONExtract* yang eksplisit sebagai gantinya.

Tabel terjemahan berdampingan

Varian fungsi

-- Float (returns 0.0 if key missing or wrong type)
JSONExtractFloat(trip_metadata, 'driver', 'rating')

-- String (returns '' if missing)
JSONExtractString(trip_metadata, 'app', 'version')

-- Integer (returns 0 if missing)
JSONExtractInt(trip_metadata, 'driver', 'id')

-- Bool (returns 0/1)
JSONExtractBool(trip_metadata, 'app', 'is_shared')

-- Raw value as string (preserves JSON sub-object)
JSONExtractRaw(trip_metadata, 'route')

Tips performa

Jika Anda mem-query kolom JSON yang sama berulang kali, pertimbangkan mengekstrak field ke kolom bertipe di tingkat model staging (di stg_trips.sql) daripada memanggil JSONExtractFloat di setiap query hilir. Inilah yang dilakukan model dbt di lab ini.

4. Fungsi Tanggal/Waktu

Snowflake dan ClickHouse punya kapabilitas tanggal/waktu yang mirip tetapi sintaks berbeda. Terjemahan yang paling umum:

Tabel terjemahan berdampingan

Kemudahan tambahan khas ClickHouse

yesterday()              -- today() - 1 day
toStartOfWeek(col)       -- Monday of the containing week
toStartOfQuarter(col)    -- first day of the quarter
toYear(col)              -- extract year as integer
toMonth(col)             -- extract month as integer (1-12)
toDayOfWeek(col)         -- 1=Monday, 7=Sunday

Sintaks interval

-- ClickHouse
now() - INTERVAL 7 DAY
now() - INTERVAL 1 HOUR
now() - INTERVAL 30 MINUTE
pickup_at + INTERVAL 90 SECOND

-- Snowflake equivalent
DATEADD('day', -7, CURRENT_TIMESTAMP())
DATEADD('hour', -1, CURRENT_TIMESTAMP())

5. Fungsi Aproksimasi

ClickHouse dibangun untuk workload analitik di mana jawaban eksak atas miliaran baris lebih lambat daripada jawaban aproksimasi yang cukup akurat untuk dashboard. ClickHouse menyertakan beberapa fungsi agregat aproksimasi bawaan.

Count distinct

-- Exact (like Snowflake COUNT(DISTINCT ...))
SELECT uniqExact(trip_id) FROM analytics.fact_trips FINAL;

-- Approximate — good for "how many unique passengers today?"
SELECT uniq(passenger_id) FROM analytics.fact_trips FINAL;

Persentil

-- P95 trip duration — approximate but uses O(1) memory
SELECT quantileTDigest(0.95)(duration_minutes)
FROM analytics.fact_trips FINAL;

-- Multiple percentiles in one pass
SELECT quantileTDigestMerge(0.5)(state), quantileTDigestMerge(0.95)(state)
FROM analytics.fact_trips FINAL;

Patokan praktis: Pakai uniq dan quantileTDigest untuk dashboard interaktif. Pakai uniqExact dan quantile hanya ketika Anda butuh nilai eksak untuk penagihan, SLA, atau kepatuhan.

6. Dictionaries

Dictionaries adalah tabel lookup in-memory yang ClickHouse jaga tetap hangat dan sudah ter-join saat query. Ia adalah padanan ClickHouse untuk tabel dimensi kecil yang ingin Anda join tanpa biaya JOIN penuh.

Apa itu

Sebuah dictionary ditopang oleh sebuah sumber (tabel ClickHouse, sebuah file, atau database eksternal) dan dimuat ke memori ketika service dimulai atau ketika Anda memanggil SYSTEM RELOAD DICTIONARIES. Lookup terjadi lewat sebuah kunci, mengembalikan satu atau lebih atribut.

Sintaks CREATE DICTIONARY

-- From 04_create_dictionary.sql
CREATE DICTIONARY analytics.taxi_zones_dict (
    zone_id      UInt16,
    borough      String,
    service_zone String
)
PRIMARY KEY zone_id
SOURCE(CLICKHOUSE(
    TABLE 'dim_taxi_zones'
    DB    'analytics'
))
LIFETIME(MIN 300 MAX 600)   -- refresh every 5-10 minutes
LAYOUT(FLAT());             -- hash map, best for < 1M rows

Opsi LAYOUT:

• FLAT() — array terindeks kunci integer, tercepat, butuh kunci integer berurutan
• HASHED() — hash map, berfungsi dengan kunci integer apa pun
• COMPLEX_KEY_HASHED() — hash map dengan kunci komposit atau string

Penggunaan dictGet

-- Instead of: JOIN analytics.dim_taxi_zones USING (zone_id)
SELECT
    trip_id,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(pickup_location_id)) AS pickup_borough,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(dropoff_location_id)) AS dropoff_borough
FROM analytics.fact_trips FINAL;

Kapan pakai dictionaries dan kapan JOIN

7. Klausa SAMPLE

ClickHouse mendukung sampling tingkat baris langsung di sintaks query. Sampling membaca fraksi data yang deterministik — berguna untuk analisis eksploratif ketika Anda tidak butuh hasil eksak.

Sintaks

-- Read approximately 10% of rows
SELECT count(), avg(total_amount)
FROM analytics.fact_trips SAMPLE 0.1;

-- Read a specific number of rows (approximately)
SELECT trip_id, pickup_at, total_amount
FROM analytics.fact_trips SAMPLE 1000000;

Menskalakan hasil

Saat melakukan sampling, kalikan agregat dengan 1 / sample_rate untuk mengestimasi nilai tabel penuh:

-- Estimate total revenue from 10% sample
SELECT sum(total_amount) * 10 AS estimated_total_revenue
FROM analytics.fact_trips SAMPLE 0.1;

Kapan memakai SAMPLE

• Analisis eksploratif ("apakah logika query saya benar?") sebelum menjalankannya pada tabel penuh
• Tile dashboard di mana nilai aproksimasi bisa diterima
• Melatih model ML pada subset yang representatif

Catatan: SAMPLE menuntut kunci ORDER BY tabel dimulai dengan kolom sampling, atau Anda harus menambahkan klausa SAMPLE BY pada pernyataan CREATE TABLE. Tabel trips_raw di lab ini dibuat dengan SAMPLE BY cityHash64(trip_id) untuk keperluan ini.

8. Catatan Adapter dbt-clickhouse

Adapter dbt-clickhouse (dbt-clickhouse>=1.8) mendukung sebagian besar fitur dbt standar tetapi punya beberapa perilaku spesifik ClickHouse yang perlu Anda pahami.

Strategi inkremental delete_insert

ClickHouse tidak punya MERGE INTO. Strategi delete_insert pada adapter dbt-clickhouse mengemulasikannya:

1. Hapus baris dari tabel target di mana kolom kunci cocok dengan batch masuk
2. Sisipkan seluruh himpunan baris masuk

-- What dbt generates for incremental models
DELETE FROM analytics.fact_trips WHERE trip_id IN (SELECT trip_id FROM __dbt_tmp);
INSERT INTO analytics.fact_trips SELECT * FROM __dbt_tmp;

Konfigurasikan di model Anda:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete_insert',
        unique_key='trip_id',
        engine='ReplacingMergeTree(updated_at)',
        order_by='(pickup_at, trip_id)'
    )
}}

Konfigurasi engine dan order_by

Setiap tabel MergeTree butuh sebuah engine dan sebuah ORDER BY. Tentukan keduanya di konfigurasi model dbt:

{{
    config(
        engine='MergeTree()',
        order_by='(zone_id)'
    )
}}

order_by bukan cluster_by

Di model dbt Snowflake Anda mungkin memakai cluster_by. Di dbt-clickhouse, pakai order_by sebagai gantinya. Tidak ada padanan cluster_by Snowflake di ClickHouse — ORDER BY selalu merupakan sortir fisik.

profiles.yml untuk ClickHouse Cloud

ClickHouse Cloud mewajibkan TLS. Set secure: true:

# ~/.dbt/profiles.yml
nyc_taxi_ch:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: default
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics       # default database for models without a custom schema
      secure: true
      threads: 4

Penamaan skema dan database terpisah

ClickHouse memakai istilah database di mana Snowflake memakai schema. Adapter dbt-clickhouse memetakan schema dbt ke database ClickHouse. Macro generate_schema_name di proyek ini menimpa perilaku default dbt sehingga model dengan +schema: analytics mendarat di database analytics, bukan staging_analytics.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema }}
  {%- else -%}
    {{ custom_schema_name }}
  {%- endif -%}
{%- endmacro %}

Ini pola yang sama dengan yang dipakai di proyek dbt Snowflake (Bagian 1) — macro-nya sengaja dibuat identik agar perilaku penamaan skema konsisten di kedua adapter.