BI mã nguồn mở 5 — Superset: SQL Lab & trực quan hoá

Từ dataset tới khai thác SQL trực tiếp

Ở bài căn bản về Superset ta đã đi qua luồng dataset-centric: kết nối database → tạo dataset → dựng chart → ghép dashboard. Bài này đào sâu hai kỹ năng cốt lõi mà một analyst dùng hằng ngày trên Superset: SQL Lab — môi trường viết và chạy SQL thô, và tư duy trực quan hoá — chọn đúng loại chart cho câu hỏi. Đây là phần khiến Superset "kỹ thuật hơn" Metabase, cũng là phần cho ra giá trị cao nhất khi phân tích giao dịch và rủi ro tại một ngân hàng như NCB.

Toàn bộ luồng có thể tóm gọn: từ SQL Lab bạn khám phá dữ liệu, chốt được một câu truy vấn, lưu nó thành virtual dataset, rồi từ dataset đó dựng chart và ghép lên dashboard.

SQL Lab — IDE SQL trong trình duyệt

SQL Lab là một trình soạn thảo SQL đầy đủ chạy ngay trong Superset. Nó cho analyst quyền truy cập trực tiếp warehouse (dưới danh nghĩa tài khoản kết nối read-only) mà không cần công cụ ngoài như DBeaver. Các thành phần chính:

• Database & schema selector: chọn kết nối và schema đang làm việc; panel trái liệt kê bảng, cột và kiểu dữ liệu để tra cứu nhanh.
• Editor: vùng viết SQL có tô màu cú pháp, autocomplete tên bảng/cột, chạy nhiều tab song song. Có thể chạy cả câu hoặc chạy phần bôi đen (highlighted selection).
• Results pane: kết quả hiển thị dạng bảng, cho filter/sort cột ngay trên lưới, xem lược đồ, và export CSV.
• Query history: mọi truy vấn đã chạy được lưu lại theo thời gian, trạng thái, thời lượng — tiện để tìm lại câu cũ hoặc audit ai chạy gì.
• LIMIT tự động: SQL Lab áp một LIMIT mặc định (ví dụ 1.000 dòng) để tránh kéo về toàn bộ bảng khổng lồ; bạn chỉnh được ngưỡng này.

Điểm mấu chốt về vận hành: SQL Lab chạy truy vấn trực tiếp xuống warehouse. Truy vấn nặng có thể được đẩy bất đồng bộ qua Celery (nếu bật async), để giao diện không treo trong khi câu SELECT quét hàng chục triệu giao dịch.

Explore từ SQL: chuỗi giao dịch theo ngày

Câu hỏi đầu tiên một analyst dòng tiền hay đặt: khối lượng giao dịch mỗi ngày biến động thế nào? Trong SQL Lab, ta gộp transactions theo ngày bằng date_trunc. Câu này chạy trực tiếp trên sandbox PostgreSQL của app:

-- ▶ Chạy được
SELECT date_trunc('day', created_at)::date       AS ngay,
       COUNT(*)                                   AS so_giao_dich,
       SUM(amount)                                AS tong_tien,
       ROUND(AVG(amount)::numeric, 2)             AS tien_tb
FROM transactions
GROUP BY date_trunc('day', created_at)
ORDER BY ngay;

Lưu ý kỹ thuật: AVG(amount) trả về kiểu double precision, nên khi bọc trong ROUND(..., 2) phải ép ::numeric (PostgreSQL không có ROUND(double, int)). Đây là lỗi cực kỳ hay gặp khi viết SQL Lab trên Postgres. Kết quả câu này là một chuỗi thời gian (time-series) hoàn hảo để nuôi một line chart hoặc area chart.

Lưu truy vấn thành virtual dataset

Khi một câu SQL trong SQL Lab đã "đủ tốt" để nhiều chart dùng chung, bấm "Save" → "Save dataset". Superset biến câu SELECT thành một virtual dataset: nó bọc SQL của bạn trong một subquery mỗi lần chart truy vấn, đại loại SELECT ... FROM (câu SQL của bạn) AS virtual_table. Từ đó dataset này xuất hiện trong danh sách như một "bảng logic" — bạn khai báo metric, calculated column lên nó và dựng chart bình thường.

Virtual dataset vs physical dataset

Nguyên tắc thực chiến: virtual dataset tốt cho tạo mẫu (prototype) nhanh. Khi một virtual dataset ổn định và bị truy vấn nặng lặp lại, hãy "hạ tầng hoá" nó — chuyển logic thành một VIEW hoặc materialized view trong PostgreSQL, rồi trỏ Superset vào đó dưới dạng physical dataset. Cách này đẩy tối ưu về đúng nơi database làm tốt nhất (index, refresh có kiểm soát).

