Skip to main content

xarray-sql

Query Xarray with SQL

PyPI Version ci lint ci-build ci-rust PyPI Downloads PyPI Downloads Hacker News

pip install xarray-sql

What is this?

This project provides a SQL interface for array datasets. Succinctly, we "pivot" Xarray Datasets to treat them like tables so we can run SQL queries against them (on the query engine of your choice).

Quickstart

Open a Dataset, register it as a table with from_dataset, compute a climatology in SQL, then write the result back to Xarray for plotting:

Note: this example also needs pooch and a netCDF backend (for the tutorial download) and matplotlib (for the plot): pip install pooch netCDF4 matplotlib.

import xarray as xr
import xarray_sql as xql

# 4x-daily surface air temperature on a lat/lon grid, 2013-2014.
ds = xr.tutorial.open_dataset('air_temperature')

ctx = xql.Context()
ctx.from_dataset('air', ds, chunks=dict(time=100))

# A climatology — the mean annual cycle — computed in SQL: average air
# temperature for each month of the year, over all grid cells and years.
clim = ctx.sql('''
  SELECT
    CAST(date_part('month', "time") AS INTEGER) AS month,
    AVG("air") AS air
  FROM "air"
  GROUP BY CAST(date_part('month', "time") AS INTEGER)
  ORDER BY month
''')

# Round-trip the result back to Xarray. `month` is a derived column, so name
# it as the dimension.
clim_ds = clim.to_dataset(dims=["month"])

# Plot the annual cycle as a time series.
clim_ds["air"].plot()  # in a script, call matplotlib.pyplot.show() to display

The same Dataset registers on other engines with one call:

import duckdb

con = duckdb.connect()
xql.register(con, 'air', ds, chunks=dict(time=100))
rel = con.sql('SELECT time, AVG("air") AS air FROM air GROUP BY time ORDER BY time')
xql.to_dataset(rel, template=ds)   # any engine's Arrow result round-trips

Any database with an ADBC driver (PostgreSQL, SQLite, Snowflake, BigQuery, ...) works too: xql.register ingests the Dataset into a table there, and xql.to_dataset(cursor, ...) brings results back. Or go the other way without copying anything: xql.serve({'air': ds}) starts an Arrow Flight SQL server that any Flight SQL client (ADBC, JDBC, ODBC) can query.

See Engines for the support matrix, DuckDB/Polars/ADBC details, and the lazy chunked round-trip.

A bigger example: ARCO-ERA5

The same interface scales to cloud-native datasets with hundreds of variables, like ARCO-ERA5.

Note: reading from gs:// requires gcsfs (pip install gcsfs).

import xarray as xr
import xarray_sql as xql


# Open ARCO-ERA5 — a weather dataset with 273 variables since 1940. 
# Turning off dask means we don't have to wait to construct a task graph.
ds = xr.open_zarr(
  'gs://gcp-public-data-arco-era5/ar/full_37-1h-0p25deg-chunk-1.zarr-v3',
  chunks=None,  # Turn dask off
  storage_options={'token': 'anon'}  # Anonymous read from the public GCS bucket — no auth required.
)

ctx = xql.Context()
# `chunks` sets the partition size; without it, partitions follow the
# store's own chunks (one hour each here).
ctx.from_dataset('era5', ds, chunks=dict(time=6), table_names={
    ('time', 'latitude', 'longitude'): 'surface',
    ('time', 'level', 'latitude', 'longitude'): 'atmosphere',
})
# Registration takes ~10s on my machine.

# Heads up: ARCO-ERA5 has 262 surface + 11 atmospheric variables. The library
# pushes column projection down to Zarr, so SELECT only fetches what you ask
# for — but `SELECT * FROM era5.surface` would try to pull every variable
# across the year (terabytes from GCS). 
#  ---> Always SELECT specific columns. <---

# Average 2m-temperature over NYC on the morning of 2020-01-01. The library
# pushes WHERE clauses on dimension columns down to partition pruning.
ctx.sql('''
  SELECT AVG("2m_temperature") - 273.15 AS avg_c
  FROM era5.surface
  WHERE time BETWEEN TIMESTAMP '2020-01-01'
                 AND TIMESTAMP '2020-01-01 05:00:00'
    AND latitude  BETWEEN 39 AND 40
    AND longitude BETWEEN 286 AND 287  -- ERA5 uses 0-360 longitudes
''').to_pandas()
#       avg_c
# 0  8.640069

