1. Cú pháp cơ bản của ClickHouse cho truy vấn chuỗi thời gian
Để bắt đầu phân tích dữ liệu Time-Series, bạn cần hiểu cách ClickHouse xử lý cột thời gian. Khác với các database truyền thống, ClickHouse tối ưu hóa mạnh mẽ các truy vấn dựa trên phần tử đầu tiên của partition key, thường là timestamp.
Bước 1: Kiểm tra dữ liệu mẫu trong bảng metrics đã được tạo ở Phần 4. Giả sử bảng này có cấu trúc: timestamp (DateTime), host (String), cpu_usage (Float32).
Bước 2: Thực hiện truy vấn lấy 100 dòng dữ liệu mới nhất, sắp xếp theo thời gian giảm dần.
Tại sao: Việc sắp xếp ORDER BY timestamp DESC giúp tận dụng chỉ mục (index) của ClickHouse để truy xuất nhanh các dữ liệu mới nhất mà không cần quét toàn bộ bảng (full scan).
Kết quả mong đợi: Trả về 100 dòng dữ liệu với timestamp gần nhất nhất.
SELECT timestamp, host, cpu_usage
FROM metrics
ORDER BY timestamp DESC
LIMIT 100;
Bước 3: Lọc dữ liệu trong khoảng thời gian cụ thể (ví dụ: 1 giờ qua) để giảm tải tính toán.
Tại sao: ClickHouse sử dụng TTL và partitioning dựa trên thời gian. Việc chỉ định WHERE timestamp > now() - INTERVAL 1 HOUR cho phép engine bỏ qua các partition cũ không liên quan.
Kết quả mong đợi: Chỉ trả về dữ liệu trong 1 giờ gần nhất, tốc độ phản hồi nhanh hơn so với truy vấn không có filter thời gian.
SELECT host, avg(cpu_usage) as avg_cpu
FROM metrics
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY host
ORDER BY avg_cpu DESC;
Verify kết quả: So sánh số lượng dòng trả về với tổng số dòng trong bảng. Nếu dùng EXPLAIN, bạn sẽ thấy Filter được áp dụng trước Aggregation.
2. Sử dụng hàm timeSeries và window function để tính trung bình
Khi dữ liệu thô (raw data) có tần suất cao (ví dụ: mỗi giây một điểm), việc vẽ biểu đồ trực tiếp sẽ gây quá tải cho frontend. Chúng ta cần sử dụng timeSeries và toStartOfInterval để gom nhóm (bucketing) dữ liệu.
Bước 1: Gom nhóm dữ liệu vào các khoảng thời gian 5 phút và tính trung bình.
Tại sao: Hàm toStartOfInterval làm tròn timestamp về mốc thời gian gần nhất của chu kỳ (ví dụ: 14:00, 14:05). Điều này tạo ra các "bin" thời gian đều nhau cho biểu đồ.
Kết quả mong đợi: Một bảng với cột time_bucket và giá trị avg_cpu tương ứng cho mỗi khoảng 5 phút.
SELECT
toStartOfInterval(timestamp, toIntervalMinute(5)) as time_bucket,
avg(cpu_usage) as avg_cpu
FROM metrics
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY time_bucket
ORDER BY time_bucket;
Bước 2: Sử dụng hàm timeSeries để tự động lấp đầy các khoảng trống (gap filling) nếu không có dữ liệu tại một số mốc thời gian.
Tại sao: Trong môi trường thực tế, dữ liệu có thể bị mất hoặc trễ. Hàm timeSeries đảm bảo biểu đồ không bị đứt gãy bằng cách chèn giá trị mặc định (thường là 0 hoặc null) vào các khoảng trống.
Kết quả mong đợi: Trả về một chuỗi thời gian liên tục, ngay cả khi server không gửi metric trong một vài phút.
SELECT
timeSeries(now() - INTERVAL 1 HOUR, now(), toIntervalMinute(5),
(SELECT avg(cpu_usage) FROM metrics WHERE timestamp > now() - INTERVAL 1 HOUR)
) as ts;
-- Hoặc cách tiếp cận phổ biến hơn với JOIN để lấp đầy:
SELECT
ts.time_bucket,
ifNull(m.avg_cpu, 0) as avg_cpu
FROM
timeSeries(now() - INTERVAL 1 HOUR, now(), toIntervalMinute(5)) as ts
LEFT JOIN
(
SELECT
toStartOfInterval(timestamp, toIntervalMinute(5)) as time_bucket,
avg(cpu_usage) as avg_cpu
FROM metrics
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY time_bucket
) as m ON ts.time_bucket = m.time_bucket
ORDER BY ts.time_bucket;
Bước 3: Tính toán giá trị trung bình trượt (Moving Average) sử dụng window function avg() OVER ().
Tại sao: Giúp làm mượt (smooth) các biến động đột ngột (spikes) để dễ dàng quan sát xu hướng dài hạn hơn.
Kết quả mong đợi: Thêm một cột moving_avg tính trung bình của 5 khoảng thời gian liên tiếp.
SELECT
toStartOfInterval(timestamp, toIntervalMinute(5)) as time_bucket,
avg(cpu_usage) as avg_cpu,
avg(avg_cpu) OVER (ORDER BY time_bucket ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as moving_avg_5
FROM metrics
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY time_bucket
ORDER BY time_bucket;
Verify kết quả: Kiểm tra xem cột moving_avg_5 có giá trị bằng avg_cpu ở dòng đầu tiên không, và giá trị tăng dần ổn định so với cột gốc.
3. Tối ưu hóa truy vấn với pre-aggregation (AggregatingMergeTree)
Truy vấn GROUP BY trên bảng chứa hàng triệu dòng raw data sẽ tốn nhiều CPU và thời gian. Giải pháp là tạo một bảng AggregatingMergeTree để lưu trữ dữ liệu đã được gom nhóm sẵn (pre-aggregated).
Bước 1: Tạo bảng mới metrics_5m sử dụng engine AggregatingMergeTree để lưu dữ liệu gom theo 5 phút.
Tại sao: Engine này tự động hợp nhất (merge) các phần tử trong cùng một bucket khi dữ liệu mới được viết vào, giảm kích thước lưu trữ và tăng tốc độ đọc.
Kết quả mong đợi: Bảng mới được tạo với các cột trạng thái của hàm aggregate (state).
CREATE TABLE IF NOT EXISTS metrics_5m
ON CLUSTER default
(
time_bucket DateTime,
host String,
cpu_avg AggregateFunction(avg, Float32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(time_bucket)
ORDER BY (time_bucket, host)
SETTINGS granule_size_bytes = 8192;
Bước 2: Viết truy vấn INSERT INTO ... SELECT ... để nạp dữ liệu từ bảng raw vào bảng pre-aggregation.
Tại sao: Trong môi trường sản xuất, bước này thường được thực hiện bởi một job batch hoặc một luồng Vector riêng biệt. Ở đây chúng ta làm thủ công để minh họa.
Kết quả mong đợi: Dữ liệu từ bảng metrics được chuyển sang metrics_5m dưới dạng state của hàm average.
INSERT INTO metrics_5m
SELECT
toStartOfInterval(timestamp, toIntervalMinute(5)) as time_bucket,
host,
avgState(cpu_usage) as cpu_avg
FROM metrics
WHERE timestamp > now() - INTERVAL 24 HOUR
GROUP BY time_bucket, host;
Bước 3: Truy vấn bảng pre-aggregation để lấy dữ liệu phân tích.
Tại sao: Thay vì tính avg() trên hàng triệu dòng, ClickHouse chỉ cần gọi avgMerge() trên vài nghìn dòng state, cực kỳ nhanh.
Kết quả mong đợi: Thời gian phản hồi giảm đáng kể (thường từ vài giây xuống vài chục mili giây).
SELECT
time_bucket,
host,
avgMerge(cpu_avg) as avg_cpu
FROM metrics_5m
WHERE time_bucket > now() - INTERVAL 1 HOUR
GROUP BY time_bucket, host
ORDER BY time_bucket;
Verify kết quả: So sánh kết quả của câu lệnh SELECT avgMerge... với câu lệnh SELECT avg... trên bảng gốc. Giá trị phải xấp xỉ nhau (sai số nhỏ do làm tròn).
4. Xây dựng dashboard đơn giản bằng ClickHouse HTTP API và Grafana
Chúng ta sẽ xây dựng một dashboard cơ bản. Cách 1 là gọi trực tiếp HTTP API của ClickHouse để lấy JSON, Cách 2 là cấu hình datasource cho Grafana.
Bước 1: Cấu hình ClickHouse để hỗ trợ trả về định dạng JSON cho HTTP API.
Tại sao: Mặc định ClickHouse trả về tab-separated. Để frontend hoặc Grafana hiểu được, cần cấu hình output_format_json_with_column_names_and_types hoặc chỉ định trong query.
Kết quả mong đợi: Query trả về JSON có cấu trúc rõ ràng.
curl -s 'http://localhost:8123?query=SELECT+time_bucket,+avgMerge(cpu_avg)+as+avg_cpu+FROM+metrics_5m+WHERE+time_bucket+%3E+now()+-+INTERVAL+1+HOUR+GROUP+BY+time_bucket+ORDER+BY+time_bucket+FORMAT+JSON'
Bước 2: Cài đặt và cấu hình ClickHouse Datasource trong Grafana.
Tại sao: Grafana có plugin chính thức cho ClickHouse, cho phép sử dụng giao diện GUI để vẽ biểu đồ thay vì code tay.
Kết quả mong đợi: Datasource "ClickHouse" xuất hiện trong danh sách và có thể kết nối thành công.
Thực hiện:
- Truy cập Grafana UI -> Configuration -> Data Sources -> Add data source.
- Chọn "ClickHouse".
- Điền thông tin: URL:
http://clickhouse-server:8123 (hoặc IP của container).
- Database:
default.
- Click "Save & Test".
Bước 3: Tạo Panel mới với query đã tối ưu hóa.
Tại sao: Sử dụng query đã viết ở phần 2 hoặc 3 để vẽ biểu đồ Time-Series.
Kết quả mong đợi: Biểu đồ hiển thị đường cong CPU usage theo thời gian.
Query trong Grafana (Editor):
SELECT
toStartOfInterval(timestamp, toIntervalMinute(5)) as time_bucket,
avg(cpu_usage) as value
FROM metrics
WHERE timestamp > $__timeFilter(timestamp)
AND host = '$host'
GROUP BY time_bucket
ORDER BY time_bucket;
Bước 4: Cấu hình biến (Variable) để lọc theo Host.
Tại sao: Cho phép người dùng chọn server cụ thể trên dropdown của dashboard.
Kết quả mong đợi: Khi chọn Host khác, biểu đồ tự động cập nhật.
Query Variable (trong phần Variables của Dashboard):
SELECT distinct host FROM metrics LIMIT 100;
Verify kết quả: Mở Dashboard trên trình duyệt, chọn một Host từ dropdown và quan sát biểu đồ thay đổi tương ứng. Kiểm tra xem dữ liệu có khớp với kết quả query trực tiếp trên terminal không.
Điều hướng series:
Mục lục: Series: Xây dựng nền tảng Data Streaming Time-Series với Kafka, ClickHouse và Vector
« Phần 5: Tích hợp luồng dữ liệu: Từ Vector đến ClickHouse qua Kafka
Phần 7: Xử lý dữ liệu trễ (Late Data) và đảm bảo tính toàn vẹn »