以刪除、更新與合併修正資料列

閱讀約需 41 分鐘 分鐘,9 節 中共有 5 個範例。

版次 2026.11.10 範例於 2026年11 月 20 通過測試 39 個範例中有 39 個通過 變更內容

由 AI 翻譯。經人工以英文驗證。 我們如何製作書籍

譯自英文書,版次 2026.11.10。 閱讀原文

已提交的資料列同樣可以修改:本章在 Iceberg、Delta Lake 與 DuckLake 中對資料列進行刪除、upsert 與合併,說明每種寫入對檔案造成的影響,並計算一次 pyiceberg upsert 會新增多少個快照。

到目前為止,資料表只會增加資料。第 1 章中的每一次寫入,都是附加到 wiki.pageviews_hourly,而資料來源有時會把某一個小時的資料重新傳送到這個資料表中。

遲到的資料以更正的形式抵達

最近一次變動於第 2026.11.10 版

第 1 章中的資料表收到的第 02 小時首次交付資料,其計數是來源當時所送出的數值。同一小時的後續交付資料則帶有部分頁面的更正計數,以及一個首次交付資料遺漏的頁面。更正中的頁面與先前是同一批頁面,因此資料表需要就地修改資料列。若將更正後的該小時資料直接附加進去,每個頁面就會被儲存兩次,各帶一個計數。

在本書中,資料列由其鍵值識別:hour_utc、domain_code 與 page_title 三者合起來構成鍵值。更正就是一組帶有鍵值的資料列,對每個鍵值而言,資料表要麼已經存有一列,這時應修改該列,要麼沒有,這時應新增一列。因此,寫入更正是一個關於鍵值的問題,而寫入動作以一條規則回答它:一個鍵值在你所寫入的批次中必須只識別一列。pyiceberg 與 DuckLake 都會強制執行這條規則。本章不測試在 deltalake 的合併(merge)中重複鍵值的情形。

更正通常以重新交付的整個小時資料到來,而不是少數幾列。送出整個小時的資料能讓批次自成一體,因為寫入動作會逐列判斷哪些內容有變動。代價是該小時的每一列都要比對,而不只是被更正的那些。由於每一列都會比對,pyiceberg 可以只計算數值不同的資料列,而同一批次重跑一次不會造成任何變動。

此處的更正是以程式碼,根據第 1 章實際切出的資料建構而成。輔助函式 hour_corrections 將第 02 小時中 20 個頁面標題的計數減半,並無條件捨去小數。遲到的那一列是該小時第一列的複本,其頁面標題是首次交付資料所沒有的。只有更正批次是產生出來的;pageviews 的資料列就是第 1 章所切出的那些。

實用原則: 在寫入更正之前,先檢查其批次中每個鍵值是否只出現一次。pyiceberg 與 DuckLake 各自以自己的方式拒絕含有重複鍵值的批次,而重試並不能修復資料。正式環境一節會展示這些失敗情形。

此部分尚未翻譯,因此以英文呈現。

Delete rows, and what each format writes

最近一次變動於第 2026.11.7 版

A delete removes the rows that match a predicate. The rows go away in every format, but the files that held them can be handled in two ways. In copy-on-write, the writer rewrites each data file that held a matching row and leaves out the deleted rows. In merge-on-read, the writer leaves the data file alone and writes a delete file that records which rows are gone. Readers apply the delete file when they scan.

The two libraries take the predicate in different forms. pyiceberg takes an expression object, built here from EqualTo and LessThan. deltalake takes a SQL string, which reads like the WHERE clause a reader already knows. The delete below uses both forms on the same rows, so you can compare them directly.

The delete removes the hour 02 rows with fewer than 180 views, a threshold this chapter chose. The cut in chapter 1 keeps only the most-viewed rows in each hour, so a predicate for fewer than two views matches nothing. pyiceberg then warns with UserWarning: Delete operation did not match any records and writes no snapshot, as its source shows. A threshold of 180 catches a few of the least-viewed rows in hour 02, which is enough to show what a delete writes.