Jinja templating & macro trong SQL

Superset nhúng Jinja — một template engine của Python — vào SQL Lab và virtual dataset. Điều này cho phép viết SQL động: chèn tham số, giá trị từ filter dashboard, hoặc lặp sinh mệnh đề. Cú pháp: {{ ... }} chèn biểu thức, {% ... %} điều khiển luồng.

Các macro thường dùng:

• {{ from_dttm }} / {{ to_dttm }} — mốc đầu/cuối của time range filter trên dashboard, cho phép SQL của virtual dataset tự nhận khoảng thời gian người dùng chọn.
• {{ current_username() }}, {{ current_user_id() }} — dùng cho row-level security động (lọc theo người đang xem).
• {{ filter_values('cot') }} — lấy danh sách giá trị đang được chọn ở một native filter, để nhúng vào WHERE ... IN (...).
• {% if ... %}...{% endif %} — bật/tắt một mệnh đề tuỳ điều kiện.

Ví dụ minh hoạ (Jinja, không đánh dấu chạy được vì {{...}} không phải SQL thuần Postgres):

SELECT date_trunc('day', created_at)::date AS ngay, SUM(amount) AS tong_tien
FROM transactions
WHERE created_at BETWEEN '{{ from_dttm }}' AND '{{ to_dttm }}'
  {% if filter_values('currency') %}
  AND account_id IN (SELECT id FROM accounts
                     WHERE currency IN {{ "('" ~ filter_values('currency') | join("','") ~ "')" }})
  {% endif %}
GROUP BY 1 ORDER BY 1;

Lưu ý an toàn: Jinja chèn chuỗi trước khi SQL chạy, nên nếu nhét thẳng giá trị người dùng vào mà không kiểm soát sẽ có nguy cơ SQL injection. Ưu tiên các macro chính thức (filter_values, {{ from_dttm }}) đã được xử lý, và chỉ bật Jinja templating cho người dùng đáng tin. Chi tiết an toàn ở osbi-07-governance-security.

Metric & calculated column trong dataset

Sau khi có dataset (physical hay virtual), lớp semantic của Superset nằm ở phần Edit dataset. Đây là nơi bạn định nghĩa một lần, dùng lại mọi chart — bàn kỹ ở osbi-06-semantic-metrics, còn ở đây ta xem hai công cụ chính.

Metric (phép tổng hợp): một biểu thức SQL có aggregate, tính trên toàn nhóm mà chart gộp. Hai kiểu:

• Aggregate đơn giản: chọn cột + hàm (SUM/COUNT/AVG/MIN/MAX) qua giao diện. Ví dụ tong_so_du = SUM(balance).
• Custom SQL metric: viết thẳng biểu thức, cho phép công thức phức tạp. Ví dụ tỷ lệ giao dịch lớn: AVG(CASE WHEN amount > 100000000 THEN 1.0 ELSE 0 END), hay số khách hoạt động COUNT(DISTINCT account_id).

Calculated column (cột tính toán): biểu thức SQL theo từng dòng (row-level), không có aggregate — dùng làm dimension để nhóm/lọc. Ví dụ:

CASE WHEN balance >= 1000000000 THEN 'VIP'
     WHEN balance >= 100000000  THEN 'trung bình'
     ELSE 'phổ thông' END

Cột này biến balance liên tục thành một phân khúc rời rạc để đưa vào trục hoặc filter. Phân biệt cốt lõi: metric có aggregate (tính trên nhóm), calculated column không aggregate (tính trên dòng, dùng như dimension).

Tư duy chọn chart đúng cho câu hỏi

Sai lầm phổ biến nhất khi làm BI không phải là chọn sai màu, mà là chọn sai loại chart cho câu hỏi. Mỗi loại chart trả lời một dạng câu hỏi khác nhau. Bảng dưới định hướng theo câu hỏi phân tích thay vì theo tên chart:

Vài nguyên tắc thực chiến quan trọng:

• Time-series line/area là mặc định cho mọi câu hỏi "theo thời gian". Area chỉ dùng khi muốn nhấn độ lớn tích luỹ hoặc xếp chồng (stacked) thành phần; nếu cần so sánh nhiều đường thì line rõ hơn.
• Big Number with Trendline cực hợp cho hàng KPI đầu dashboard: một con số lớn (tổng huy động, số giao dịch) kèm một đường nhỏ (sparkline) cho thấy xu hướng gần đây — vừa "chốt số" vừa cho ngữ cảnh.
• Pie/Donut nên hạn chế. Mắt người kém so sánh diện tích cung tròn; pie chỉ ổn khi có 2–5 hạng mục và bạn thực sự muốn nói về tỷ trọng của tổng. Nhiều hơn 5 hạng mục → dùng bar (dễ đọc thứ hạng hơn).
• Table & Pivot Table thường bị coi nhẹ nhưng là chart được yêu cầu nhiều nhất trong ngân hàng — cán bộ nghiệp vụ cần con số chính xác để đối soát, không phải hình đẹp. Pivot table cho phép gộp hai chiều (ví dụ loại nghiệp vụ × tháng) rất tự nhiên.
• Heatmap, box plot thuộc nhóm phân tích: heatmap tìm mẫu theo hai chiều (giao dịch dồn vào khung giờ nào), box plot lộ ngoại lai và độ phân tán — hữu ích cho phân tích rủi ro. (Về khái niệm ngoại lai/phân bố, xem thêm bi-04-metrics-kpi.)

Ví dụ chart phân bố: số dư theo loại tiền

Một chart phân tích rủi ro điển hình: số dư phân bố ra sao trong mỗi loại tiền? Ta tính trung vị và các phân vị (percentile) — nền cho một box plot hoặc bảng phân bố. Câu này chạy trực tiếp trên sandbox:

-- ▶ Chạy được
SELECT currency,
       COUNT(*)                                                    AS so_tai_khoan,
       ROUND(AVG(balance)::numeric, 2)                             AS so_du_tb,
       ROUND(PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY balance)::numeric, 2) AS trung_vi,
       ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY balance)::numeric, 2) AS p95,
       ROUND(STDDEV(balance)::numeric, 2)                          AS do_lech_chuan
FROM accounts
GROUP BY currency
ORDER BY so_tai_khoan DESC;

Ở đây PERCENTILE_CONT (một ordered-set aggregate) và STDDEV đều trả double precision, nên lại ép ::numeric trước khi ROUND. So sánh so_du_tb (trung bình) với trung_vi (median) lộ ngay độ lệch: nếu trung bình cao hơn hẳn median, phân bố lệch phải — vài tài khoản số dư khổng lồ kéo trung bình lên. Đúng là loại insight mà box plot trực quan hoá tốt.

Filter nâng cao trên dashboard

Bài căn bản đã giới thiệu native filters, filter scoping, cross-filter ở mức khái niệm. Ở đây đi vào chiều sâu vận hành:

• Native filters: khi người dùng chọn giá trị, Superset chèn giá trị đó thành mệnh đề WHERE vào truy vấn nền của các chart bị áp. Filter có nhiều kiểu: value (chọn từ danh sách), numerical range (khoảng số), time range/time column/time grain. Có thể đặt filter phụ thuộc nhau (dependent filters): chọn "loại tiền" xong thì filter "khách hàng" chỉ hiện khách thuộc loại tiền đó.
• Filter scoping: kiểm soát mỗi filter tác động tới những chart nào. Mặc định áp toàn dashboard; nhưng khi một dashboard trộn khối giao dịch và khối nhân sự, hãy giới hạn scope để filter "phòng ban" chỉ đụng chart nhân sự, filter "loại tiền" chỉ đụng chart số dư — tránh filter "chết" trên chart không có cột tương ứng.
• Cross-filtering: bấm vào một phần tử của chart (ví dụ cột "VND") để lọc các chart khác trên dashboard theo giá trị đó. Đây là cơ chế drill tương tác mạnh: analyst bấm một loại nghiệp vụ đáng ngờ, toàn dashboard tự thu hẹp theo nghiệp vụ đó để điều tra.
• Time range filter: loại filter đặc thù cho phân tích. Superset hỗ trợ khoảng tuyệt đối, tương đối ("Last 30 days", "Last quarter") và các preset. Giá trị của nó chính là thứ Jinja lấy qua {{ from_dttm }} / {{ to_dttm }} — cầu nối giữa filter và SQL động của virtual dataset.

Ví dụ tăng trưởng theo tháng với window function

Một chart mà ban điều hành hay hỏi: tổng giao dịch tháng này tăng/giảm bao nhiêu % so với tháng trước? Cần hàm cửa sổ (window function) LAG để lấy giá trị tháng liền trước. Câu này chạy trực tiếp trên sandbox — bọc trong WITH nên vẫn là một câu:

