HTTP tracker 5: Sử dụng PostgreSQL xây dựng báo cáo

Xin chào các bạn đã quay lại chuỗi bài viết HTTP tracker. Ở phần này chúng ta sẽ tiếp tục phần bỏ dở từ bài trước, thực hành việc sử dụng PostgreSQL xây dựng báo cáo số liệu.

Đặt vấn đề

Trước hết, chúng ta sẽ cần ôn lại bài từ phần trước.

Ôn bài

Raw table sẽ chính là dữ liệu vào từ HTTP, nên chúng ta sẽ lưu trữ như thế, không cần phải thiết kế lại. Riêng với Fact & Dimension tables, chúng ta sẽ thiết kế như sau:

Như vậy, chúng ta sẽ cần 03 bảng Dimension cho device, browser và message, để đơn giản chúng ta sẽ chỉ sử dụng một vài giá trị sẵn cho mỗi bảng.

Mở rộng vấn đề

Chúng ta đã thiết các model cần thiết (data modeling). Tuy nhiên, dữ liệu chúng ta đẩy vào mới chỉ là dữ liệu raw (đẩy vào bảng raw_data), vẫn cần một số thao tác nữa để PostgreSQL tự động update dữ liệu sang bảng fact giùm chúng ta.

Sẽ bao gồm 03 công việc như sau:

• Sử dụng plugin pg_cron của CitusData.
• Tạo procedure trên PostgreSQL.
• Cài đặt lịch hẹn tự động chạy procedure bằng crontab.

Thực hành

Cài đặt pg_cron

Plugin pg_cron của CitusData thường được sử dụng rộng rãi đối với người dùng PostgreSQL, nhưng rất tiếc plugin này vẫn chưa cài đặt sẵn trong phiên bản docker. Như vậy chúng ta cần phải sửa đổi lại docker image một chút để có thể sử dụng.

Bạn truy cập vào container của Postgresql:

docker exec -it my-postgres-container bash

Chạy 03 câu lệnh sau

apt-get update
apt-get install -y postgresql-<version>-cron
apt-get install -y nano

Sau đó bạn có thể sử dụng câu lệnh: nano /var/lib/postgresql/data/postgresql.conf để thêm vào 03 đoạn config bên dưới.

//edit file: nano /var/lib/postgresql/data/postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
cron.timezone = 'ICT'

Sau đó bạn restart lại Docker, lúc này thư viện pg_cron đã được cài đặt trên Postgresql rồi, tuy nhiên vẫn cần bạn thêm đoạn lệnh sau trên PostgreSQL để kích hoạt thư viện:

CREATE EXTENSION pg_cron;

Viết code

Procedure trong PostgreSQL là một function nằm trong cơ sở dữ liệu, đã được compiled sẵn để có thể gọi và tái sử dụng nhiều lần. Nhưng trước hết chúng ta sẽ thử viết SQL để tạo dữ liệu thống kê từ dữ liệu raw (aggregation data).

select date_trunc('hour', event_time) as time,
	dd.id as device ,
	bd.id as browser ,
	(case when lower(rd.result) = 'success' then true else false end) as success,
	md.id as message,
	sum(rd.duration) as total_duration,
	sum(1) as event_count
	from raw_data rd
	left join message_dim md on lower(md.message) = lower(rd.message) 
	left join device_dim dd on lower(dd.device_name) = lower(rd.device)
	left join browser_dim bd on lower(bd.browser_name) = lower(rd.browser)
	group by 1, 2, 3, 4, 5;

Bạn chạy thử dữ liệu thì kết quả sẽ ra tương tự như thế này:

Đánh giá kết quả bạn thấy khá ổn rồi. Giải thích một chút thì đoạn SQL trên sẽ lấy ra các id của các bảng dimensions tương ứng với cột trong bảng raw, và sum dữ liệu lại theo giờ ở các cột duration và event_count.

Vậy bây giờ chúng ta sẽ tạo procedure và đẩy kết quả vào bảng fact.

Bạn copy đoạn SQL dưới đây và cho chạy trong database:

