DataOps Peach

Data Foundations · Idempotency và incremental load · Bài 4/5

MERGE / upsert

Upsert theo khoá - có thì cập nhật, chưa có thì chèn - là cách nạp incremental idempotent. Dedupe lô trước, chạy MERGE ba lần và thấy cùng một vân tay.

Học tương tác →

Logic upsert

Với mỗi dòng của lô mới, theo khoá (order_id):

"Upsert" = update + insert. Không có nhánh nào cộng thêm dòng cho một khoá đã tồn tại, nên chạy lại cùng lô chỉ ghi đè các dòng bằng đúng giá trị chúng đang có: MERGE theo khoá idempotent theo bản chất, với điều kiện mỗi khoá chỉ xuất hiện một lần trong lô.

MERGE trong DuckDB

MERGE INTO silver_orders t
USING stg_orders s
   ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE          -- cập nhật mọi cột theo bản của lô
WHEN NOT MATCHED THEN INSERT;     -- chèn đơn mới

Kết quả với lô 548 dòng: 148 đơn cũ được cập nhật, 400 đơn mới được chèn. silver_orders có 5372 dòng, 5372 đơn, tổng tiền 70,538,094,000, còn 324 đơn pending. Chạy lại MERGE hai lần nữa: vân tay y hệt. So từng dòng với bản full refresh silver_full của bài trước: EXCEPT hai chiều đều ra 0.

Lưu ý: trong DuckDB, UPDATE / INSERT không kèm danh sách cột ghép cột theo vị trí (trong DuckDB, kể cả UPDATE SET * / INSERT *); vì vậy bài 4.2 dựng stg_orders đúng thứ tự cột của silver. Muốn ghép theo tên thì viết UPDATE BY NAME / INSERT BY NAME. Databricks (Delta Lake, Track 2) viết tương tự với UPDATE SET * và INSERT *, và ghép theo tên.

Dedupe lô trước khi MERGE

Nếu lô có hai bản của cùng một đơn, kết quả tuỳ khoá đó đã có trong bảng đích hay chưa:

Invalid Input Error: MERGE INTO command cannot affect the same target row more than once. ...

Vì vậy dedupe lô là bắt buộc, không chỉ để tránh lỗi: luôn kiểm khoá của lô (count(*) = count(DISTINCT order_id)) và dedupe trước nếu cần, bằng ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY <cột thời gian> DESC, <phá hoà>) như Module 02. Lô của bài không có khoá trùng (548 = 548), nhưng lô thật thì không hứa trước được.

Cách cũ: DELETE + INSERT

Công cụ không có MERGE thì làm tương đương: xoá các khoá có trong lô, rồi chèn cả lô.

BEGIN;
DELETE FROM silver_orders WHERE order_id IN (SELECT order_id FROM stg_orders);
INSERT INTO silver_orders SELECT * FROM stg_orders;
COMMIT;

Bắt buộc gói trong transaction. Nếu job chết giữa DELETE và INSERT mà không có transaction, 148 đơn cũ đã bị xoá mà chưa được chèn lại: bảng thiếu 148 đơn cho tới khi có người chạy lại (retry sẽ chèn đủ), và nếu không ai retry thì chúng mất hẳn. Trong lúc đó, ai đọc bảng cũng thấy số sai. Bài 4.5 dùng đúng cách này để backfill một cửa sổ ngày.

flowchart LR
  L[stg_orders · 548] --> K{khoá duy nhất?}
  K -->|có| M[MERGE theo order_id]
  K -->|không| D[dedupe ROW_NUMBER] --> M
  M --> S[silver_orders · 5372]

Ví dụ: làm đúng với cùng lô

Bài 4.2 nối thẳng lô vào silver và được 5520 dòng, 148 đơn có hai trạng thái, báo cáo 472 đơn treo. Cùng lô đó, qua MERGE: 5372 dòng, mỗi đơn một trạng thái (bản mới nhất), 324 đơn treo. Chạy lại bao nhiêu lần cũng vậy.

Lệnh hay dùng

Lệnh Để làm gì
SELECT count(*), count(DISTINCT <khoá>) FROM <lô> kiểm khoá lô trước khi merge
MERGE INTO t USING s ON t.k = s.k WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT upsert
BEGIN; DELETE ...; INSERT ...; COMMIT; upsert khi không có MERGE
SELECT * FROM a EXCEPT SELECT * FROM b so từng dòng hai bảng

Lỗi hay gặp

Học tương tác →