delete-low-views.py Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 通過
"""Runs one delete through Iceberg, Delta Lake and DuckLake and prints what each format removed and rewrote. The data-file lines carry the point; the deleted-row lines beside them are for comparison."""from datetime import datetimefrom deltalake import DeltaTablefrom pyiceberg.expressions import And, EqualTo, LessThanfrom lib.lakehouse import DUCKLAKE_ALIAS, NAMESPACE, TABLE, ducklake_file_count, load_three_formats, new_warehouse, read_delta, safe_error, scan_arrowHOUR_02 = datetime(2024, 6, 3, 2)# A threshold, not a fact about the data: the cut keeps the most-viewed rows of each hour, so a small one would match nothing.LOW_VIEWS = 180def main():    with new_warehouse() as wh:        iceberg, delta_path, lake = load_three_formats(wh, range(6))  # hours 00 to 05, one commit per hour in each format        iceberg_before = scan_arrow(iceberg).num_rows        delta_before = read_delta(delta_path).num_rows        table = f"{DUCKLAKE_ALIAS}.{NAMESPACE}.{TABLE}"        lake_before = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]        files_before = ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE)  # counted before the delete, to compare with the count after        iceberg.delete(And(EqualTo("hour_utc", HOUR_02), LessThan("views", LOW_VIEWS)))        summary = iceberg.current_snapshot().summary  # the snapshot summary counts each data file the delete removed from the table        print("Iceberg deleted rows:", iceberg_before - scan_arrow(iceberg).num_rows)        print("Iceberg remaining rows:", scan_arrow(iceberg).num_rows)        print("Iceberg data files rewritten:", summary["deleted-data-files"])        delta = DeltaTable(delta_path)        # the hour is written as a timestamp string inside the SQL predicate        metrics = delta.delete(f"hour_utc = '2024-06-03T02:00:00' AND views < {LOW_VIEWS}")        print("Delta Lake deleted rows:", delta_before - read_delta(delta_path).num_rows)        print("Delta Lake remaining rows:", read_delta(delta_path).num_rows)        print("Delta Lake data files rewritten:", metrics["num_removed_files"])        lake.execute(f"DELETE FROM {table} WHERE hour_utc = TIMESTAMP '2024-06-03 02:00:00' AND views < {LOW_VIEWS}")        lake_after = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]        print("DuckLake deleted rows:", lake_before - lake_after)        print("DuckLake remaining rows:", lake_after)        print("DuckLake data files before:", files_before)        print("DuckLake data files after:", ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE))        # a delete file is listed against the data file it applies to, so counting the listed rows that have one counts the delete files        print("DuckLake delete files written:", lake.execute(            f"SELECT count(*) FROM ducklake_list_files('{DUCKLAKE_ALIAS}', '{TABLE}', schema => '{NAMESPACE}') "            "WHERE delete_file IS NOT NULL").fetchall()[0][0])if __name__ == "__main__":    try:        main()    except Exception as exc:  # the redacted error is what a reader sees        print(safe_error(exc))        raise SystemExit(1)
輸出python delete-low-views.py
Iceberg deleted rows: 4
Iceberg remaining rows: 2995
Iceberg data files rewritten: 1
Delta Lake deleted rows: 4
Delta Lake remaining rows: 2995
Delta Lake data files rewritten: 1
DuckLake deleted rows: 4
DuckLake remaining rows: 2995
DuckLake data files before: 6
DuckLake data files after: 6
DuckLake delete files written: 1

All three formats delete the same number of rows: 4 in Iceberg, 4 in Delta Lake and 4 in DuckLake, but the files they rewrite differ.

Iceberg rewrote 1 data file, the one that held hour 02, and leaves 2995 rows. Delta Lake removes the same rows, leaves 2995, and rewrote 1 file. Both formats rewrote the file for hour 02 and left the other five hours alone, because a file is the unit a copy-on-write delete replaces.

