Skip to content

Commit 78f1c0d

Browse files
authored
Rewriting.
1 parent aafe07d commit 78f1c0d

1 file changed

Lines changed: 33 additions & 77 deletions

File tree

‎README.md‎

Lines changed: 33 additions & 77 deletions
Original file line numberDiff line numberDiff line change
@@ -16,18 +16,14 @@ pip install xarray-sql
1616

1717
## What is this?
1818

19-
This is an experiment to provide a SQL interface for array datasets.
19+
This project provides a SQL interface for array datasets.
2020
Succinctly, we "pivot" Xarray Datasets to treat them like tables so we can run
21-
SQL queries against them — on the query engine of your choice. xarray-sql
22-
translates data, not queries: it registers a lazy Dataset as a table on
23-
DataFusion (built in), DuckDB, or Polars, and turns any engine's Arrow result
24-
back into a labeled Dataset. Dialects, geometry functions, and optimizers stay
25-
with the engine.
21+
SQL queries against them (on the query engine of your choice).
2622

2723
## Quickstart
2824

2925
Open a Dataset, register it as a table with `from_dataset`, compute a
30-
climatology in SQL, then write the result back to Xarray and plot it:
26+
climatology in SQL, then write the result back to Xarray for plotting:
3127

3228
> **Note:** this example also needs `pooch` and a netCDF backend (for the
3329
> tutorial download) and `matplotlib` (for the plot):
@@ -62,11 +58,7 @@ clim_ds = clim.to_dataset(dims=["month"])
6258
clim_ds["air"].plot() # in a script, call matplotlib.pyplot.show() to display
6359
```
6460

65-
That's the round trip — Xarray in, SQL in the middle, Xarray (and a plot) back
66-
out.
67-
68-
The same Dataset registers on other engines with one call — DuckDB gets a
69-
native lazy table with predicate pushdown, Polars scans the same object:
61+
The same Dataset registers on other engines with one call:
7062

7163
```python
7264
import duckdb
@@ -82,10 +74,7 @@ Any database with an [ADBC](https://arrow.apache.org/adbc/) driver
8274
ingests the Dataset into a table there, and `xql.to_dataset(cursor, ...)`
8375
brings results back. Or go the other way without copying anything:
8476
`xql.serve({'air': ds})` starts an Arrow Flight SQL server that any Flight SQL
85-
client (ADBC, JDBC, ODBC) can query, with the same chunk pruning as in process.
86-
87-
`table_names` (below) works the same way on every engine, so a query written
88-
against `era5.surface` is not tied to the engine it was written for.
77+
client (ADBC, JDBC, ODBC) can query.
8978

9079
See [Engines](https://xqlsystems.github.io/xarray-sql/latest/engines/) for the support matrix, DuckDB/Polars/ADBC details,
9180
and the lazy chunked round-trip.
@@ -187,9 +176,6 @@ ctx.sql('''
187176
# valid_time_stop_era5t: 2026-06-14
188177
```
189178

190-
_(A runnable version of this example lives at
191-
[`perf_tests/era5_temp_profile.py`](https://github.com/xqlsystems/xarray-sql/blob/main/perf_tests/era5_temp_profile.py).)_
192-
193179
## Why build this?
194180

195181
A few reasons:
@@ -206,8 +192,8 @@ A few reasons:
206192
This is a light-weight way to prove the value of the interface.
207193

208194
The larger goal is to explore the hypothesis that the [Pangeo
209-
ecosystem is a scientific database](https://www.hytradboi.com/2025/c18b8cdc-fd17-4099-9c03-eb107217f627-pangeo-is-a-database). Here, xarray-sql can be thought of as a missing
210-
DB front end.
195+
ecosystem is a scientific database](https://www.hytradboi.com/2025/c18b8cdc-fd17-4099-9c03-eb107217f627-pangeo-is-a-database).
196+
Here, xarray-sql can be thought of as a missing DB front end.
211197

212198
## How does it work?
213199

@@ -227,36 +213,29 @@ DuckDB and Polars consume registered Datasets with no engine-specific code.
227213
Ultimately, the initial insight of the `pivot()` function -- that any ndarray can be
228214
translated into a 2D table -- underlies this performant query mechanism.
229215

216+
_Autumn 2026_: we take a leaf from LanceDB's book: we provide a common Arrow interface
217+
within DataFusion abstractions to perform the round trip journy between dataframe and Xarray Dataset.
218+
Then, we've built an extensible engine system to make it easy to add the next DB engine.
219+
230220
## Does it work?
231221

232-
Yes. The recurring worry is that the SQL interface is a toy — fine for `SELECT`s,
233-
but not for the operations geoscience actually runs. So we wrote a suite that
234-
takes the staples of geospatial and climate analysis — the ones we assume *need*
235-
an array library — and expresses each one in SQL, then **checks the SQL answer
236-
against an xarray/array reference** to floating-point tolerance:
237-
238-
* **Spectral indices** (NDVI) — column arithmetic over a real Sentinel-2 scene.
239-
* **Climatology, anomalies, zonal means** — `GROUP BY` and self-`JOIN` against
240-
the 0.25° **ARCO-ERA5** archive registered as a lazy table. Each query is
241-
bounded to a small window (a few days over a region) and reads only that
242-
slice — the point is that you can aim a query at a multi-decade archive and
243-
pay only for the data it asks for, not that the query scans the whole record.
244-
* **Forecast skill** — scoring the **Pangu-Weather** and **GraphCast** ML models
245-
against ERA5 (WeatherBench 2) as a `JOIN` on `valid_time = init + lead`; it
246-
reproduces the published result that GraphCast beats Pangu at every lead.
247-
* **Raster × vector zonal stats** — a range `JOIN` of the ERA5 grid against a
248-
table of regions.
249-
* **Reprojection and regridding** — a `reproject(x, y, src_crs, dst_crs)`
250-
scalar PROJ UDF, shipped as the optional geo extension
251-
(`pip install xarray-sql[geo]`, validated against Earth Engine's own
252-
geodesy via [Xee](https://github.com/google/Xee)) and a
253-
sparse-weight-table `JOIN` (regridding real SRTM terrain).
254-
255-
Every case matches its array reference. The headline finding: these operations
256-
are not really "array" operations at all — they are `GROUP BY`, `JOIN`, window
257-
functions, and `CASE` in disguise, and a query engine runs them at scale. See
258-
[`benchmarks/geospatial/`](https://github.com/xqlsystems/xarray-sql/tree/main/benchmarks/geospatial/) and the write-up,
259-
[Geospatial operations are relational operations](https://xqlsystems.github.io/xarray-sql/latest/geospatial/).
222+
Yes, and not half bad either. Our initial fear was that this was merely a toy —
223+
fine for `SELECT`s, but not for operations scientists actually run.
224+
225+
To build confidence in our hypothesis, we built a suite of benchmarks catering to the primary
226+
user base for Xarray – climate & weather scientists and geospatial practitioners. Luckily for us,
227+
there already was this [very helpful discussion](https://github.com/coiled/benchmarks/discussions/1545) initiated by Coiled that attempted to gather
228+
a representative range of geospatial queries (in order to improve Dask's performance on this category of workloads).
229+
230+
Based on this survey from experts in this domain, we create a SQL geospatial
231+
benchmark (as compared to native Xarray ops). You can read our claim and finings in "[Geospatial operations are relational operations](https://xql.systems/xarray-sql/latest/geospatial/)."
232+
233+
Some highlights:
234+
235+
* We benchmarked everything on real cloud-native datasets: ARCO-ERA5, Sentinel 2, Weatherbench 2, and Google Earth Engine via [Xee](https://github.com/google/Xee).
236+
* SQL provides an elegant (yet verbose) way to make every array-oriented query. Some queries, like calculating zonal mean statistics, are more elegant expressed in SQL than their Xarray counterparts.
237+
* Dense tensor operations are slower in SQL than Xarray. It seems like the vanilla HashJoin implementation in standard DB engines are not as performant as array ops (they don't take advantage of contiguous array layouts), though this gives us some idea for how it could [one day be possible](https://github.com/xqlsystems/einfold).
238+
* Sparse tensor operations are just as fast (or faster!) than Xarray today. Specifically, SQL is an excellent way to perform regridding and reprojecting between different spatial scales (see also: we include a `proj` udf via the `xarray-sql[geo]` extra).
260239

261240
## Why does this work?
262241

@@ -276,47 +255,24 @@ early users – "tire kickers", if you will. We'd love your input to shape the d
276255
project! Please, give this a try and [file issues](https://github.com/xqlsystems/xarray-sql/issues) as
277256
you see fit. Check out our [contributing guide](https://xqlsystems.github.io/xarray-sql/latest/contributing/), too 😉.
278257

279-
## What would a deeper integration look like?
280-
281-
I have a few ideas so far. One approach involves applying operations directly on
282-
Xarray Datasets. This approach is being pursued
283-
[here](https://github.com/google/weather-tools/tree/main/xql), as `xql`.
284-
285-
Deeper still: I was thinking we could make
286-
a [virtual](https://fsspec.github.io/kerchunk/)
287-
filesystem for parquet that would internally map to Zarr. Raster-backed virtual
288-
parquet would open up integrations to numerous tools like dask, pyarrow, duckdb,
289-
and BigQuery. More thoughts on this
290-
in [#4](https://github.com/xqlsystems/xarray-sql/issues/4).
291-
292-
_2025 update_: Something like this is being built across a few projects! The ones I know about are:
293-
294-
- [CartoDB's Raquet](https://github.com/CartoDB/raquet)
295-
- The DataFusion community's [arrow-zarr](https://github.com/datafusion-contrib/arrow-zarr)
296-
297-
_2026 update_: A colleague and I are experimenting with native Zarr RDBMS engines. Check out:
298-
299-
- [Zarr-Datafusion](https://lib.rs/crates/zarr-datafusion)
300-
- [DuckDB-Zarr](https://github.com/xqlsystems/duckdb-zarr)
301-
302258
## Roadmap
303259

304260
- [x] ~Lazy evaluation via the pyarrow Dataset interface [#93](https://github.com/xqlsystems/xarray-sql/issues/93).~ _Implemented in [#100](https://github.com/xqlsystems/xarray-sql/pull/100)_
305261
- [x] Support proper parallelism via proper partition handling on the rust/datafusion side. [#106](https://github.com/xqlsystems/xarray-sql/issues/106)
306262
- [x] Support core datafusion optimizations to scan less data, like [#104](https://github.com/xqlsystems/xarray-sql/issues/104), ...
307263
- [x] Translate a single Zarr to a collection of tables [#85](https://github.com/xqlsystems/xarray-sql/issues/85).
308-
- [ ] Distributed beyond a single node through the DataFusion integration with Ray Datasets [#68](https://github.com/xqlsystems/xarray-sql/issues/68) or Apache Ballista [#98](https://github.com/xqlsystems/xarray-sql/issues/98).
309-
- [ ] Demo: calculate Sea Surface Temperature from 1940 - Present in SQL [#36](https://github.com/xqlsystems/xarray-sql/issues/36).
310-
- [ ] Provide an option to integrate DataFusion directly to Zarr via Rust [#4](https://github.com/xqlsystems/xarray-sql/issues/4).
264+
- [ ] Distributed beyond a single node through the DataFusion integration (with Ray Datasets?) [#68](https://github.com/xqlsystems/xarray-sql/issues/68) or Apache Ballista [#98](https://github.com/xqlsystems/xarray-sql/issues/98).
265+
- [x] Demo: calculate Sea Surface Temperature from 1940 - Present in SQL [#36](https://github.com/xqlsystems/xarray-sql/issues/36).
266+
- [ ] ~Provide an option to integrate DataFusion directly to Zarr via Rust [#4](https://github.com/xqlsystems/xarray-sql/issues/4).~
311267
- [ ] (To be formally announced eventually): The 100 Trillion Row Challenge [#34](https://github.com/xqlsystems/xarray-sql/issues/34).
312268

313269
## Sponsors & Contributors
314270

315271
I want to give a special thanks to the following folks and institutions:
316272

317-
- Pramod Gupta and the Anthromet Team at Google Research for the problem
273+
- Pramod Gupta and the [Anthromet Team at Google Research](https://github.com/google-deepmind/weathernext) for the problem
318274
formation and design inspiration.
319-
- Jake Wall and AI2/Ecoscope for compute resources and key use cases.
275+
- Jake Wall and AI2/[Ecoscope](https://ecoscope.io/en/stable/) for compute resources and key use cases.
320276
- Charles Stern, Stephan Hoyer, Alexander Kmoch, Wei Ji, and Qiusheng Wu
321277
for the early review and discussion of this project.
322278
- Tom Nichols, Kyle Barron, Tom White, and Maxime Dion for the [Array Working

0 commit comments

Comments
 (0)