How we built the Internal Data Warehouse at ClickHouse

ClickHouse · 2023 · 2. Tăng trưởng · Độ khó: Trung bình — bài gốc

Phần 1 của hai bài về DWH nội bộ của ClickHouse; phần tiếp theo một năm sau: ClickHouse 2024. Nhà cung cấp dùng chính sản phẩm của mình, nên các nhận định về chi phí so với đối thủ là theo ClickHouse.

Bối cảnh

• ClickHouse Cloud ra private preview 5/2022. Lúc đầu VP Product phân tích workload khách hàng bằng Excel, tra cứu tay mỗi ngày. Từ đó có DWH nội bộ đầu tiên.
• Khoảng 10 nguồn dữ liệu, mỗi nguồn một kiểu: Control Plane (DocumentDB, khoảng 500 MB/giờ), Data Plane (ClickHouse Cloud, khoảng 15 GB/giờ), AWS CUR và GCP Billing, Salesforce, M3ter (metering), Galaxy (observability của control plane), Segment, Marketo, bảng giá AWS/GCP.
• Không thu thập dữ liệu của khách (dữ liệu bảng, nội dung query); với query chỉ lấy danh sách hàm dùng, thời gian chạy, bộ nhớ.

Thay đổi chính

• Giả định ban đầu: độ chi tiết theo giờ là đủ; không dùng CDC (đắt hơn nhiều), ETL trực tiếp, nguồn có update thì nạp lại toàn bộ; transform bằng SQL ngay trong ClickHouse; chỉ dùng thành phần open source.
• Kiến trúc: mỗi nguồn dùng công cụ riêng để đẩy dữ liệu vào S3 làm tầng trung gian chuẩn (export native, BigQuery export, AWS AppFlow cho Salesforce, Fivetran cho Marketo, một app tự viết cho M3ter). Bảng fact lấy phần tăng theo giờ; bảng nhỏ hay có update thì thay toàn bộ mỗi giờ. Từ S3 nạp vào tầng RAW (cùng cấu trúc nguồn) bằng hàm bảng s3, rồi Airflow chạy transform sang tầng MART cho Superset. Kết quả ghi vào bảng staging có tên riêng mỗi lượt chạy DAG rồi mới chèn vào bảng đích, để dùng lại phần tăng mà không quét lại bảng đích.
• Idempotent: hầu hết bảng dùng ReplicatedReplacingMergeTree (cùng khoá thì giữ bản cuối), nên nạp lại một giờ bao nhiêu lần cũng được; khi đọc để transform dùng FINAL cho khỏi đếm đôi.
• Nhất quán giữa replica: ClickHouse mặc định eventual consistency, insert xong chưa chắc mọi replica có dữ liệu, bước ELT sau có thể đọc thiếu. Họ chạy mọi insert với insert_quorum=3 (cụm có 3 node), không dùng auto: khi một node đang nâng cấp, thà insert lỗi (TOO_FEW_LIVE_REPLICAS) rồi Airflow retry còn hơn để node vừa khởi động lại thiếu dữ liệu một lúc.
• DAG: cân nhắc nhiều DAG phụ thuộc nhau, nhưng dataset của Airflow không cho đổi tên giữa các lượt chạy và trigger thì thành "ác mộng phụ thuộc". Chọn: DAG riêng cho mỗi lượt nguồn → S3, và một DAG chính rất lớn làm mọi transform.
• Hạ tầng: Airflow và Superset trong Docker trên EC2, đồng bộ qua Redis, metadata trong RDS Postgres. Hai môi trường (Preprod, Prod) ở hai region, Preprod giữ nhất quán để chuyển sang được khi Prod hỏng; thay đổi đi qua PR vào Preprod rồi PR Preprod → Prod.
• Phân quyền ở tầng DB, không ở BI: Google Groups ánh xạ sang role trong ClickHouse qua một bảng quyền (DB, bảng, cột, filter dòng); script dựng lại user/role và cấp quyền WITH REPLACE OPTION để xoá mọi quyền cấp tay. Superset thay user DB bằng user đang đăng nhập (DB_CONNECTION_MUTATOR).
• GDPR: không cần UPDATE/DELETE; chèn bản mới đã che dữ liệu cá nhân, ReplacingMergeTree giữ bản cuối, rồi OPTIMIZE ... FINAL; phần tăng mới join với danh sách ID đã xoá để tự che.

Kết quả (2023)

• Đội 3 người (data engineer, product analyst, lead dành 30% thời gian). Hai service ClickHouse Cloud, mỗi service 3 node, khoảng 200 GB RAM; 8 máy EC2 + S3 khoảng 1.500 USD/tháng (không tính ClickHouse Cloud vì là nội bộ).
• Hơn 70 user hằng tháng, khoảng 40.000 query/ngày; khoảng 115 TB dữ liệu chưa nén trong 150 bảng, nén còn 13 TB.
• Giả định sai: hai tầng (RAW, MART) là không đủ. Metric phức tạp cần dữ liệu từ hơn 5 nguồn phải tạo phụ thuộc giữa các mart, có khi vòng; cần tầng trung gian (Detail Data Store) chứa thực thể nghiệp vụ. Còn định thêm dbt và sửa quy ước đặt tên (đã không đầu tư từ đầu, giờ khó hiểu).

Vì sao

• Batch theo giờ + nạp lại toàn bộ khi có update rẻ hơn CDC nhiều khi dữ liệu nhỏ và chỉ cần độ tươi theo giờ (Pipeline dữ liệu và analytics).
• Idempotent ở tầng lưu trữ (giữ bản cuối theo khoá) làm cho retry của bộ điều phối an toàn, nên chấp nhận được insert lỗi khi thiếu quorum (Failover, replica và dự phòng dữ liệu).

Bài học cho SWE

• Eventual consistency giữa replica là vấn đề thật cho pipeline nhiều bước "ghi xong rồi đọc ngay"; chọn quorum cố định (lỗi rõ ràng) hơn là quorum tự giảm (âm thầm thiếu dữ liệu).
• Thiết kế cho retry: ghi idempotent + DAG chạy lại được là điều kiện để mọi thứ khác đơn giản.
• Tầng trung gian chứa thực thể nghiệp vụ và quy ước đặt tên là những thứ bị bỏ qua lúc đầu và phải trả giá sau một năm.
• Phân quyền ở database, đồng bộ từ hệ nhóm sẵn có, xoá mọi quyền cấp tay mỗi lần chạy.

Liên quan

• Xác thực và phân quyền (authn/authz)
• ClickHouse 2024 · Doximity 2026 · Shopify 2022
• Pipeline dữ liệu và analytics · Failover, replica và dự phòng dữ liệu