DuckLake's DELETE leaves 2995 rows and rewrites no data file. Its data files stay at 6 before the delete and 6 after it, and the delete writes 1 delete file.

The cost of the Iceberg and Delta Lake deletes follows the size of the files they touch, not the number of rows they remove. 4 rows went, and 1 whole file was rewritten to remove them. A table whose matches are spread across many files pays for each of those files. So the cost depends on where the matching rows sit.

Each library reports its own counts, and this example computes the deleted rows as the rows before minus the rows after, so the deleted and remaining counts agree by construction. The rewritten-file counts come from the library: the Iceberg snapshot summary's deleted-data-files and the num_removed_files metric that deltalake returns from delete.

寫入時複製與讀取時合併

這兩種寫入方式在寫入端與讀取端之間分配成本的方式不同。寫入時複製(copy-on-write)使寫入的成本較高、讀取的成本較低:讀取端取得的資料列就是檔案中所存的內容,無須再套用任何變更。讀取時合併(merge-on-read)使寫入的成本較低,因為它只寫入被刪除資料列的位置,並把成本移到讀取端,直到有程序重寫資料檔案為止。

本章的三種格式差異如下。

格式 固定版本下的函式庫 函式庫在固定版本下如何寫入刪除 本章是否示範
Iceberg pyiceberg 0.12.0 寫入時複製。delete 會重寫含有相符資料列的資料檔案,且不寫入刪除檔案。當 write.delete.mode 設為讀取時合併時,pyiceberg 會發出警告「Merge on read is not yet supported, falling back to copy-on-write」(原始碼)。 是,見刪除範例
Delta Lake deltalake 1.6.6 寫入時複製。含有相符資料列的檔案會被重寫,日誌則將舊檔案記錄為已移除。 是,見刪除範例
DuckLake DuckDB 2.0.0 讀取時合併。DELETE 會寫入一個刪除檔案,並保留原資料檔案不動。 是,見刪除範例

DuckLake 這一列由刪除範例來示範,該範例會計算刪除檔案的數量,以及仍留在原處的資料檔案數量。DuckLake 關於刪除向量的文件說明了一個實驗性選項,可將被刪除的資料列位置記錄在 .puffin 檔案中。該選項預設為關閉,本章也不會啟用。本章並未量測透過刪除檔案讀取的成本。讀取時合併資料表的讀取端,在資料檔案被重寫之前,每次掃描都要付出這項成本,因此請在讀者自己的資料表上量測。

Delta Lake 也有刪除向量(deletion vectors),用來在檔案中記錄被刪除資料列的位置。這使它成為 Delta 的讀取時合併選項,而不是第三種寫入方式,本書並不使用。在固定版本下,deltalake 1.6.6 不會寫入刪除向量:在已啟用該功能的資料表上執行 delete,仍然會重寫檔案。當資料表使用刪除向量時,Delta 通訊協定要求資料表的讀取端與寫入端功能中都要有刪除向量功能,而啟用該功能會同時將它加入兩者。本書所有 Delta 讀取都使用的 QueryBuilder 可以讀取這類資料表。to_pyarrow_table 與 to_pyarrow_dataset 則會拒絕讀取,因為資料表宣告了該功能。所以本書不啟用該功能,其 Delta 刪除皆為寫入時複製。

實用原則: 預期寫入時複製的刪除,會對每個含有相符資料列的檔案付出一次檔案重寫的成本。請優先採用相符資料列集中在少數檔案內的條件,例如單一小時,並把已知要刪除的資料合併成一次寫入。讀取時合併適合寫入頻繁、且讀取端可以在檔案被重寫之前負擔刪除檔案成本的資料表。

以 upsert 更正某一小時的資料

最近一次變動於第 2026.11.10 版

upsert 會更新鍵相符的資料列,並插入鍵不存在的資料列。對於更正而言,這是正確的寫入方式,因為更正後的該小時資料帶有資料表中已有的鍵,而遲到的那一列帶有資料表中尚未存在的鍵。若只是單純附加(append),更正後的資料列會與原本的資料列並存,該小時內的每個頁面就會出現兩次。