# Average temperature per pressure level, globally. 
result = ctx.sql('''
  SELECT level, AVG(temperature) - 273.15 AS avg_c
  FROM era5.atmosphere
  WHERE time BETWEEN TIMESTAMP '2020-01-01'
                 AND TIMESTAMP '2020-01-01 05:00:00'
  GROUP BY level
  ORDER BY level DESC
''')
# DataFrame()
# +-------+----------------------+
# | level | avg_c                |
# +-------+----------------------+
# | 1000  | 6.6210120796502565   |
# | 975   | 5.185637919348153    |
# | 950   | 4.028428657263021    |
# | 925   | 3.0828117974912743   |
# | 900   | 2.2109172992531967   |
# | 875   | 1.395017610194202    |
# | 850   | 0.6342670572626616   |
# | 825   | -0.21037158786759846 |
# | 800   | -1.1810754318269687  |
# | 775   | -2.3064649711534457  |
# +-------+----------------------+

# `latitude`/`longitude` are inferred from the registered table's surviving
# dims; `template` is kept only to recover metadata (attrs, encoding).
ctx.sql('''
  SELECT latitude, longitude, AVG("2m_temperature") - 273.15 AS avg_c
  FROM era5.surface
  WHERE time BETWEEN TIMESTAMP '2020-01-01'
                 AND TIMESTAMP '2020-01-01 05:00:00'
  GROUP BY latitude, longitude
  ORDER BY latitude DESC, longitude
''').to_dataset(template=ds)
# <xarray.Dataset> Size: 8MB
# Dimensions:    (latitude: 721, longitude: 1440)
# Coordinates:
#   * latitude   (latitude) float32 3kB 90.0 89.75 89.5 ... -89.5 -89.75 -90.0
#   * longitude  (longitude) float32 6kB 0.0 0.25 0.5 0.75 ... 359.2 359.5 359.8
# Data variables:
#     avg_c      (latitude, longitude) float64 8MB -26.84 -26.84 ... -27.38 -27.38
# Attributes:
#     last_updated:           2026-06-20 02:33:34.265980+00:00
#     valid_time_start:       1940-01-01
#     valid_time_stop:        2025-12-31
#     valid_time_stop_era5t:  2026-06-14

Why build this?

A few reasons:

  • Even though SQL is the lingua franca of data, scientific datasets are often inaccessible to non-scientists (SQL users).
  • Joining tabular data with raster data is common yet difficult. It could be easy.
  • There are many cloud-native, Xarray-openable datasets, from Google Earth Engine to the Source Cooperative. Wouldn’t it be great if these were also SQL-accessible? How can the bridge be built with minimal effort?

This is a light-weight way to prove the value of the interface.

The larger goal is to explore the hypothesis that the Pangeo ecosystem is a scientific database. Here, xarray-sql can be thought of as a missing DB front end.

How does it work?

All chunks in a Xarray Dataset are transformed into a Dask DataFrame via from_map() and to_dataframe(). For SQL support, we just use dask-sql. That's it!

2025 update: This library now implements a Dask-like from_map interface in pure DataFusion and PyArrow, but works with the same principle!

2026 update: Instead of from_map(), we create a way to translate Xarray chunks into Arrow RecordBatches. We pass a Python callback into a DataFusion TableProvider that lets the DB engine translate the underlying Dataset arrays into DataFusion partitions. The same chunks-to-batches translation is also exposed as a pyarrow.dataset.Dataset with predicate and projection pushdown, which is how DuckDB and Polars consume registered Datasets with no engine-specific code. Ultimately, the initial insight of the pivot() function -- that any ndarray can be translated into a 2D table -- underlies this performant query mechanism.

Autumn 2026: we take a leaf from LanceDB's book: we provide a common Arrow interface within DataFusion abstractions to perform the round trip journy between dataframe and Xarray Dataset. Then, we've built an extensible engine system to make it easy to add the next DB engine.

Does it work?

Yes, and not half bad either. Our initial fear was that this was merely a toy — fine for SELECTs, but not for operations scientists actually run.