CREATE OR REPLACE PROCEDURE public.proc_build_fact_data(_from timestamp, _to timestamp DEFAULT current_timestamp)
 LANGUAGE plpgsql
AS $procedure$BEGIN
delete from public.fact_tracking_data where "time" between _from and _to;
insert into public.fact_tracking_data 
		(select date_trunc('hour', event_time) as time,
			dd.id as device ,
			bd.id as browser ,
			(case when lower(rd.result) = 'success' then true else false end) as success,
			md.id as message,
			sum(rd.duration) as total_duration,
			sum(1) as event_count
			from raw_data rd
			left join message_dim md on lower(md.message) = lower(rd.message) 
			left join device_dim dd on lower(dd.device_name) = lower(rd.device)
			left join browser_dim bd on lower(bd.browser_name) = lower(rd.browser)
			where rd.event_time between date_trunc('hour', _from) and date_trunc('hour', _to + interval '1' hour) 
			group by 1, 2, 3, 4, 5);
END$procedure$
;

Giải thích một chút như sau, đoạn SQL trên sẽ tạo procedure với đầu vào là _from và _to là 02 biến thời gian (biến _to nếu không truyền vào thì mặc định sẽ là hiện tại). Procedure sẽ tiến hành xoá dữ liệu trên bảng fact từ 02 biến thời gian và đẩy dữ liệu mới từ đoạn SQL chúng ta đã thử ở trên vào bảng fact. Chúng ta cần phải xoá vì nếu nếu không, chúng ta chạy lại thì dữ liệu sẽ tăng lên và không còn chính xác.

Chúng ta chạy thử đoạn sau nhé:

call public.proc_build_fact_data('2023-01-01 00:00:00'); 

Truy xuất dữ liệu từ bảng fact_tracking_data, chúng ta sẽ có dữ liệu tương tự như sau.

So sánh dữ liệu từ đoạn chạy thử và dữ liệu từ bảng Fact, cả hai bên đều bằng nhau. Vậy chúng ta đã xong phần xây dữ liệu bảng Fact từ bảng raw và dimensions.

Note: Vì sao phải chuyển đổi các cột device, browser, message sang id? Vì nếu lưu trữ theo kiểu text, chúng ta sẽ tốn nhiều dung lượng, và đây là bảng tổng hợp dữ liệu từ bảng raw, việc thống kê lúc này sẽ chỉ theo nhu cầu cột. Ví dụ tìm tổng số đăng ký trên mobile (sum(event_count) where device = 1) hoặc trung bình thời gian đăng ký trên Chrome (sum(total_duration)/sum(event_count) where browser = 1). Nếu có nhu cầu search trên cột browser hoặc message, chúng ta chỉ cần tìm kiếm trên bảng Dimension tương ứng và join dữ liệu lại là được.

Chạy tự động

Sau khi chúng ta đã viết đoạn procedure để xây dựng dữ liệu lên bảng fact. Chúng ta sẽ tiến hành cho chạy tự động ngay trên PostgreSQL luôn. Vì dữ liệu chúng ta là hourly nên hãy setup dữ liệu chạy vào lúc phút 05, đồng thời chúng ta sẽ lấy từ giờ hiện tại lùi về tầm 2-3 giờ để đảm bảo dữ liệu giờ trước đó luôn luôn đủ.

SELECT cron.schedule('5 * * * *', $$call public.proc_build_fact_data(current_timestamp - interval '3' hour)$$);

Tóm tắt

Qua bài chúng ta đã học thêm về cách xây dựng dữ liệu bảng Fact dựa trên bảng Raw data và bảng Dimensions. Chúng ta cũng đã học thêm về cách sử dụng plugin pg_cron để tự động hoá việc xây dựng dữ liệu bảng Fact.

Ngoài ra, đây cũng là một trong những cách phổ biến nhất mà các team data hay sử dụng để xây dựng dữ liệu từ các bảng raw. Chúng ta sẽ chấp nhận và giữ nguyên tất cả dữ liệu được gửi về đúng, sau đó mới tiến hành xử lý trong hệ thống của chúng ta.