接下來的兩個小節以同一批資料,分別示範 pyiceberg 的方法與 DuckLake 的 SQL MERGE。

鍵用來找出變更所套用的資料列,也用來決定兩筆傳入的資料列是否為同一列。鍵過寬,會把兩個實際不同的資料列合併成一列;鍵過窄,則會插入第二份副本。這裡的鍵是 hour_utc、domain_code 與 page_title,即第 1 章中切分方式賦予每個資料列的識別。範例中的各批資料,每個鍵都只出現一次。

pyiceberg 中的 upsert

最近一次變動於第 2026.11.7 版

pyiceberg 的 upsert 接受一個由資料列組成的 Arrow table,以及構成鍵值的欄位,這些欄位以 join_cols 傳入。它會回傳更新的資料列數與插入的資料列數。只有在非鍵值欄位發生變更時,資料列才會算作已更新,因此與資料表完全相同的資料列不會計入更新。若該資料列所在的檔案中還有已變更的資料列,copy-on-write 仍會重寫這筆資料列。

將修正後的該小時資料與遲到的資料列透過一次 upsert 送出,並計算這次呼叫在資料表歷史中留下了什麼。

redeliver-hour-upsert.py Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 通過
"""Upserts the corrected hour 02, with its late row, into the Iceberg table, then upserts the same batch again. The snapshot lines carry the point, in the order the commits were made."""import pyarrow as pafrom lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, snapshot_labelsdef main():    with new_warehouse() as wh:        iceberg, _, _ = load_three_formats(wh, range(6))        before = {snapshot.snapshot_id for snapshot in iceberg.snapshots()}  # so the loop below lists only the snapshots this upsert adds        # hour_corrections halves the views of the first 20 page titles in byte order, so the same titles change on every run        corrected = hour_corrections(read_cut_hour(2))        # A late row: a page the first delivery of hour 02 did not have.        late = corrected.slice(0, 1).set_column(            corrected.schema.get_field_index("page_title"),            "page_title",            pa.array(["Late_arrival_example"], type=pa.string()),        )        batch = pa.concat_tables([corrected, late])        result = iceberg.upsert(batch, join_cols=list(KEY))  # KEY is the three key columns; a row matches when all three are equal        # rows_updated counts only rows whose non-key values changed, so identical rows are not counted        print("rows updated:", result.rows_updated)        print("rows inserted:", result.rows_inserted)        iceberg.refresh()        labels = snapshot_labels([snapshot.snapshot_id for snapshot in iceberg.snapshots()])  # snapshot IDs change on every run, so the labels number commits        added = [snapshot for snapshot in iceberg.snapshots() if snapshot.snapshot_id not in before]        for snapshot in added:            label = labels[snapshot.snapshot_id]            print(label, snapshot.summary.operation.value)            # a snapshot that adds or deletes no records has no such key, so the count is 0            print(f"{label} records added:", snapshot.summary.get("added-records") or 0)            print(f"{label} records deleted:", snapshot.summary.get("deleted-records") or 0)        print("snapshots added:", len(added))        again = iceberg.upsert(batch, join_cols=list(KEY))  # the same batch again, as a retry would send it        print("rows updated on second upsert:", again.rows_updated)        print("rows inserted on second upsert:", again.rows_inserted)if __name__ == "__main__":    try:        main()    except Exception as exc:  # the redacted error is what a reader sees        print(safe_error(exc))        raise SystemExit(1)
輸出python redeliver-hour-upsert.py
rows updated: 20
rows inserted: 1
snapshot 7 overwrite
snapshot 7 records added: 480
snapshot 7 records deleted: 500
snapshot 8 append
snapshot 8 records added: 20
snapshot 8 records deleted: 0
snapshot 9 append
snapshot 9 records added: 1
snapshot 9 records deleted: 0
snapshots added: 3
rows updated on second upsert: 0
rows inserted on second upsert: 0

