Logic upsert
Với mỗi dòng của lô mới, theo khoá (order_id):
- khoá đã có trong bảng đích → cập nhật dòng đó bằng giá trị mới;
- khoá chưa có → chèn dòng mới.
"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:
- Khoá đã có: một dòng đích khớp hai dòng nguồn, MERGE không biết lấy bản nào. DuckDB (và Delta Lake) từ chối, lệnh bị huỷ, không dòng nào bị đổi (thông báo bên dưới; chữ có thể khác chút theo phiên bản). Bài giả lập bằng cách nối lô với chính nó, như nguồn gửi trùng.
- Khoá mới: không có dòng đích nào để khớp, nên cả hai bản đều được chèn, không có lỗi nào. Bảng có hai dòng cho một đơn, đúng cái lỗi mà MERGE được dùng để tránh; lần MERGE sau mới báo lỗi.
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
- Không dedupe lô: đơn mới bị chèn hai lần âm thầm, rồi MERGE báo lỗi lúc 2 giờ sáng ở lần chạy sau.
- MERGE với điều kiện ON không phải khoá duy nhất (ví dụ chỉ
customer_id): một dòng nguồn khớp nhiều dòng đích, cập nhật nhầm hàng loạt. - DELETE + INSERT ngoài transaction.