-- ▶ Chạy được
WITH thang AS (
    SELECT date_trunc('month', created_at) AS thang,
           SUM(amount)                      AS tong_tien
    FROM transactions
    GROUP BY date_trunc('month', created_at)
)
SELECT thang,
       tong_tien,
       LAG(tong_tien) OVER (ORDER BY thang) AS thang_truoc,
       ROUND(
         (tong_tien - LAG(tong_tien) OVER (ORDER BY thang))
         / NULLIF(LAG(tong_tien) OVER (ORDER BY thang), 0)::numeric * 100,
         2
       ) AS tang_truong_pct
FROM thang
ORDER BY thang;

NULLIF(..., 0) tránh chia cho 0 ở tháng đầu (không có tháng trước → LAG trả NULL → phép chia ra NULL, an toàn). Kết quả nuôi một bar chart tăng trưởng hoặc cột tang_truong_pct hiển thị dạng Big Number. Trong Superset, phần lớn logic này có thể lưu thành virtual dataset để nhiều chart cùng dùng.

Tối ưu: cache và ngăn truy vấn nặng

Dashboard chậm gần như luôn do câu SQL nền chậm, không phải do Superset. Các đòn tối ưu theo thứ tự ưu tiên:

1. Dataset caching: Superset cache kết quả truy vấn vào backend (thường Redis) với một TTL (time-to-live). Lần sau mở cùng chart trong TTL, kết quả trả từ cache thay vì hỏi lại warehouse. Đặt TTL theo độ "tươi" dữ liệu cần: dashboard tổng hợp cuối ngày có thể TTL vài giờ; dashboard giám sát gần thời gian thực thì TTL ngắn hoặc tắt cache. Có cache warm-up (làm ấm cache theo lịch qua Celery Beat) để người đầu tiên mở dashboard buổi sáng không phải chờ.
2. Ngăn truy vấn nặng: đặt row limit cho chart và SQL Lab; bật query timeout để cắt câu chạy quá lâu; giới hạn khoảng thời gian mặc định của time filter (đừng để mặc định "toàn bộ lịch sử"). Với warehouse, tắt DML trên kết nối và dùng tài khoản read-only quyền tối thiểu.
3. Đẩy tối ưu về database: virtual dataset nặng lặp lại → chuyển thành view/materialized view có index; tách tải phân tích sang read replica (db-opt-06-replication) để không đụng OLTP; cân nhắc bảng tổng hợp (pre-aggregation) cho các chart chạy suốt.
4. Async query qua Celery: truy vấn dài chạy nền để không treo giao diện.

Nguyên tắc vàng: cache che giấu chậm chứ không chữa chậm. Câu SQL nền vẫn phải được tối ưu ở tầng database; cache là lớp gia tốc bên trên.

Annotation — chú thích trên biểu đồ

Annotation cho phép ghi chú ngữ cảnh lên chart, đặc biệt là time-series — để người xem hiểu tại sao đường biến động. Ba dạng chính:

• Event annotation: đánh dấu một mốc thời điểm (đường thẳng đứng), ví dụ "ngày điều chỉnh lãi suất điều hành" trên biểu đồ huy động.
• Interval annotation: tô một khoảng thời gian, ví dụ "kỳ nghỉ Tết" — giải thích vì sao giao dịch tụt.
• Annotation từ layer/nguồn: kéo chú thích từ một annotation layer dùng chung, hoặc thậm chí từ một chart khác, để nhiều dashboard dùng lại cùng bộ mốc sự kiện.

Với ngân hàng, annotation biến một biểu đồ khô khan thành câu chuyện có ngữ cảnh: nhìn đường dòng tiền tụt vào một ngày, chú thích ngay cạnh cho biết đó là ngày hệ thống bảo trì — người xem không phải đi hỏi.

Use case thực tế

Bối cảnh: Đội Quản lý rủi ro (RRO) NCB cần một "Dashboard giám sát giao dịch & rủi ro" cho ~25 cán bộ rủi ro và tuân thủ. Yêu cầu: theo dõi khối lượng giao dịch hằng ngày, phát hiện bất thường theo loại nghiệp vụ, xem phân bố số dư để tìm tài khoản ngoại lai, và so sánh tăng trưởng theo tháng. Dữ liệu ở warehouse PostgreSQL on-prem (customers, accounts, transactions).

Cách triển khai bằng SQL Lab + chart:

1. Explore trong SQL Lab: analyst viết câu gộp giao dịch theo ngày (câu date_trunc('day', ...) ở trên), chạy thử, thấy có ngày đột biến; tinh chỉnh rồi Save dataset thành virtual dataset "giao dịch theo ngày".
2. Virtual dataset phân bố số dư: lưu câu PERCENTILE_CONT/STDDEV theo currency thành dataset "phân bố số dư"; khai báo calculated column phân khúc VIP/trung bình/phổ thông để nhóm.
3. Virtual dataset tăng trưởng: lưu câu LAG theo tháng thành dataset "tăng trưởng giao dịch", với metric tang_truong_pct.
4. Chart theo đúng câu hỏi:
   • Line chart (time-series) "Giao dịch theo ngày" + event annotation đánh dấu ngày bảo trì hệ thống.
   • Big Number with Trendline "Tổng giao dịch hôm nay" + sparkline 30 ngày.
   • Box plot "Phân bố số dư theo loại tiền" để lộ tài khoản ngoại lai.
   • Bar chart "Tăng trưởng % theo tháng" từ dataset LAG.
   • Pivot table số tiền theo kind × tháng cho báo cáo tuân thủ.
5. Dashboard + filter nâng cao: thêm time range filter (mặc định "Last 90 days"), native filter loại tiền và loại nghiệp vụ; đặt filter scoping để filter loại tiền chỉ áp khối số dư; bật cross-filter — bấm một kind đáng ngờ là cả dashboard thu hẹp để điều tra.
6. Tối ưu: đặt cache TTL 30 phút cho chart tổng hợp; cache warm-up 6h sáng; row limit 50.000; chuyển virtual dataset "phân bố số dư" thành materialized view khi nó bị truy vấn liên tục.

Kết quả (minh hoạ): cán bộ rủi ro tự khoan sâu mà không cần DE viết truy vấn hộ; box plot lộ ngay 3 tài khoản số dư bất thường trong nhóm ngoại tệ; annotation giúp phân biệt "tụt do bảo trì" với "tụt bất thường". Câu SQL nền được lưu tập trung ở 3 virtual dataset, dùng lại trên toàn dashboard — sửa logic một chỗ, mọi chart cập nhật.

Ghi nhớ

• SQL Lab = IDE SQL trong trình duyệt: viết/chạy truy vấn (cả câu hoặc phần bôi đen), xem & export kết quả, query history, LIMIT tự động; truy vấn nặng đẩy async qua Celery. Chốt được câu SQL thì Save dataset thành virtual dataset.
• Virtual dataset (từ SQL) linh hoạt cho join/gộp/prototype; physical dataset (1–1 với bảng) nhanh và ổn định. Virtual dataset nặng lặp lại → hạ tầng hoá thành view/materialized view.
• Jinja templating cho SQL động: {{ from_dttm }}/{{ to_dttm }} (time filter), filter_values('cot') (giá trị native filter), {% if %}, current_username() cho RLS. Cẩn thận SQL injection — ưu tiên macro chính thức.
• Metric = biểu thức có aggregate (SUM/COUNT/AVG hoặc custom SQL); calculated column = biểu thức row-level không aggregate, dùng như dimension. Định nghĩa một lần ở dataset, dùng lại mọi chart.
• Chọn chart theo câu hỏi: thời gian → line/area; so sánh nhóm → bar; KPI + xu hướng → Big Number with Trendline; chi tiết/đối soát → table/pivot; tỷ trọng ít hạng mục → pie/donut (hạn chế, >5 mục dùng bar); phân bố/ngoại lai → box plot; mẫu hai chiều → heatmap; sụt giảm theo bước → funnel; địa lý → map/deck.gl.
• Filter nâng cao: native filter (→ WHERE) với kiểu value/range/time; dependent filters; filter scoping (giới hạn chart bị áp); cross-filter (bấm chart này lọc chart khác); time range filter nối với Jinja.
• Tối ưu theo thứ tự: dataset cache (TTL + warm-up) → ngăn truy vấn nặng (row limit, timeout, read-only) → đẩy tối ưu về DB (view, replica, pre-aggregation) → async Celery. Cache che chậm chứ không chữa chậm.
• Annotation (event/interval/layer) thêm ngữ cảnh cho time-series — biến biểu đồ thành câu chuyện có lời giải thích.
• Postgres SQL: khi ROUND(..., n) bọc AVG/PERCENTILE_CONT/STDDEV (trả double precision) phải ép ::numeric; dùng NULLIF(x, 0) để tránh chia 0 với LAG.