To build confidence in our hypothesis, we built a suite of benchmarks catering to the primary user base for Xarray – climate & weather scientists and geospatial practitioners. Luckily for us, there already was this very helpful discussion initiated by Coiled that attempted to gather a representative range of geospatial queries (in order to improve Dask's performance on this category of workloads).

Based on this survey from experts in this domain, we create a SQL geospatial benchmark (as compared to native Xarray ops). You can read our claim and finings in "Geospatial operations are relational operations."

Some highlights:

  • We benchmarked everything on real cloud-native datasets: ARCO-ERA5, Sentinel 2, Weatherbench 2, and Google Earth Engine via Xee.
  • 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.
  • 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.
  • 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).

Why does this work?

Underneath Xarray, Dask, and Pandas, there are NumPy arrays. These are paged in chunks and represented contiguously in memory. It is only a matter of metadata that breaks them up into ndarrays. pivot(), which uses to_dataframe(), just changes this metadata (via a ravel()/reshape()), back into a column amenable to a DataFrame. We take advantage of this lightweight metadata change to make chunked information scannable by a DB engine (DataFusion, DuckDB, Polars — anything that speaks Arrow).

What are the current limitations?

The sharp edges we know about — per engine and fundamental — are cataloged in Known issues & limitations. Currently, we're looking for early users – "tire kickers", if you will. We'd love your input to shape the direction of this project! Please, give this a try and file issues as you see fit. Check out our contributing guide, too 😉.

Roadmap

  • Lazy evaluation via the pyarrow Dataset interface #93. Implemented in #100
  • Support proper parallelism via proper partition handling on the rust/datafusion side. #106
  • Support core datafusion optimizations to scan less data, like #104, ...
  • Translate a single Zarr to a collection of tables #85.
  • Distributed beyond a single node through the DataFusion integration (with Ray Datasets?) #68 or Apache Ballista #98.
  • Demo: calculate Sea Surface Temperature from 1940 - Present in SQL #36.
  • Provide an option to integrate DataFusion directly to Zarr via Rust #4.
  • (To be formally announced eventually): The 100 Trillion Row Challenge #34.

Sponsors & Contributors

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

  • Pramod Gupta and the Anthromet Team at Google Research for the problem formation and design inspiration.
  • Jake Wall and AI2/Ecoscope for compute resources and key use cases.
  • Charles Stern, Stephan Hoyer, Alexander Kmoch, Wei Ji, and Qiusheng Wu for the early review and discussion of this project.
  • Tom Nichols, Kyle Barron, Tom White, and Maxime Dion for the Array Working Group and DataFusion-specific collaboration.
  • The gracious volunteer data science students at UCSD's DS3 org, who are working to make this library better.
  • Andrew Huang for the sense of taste he brings to the project and consummate code changes.
  • Aman Kumar for spending a considerable amount of his GSoC internship contributing to this project.
  • Miguel Moncada Isla for reimagining this project to be a cross SQL engine interface to and from Xarray. He brought new life to this experiment, in my opinion.

License

Copyright 2024 Alexander Merose

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

All vendored code has proper license attribution.

Metadata

Release files for xarray_sql 0.7.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for xarray_sql 0.7.0
File Size Uploaded Provenance Actions
xarray_sql-0.7.0.tar.gz 377.6 kB Without provenance Details for xarray_sql-0.7.0.tar.gz

Built distributions (wheels)

Table of built distributions (wheels) for xarray_sql 0.7.0
File Actions
xarray_sql-0.7.0-cp310-abi3-win_amd64.whl CPython 3.10 abi3 Windows x86-64 Details for xarray_sql-0.7.0-cp310-abi3-win_amd64.whl
xarray_sql-0.7.0-cp310-abi3-pyemscripten_2026_0_wasm32.whl CPython 3.10 abi3 PyEmscripten 2026.0+ WebAssembly Details for xarray_sql-0.7.0-cp310-abi3-pyemscripten_2026_0_wasm32.whl
xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_x86_64.whl CPython 3.10 abi3 Linux glibc 2.28+ x86-64 Details for xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_x86_64.whl
xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_aarch64.whl CPython 3.10 abi3 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_aarch64.whl
xarray_sql-0.7.0-cp310-abi3-macosx_11_0_arm64.whl CPython 3.10 abi3 macOS 11.0+ ARM64 Details for xarray_sql-0.7.0-cp310-abi3-macosx_11_0_arm64.whl
xarray_sql-0.7.0-cp310-abi3-macosx_10_12_x86_64.whl CPython 3.10 abi3 macOS 10.12+ x86-64 Details for xarray_sql-0.7.0-cp310-abi3-macosx_10_12_x86_64.whl