一次 upsert 呼叫並不等於一次提交:這次呼叫寫入了 3 個快照,用來更新 20 筆資料列並插入 1 筆。快照清單會顯示每個快照所記錄的內容。

第一個快照是 overwrite。它刪除了 500 筆資料列,也就是 hour-02 檔案中的資料列,並將其中 480 筆原封不動地加回。接下來的兩個是 append。第二個加入 20 筆已變更的資料列,第三個加入 1 筆資料列,也就是遲到的那一筆。這些標籤來自 snapshot_labels,因此編號就是提交的順序。

在 pyiceberg 0.12.0 原始碼中,upsert 只有在有資料列變更時才會呼叫 overwrite,而且只有在有新資料列時才會對新資料列呼叫 append。overwrite 會寫入兩個快照,一個是刪除,一個是附加。函式庫將第一個記錄為 overwrite,因為它同時也會將資料列寫回。因此,同時包含變更與新增資料列的批次會增加三個快照,只含變更資料列的批次增加兩個,只含新資料列的批次增加一個。請從快照清單中計算數量,而不要依據函式庫的呼叫來推算。

再次執行相同的 upsert 不會改變任何內容:它回報更新 0 筆、插入 0 筆。第二次執行時,每個鍵值都已是修正後的值,所以重複送出的修正不會讓資料表改變兩次,這正是重試之所以安全的原因。

實用原則: 使用 upsert,並將 join_cols 設為鍵值,從回傳結果讀取其計數。快照數量請從快照清單計算,不要假設每次呼叫只有一次提交。當不確定第一次寫入是否成功時,請再執行一次相同的批次,並確認第二次執行回報沒有任何更新。

此部分尚未翻譯,因此以英文呈現。

MERGE in DuckLake

最近一次變動於第 2026.11.10 版

DuckLake takes the same correction as a MERGE statement in SQL. A MERGE statement names the target table, the source batch and the key in its ON clause, and each WHEN clause says what to do with a matched or an unmatched row. The statement below is the one the example after it runs.

MERGE INTO lake.wiki.pageviews_hourly AS t
USING batch AS s
ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title
WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views
WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)
    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)

The WHEN NOT MATCHED branch also inserts response_bytes, which is 0 in every row of the cut, so the late row's value is 0 too.

The AND t.views <> s.views condition matters. Without it, the MERGE would update and count every matched row, so a rerun would count them all again. With it, a matched row whose counts already agree is left alone and not counted. No example runs the statement without the condition, so the claim about a rerun without it is a reading of the statement, not a measured count.

merge-correction-ducklake.py Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 通過
"""Merges the corrected hour 02 and its late row into the DuckLake copy with a SQL MERGE, then runs the same MERGE again. The matched-row line splits the changed count into inserts and updates; the second changed-row line is the rerun."""import pyarrow as pafrom lib.lakehouse import hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error# The WHEN MATCHED condition is what keeps a rerun from counting matched rows that already agree.MERGE = """MERGE INTO lake.wiki.pageviews_hourly AS tUSING batch AS sON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_titleWHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.viewsWHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)"""def main():    with new_warehouse() as wh:        _, _, lake = load_three_formats(wh, range(6))        corrected = hour_corrections(read_cut_hour(2))        # a copy of the hour's first row, with a page title the table never held        late = corrected.slice(0, 1).set_column(            corrected.schema.get_field_index("page_title"),            "page_title",            pa.array(["Late_arrival_example"], type=pa.string()),        )        lake.register("batch", pa.concat_tables([corrected, late]))        print("source rows:", lake.execute("SELECT count(*) FROM batch").fetchall()[0][0])        # the join counts source rows whose key the table already holds, which splits the changes into inserts and updates        matched = lake.execute(            "SELECT count(*) FROM lake.wiki.pageviews_hourly AS t JOIN batch AS s "            "ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title"        ).fetchall()[0][0]        print("source rows matched:", matched)        # DuckDB returns one count for a MERGE, with updates and inserts added together        print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])        print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])        print("rows in table:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])if __name__ == "__main__":    try:        main()    except Exception as exc:  # the redacted error is what a reader sees        print(safe_error(exc))        raise SystemExit(1)
輸出python merge-correction-ducklake.py
source rows: 501
source rows matched: 500
rows changed by the merge: 21
rows changed by the same merge again: 0
rows in table: 3000

