Register tables in a catalog and query them from DuckDB
阅读约需 42分钟 分钟,8节中含5个示例。
这部分尚未翻译,因此以英文显示。
In this chapter you register an Iceberg table in a SQL catalog, attach a DuckLake catalog, open a Delta Lake table by its path, and run one DuckDB statement that joins a table from Iceberg with a table from DuckLake. You also learn what each catalog holds, and what happens to a table when its catalog and its data files disagree.
The earlier chapters opened each table by its own route: a catalog object for Iceberg, a file for DuckLake, a folder for Delta Lake. The first table registered wiki.pageviews_hourly in an Iceberg SQL catalog under the name local. This chapter explains what that name does, and what each format uses in its place. It also adds a second table, wiki.domains. It is a 4-row list of domain codes and the language each one is in. That table lives in the DuckLake catalog, and the join section joins it to the Iceberg pageviews.
目录保存什么
要问 wiki.pageviews_hourly 存放在哪里,答案取决于表格式。在底层,每种格式都把表存为数据文件加元数据文件。元数据说明表的当前状态:它的模式、分区规范,以及(直接或通过清单文件给出的)当前快照或版本所包含的数据文件。每次提交都会写入新的元数据,并使表指向这份元数据。目录是一份登记簿,把表名映射到该表当前元数据所在的位置。读取方向目录查询 wiki.pageviews_hourly,得到的是指向元数据的指针,然后再去读取元数据所列出的数据文件。
目录保存的是指针,而不是数据行。Parquet 文件仍留在仓库文件夹中,目录只指明从哪里开始读取。名称还有第二个层级:wiki 是一个命名空间,本书中每个 Iceberg 表名,都是命名空间与表名以点号相连而成。
选择目录归结为两个问题:谁写入这张表,什么来读取它。若一张表只由一个作业写入、一个引擎读取,它可以放在基于文件的目录中,通过路径找到。若一张表要被多个引擎读取,就需要一个它们都能解析的名称,这正是 SQL 目录或某种服务所提供的。我们建议每张表只使用一个目录,并在表旁保留一条记录,写明它所属的目录和负责人。跨目录的连接,如连接一节所示,可以在同一个会话中进行,但这并不会使两个目录合而为一。
本书中的三种格式使用三种不同的目录:
| 格式 | 由什么来命名表 | 当前元数据的位置 |
|---|---|---|
| Iceberg | 一个 SQL 目录对象,此处命名为 local |
目录的数据库,其中指向元数据文件 |
| DuckLake | 按路径挂载的目录文件 | 目录文件内部 |
| Delta Lake | 无;表的文件夹路径即是名称 | 表的文件夹中 |
Iceberg REST 目录是另一种类型的目录,它是一项服务,而不是一个文件。其 REST 目录页面描述了一套通用的 API,以 OpenAPI 规范定义,用于与任何 Iceberg 目录交互,因此客户端只需一种实现即可对接所有目录。同一页面指出,该协议采用基于变更的提交,因此服务可以在其一侧解决冲突并重试,并且由目录服务写入根元数据。本章不运行 REST 目录。每个 REST 目录都需要一个运行中的服务,而这里的示例是离线运行的,不依赖任何服务。本书不比较实现该协议的各种产品。
目录所保存的可以不止名称。分类和保留规则同样是表的元数据。Data Governance as Code讨论了它们,本书不讲授这部分内容。
**工作准则:**在向一张表写入之前,先弄清是哪个目录或路径为它命名,并且在备份数据文件夹的同时备份该目录。Parquet 文件并不为表命名,命名的是目录,如故障一节所示。
在 SQL 目录中注册表
pyiceberg 的 SqlCatalog 把表保存在数据库中。你需要为该数据库提供 uri,并为存放数据文件和元数据的文件夹提供 warehouse。本书使用一个 SQLite 文件作为数据库,使用一个本地文件夹作为仓库。pyiceberg 配置页面指出,SQL 目录背后需要有一个数据库,并提醒 SQLite 并非为并发而设计,适合探索或开发用途。这些示例用于开发,因此这一限制不会影响其中任何内容。生产环境的目录需要一个可供多个写入方使用的数据库。
下面的示例用同一个名称第二次打开同一个目录文件,并通过第二个对象读取该表。随后,它为该表的元数据注册第二个名称,并展示名称不同的目录对象能看到什么。
1"""Register an Iceberg table in a SQL catalog, then reach it through a second catalog object, a stale object and a2second name. The point rests on the 'loaded object' and 'after a reload' lines, and the last line shows what an3object under another catalog name can see."""45from lib.lakehouse import (ICEBERG_ID, NAMESPACE, create_iceberg_table, new_warehouse,6 open_catalog, read_cut_hour, safe_error, scan_arrow)78with new_warehouse() as wh:9 table = create_iceberg_table(wh)10 for hour in range(3): # hours 0, 1 and 2 of the cut, one commit each11 table.append(read_cut_hour(hour))1213 # A second object with the same catalog name sees the same tables.14 same = open_catalog(wh) # the default name, local, as the first object had15 loaded = same.load_table(ICEBERG_ID)16 print("rows read through the second object:", scan_arrow(loaded).num_rows)1718 # A loaded table is a snapshot of the catalog as it was when loaded.19 stale = same.load_table(ICEBERG_ID)20 table.append(read_cut_hour(3)) # the fourth hour, committed after stale was loaded21 print("rows in the loaded object after another append:", scan_arrow(stale).num_rows)22 print("rows after a reload:", scan_arrow(same.load_table(ICEBERG_ID)).num_rows)2324 # Register the same metadata file under a new name: a pointer, not a copy.25 same.create_namespace("archive")26 same.register_table("archive.pageviews_copy", table.metadata_location)27 print("rows in archive copy:", scan_arrow(same.load_table("archive.pageviews_copy")).num_rows)2829 # An object with another name does not see the namespace: the SQL catalog keeps tables apart by catalog name.30 try:31 open_catalog(wh, name="other").list_tables(NAMESPACE)32 except Exception as exc:33 print("other name:", safe_error(exc, wh))
python register-iceberg-table.pyrows read through the second object: 1499
rows in the loaded object after another append: 1499
rows after a reload: 1999
rows in archive copy: 1999
other name: NoSuchNamespaceError: Namespace does not exist: wiki
第二个对象读到 wiki.pageviews_hourly 的 1499 行,即第 1 章写入的三个小时的数据。pyiceberg 把每张表存放在你传给 SqlCatalog 的目录名称之下。名称相同的对象能找到相同的表。
目录和仓库文件夹是一对。目录保存元数据文件的位置,而这些位置位于 warehouse 属性所指定的文件夹之下。只有目录文件而没有仓库文件夹,或者只有文件夹而没有目录文件,得到的都是指向不存在文件的指针。请同时备份两者,并一起迁移。
每个进程打开自己的目录对象,彼此不共享状态。这里用同一进程中的两个对象代表两个进程。
已加载的表会保持加载时的状态。在第四个小时的追加写入之前取得的对象仍然读到 1499 行,而重新加载后读到 1999 行。一个进程如果在开始时加载表,在结束时才读取,读到的就是开始时的状态。它必须重新加载,才能看到期间提交的变更。
工作准则: 在代码的各处都用同一个名称打开 SQL 目录文件;当自加载以来可能有其他写入方提交过变更时,先重新加载表再读取。SQLite 只用于同一时刻只有一个写入方的场景;在运行多个写入方之前,请改用为并发写入而设计的数据库。
通过元数据文件注册表
register_table(identifier, metadata_location) 会为一个已有的元数据文件添加一个名称。pyiceberg API 页面将它列为通过元数据注册表的方法,并说明传入 overwrite=True 会替换已存在的条目。该示例把这张表的元数据注册为 archive.pageviews_copy,该命名空间由示例先行创建。归档名称读取的行与原表相同,共 1999 行。两个名称指向同一份元数据,而且注册过程不写入任何数据文件。
注册得到的名称是通向同一张表的第二条途径,而不是第二张表。通过其中一个名称提交的内容,就是两个名称共同的提交,因为二者读取的是同一份元数据。应把第二个名称当作读取途径,而不要当作备份。
工作准则: 每张表只保留一个写入名称。只有为了之后会移除的读取用途,才把元数据文件注册为第二个名称;只有确定要替换该条目时,才传入 overwrite=True。
这部分尚未翻译,因此以英文显示。
Attach a DuckLake catalog
DuckLake's catalog is a database file, and DuckDB attaches it with an ATTACH statement that starts with ducklake: and gives the file's path. The data path, the folder where DuckLake writes Parquet files, is stored in the catalog. The DuckLake connecting page says the data storage location only has to be given when a new DuckLake is created, and that the location is loaded from the catalog database after that. The helper connect_ducklake loads DuckLake into the DuckDB session with downloads off, and attach_ducklake runs the ATTACH for a catalog file under a folder.
The next example creates wiki.domains in a fresh DuckLake catalog, detaches it, and attaches the file again with no data path. It then lists the tables in the wiki schema of the file.
1"""Attach a DuckLake catalog file, detach it, and attach it again from the file alone. The point rests on the2'rows after re-attach' line, and the table list shows what the catalog holds, not what the data folder holds."""34from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, attach_ducklake, connect_ducklake,5 create_ducklake_domains, new_warehouse, print_rows, sql_rows)67with new_warehouse() as wh:8 con = connect_ducklake(wh.con)9 create_ducklake_domains(con, wh.root) # inlining is off, so the rows become Parquet files10 print("rows written:", sql_rows(con, f"SELECT count(*) FROM {DUCKLAKE_ALIAS}.{NAMESPACE}.domains")[0][0])1112 # Detach, then attach with no DATA_PATH: the catalog file stores the data path.13 con.execute(f"DETACH {DUCKLAKE_ALIAS}")14 attach_ducklake(con, DUCKLAKE_ALIAS, wh.root)15 print("tables in the catalog:")16 print_rows(sql_rows(con, f"""SELECT schema_name, table_name FROM duckdb_tables()17 WHERE database_name = '{DUCKLAKE_ALIAS}' AND schema_name = '{NAMESPACE}'"""))18 print("rows after re-attach:", sql_rows(con, f"SELECT count(*) FROM {DUCKLAKE_ALIAS}.{NAMESPACE}.domains")[0][0])
python attach-ducklake.pyrows written: 4
tables in the catalog:
wiki domains
rows after re-attach: 4
The 4 rows of wiki.domains are written, and 4 rows read back after the re-attach. The table list shows one table in the wiki schema, domains. The second attach needed no data path, because the file already held it. The listing comes from the catalog, not from the data folder. The catalog file holds the list of tables, and the data folder holds the bytes. Losing the first loses the names even when the second is intact, as the failure section shows.
The same connecting page says that DuckLake inlines small inserts into the catalog database, with a default limit of 10 rows. The helper this chapter uses sets the inlining limit to 0, so every insert in these examples becomes a Parquet file in the data folder, the setting chapter 1 explains. Without that setting, a small table lives in the catalog file, and a copy of the data folder would not hold its rows. That is one reason the catalog file needs a backup of its own.
Working rule: Back up a DuckLake catalog file with the data folder, and copy the file while no session has it attached. Give the data path when you create a catalog, and attach later with the catalog file alone.
通过路径查找表,不使用目录
本书中的 Delta Lake 表就是一个文件夹。它没有目录,本书的配置中也没有任何东西会用路径之外的方式给它命名。deltalake 包通过 DeltaTable(path) 打开该文件夹,每一次读取都经由这个路径。下一个示例中的三次追加写入分别对应版本 0、1 和 2。
1"""Open a Delta Lake table by its path, with no catalog, and read its first version and its current one. The point2rests on the 'rows at that version' and 'rows at version 0' lines, and the missing-path line shows the error."""34from deltalake import DeltaTable, write_deltalake56from lib.lakehouse import new_warehouse, read_cut_hour, read_delta78with new_warehouse() as wh:9 path = str(wh.root / "delta" / "pageviews_hourly")10 for hour in range(3):11 write_deltalake(path, read_cut_hour(hour), mode="append") # each append is one version, counted from 01213 table = DeltaTable(path)14 print("delta version:", table.version())15 print("rows at that version:", read_delta(path).num_rows)16 print("rows at version 0:", read_delta(path, version=0).num_rows)1718 # The path is the only name a Delta table has here: a wrong path is a missing table.19 try:20 DeltaTable(str(wh.root / "delta" / "pageviews_typo")) # a path never written, so no log to open21 print("missing path: opened")22 except Exception as exc:23 print("missing path:", type(exc).__name__)
python open-delta-by-path.pydelta version: 2
rows at that version: 1499
rows at version 0: 499
missing path: TableNotFoundError
该表的当前版本是 2,读取到 1499 行。版本 0 有 499 行,仅为第一个小时的数据。这里的行通过 read_delta 读取,它返回一个 Arrow 表。本章末尾的决策说明了本书为何以这种方式读取 Delta 表。
路径就是完整的名称,所以路径中的拼写错误就等于表不存在。该示例打开了一个不存在的文件夹,DeltaTable 随即抛出 TableNotFoundError。错误信息会给出它尝试过的路径,但这要等到错误的路径已被使用之后,因此检查应当放在构建路径的代码中。
对于由一个作业写入、少数几个作业读取的表,路径是一个不错的名称。它不需要任何服务,也无需任何设置。不过它也有代价。路径并不说明表属于哪个命名空间,因此位于不同位置、文件夹名相同的两张表,在读者看来并无区别。想要按名称查找该表的引擎则无处可查。本书中的 Delta 表规模小且位于本地,路径对它们已经足够。由多个引擎读取的共享数据湖,则需要为其 Delta Lake 表配备目录,而本书不讲授这一点。
工作准则: 将每个 Delta 表的路径放在代码读取的一个固定位置,例如一个配置值,并在作业开始时打开该表一次,以确认路径无误。不存在的路径是错误,而不是一张空表。
跨两个目录连接表
DuckDB 可以在一条语句中同时查询由某个库交给它的 Arrow 表,以及挂载到同一会话的 DuckLake 表。Iceberg 表通过 scan_arrow 从 pyiceberg 中以 Arrow 表的形式取出。DuckDB 用一个名称注册这张表,连接时再通过已挂载的目录读取 wiki.domains。本书不为此加载 DuckDB 的 iceberg 扩展,因此 Iceberg 一侧就是这张 Arrow 表,别无其他。
1"""Join an Iceberg table, read through Arrow, with a DuckLake table in one DuckDB statement. The point rests on the2'rows with no language' line, and the top-three block shows which rows the join returns first."""34from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, connect_ducklake, create_iceberg_table,5 create_ducklake_domains, new_warehouse, print_rows, read_cut_hour,6 scan_arrow, sql_rows)78with new_warehouse() as wh:9 table = create_iceberg_table(wh)10 for hour in range(3): # hours 0, 1 and 2 of the cut11 table.append(read_cut_hour(hour))12 con = connect_ducklake(wh.con)13 create_ducklake_domains(con, wh.root)1415 pageviews = scan_arrow(table) # read now: the join sees the table as it is at this line16 # sql_rows registers pageviews under that keyword name, so the SQL can refer to it as pageviews17 joined = f"FROM pageviews p JOIN {DUCKLAKE_ALIAS}.{NAMESPACE}.domains d ON p.domain_code = d.domain_code"18 print("joined rows:", sql_rows(con, f"SELECT count(*) {joined}", pageviews=pageviews)[0][0])19 # the left join keeps every pageview, and this condition counts the ones that found no domain row20 print("rows with no language:", sql_rows(con, f"""SELECT count(*) FROM pageviews p21 LEFT JOIN {DUCKLAKE_ALIAS}.{NAMESPACE}.domains d ON p.domain_code = d.domain_code22 WHERE d.domain_code IS NULL""", pageviews=pageviews)[0][0])23 print("top three by views:")24 print_rows(sql_rows(con, f"""SELECT p.domain_code, d.language, p.page_title, p.views {joined}25 ORDER BY p.views DESC, p.domain_code, p.page_title, p.hour_utc LIMIT 3""", pageviews=pageviews, keep_order=True),26 keep_order=True)
python join-pageviews-and-domains.pyjoined rows: 1499
rows with no language: 0
top three by views:
en English Main_Page 64859
en English Main_Page 64564
en English Main_Page 61107
页面浏览数据中的每个域名代码都出现在含 4 行的维度表中。该连接把 1499 行页面浏览记录匹配到某种语言,另有 0 行没有匹配到语言。按浏览量排在前三的是同一个页面,即英文域名下的 Main_Page。
连接键是 domain_code,两侧均为字符串。如果连接键在大小写或末尾空格上不一致,就什么也匹配不到:连接后的行数会降为 0,没有语言的行数则会等于该表的总行数。在信任一次连接之前,应先在同一会话中检查两侧的键。
这种查询之所以可行,是因为两侧在同一个 DuckDB 会话中汇合。但它并不会把两张表变成一张表。每张表仍保有各自的提交,而 Arrow 表是 scan_arrow 运行那一刻 Iceberg 表的快照。对一张正被其他写入方修改的表做连接,读到的是那一刻的当前状态。
对于这类查找,我们推荐使用跨目录连接:小型维度表留在自己的目录中,由针对大表的查询来读取。如果某张表几乎出现在所有读取另一张表的查询中,就把它复制到这些查询所用的目录里。第 9 章在不同格式之间复制表,并检查每一份副本,在把任何表迁移到别处之前,都应先执行这一检查。
工作准则: 只有当作为 Arrow 读取的一侧能放进内存时,才在同一会话中跨目录连接。读取之前先选定它的状态,并统计找不到匹配的行数,这个数应为零。
这部分尚未翻译,因此以英文显示。
What goes wrong in production
The symptoms are usually small: a count that stops moving, a namespace that is missing, or a read error on a table that was read before. Each case here starts from one of them.
Only the last failure below has no example.
The wrong catalog name. The last line of register-iceberg-table opens the same file under the name other. Listing the namespace then fails with NoSuchNamespaceError: Namespace does not exist: wiki. The table is still in the file. The SQL catalog scopes its tables by catalog name, so the second name sees an empty catalog. The message names the namespace, not the name, so the error looks like a missing schema. A job that builds its catalog name from configuration can fail this way after a small edit. You notice it at the first read, not at the write, so log the catalog name at the start of the job.
The stale pointer. A process that loads a table and reads it much later reads the state from when it loaded. register-iceberg-table showed 1499 rows in the loaded object, where a reload shows 1999. Nothing fails. The job runs and reports fewer rows than the table holds. You notice it when two jobs disagree about a count, so keep the load next to the read.
The catalog file replaced by an empty one. The next example attaches a fresh, empty catalog file to the data folder of a live catalog, then runs ducklake_delete_orphaned_files with cleanup_all => true. The DuckLake cleanup page shows cleanup_all deleting all orphaned files, and dry_run listing them instead. It defines an orphan as a file that the catalog you run it against does not track. A fresh catalog tracks nothing, so every Parquet file in the folder is an orphan to it.
1"""Run cleanup_all against a fresh, empty DuckLake catalog that shares a data folder with a live one. The point rests2on the 'parquet files after cleanup_all' line and the 'reading the live catalog' line."""34from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, connect_ducklake, create_ducklake_domains,5 new_warehouse, sql_rows)67# One statement, run twice: first with dry_run => true, then for real. {dry} holds the dry-run argument or nothing.8CLEANUP = "CALL ducklake_delete_orphaned_files('fresh', cleanup_all => true{dry})"910def parquet_count(root):11 # the data folder that attach_ducklake gives the catalog with the live alias; rglob searches it at any depth12 return len(list((root / f"{DUCKLAKE_ALIAS}_data").rglob("*.parquet")))1314with new_warehouse() as wh:15 con = connect_ducklake(wh.con)16 create_ducklake_domains(con, wh.root)17 print("parquet files, live catalog:", parquet_count(wh.root))1819 # A replacement catalog: a new, empty file that points at the same data folder.20 con.execute(f"DETACH {DUCKLAKE_ALIAS}")21 # Attached by hand: attach_ducklake would give fresh its own data folder, not the live one.22 con.execute(f"ATTACH 'ducklake:{wh.root}/fresh.ducklake' AS fresh (DATA_PATH '{wh.root}/{DUCKLAKE_ALIAS}_data/')")23 print("files the dry run lists:", len(sql_rows(con, CLEANUP.format(dry=", dry_run => true"))))24 con.execute(CLEANUP.format(dry=""))25 print("parquet files after cleanup_all:", parquet_count(wh.root))2627 # The live catalog still names the table, and its data files are gone.28 con.execute(f"ATTACH 'ducklake:{wh.root}/{DUCKLAKE_ALIAS}.ducklake' AS live")29 print("count through the live catalog:", sql_rows(con, f"SELECT count(*) FROM live.{NAMESPACE}.domains")[0][0])30 try:31 print("reading the live catalog:", len(sql_rows(con, f"SELECT domain_code, language FROM live.{NAMESPACE}.domains")))32 except Exception as exc:33 print("reading the live catalog:", type(exc).__name__)
python ducklake-orphan-cleanup-hazard.pyparquet files, live catalog: 1
files the dry run lists: 1
parquet files after cleanup_all: 0
count through the live catalog: 4
reading the live catalog: IOException
A fresh catalog is enough to delete the live data. Before the cleanup, the live catalog's data is in 1 Parquet file. The dry run with cleanup_all lists 1 file, which the real run deletes, and afterwards the folder holds 0 Parquet files.
A count check passes here, and that is the trap: it does not show missing files. The live catalog still names the table, and it answers count(*) with 4, from the catalog's statistics. Reading its columns fails with IOException.
The safe order begins with what the example skipped. Back up the catalog file first. Run the cleanup with dry_run => true against the catalog that owns the data folder, and read the list. Run it for real only when every listed file is one you know is orphaned. Never run cleanup_all => true against a catalog you have not confirmed.
Two writers on one SQLite catalog. The pyiceberg documentation says SQLite is not built for concurrency. This chapter does not show what two writers do to the SQL catalog, so the book does not claim that they are safe. The stale-pointer example is the nearest case it does show: an object loaded before a commit reads the older state, and no error is raised. A writer that holds an old object does not write against that old state. At the pinned version of pyiceberg, an append through such an object is retried against the new metadata and commits on top of the newer state. The book makes no claim for other operations, or for two processes on one SQLite catalog. The reload rule in the SQL catalog section applies to reads.
Working rule: Run an orphan cleanup with dry_run => true first, against the catalog that owns the data folder, and read the list before any real run. If the catalog file is missing or empty, restore it before you run anything that deletes files.
本章做出的决定
- Iceberg 注册在基于 SQLite 的 pyiceberg SQL 目录中,仓库位于本地。 本书只使用一个目录名称
local,所有示例都用它。pyiceberg 文档将 SQLite 描述为适用于开发的方案,而本书的示例都是开发示例。 - Iceberg 表以 Arrow 表的形式进入 DuckDB。 本书不使用 DuckDB 的
iceberg扩展,因此示例通过scan_arrow读取 Iceberg 数据。 - Delta Lake 表按路径查找,本书不讲授 Delta 目录。 每个 Delta 示例都打开一个文件夹,文件夹不存在即为错误。
- Delta 表只通过
QueryBuilder读取。 这是本书构建过程中的一项测量结果,不是一手资料。在固定的版本下,其他 Arrow 转换方法在 20 次运行中约有 17 次在退出时使进程异常中止。QueryBuilder路径在 24 次运行中一次也没有中止,其中包括对早期版本的读取。 - DuckLake 通过
connect_ducklake加载,并关闭下载。 每个示例都能在固定版本的 DuckDB 上离线运行。 - 清理隐患在临时文件夹中运行,并且只打印数量。 本书展示这一失败过程,是为了让安全步骤的顺序清晰。本书不打印文件名,因为文件名每次运行都会变化。
- 每个示例只打印数量和带标签的值, 原因见第 1 章:这样输出在每次运行、每台机器上都相同。
- REST 目录没有可运行的示例。 它们需要网络,因此本章根据其页面描述该协议,不对任何产品作出断言。
本章,在本版次中
版次 2026.11.10示例于 2026年11月20日 通过本章 5个示例 个示例中有 5 个通过
译自英文版图书,版次 2026.11.10。