03 Managed Postgres CDC

Tạo Postgres do ClickHouse quản lý và một ClickPipe bằng clickhousectl, rồi nạp các dòng dữ liệu trực tiếp vào bảng taxi.

Kết quả

Trong khoảng 20 phút, các chuyến xe trực tiếp sẽ chảy như sau:

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

Yêu cầu tiên quyết: Module 00–02 đã hoàn thành, và terminal của bạn đang ở ClickHouse_Demos/workshops/build_workshop/app.

Bước 1 — Tạo Postgres được quản lý trong ClickHouse Cloud

Hãy dùng cùng region với service ClickHouse của bạn:

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

Hãy lưu lại Postgres ID, hostname, và password postgres dùng một lần được trả về. Các câu lệnh list và get ở bản beta có thể trả về rỗng hoặc FORBIDDEN; phép kiểm tra mức sẵn sàng bắt buộc là ./preflight.sh --require-postgres ở Bước 2.

Nếu mất password, hãy tạo một password mới:

clickhousectl cloud postgres reset-password <postgres-id>

Bước 2 — Khởi động bộ ghi chuyến xe

Không có phương án Postgres local dự phòng. Trong .env.workshop, hãy điền các field PGHOST và PGPASSWORD đang trống bằng các giá trị mà clickhousectl trả về; hãy giữ yêu cầu TLS:

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

Hãy xác nhận endpoint được quản lý, rồi khởi động bộ ghi local trỏ tới nó và theo dõi log của nó:

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

Việc cấp phát tài nguyên có thể mất vài phút. Hãy tiếp tục khi log lặp lại như sau:

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

Nhấn Ctrl-C để dừng theo dõi log; container vẫn tiếp tục chạy.

Bước 3 — Tạo ClickPipe

Hãy thay service ID của ClickHouse và các giá trị Postgres:

Tải chứng chỉ CA của Postgres được quản lý

Tải chứng chỉ CA riêng cho phiên bản từ Settings → Security → Download CA certificate, rồi lưu thành .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 không có phần nhập password tương tác cho câu lệnh này. Việc đọc giá trị vào một biến tạm giữ nó khỏi lịch sử shell; dạng --password=... cũng hoạt động khi password được sinh ra bắt đầu bằng -. Giá trị này vẫn hiện ra trong thời gian ngắn với các công cụ kiểm tra tiến trình cục bộ trong lúc câu lệnh chạy, nên hãy dùng một máy đáng tin cậy và unset nó ngay lập tức như trên.

Một ClickPipe được tạo qua CLI sẽ đặt đích này ở default.realtime_trips. Hãy kiểm tra trạng thái của nó:

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

Hãy tiếp tục khi pipe đang chạy và snapshot ban đầu đã tạo bảng đích.

Bước 4 — Tạo materialized view cho CDC

Trước tiên hãy kiểm chứng chính xác bảng nguồn:

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

Kết quả mong đợi: default.realtime_trips. Giờ hãy tạo một incremental materialized view; không có biến thể nào để chọn hay file nào để sửa:

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
"

Hãy dùng skill best-practices đã cài để kiểm tra thiết kế:

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.

Bước 5 — Chứng minh dữ liệu đang chảy

Hãy chạy lệnh này hai lần, cách nhau khoảng 15 giây:

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
"

Cả hai số đếm đều phải tăng lên. Sau đó hãy mở localhost:8080, đặt interval của dashboard Ops thành 1m và auto-refresh thành 5s.

Kiểm tra hoàn thành

Log của bộ ghi hiển thị các lần insert lặp lại.
clickhousectl cloud clickpipe get ... báo pipe đang chạy.
Số đếm của default.realtime_trips và số dòng cho dashboard đều tăng lên.
Dashboard Ops tự cập nhật mà không cần refresh thủ công.

Tiếp tục tới 04 ClickHouse Agents.