Like the upsert, the merge is safe to rerun: it changes 21 rows the first time and 0 the second, and the table ends with 3000 rows, the six hours and the late row.

The changed count adds the two kinds of change together, so the split needs the source size. Of the 501 rows in the batch, 500 match a key already in the table, so the only row without a match is the late row. The rows inserted are the source rows minus the matched rows, here 501 minus 500. The rows updated are the changed rows minus the rows inserted. A count of matched rows alone gives neither number.

DuckLake is attached with inlining off in these examples, as chapter 1 explains, so each load writes data files rather than rows in the catalog. The merge's counts are of rows.

Working rule: Write a MERGE with a WHEN MATCHED AND condition that compares the columns you update, so that a rerun changes nothing. Make sure the source holds each key once before you run it. DuckLake refuses a merge that matches one target row to two source rows, and the production section shows the error.

將變更後的標籤合併到 domains 資料表

最近一次變動於第 2026.11.7 版

本書的第二個資料表 wiki.domains 將每個網域代碼對應到一種語言。維度資料表存放的是標籤而不是計數,若有少數標籤同時變更,適合使用合併。重點在於同樣的陳述式形式也適用於維度資料表,而且關於條件的規則在此同樣適用。

UPDATE 會變更標籤。MERGE 的形式還有 WHEN NOT MATCHED 子句,可以插入資料表尚未存放的網域代碼,因此新增網域的資料饋送也能使用同一個陳述式。這個資料表只存在於 DuckLake,所以合併在 DuckLake 中執行。pageviews 資料表的合併,請見 DuckLake 中的 MERGE。在管線中,你在這裡合併的標籤,就是第 8 章的 join所回傳的標籤。

merge-domains-label.py Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 通過
"""Merges one changed language label into the DuckLake copy of the domains table, then runs the same MERGE again. Read the second changed-row line against the sorted table, which shows the label that merged in."""import pyarrow as pafrom lib.lakehouse import attach_ducklake, connect_ducklake, print_rows, read_domains, new_warehouse, safe_errorMERGE = """MERGE INTO lake.wiki.domains AS tUSING labels AS sON t.domain_code = s.domain_codeWHEN MATCHED AND t.language <> s.language THEN UPDATE SET language = s.languageWHEN NOT MATCHED THEN INSERT (domain_code, language) VALUES (s.domain_code, s.language)"""def main():    with new_warehouse() as wh:        lake = connect_ducklake(wh.con)        attach_ducklake(lake, "lake", wh.root, inlining_row_limit=0)  # inlining off, so the rows land in data files        lake.execute("CREATE SCHEMA lake.wiki")        lake.execute("CREATE TABLE lake.wiki.domains (domain_code VARCHAR, language VARCHAR)")        lake.register("dims", read_domains())        lake.execute("INSERT INTO lake.wiki.domains SELECT * FROM dims")        # 'fr' is already in the table, so this label is an update and not an insert        lake.register("labels", pa.table({"domain_code": ["fr"], "language": ["French (France)"]}))        print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])        print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])        print_rows(lake.execute("SELECT domain_code, language FROM lake.wiki.domains").fetchall())  # sorted, so the order DuckLake returns rows in does not matterif __name__ == "__main__":    try:        main()    except Exception as exc:  # the redacted error is what a reader sees        print(safe_error(exc))        raise SystemExit(1)
輸出python merge-domains-label.py
rows changed by the merge: 1
rows changed by the same merge again: 0
de	German
en	English
fr	French (France)
ja	Japanese

將一個標籤 fr 從 French 合併為 French (France),會變更 1 列,而同一個合併再執行第二次,則變更 0 列。資料表一開始有本書自有資料中的四列,輸出末尾排序後的資料表顯示每個網域及其標籤。

