1A Dataset is a table

Registering a Dataset gives SQL a table with one row per grid cell: a column for each dimension and one for each data variable. Nothing is copied. Each chunk becomes a partition of the table, read only when a query needs it. Hover over the grid or the table to match cells to rows.

import duckdb, xarray as xr, xarray_sql as xql

era5 = xr.open_zarr(
    "gs://gcp-public-data-arco-era5/ar/full_37-1h-0p25deg-chunk-1.zarr-v3",
    chunks=None, storage_options={"token": "anon"},
)
# A grid small enough to see: 12:00 UTC on 8 days, 6 latitudes, 8 longitudes.
ds = era5[["2m_temperature"]].rename({"2m_temperature": "t2m"}).sel(
    time=xr.date_range("2024-07-01T12", periods=8, freq="D"),
    latitude=[75, 45, 15, -15, -45, -75],
    longitude=range(0, 360, 45),
)
con = duckdb.connect()
xql.register(con, "era5", ds, chunks={"time": 4, "latitude": 3, "longitude": 4})

2SQL reads only the chunks it needs

Filters on dimension columns are pushed down to the table, and xarray-sql compares them with each partition's coordinate range. A partition that cannot match is skipped, so its chunk is never read. Pick a query.

Partitions follow chunks=. ARCO-ERA5 stores each hour of the whole globe as one chunk, so registered with its storage chunks, a time filter would still skip chunks but a bounding box could not.

3Results come back as a Dataset

to_dataset pivots the result rows back onto a grid. The dimension columns that survive the query become the dimensions, and the registered Dataset, passed as the template, restores attributes such as units.

out = xql.to_dataset(con.sql(query), template=ds)