Total release size: 224.6 MB

Release files / xarray_sql-0.7.0.tar.gz

Download URL xarray_sql-0.7.0.tar.gz
Size 377.6 kB
Tags Source
SHA-256 checksum
How to use checksums
b654fd9d0e1cb9a8fffe6c7d8388af0eac3097372f385242c6ead446e0f29201
BLAKE2b-256 checksum
How to use checksums
7c913ea0b920e3fd10049439154d42d8f6960a2be5a09ac5c73ae21e845918e7
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-win_amd64.whl

Download URL xarray_sql-0.7.0-cp310-abi3-win_amd64.whl
Size 45.7 MB
Tags CPython 3.10 Windows x86-64 abi3
SHA-256 checksum
How to use checksums
18da63a28bc303959efa8ec26635ede9c8d345b4291f4f69baa05a678a40a07e
BLAKE2b-256 checksum
How to use checksums
6d3157ee7e214ba0b917d076a3f5c4bd519c8581e47862fea47a8df62b500556
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-pyemscripten_2026_0_wasm32.whl

Download URL xarray_sql-0.7.0-cp310-abi3-pyemscripten_2026_0_wasm32.whl
Size 5.5 MB
Tags CPython 3.10 PyEmscripten 2026.0+ WebAssembly abi3
SHA-256 checksum
How to use checksums
96ea0405e74ac14a402f7132b7c2ffa3cee84f2a1aaedeb243bcbf64428cc30c
BLAKE2b-256 checksum
How to use checksums
207dd7e3c4d804a109ad0f313bd8b406278bcd059ba8199ac1928ba6c79a5c9d
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_x86_64.whl
Size 45.5 MB
Tags CPython 3.10 Linux glibc 2.28+ x86-64 abi3
SHA-256 checksum
How to use checksums
1af5b7c6f55f5c679c1ae9c3a62c4b20f81ff450c1fa1c9b858f2620d2621392
BLAKE2b-256 checksum
How to use checksums
77fd4851bb0104bd53ae8de9250668316aed751235e98a4668edb0071d1d5049
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.7.0-cp310-abi3-manylinux_2_28_aarch64.whl
Size 43.2 MB
Tags CPython 3.10 Linux glibc 2.28+ ARM64 abi3
SHA-256 checksum
How to use checksums
b73d560771337a66536c5d833c5c2e06304f6065e6b3390ca52b777564207f4d
BLAKE2b-256 checksum
How to use checksums
8954d6bab8e3d56b387718a01fbc4e098caea890798c17c592856ab449cb86b0
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-macosx_11_0_arm64.whl

Download URL xarray_sql-0.7.0-cp310-abi3-macosx_11_0_arm64.whl
Size 41.1 MB
Tags CPython 3.10 abi3 macOS 11.0+ ARM64
SHA-256 checksum
How to use checksums
f9b02c4cbe652956d86bb63b298febb540e44525659b6e6666a93dd79fe46250
BLAKE2b-256 checksum
How to use checksums
abc7f92f29cdcc96fb817ac2326841924a9ed1fd4049a882fd01a7048a391f1e
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / xarray_sql-0.7.0-cp310-abi3-macosx_10_12_x86_64.whl

Download URL xarray_sql-0.7.0-cp310-abi3-macosx_10_12_x86_64.whl
Size 43.2 MB
Tags CPython 3.10 abi3 macOS 10.12+ x86-64
SHA-256 checksum
How to use checksums
f8460123a69d3847806620106156c6304cb759b002df37bed9d88836b213e737
BLAKE2b-256 checksum
How to use checksums
09a6023f1d40930d1fdc315999c47c892d76a77d1674f9bc01ae356eb0af1242
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.8 {"installer":{"name":"uv","version":"0.13.8","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Security for xarray_sql 0.7.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page