實用原則: 合併維度資料表時,使用與事實資料表相同的 WHEN MATCHED AND 條件,並再執行第二次合併,確認它沒有變更任何內容。標籤的變更會影響所有與該維度做 join 的查詢,因此合併前要先檢視即將合併的標籤。

正式環境中會出什麼問題

最近一次變動於第 2026.11.7 版

修正作業在實務上出問題,通常來自重複的索引鍵、逐列刪除的迴圈,以及讓閱讀歷史紀錄的人感到意外的快照數量。這幾種問題在寫入前檢查成本很低,事後才發現則代價高昂,因此檢查應放在寫入該批資料的作業之中。

**批次中出現重複的索引鍵。**修正資料被送出兩次,或是錯誤的 join 產生兩列相同索引鍵的資料,都會使該批資料無效。這個範例把同一列修正資料送出兩次,pyiceberg 在寫入任何內容之前就拒絕了這次 upsert。

duplicate-source-keys.py Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 通過
"""Sends a batch with one key twice to an Iceberg upsert and to a DuckLake MERGE, and prints each refusal with the row count after it. The two error lines carry the point: each names the cause of its refusal."""import pyarrow as pafrom lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, scan_arrowMERGE = """MERGE INTO lake.wiki.pageviews_hourly AS tUSING batch AS sON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_titleWHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.viewsWHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)"""def main():    with new_warehouse() as wh:        iceberg, _, lake = load_three_formats(wh, range(6))        corrected = hour_corrections(read_cut_hour(2))        # A corrected row, sent twice: the batch holds two rows with one key.        before = read_cut_hour(2)["views"].to_pylist()  # the cut's views, to find the rows the correction changed        changed = [i for i, after in enumerate(corrected["views"].to_pylist()) if after != before[i]]        doubled = pa.concat_tables([corrected, corrected.slice(changed[0], 1)])        try:            iceberg.upsert(doubled, join_cols=list(KEY))            print("Iceberg upsert: no error")        except Exception as exc:            print("Iceberg upsert:", safe_error(exc, wh))  # safe_error replaces the warehouse path, so no local path is printed        print("Iceberg rows after the failed upsert:", scan_arrow(iceberg).num_rows)        lake.register("batch", doubled)        try:            lake.execute(MERGE)            print("DuckLake merge: no error")        except Exception as exc:            print("DuckLake merge:", safe_error(exc, wh))        print("DuckLake rows after the failed merge:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])if __name__ == "__main__":    try:        main()    except Exception as exc:  # the redacted error is what a reader sees        print(safe_error(exc))        raise SystemExit(1)
輸出python duplicate-source-keys.py
Iceberg upsert: ValueError: Duplicate rows found in source dataset based on the key columns. No upsert executed
Iceberg rows after the failed upsert: 2999
DuckLake merge: InvalidInputException: Invalid Input Error: MERGE INTO command cannot affect the same target row more than once. A target row matched more than one source row; ensure the source rows are deduplicated or the ON condition is unique.
DuckLake rows after the failed merge: 2999

兩次拒絕之後,資料表都保有寫入前的所有資料列。Iceberg 的 upsert 會引發 ValueError,資料表仍有 2999 列。DuckLake 的 merge 會引發 InvalidInputException,並指出原因:一個目標列符合多個來源列,資料表仍有 2999 列。若管線捕捉錯誤後以同一批資料重試,會再次失敗,因為問題出在這批資料本身。請在寫入前依索引鍵對來源資料去除重複。這裡並未測試 Delta Lake 在索引鍵重複時的 merge 行為(見第一節)。

**逐列刪除的迴圈。**Copy-on-write 使每次刪除都成為一次提交,並重寫包含符合列的檔案。在迴圈中執行十次刪除,會把包含這些符合列的檔案重寫十次,並留下十個快照或版本。這是由成本模型推得的結論,而不是這裡的實測結果。請先蒐集要刪除的資料列,再以涵蓋這些資料列的條件式執行一次刪除寫入。

