03 CDC Postgres terkelola

Buat Postgres terkelola ClickHouse dan sebuah ClickPipe dengan clickhousectl, lalu alirkan baris langsung ke tabel taksi.

Hasil

Dalam sekitar 20 menit, perjalanan langsung akan mengalir:

Postgres managed by ClickHouse → ClickPipe → default.realtime_trips
→ materialized view → nyc_tlc_data.taxi_trips → Ops dashboard

Prasyarat: Modul 00–02 selesai, dan terminal Anda berada di ClickHouse_Demos/workshops/build_workshop/app.

Langkah 1 — Buat Postgres terkelola di ClickHouse Cloud

Gunakan region yang sama dengan layanan ClickHouse Anda:

clickhousectl cloud postgres create \
  --name my-workshop-postgres \
  --provider aws \
  --region ap-southeast-1 \
  --size c6gd.large \
  --pg-version 17 \
  --ha-type none

Simpan Postgres ID, hostname, dan kata sandi postgres sekali-pakai yang dikembalikan. Perintah list dan get yang masih beta bisa mengembalikan hasil kosong atau FORBIDDEN; pemeriksaan kesiapan yang diwajibkan adalah ./preflight.sh --require-postgres di Langkah 2.

Jika kata sandinya hilang, buat yang baru:

clickhousectl cloud postgres reset-password <postgres-id>

Langkah 2 — Jalankan penulis perjalanan

Tidak ada fallback Postgres lokal. Di .env.workshop, isi kolom PGHOST dan PGPASSWORD yang kosong dengan nilai yang dikembalikan clickhousectl; pertahankan TLS sebagai keharusan:

PGHOST=replace-with-hostname-from-clickhousectl
PGPORT=5432
PGDATABASE=postgres
PGUSER=postgres
PGPASSWORD=replace-with-one-time-password
PGSSLMODE=require
PG_PUBLICATION=pub_taxi

Validasi endpoint terkelolanya, lalu jalankan penulis lokal terhadapnya dan ikuti log-nya:

cd "$(git rev-parse --show-toplevel)/workshops/build_workshop/app"
./preflight.sh --require-postgres
docker compose --profile cdc --env-file .env.workshop -f docker-compose.workshop.yml up -d pg-trip-writer
docker compose --profile cdc --env-file .env.workshop -f docker-compose.workshop.yml logs -f pg-trip-writer

Penyediaannya bisa memakan beberapa menit. Lanjutkan ketika log-nya berulang:

[loadgen] ensured realtime_trips table exists
[loadgen] created publication pub_taxi for public.realtime_trips
[loadgen] inserted 10 trips @ ...

Tekan Ctrl-C untuk berhenti mengikuti log; kontainernya tetap berjalan.

Langkah 3 — Buat ClickPipe

Ganti service ID ClickHouse dan nilai-nilai Postgres:

Unduh sertifikat CA Postgres terkelola

Unduh sertifikat CA khusus instance dari Settings → Security → Download CA certificate, lalu simpan sebagai .work/managed-postgres-ca.pem.

mkdir -p .work
mv "$HOME/Downloads/<downloaded-ca-filename>" .work/managed-postgres-ca.pem

workshop_env() { sed -n "s/^$1=//p" .env.workshop | tail -n 1; }
PGHOST=$(workshop_env PGHOST)
PGPORT=$(workshop_env PGPORT)
PGDATABASE=$(workshop_env PGDATABASE)
PGUSER=$(workshop_env PGUSER)
PGPASSWORD=$(workshop_env PGPASSWORD)
PG_PUBLICATION=$(workshop_env PG_PUBLICATION)
unset -f workshop_env
PG_CA_CERT="$PWD/.work/managed-postgres-ca.pem"
test -s "$PG_CA_CERT" || { echo "Missing CA certificate: $PG_CA_CERT" >&2; exit 1; }

clickhousectl cloud clickpipe create postgres <clickhouse-service-id> \
  --name taxi-cdc \
  --host "$PGHOST" \
  --port "$PGPORT" \
  --pg-database "$PGDATABASE" \
  --username "$PGUSER" \
  --password="$PGPASSWORD" \
  --publication-name "$PG_PUBLICATION" \
  --ca-certificate "$PG_CA_CERT" \
  --replication-mode cdc \
  --table-mapping "public.realtime_trips:realtime_trips"

clickhousectl tidak punya permintaan kata sandi interaktif untuk perintah ini. Membaca nilainya ke dalam variabel sementara menjaganya tidak masuk riwayat shell; bentuk --password=... juga berfungsi ketika kata sandi yang dihasilkan dimulai dengan -. Nilainya masih sesaat terlihat oleh alat inspeksi proses lokal selama perintah berjalan, jadi gunakan mesin terpercaya dan hapus variabelnya segera seperti yang ditunjukkan.

ClickPipe yang dibuat lewat CLI menempatkan target ini di default.realtime_trips. Periksa keadaannya:

clickhousectl cloud clickpipe list <clickhouse-service-id>
clickhousectl cloud clickpipe get <clickhouse-service-id> <clickpipe-id>

Lanjutkan ketika pipe-nya berjalan dan snapshot awal telah membuat tabel targetnya.

Langkah 4 — Buat materialized view CDC

Pertama verifikasi tabel sumber yang tepat:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
  SELECT database, name, engine
  FROM system.tables
  WHERE name = 'realtime_trips'
"

Diharapkan: default.realtime_trips. Sekarang buat satu materialized view inkremental; tidak ada varian untuk dipilih atau berkas untuk diubah:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
CREATE MATERIALIZED VIEW IF NOT EXISTS nyc_tlc_data.realtime_trips_to_taxi_trips_mv
TO nyc_tlc_data.taxi_trips
AS
SELECT
  car_type,
  CAST(vendor_id AS UInt16) AS vendor_id,
  CAST(pickup_datetime AS DateTime('UTC')) AS pickup_datetime,
  CAST(dropoff_datetime AS DateTime('UTC')) AS dropoff_datetime,
  CAST(pickup_location_id AS UInt16) AS pickup_location_id,
  CAST(dropoff_location_id AS UInt16) AS dropoff_location_id,
  CAST(passenger_count AS UInt16) AS passenger_count,
  trip_distance,
  CAST(payment_type AS UInt16) AS payment_type,
  fare_amount,
  tip_amount,
  total_amount,
  'realtime_cdc' AS filename
FROM default.realtime_trips
WHERE _peerdb_is_deleted = 0
"

Gunakan keahlian best-practices yang sudah terpasang untuk memeriksa desainnya:

Use the ClickHouse best-practices skill to review this incremental materialized view.
Confirm why it processes inserted blocks and why FINAL is not part of this append-only path.

Langkah 5 — Buktikan baris sedang bergerak

Jalankan ini dua kali, dengan jeda sekitar 15 detik:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
  SELECT
    (SELECT count() FROM default.realtime_trips) AS clickpipe_rows,
    (SELECT count() FROM nyc_tlc_data.taxi_trips
      WHERE filename = 'realtime_cdc') AS dashboard_rows
"

Kedua jumlahnya seharusnya bertambah. Lalu buka localhost:8080, atur interval dashboard Ops ke 1m dan auto-refresh ke 5s.

Pemeriksaan penyelesaian

Log penulis menunjukkan insert yang berulang.
clickhousectl cloud clickpipe get ... melaporkan pipe yang berjalan.
default.realtime_trips dan jumlah baris dashboard keduanya bertambah.
Dashboard Ops memperbarui diri tanpa refresh manual.

Lanjutkan ke 04 ClickHouse Agents.