Shared design for DataTree tables with duckdb-zarr: one schema per node #262

Activity

1. alxmrs commented on Sep 29, 2026

   

   alxmrs

   on Sep 29, 2026

   MemberAuthor

   More actions

   Update from the duckdb-zarr side: duckdb-zarr won't mount a store as a catalog itself, at least for now.

   We spiked a CALL zarr_attach(store, alias) that creates one schema per node and one view per dimension group. The DuckDB 1.5.5 C API has no storage-extension hook and can't run SQL on the calling connection, so the procedure needs a second connection that it opens at load and keeps. That connection keeps the database alive after close(): the file stays locked, and reopening it in the same process hangs. Details are in xqlsystems/duckdb-zarr#52.

   This doesn't change the proposal above. duckdb-zarr will publish the mapping as columns of read_zarr_groups (group, dims, table_name), and users create the views from them. The part that has to match between the two projects is still the node → schema → table mapping and the table names. xarray-sql can implement it as a real CatalogProvider.
2. alxmrs commented on Oct 9, 2026

   

   alxmrs

   on Oct 9, 2026

   MemberAuthor

   More actions

   Two updates from implementing this in duckdb-zarr (xqlsystems/duckdb-zarr#52 for the tree, #56 for AnnData).

   The group parameter is group_path, not group. GROUP is a reserved word in DuckDB, so read_zarr(store, group := '...') doesn't parse. duckdb-zarr uses group_path :=, with the quoted alias "group" :=, the same pattern as its existing array_path / "array". In read_zarr_groups, the column is group_path and holds the DataTree path (/, /simulation/fine). A new schema_name column holds the schema each group maps to (main for the root, otherwise the path without the leading slash). Nothing else in the proposal above changed: one schema per group, tables named "_".join(dims), coordinates inherited from ancestors.

   AnnData: obs and var are not nodes after all. Real queries against the first version read badly: pbmc.obs.obs and pbmc.var.var, joined to X through an _index column. AnnData stores now map as follows (design decision 9 in duckdb-zarr):

   Element	Dimensions	Table
   X	obs, var	main.obs_var
   obs columns	obs	main.obs
   var columns	var	main.var
   layers/*	obs, var	layers.obs_var
   obsm/<k>	obs, <k>_component	obsm.obs_<k>_component
   obsp/*	obs_i, obs_j	obsp.obs_i_obs_j

   • obs and var are variables of the root node, next to X. This is the xarray Dataset view of an AnnData object: X(obs, var), cell_type(obs), gene_symbol(var).
   • Each data frame's index is the coordinate of its axis. The obs and var columns therefore hold cell and gene names in every table, inherited by layers, obsm and obsp.
   • Categoricals read as ENUMs, and sparse matrices read as one row per stored entry.

   If xarray-sql reads AnnData stores too, matching these rules would give the two projects the same tables. Examples of the queries this gives are in xqlsystems/duckdb-zarr#40.