**讓讀者意外的快照數量。**在上方的執行中,一次同時包含變更列與新增列的 upsert 會新增 3 個快照。若讀者預期每項變更對應一次提交,就會發現歷史紀錄比變更紀錄更長。快照會留在資料表的中繼資料中,直到被過期處理為止,這部分由第 7 章說明,而且每個快照都會保留它所參照的檔案。

**如何察覺。**這些問題都會反映在你原本就會記錄的數字上。pyiceberg 結果中的更新列數與插入列數,以及 merge 的變更列數,都應與你送出的批次相符。重跑時若回報有變更,表示該批資料與你所認為的內容不同。若快照數量成長得比每次載入預期的快照數(upsert 最多三個)更快,就表示重試並非冪等,例如同一次 append 被送出兩次。拒絕寫入會以例外的形式出現,因此捕捉例外後繼續執行的作業看起來會很正常,資料表卻停留在舊狀態。

**實用原則:**在任何寫入之前檢查批次中是否有重複的索引鍵,並將拒絕寫入視為批次本身的錯誤,而非暫時性故障。將刪除合併為一次寫入。稽核修正作業時,請從快照清單計算快照數量。

本章做出的決定

最近一次變動於第 2026.11.7 版
  • 更正資料以 hour_utc、domain_code 和 page_title 作為鍵值來比對,而且批次在寫入之前,每個鍵值必須只出現一次。
  • 刪除範例使用 180 次瀏覽作為門檻,因為篩選後每個小時只保留瀏覽次數最高的資料列,所以門檻設為 2 時不會比對到任何資料。
  • Iceberg 與 Delta Lake 的刪除採用寫入時複製(copy-on-write),它們的範例會顯示被重寫的檔案。DuckLake 的 DELETE 則是寫入一個刪除檔,並讓資料檔維持原狀,同一個範例也會顯示這一點。
  • 本章不使用刪除向量(deletion vectors)。在固定的版本下,deltalake 1.6.6 不會寫入刪除向量,而且啟用該功能後,to_pyarrow_table 與 to_pyarrow_dataset 會拒絕讀取該資料表。
  • 更新插入(upsert)與合併(merge)的檢查方式是執行兩次:第二次執行不會改變任何資料列。
  • 快照數量從快照清單中讀取。本章不把一次呼叫視為一次提交。
  • DuckLake 範例會關閉內嵌(inlining),如第 1 章所說明,因此計數所描述的是資料檔。
  • 更正資料是以程式碼建構的,遲到的資料列也是產生出來的,所以這些範例顯示的是寫入路徑,而不是真實更正的歷史。
  • 重複鍵值的範例顯示的是拒絕寫入本身,而不是對它的描述,因此失敗會出現在讀者看到的輸出中。
  • 本章沒有 Delta Lake 的更新插入或合併範例,所以除了刪除之外,對 deltalake 的合併不做任何主張。
  • 遲到的資料列與更正資料放在同一個批次中,因此一次呼叫就能同時顯示更新與插入。

本章,於本版次中

版次 2026.11.10範例於 2026年11 月 20 通過測試本章 5 個範例 個範例中有 5 個通過

譯自英文書,版次 2026.11.10。

引用本節

格式

各種格式皆引用英文原書,本譯本即依循該原書。

每種格式都會標示本版。其連結會開啟目前版次,節標題會說明該節內文最後一次變動的時間。存取日期與擷取日期即為你開啟此頁的當日。

本頁內容

第 5 章,共 10 章: 以刪除、更新與合併修正資料列

  1. 遲到的資料以更正的形式抵達
  2. Delete rows, and what each format writes
  3. 寫入時複製與讀取時合併
  4. 以 upsert 更正某一小時的資料
  5. pyiceberg 中的 upsert
  6. MERGE in DuckLake
  7. 將變更後的標籤合併到 domains 資料表
  8. 正式環境中會出什麼問題
  9. 本章做出的決定

返回頂端所有章節