Sử dụng hàm Window và Aggregate Function cho dữ liệu thời gian thực
Trong môi trường dữ liệu Real-time, việc tính toán chỉ số tổng hợp (Aggregation) theo thời gian thực (Real-time aggregation) là cốt lõi. Chúng ta sẽ sử dụng các hàm Window Function để tính toán xu hướng mà không cần nhóm lại toàn bộ bảng (GROUP BY), giúp giảm áp lực tính toán.
Đầu tiên, tạo một bảng dữ liệu mẫu chứa các event log với timestamp và giá trị metric để thực hành.
Bước 1: Tạo bảng sự kiện (Events table) với engine MergeTree, phân vùng theo ngày.
clickhouse-client --query "
CREATE TABLE IF NOT EXISTS real_time_events (
event_time DateTime,
event_id UInt64,
user_id UInt32,
value Float64,
status String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, event_id)
SETTINGS index_granularity = 8192;
"
Kết quả: Bảng `real_time_events` được tạo sẵn sàng để nhận dữ liệu với index granular tối ưu cho truy vấn thời gian.
Bước 2: Chèn dữ liệu mẫu giả lập dòng chảy dữ liệu (stream) trong 1 giờ qua.
clickhouse-client --query "
INSERT INTO real_time_events
SELECT
now() - toIntervalMinute(number) as event_time,
number as event_id,
(number % 100) + 1 as user_id,
(number * 1.5 + sin(number)) as value,
if(number % 10 == 0, 'ERROR', 'OK') as status
FROM numbers(100000);
"
Kết quả: 100.000 dòng dữ liệu được chèn vào bảng, mô phỏng lưu lượng truy cập cao.
Bước 3: Sử dụng hàm Window Function `runningAverage()` và `argMax()` để tính trung bình trượt và lấy giá trị mới nhất theo user_id.
clickhouse-client --query "
SELECT
event_time,
user_id,
runningAverage(value) OVER (ORDER BY event_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as avg_value_5min,
argMax(value, event_time) as latest_value,
countIf(status = 'ERROR') OVER (ORDER BY event_time ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) as error_count_10min
FROM real_time_events
WHERE event_time >= now() - toIntervalHour(1)
GROUP BY event_time, user_id
ORDER BY event_time DESC
LIMIT 20;
"
Kết quả: Truy vấn trả về bảng dữ liệu đã được tính toán sẵn các chỉ số thống kê theo cửa sổ thời gian (window) mà không cần nhóm toàn bộ dữ liệu lịch sử, giúp truy vấn nhanh hơn khi dữ liệu tăng.
Bước 4: Verify kết quả bằng cách kiểm tra số lượng dòng và giá trị tính toán.
clickhouse-client --query "SELECT count() FROM real_time_events;"
Kết quả mong đợi: Trả về số 100000, xác nhận dữ liệu đã sẵn sàng.
Tối ưu hóa câu lệnh SELECT với Materialized View
Trong môi trường Real-time, việc chạy các câu lệnh `GROUP BY` phức tạp trực tiếp trên bảng lớn (Raw Data) sẽ gây nghẽn CPU. Giải pháp là sử dụng `Materialized View` (MV) để tự động tính toán và lưu trữ kết quả tổng hợp ngay khi dữ liệu được chèn vào.
Bước 1: Tạo bảng đích (Target Table) để lưu trữ dữ liệu đã được tổng hợp theo phút.
clickhouse-client --query "
CREATE TABLE IF NOT EXISTS real_time_metrics_minute (
time_slot DateTime,
user_id UInt32,
total_value Float64,
avg_value Float64,
error_count UInt64,
event_count UInt64
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(time_slot)
ORDER BY (time_slot, user_id)
SETTINGS index_granularity = 8192;
"
Kết quả: Bảng `real_time_metrics_minute` được tạo với engine `SummingMergeTree`, tự động gộp các dòng trùng lặp khi compact data.
Bước 2: Tạo Materialized View để tự động chuyển đổi dữ liệu từ bảng Raw sang bảng Metrics.
clickhouse-client --query "
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_real_time_metrics_minute
TO real_time_metrics_minute
AS SELECT
toStartOfMinute(event_time) as time_slot,
user_id,
sum(value) as total_value,
avg(value) as avg_value,
countIf(status = 'ERROR') as error_count,
count() as event_count
FROM real_time_events
GROUP BY time_slot, user_id;
"
Kết quả: MV được tạo. Mọi lệnh INSERT vào `real_time_events` sẽ tự động kích hoạt tính toán và INSERT vào `real_time_metrics_minute`.
Bước 3: Chèn thêm dữ liệu mới để test cơ chế tự động của MV.
clickhouse-client --query "
INSERT INTO real_time_events
SELECT
now() - toIntervalSecond(number) as event_time,
number as event_id,
1 as user_id,
100.0 as value,
'OK' as status
FROM numbers(1000);
"
Kết quả: Dữ liệu mới được chèn vào bảng nguồn.
Bước 4: Query trực tiếp vào bảng Metrics đã được tổng hợp sẵn để xem kết quả.
clickhouse-client --query "
SELECT time_slot, user_id, total_value, avg_value, error_count, event_count
FROM real_time_metrics_minute
WHERE time_slot >= now() - toIntervalMinute(10)
ORDER BY time_slot DESC;
"
Kết quả mong đợi: Trả về dữ liệu đã được gom nhóm theo phút, tổng giá trị và số lượng sự kiện đã được tính toán sẵn. Tốc độ truy vấn này sẽ nhanh hơn hàng chục lần so với query GROUP BY trực tiếp trên bảng nguồn.
Bước 5: Verify hiệu năng bằng cách so sánh thời gian thực thi (nếu cần) hoặc kiểm tra số lượng dòng.
clickhouse-client --query "SELECT count() FROM real_time_metrics_minute;"
Kết quả: Số dòng ít hơn nhiều so với bảng nguồn, chứng tỏ dữ liệu đã được tổng hợp hiệu quả.
Cấu hình Cache Query và Dictionary để giảm tải CPU
Để giảm tải CPU cho các truy vấn lặp lại (repeated queries) và các thao tác join với bảng từ điển (dictionary lookup), chúng ta cần cấu hình Cache và Dictionary trong ClickHouse.
Bước 1: Cấu hình Query Cache trong file `users.xml` (hoặc `users.d/` tùy phiên bản, ở đây dùng đường dẫn mặc định).
Sửa file `/etc/clickhouse-server/users.xml`. Lưu ý: Backup file cũ trước khi sửa.
cp /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.xml.bak
Kết quả: File cấu hình đã được sao lưu.
Thêm phần cấu hình `query_cache` vào phần `` trong file `/etc/clickhouse-server/users.xml`. Dán nội dung sau vào đúng vị trí thẻ ``:
<profiles>
<default>
... các cấu hình cũ ...
<query_cache_size>1000000000</query_cache_size>
<use_query_cache>1</use_query_cache>
<query_cache_key>
<query></query>
<database></database>
</query_cache_key>
</default>
</profiles>
Kết quả: ClickHouse sẽ lưu kết quả của 1GB truy vấn gần nhất vào bộ nhớ đệm RAM. Khi cùng một câu lệnh được chạy lại, hệ thống sẽ trả kết quả từ cache ngay lập tức.
Bước 2: Khởi động lại ClickHouse Server để áp dụng cấu hình Cache.
sudo systemctl restart clickhouse-server
Kết quả: Server khởi động lại thành công.
Bước 3: Tạo Dictionary để thay thế cho các phép JOIN tốn kém CPU. Giả sử ta có bảng danh mục trạng thái (StatusDict).
clickhouse-client --query "
CREATE DICTIONARY IF NOT EXISTS status_dictionary (
status_id UInt32,
status_name String,
status_desc String
)
PRIMARY KEY status_id
SOURCE(CLICKHOUSE(
HOST 'localhost'
PORT 9000
DB 'default'
TABLE 'status_source'
USER 'default'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 3600);
"
Kết quả: Dictionary được tạo. Nó lưu trữ dữ liệu trong RAM, truy xuất O(1), nhanh hơn JOIN hàng nghìn lần.
Bước 4: Tạo bảng nguồn cho Dictionary (bắt buộc để Dictionary hoạt động).
clickhouse-client --query "
CREATE TABLE IF NOT EXISTS status_source (
status_id UInt32,
status_name String,
status_desc String
) ENGINE = Memory;
INSERT INTO status_source VALUES (1, 'OK', 'Normal Operation'), (2, 'ERROR', 'System Error');
"
Kết quả: Dữ liệu từ điển đã sẵn sàng để Dictionary load vào RAM.
Bước 5: Sử dụng hàm `dictGet` trong truy vấn thay vì JOIN để lấy thông tin mô tả.
clickhouse-client --query "
SELECT
event_time,
user_id,
status,
dictGet('status_dictionary', 'status_name', toUInt32(if(status='OK', 1, 2))) as status_desc
FROM real_time_events
WHERE event_time >= now() - toIntervalMinute(5)
LIMIT 10;
"
Kết quả mong đợi: Truy vấn trả về dữ liệu kèm mô tả trạng thái, thực thi cực nhanh vì không cần quét bảng thứ 2 (JOIN), chỉ tra cứu trong RAM.
Bước 6: Verify hiệu quả của Query Cache bằng cách chạy cùng một query 2 lần.
clickhouse-client --query "SELECT count() FROM real_time_events WHERE event_time >= now() - toIntervalMinute(5);"
clickhouse-client --query "SELECT count() FROM real_time_events WHERE event_time >= now() - toIntervalMinute(5);"
system query_log
Kết quả: Chạy lệnh `system query_log` sẽ thấy trường `is_query_cache_hit` chuyển thành `1` ở lần chạy thứ 2, chứng tỏ Cache hoạt động.
Điều hướng series:
Mục lục: Series: Triển khai Database Real-time với ClickHouse trên Ubuntu 24.04
« Phần 5: Triển khai thu thập dữ liệu Real-time từ nguồn bên ngoài
Phần 7: Giám sát, Backup và xử lý sự cố (Troubleshooting) »