通过删除、更新和合并来修正数据行
阅读约需 35分钟 分钟,9节中含5个示例。
已经提交的数据行同样可以修改:本章在 Iceberg、Delta Lake 和 DuckLake 中对这些行执行删除、upsert 和合并,说明每种写入对文件产生的影响,并统计一次 pyiceberg upsert 会新增多少个快照。
到目前为止,这张表只会不断增长。第 1 章中的每一次写入,都是向 wiki.pageviews_hourly 追加数据,而数据源有时会向这张表中已有的某个小时重新投递数据。
迟到的数据以更正的形式到达
02 时的首次投递已进入第 1 章中的那张表,带的是来源当时发送的计数。同一小时的后一次投递为部分页面带来了更正后的计数,还多出一个首次投递遗漏的页面。更正中的页面与之前是同一批页面,因此表需要就地修改各行。若把更正后的小时追加进去,每个页面都会被存两次,各带一个计数。
本书中,一行由它的键标识:hour_utc、domain_code 与 page_title 三者合在一起。更正是一组带键的行;对每个键,表中要么已有一行,此时应修改它,要么没有,此时应添加它。因此,写入更正就是一个关于键的问题,写入用一条规则来回答它:在你所写入的批次中,一个键必须只标识一行。pyiceberg 与 DuckLake 都强制执行这条规则。本章不测试在 deltalake 的合并中出现重复键的情形。
更正通常以整个小时重新投递的形式到达,而不是寥寥几行。发送整个小时能让批次自成一体,因为写入会逐行判定哪些内容发生了变化。代价是该小时的每一行都要比较,而不只是被更正的那些行。正因为每一行都参与比较,pyiceberg 才能只统计值有差异的行,而对同一批次的重跑不会改变任何东西。
这里的更正由代码构造,取自第 1 章的真实切片。辅助函数 hour_corrections 把 02 时中 20 个页面标题的计数减半,向下取整。迟到的那一行是该小时第一行的副本,带有一个首次投递中从未出现过的页面标题。只有更正批次是生成的;pageviews 的各行就是第 1 章切出的那些行。
**工作准则:**在写入更正之前,先检查其批次中每个键只出现一次。pyiceberg 与 DuckLake 会各以自己的方式拒绝含重复键的批次,而重试并不能修复数据。生产环境一节展示了这些失败。
这部分尚未翻译,因此以英文显示。
Delete rows, and what each format writes
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.
1"""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."""23from datetime import datetime45from deltalake import DeltaTable6from pyiceberg.expressions import And, EqualTo, LessThan78from lib.lakehouse import DUCKLAKE_ALIAS, NAMESPACE, TABLE, ducklake_file_count, load_three_formats, new_warehouse, read_delta, safe_error, scan_arrow910HOUR_02 = datetime(2024, 6, 3, 2)11# 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.12LOW_VIEWS = 180131415def main():16 with new_warehouse() as wh:17 iceberg, delta_path, lake = load_three_formats(wh, range(6)) # hours 00 to 05, one commit per hour in each format18 iceberg_before = scan_arrow(iceberg).num_rows19 delta_before = read_delta(delta_path).num_rows20 table = f"{DUCKLAKE_ALIAS}.{NAMESPACE}.{TABLE}"21 lake_before = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]22 files_before = ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE) # counted before the delete, to compare with the count after2324 iceberg.delete(And(EqualTo("hour_utc", HOUR_02), LessThan("views", LOW_VIEWS)))25 summary = iceberg.current_snapshot().summary # the snapshot summary counts each data file the delete removed from the table26 print("Iceberg deleted rows:", iceberg_before - scan_arrow(iceberg).num_rows)27 print("Iceberg remaining rows:", scan_arrow(iceberg).num_rows)28 print("Iceberg data files rewritten:", summary["deleted-data-files"])2930 delta = DeltaTable(delta_path)31 # the hour is written as a timestamp string inside the SQL predicate32 metrics = delta.delete(f"hour_utc = '2024-06-03T02:00:00' AND views < {LOW_VIEWS}")33 print("Delta Lake deleted rows:", delta_before - read_delta(delta_path).num_rows)34 print("Delta Lake remaining rows:", read_delta(delta_path).num_rows)35 print("Delta Lake data files rewritten:", metrics["num_removed_files"])3637 lake.execute(f"DELETE FROM {table} WHERE hour_utc = TIMESTAMP '2024-06-03 02:00:00' AND views < {LOW_VIEWS}")38 lake_after = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]39 print("DuckLake deleted rows:", lake_before - lake_after)40 print("DuckLake remaining rows:", lake_after)41 print("DuckLake data files before:", files_before)42 print("DuckLake data files after:", ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE))43 # a delete file is listed against the data file it applies to, so counting the listed rows that have one counts the delete files44 print("DuckLake delete files written:", lake.execute(45 f"SELECT count(*) FROM ducklake_list_files('{DUCKLAKE_ALIAS}', '{TABLE}', schema => '{NAMESPACE}') "46 "WHERE delete_file IS NOT NULL").fetchall()[0][0])474849if __name__ == "__main__":50 try:51 main()52 except Exception as exc: # the redacted error is what a reader sees53 print(safe_error(exc))54 raise SystemExit(1)
python delete-low-views.pyIceberg 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.
写时复制与读时合并
这两种写入方式在写入方和读取方之间分摊成本。写时复制使写入代价高昂而读取代价低廉:读取方按数据文件中保存的样子直接得到行,无需再应用任何内容。读时合并使写入代价低廉,因为它只写入被删除行的位置,并把成本转移到读取上,直到有操作重写数据文件为止。
本章涉及的三种格式区别如下。
| 格式 | 固定版本下的库 | 该库在固定版本下如何写入删除 | 本章中的展示 |
|---|---|---|---|
| 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 同样有删除向量,它在文件中记录被删除行的位置。这使其成为 Delta 的读时合并选项,而不是第三种写入方式,本书不使用它。在固定版本下,deltalake 1.6.6 不会写入删除向量:在已启用该特性的表上执行 delete,仍然会重写文件。当表使用删除向量时,Delta 协议要求表的读取方特性和写入方特性中都包含删除向量特性,启用该特性会把它同时加入两者。本书所有 Delta 读取都使用 QueryBuilder,它可以读取这样的表。to_pyarrow_table 和 to_pyarrow_dataset 则会拒绝读取,因为该表声明了这一特性。因此本书保持该特性关闭,其 Delta 删除均为写时复制。
工作准则: 预期写时复制的删除,会为每个包含匹配行的文件付出一次文件重写的成本。优先选用匹配行集中在少数文件中的谓词,例如某一个小时,并把已知要执行的删除合并为一次写入。读时合并适合写入频繁、且读取方可以在文件被重写之前承担删除文件成本的表。
对更正后的小时数据执行 upsert
upsert 会更新键匹配的行,并插入键不存在的行。对于更正而言,这是恰当的写入方式:更正后的小时数据包含表中已有的键,而迟到的行所带的键在表中尚不存在。如果只是普通追加,更正后的行会与原始行并存,该小时内的每个页面都会出现两次。
接下来的两个小节使用同一批数据,分别展示 pyiceberg 的方法和 DuckLake 的 SQL MERGE。
键用于找到某项变更所作用的行,并决定两条传入的行是否为同一行。键过宽,会把两条真实的行合并为一条;键过窄,则会插入第二份副本。这里的键由 hour_utc、domain_code 和 page_title 组成,即第 1 章中切分方式赋予每一行的标识。示例中的各批数据,每个键只出现一次。
pyiceberg 中的 upsert
pyiceberg 的 upsert 接收一个由行组成的 Arrow 表,以及构成键的列,这些列通过 join_cols 传入。它返回更新的行数和插入的行数。只有当某个非键列发生变化时,该行才算作已更新,因此与表中内容完全相同的行不计入已更新。如果这样的行所在的文件中还有被修改的行,写时复制(copy-on-write)仍会重写这一行。
将修正后的那个小时的数据和迟到的行通过一次 upsert 发送,然后统计这次调用在表的历史中留下了什么。
1"""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."""23import pyarrow as pa45from lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, snapshot_labels678def main():9 with new_warehouse() as wh:10 iceberg, _, _ = load_three_formats(wh, range(6))11 before = {snapshot.snapshot_id for snapshot in iceberg.snapshots()} # so the loop below lists only the snapshots this upsert adds12 # hour_corrections halves the views of the first 20 page titles in byte order, so the same titles change on every run13 corrected = hour_corrections(read_cut_hour(2))14 # A late row: a page the first delivery of hour 02 did not have.15 late = corrected.slice(0, 1).set_column(16 corrected.schema.get_field_index("page_title"),17 "page_title",18 pa.array(["Late_arrival_example"], type=pa.string()),19 )20 batch = pa.concat_tables([corrected, late])2122 result = iceberg.upsert(batch, join_cols=list(KEY)) # KEY is the three key columns; a row matches when all three are equal23 # rows_updated counts only rows whose non-key values changed, so identical rows are not counted24 print("rows updated:", result.rows_updated)25 print("rows inserted:", result.rows_inserted)2627 iceberg.refresh()28 labels = snapshot_labels([snapshot.snapshot_id for snapshot in iceberg.snapshots()]) # snapshot IDs change on every run, so the labels number commits29 added = [snapshot for snapshot in iceberg.snapshots() if snapshot.snapshot_id not in before]30 for snapshot in added:31 label = labels[snapshot.snapshot_id]32 print(label, snapshot.summary.operation.value)33 # a snapshot that adds or deletes no records has no such key, so the count is 034 print(f"{label} records added:", snapshot.summary.get("added-records") or 0)35 print(f"{label} records deleted:", snapshot.summary.get("deleted-records") or 0)36 print("snapshots added:", len(added))3738 again = iceberg.upsert(batch, join_cols=list(KEY)) # the same batch again, as a retry would send it39 print("rows updated on second upsert:", again.rows_updated)40 print("rows inserted on second upsert:", again.rows_inserted)414243if __name__ == "__main__":44 try:45 main()46 except Exception as exc: # the redacted error is what a reader sees47 print(safe_error(exc))48 raise SystemExit(1)
python redeliver-hour-upsert.pyrows 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 设置为键,从返回结果中读取计数。快照数量以快照列表为准,不要假定每次调用对应一次提交。当不确定第一次写入是否生效时,再运行一次同一个批次,并检查第二次运行报告没有更新。
DuckLake 中的 MERGE
DuckLake 可以用 SQL 中的 MERGE 语句完成同样的更正。MERGE 语句在其 ON 子句中指明目标表、源批次和键,每个 WHEN 子句则说明如何处理匹配或不匹配的行。下面的语句就是随后的示例所运行的那一条。
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)
WHEN NOT MATCHED 分支还会插入 response_bytes,该列在这批数据的每一行中都是 0,因此迟到的那一行的该值也是 0。
AND t.views <> s.views 这个条件很重要。没有它,MERGE 会更新并统计每一个匹配的行,于是重新运行时又会把它们全部再统计一遍。有了它,计数已经一致的匹配行不会被改动,也不会被统计。没有任何示例在去掉该条件的情况下运行此语句,所以关于不带条件时重新运行的说法,是对语句的推断,而不是实测的计数。
1"""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."""23import pyarrow as pa45from lib.lakehouse import hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error67# The WHEN MATCHED condition is what keeps a rerun from counting matched rows that already agree.8MERGE = """9MERGE INTO lake.wiki.pageviews_hourly AS t10USING batch AS s11ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title12WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views13WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)14 VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)15"""161718def main():19 with new_warehouse() as wh:20 _, _, lake = load_three_formats(wh, range(6))21 corrected = hour_corrections(read_cut_hour(2))22 # a copy of the hour's first row, with a page title the table never held23 late = corrected.slice(0, 1).set_column(24 corrected.schema.get_field_index("page_title"),25 "page_title",26 pa.array(["Late_arrival_example"], type=pa.string()),27 )28 lake.register("batch", pa.concat_tables([corrected, late]))29 print("source rows:", lake.execute("SELECT count(*) FROM batch").fetchall()[0][0])30 # the join counts source rows whose key the table already holds, which splits the changes into inserts and updates31 matched = lake.execute(32 "SELECT count(*) FROM lake.wiki.pageviews_hourly AS t JOIN batch AS s "33 "ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title"34 ).fetchall()[0][0]35 print("source rows matched:", matched)36 # DuckDB returns one count for a MERGE, with updates and inserts added together37 print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])38 print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])39 print("rows in table:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])404142if __name__ == "__main__":43 try:44 main()45 except Exception as exc: # the redacted error is what a reader sees46 print(safe_error(exc))47 raise SystemExit(1)
python merge-correction-ducklake.pysource rows: 501
source rows matched: 500
rows changed by the merge: 21
rows changed by the same merge again: 0
rows in table: 3000
与 upsert 一样,这个合并可以放心地重新运行:第一次改动了 21 行,第二次改动了 0 行,最终表中有 3000 行,即那六个小时加上迟到的那一行。
改动行数把两种改动加在了一起,所以要区分二者,还需要知道源的大小。批次中的 501 行里,有 500 行与表中已有的键匹配,所以唯一没有匹配的就是迟到的那一行。插入的行数等于源行数减去匹配的行数,这里是 501 减 500。更新的行数等于改动的行数减去插入的行数。只有匹配行数,则两个数字都得不到。
如第 1 章所述,这些示例在挂载 DuckLake 时关闭了内联,因此每次加载写入的是数据文件,而不是目录中的行。合并的计数针对的是行。
工作准则: 编写 MERGE 时,配上一个 WHEN MATCHED AND 条件,比较你要更新的各列,这样重新运行就不会改变任何内容。运行之前,先确保源中每个键只出现一次。DuckLake 会拒绝把一个目标行匹配到两个源行的合并,生产环境一节展示了这个错误。
将变更后的标签合并到域名表
本书的第二张表 wiki.domains 把每个域名代码映射到一种语言。维度表存放的是标签而非计数,当有少量标签同时变化时,适合使用合并。这里要说明的是,同样形式的语句可以用于维度表,关于条件的规则在这里同样适用。
UPDATE 可以修改标签。MERGE 形式还有 WHEN NOT MATCHED 子句,用于插入表中尚不存在的域名代码,因此新增域名的数据源也可以使用同一条语句。这张表只存在于 DuckLake 中,所以合并在 DuckLake 中运行。页面浏览量表的合并在 DuckLake 中进行,见 DuckLake 中的 MERGE。在流水线中,你在这里合并的标签,就是第 8 章的连接所返回的标签。
1"""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."""23import pyarrow as pa45from lib.lakehouse import attach_ducklake, connect_ducklake, print_rows, read_domains, new_warehouse, safe_error67MERGE = """8MERGE INTO lake.wiki.domains AS t9USING labels AS s10ON t.domain_code = s.domain_code11WHEN MATCHED AND t.language <> s.language THEN UPDATE SET language = s.language12WHEN NOT MATCHED THEN INSERT (domain_code, language) VALUES (s.domain_code, s.language)13"""141516def main():17 with new_warehouse() as wh:18 lake = connect_ducklake(wh.con)19 attach_ducklake(lake, "lake", wh.root, inlining_row_limit=0) # inlining off, so the rows land in data files20 lake.execute("CREATE SCHEMA lake.wiki")21 lake.execute("CREATE TABLE lake.wiki.domains (domain_code VARCHAR, language VARCHAR)")22 lake.register("dims", read_domains())23 lake.execute("INSERT INTO lake.wiki.domains SELECT * FROM dims")2425 # 'fr' is already in the table, so this label is an update and not an insert26 lake.register("labels", pa.table({"domain_code": ["fr"], "language": ["French (France)"]}))27 print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])28 print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])29 print_rows(lake.execute("SELECT domain_code, language FROM lake.wiki.domains").fetchall()) # sorted, so the order DuckLake returns rows in does not matter303132if __name__ == "__main__":33 try:34 main()35 except Exception as exc: # the redacted error is what a reader sees36 print(safe_error(exc))37 raise SystemExit(1)
python merge-domains-label.pyrows 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 条件进行合并,并再运行一次合并,以确认它没有再更改任何内容。标签的变更会影响所有与该维度表连接的查询,因此在合并之前,请先审核即将合并的标签。
生产中会出什么问题
在实际中,更正操作通常因三类问题出错:重复的键、逐行删除的循环,以及让阅读历史记录的人感到意外的快照数量。这些问题在写入之前检查的成本很低,事后发现的成本却很高,因此检查应放在写入该批数据的作业中。
批次中的重复键。 更正被发送了两次,或者错误的连接产生了两行相同的键,都会使该批次无效。本示例把同一行更正数据发送了两次,pyiceberg 在写入任何内容之前就拒绝了这次 upsert。
1"""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."""23import pyarrow as pa45from lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, scan_arrow67MERGE = """8MERGE INTO lake.wiki.pageviews_hourly AS t9USING batch AS s10ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title11WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views12WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)13 VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)14"""151617def main():18 with new_warehouse() as wh:19 iceberg, _, lake = load_three_formats(wh, range(6))20 corrected = hour_corrections(read_cut_hour(2))21 # A corrected row, sent twice: the batch holds two rows with one key.22 before = read_cut_hour(2)["views"].to_pylist() # the cut's views, to find the rows the correction changed23 changed = [i for i, after in enumerate(corrected["views"].to_pylist()) if after != before[i]]24 doubled = pa.concat_tables([corrected, corrected.slice(changed[0], 1)])2526 try:27 iceberg.upsert(doubled, join_cols=list(KEY))28 print("Iceberg upsert: no error")29 except Exception as exc:30 print("Iceberg upsert:", safe_error(exc, wh)) # safe_error replaces the warehouse path, so no local path is printed31 print("Iceberg rows after the failed upsert:", scan_arrow(iceberg).num_rows)3233 lake.register("batch", doubled)34 try:35 lake.execute(MERGE)36 print("DuckLake merge: no error")37 except Exception as exc:38 print("DuckLake merge:", safe_error(exc, wh))39 print("DuckLake rows after the failed merge:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])404142if __name__ == "__main__":43 try:44 main()45 except Exception as exc: # the redacted error is what a reader sees46 print(safe_error(exc))47 raise SystemExit(1)
python duplicate-source-keys.pyIceberg 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 最多三个),则说明存在非幂等的重试,例如把一次追加发送了两次。拒绝以异常的形式出现,因此捕获异常后继续运行的作业看起来会很正常,而它的表却停留在旧状态。
工作准则: 在任何写入之前检查批次中是否有重复的键,并把拒绝视为批次中的缺陷,而不是暂时性故障。把删除合并为一次写入。审计更正时,根据快照列表统计快照数量。
本章做出的决定
- 更正按键
hour_utc、domain_code和page_title进行匹配,一个批次在写入之前,每个键只能出现一次。 - 删除示例使用 180 次浏览作为阈值,因为这次截取在每个小时内只保留浏览量最高的行,所以阈值取 2 时不会匹配到任何行。
- Iceberg 和 Delta Lake 的删除采用写时复制,它们的示例会显示被重写的文件。DuckLake 的
DELETE则写入一个删除文件,并保持数据文件不动,同一个示例也展示了这一点。 - 本章不使用删除向量。在所固定的版本下,deltalake 1.6.6 不会写入删除向量,而启用该特性会使
to_pyarrow_table和to_pyarrow_dataset拒绝读取该表。 - 更新插入(upsert)和合并(merge)通过运行两次来检验:第二次运行不会改变任何行。
- 快照数量从快照列表中读取。本章不把一次调用等同于一次提交。
- DuckLake 的示例关闭了内联,如第 1 章所述,因此计数描述的是数据文件。
- 更正数据是在代码中构造的,迟到的行也是生成的,所以这些示例展示的是写入路径,而不是一次真实更正的历史。
- 重复键示例展示的是拒绝本身,而不是对它的描述,因此读者看到的输出中包含这次失败。
- 本章没有 Delta Lake 的更新插入或合并示例,所以除删除之外,本章不对 deltalake 的合并作任何断言。
- 迟到的行与更正共用一个批次,因此一次调用就能同时展示一次更新和一次插入。
本章,在本版次中
版次 2026.11.10示例于 2026年11月20日 通过本章 5个示例 个示例中有 5 个通过
译自英文版图书,版次 2026.11.10。