Skip to main content

xarray-sql

Query Xarray with SQL

ci lint ci-build ci-rust

pip install xarray-sql

What is this?

This is an experiment to provide a SQL interface for array datasets.

import xarray as xr
import xarray_sql as xql

ds = xr.tutorial.open_dataset('air_temperature')

# The same as a dask-sql Context; i.e. an Apache DataFusion Context.
ctx = xql.XarrayContext()
ctx.from_dataset('air', ds, chunks=dict(time=24))  # the dataset needs to be chunked!
# data is only materialized when we make a query.

result = ctx.sql('''
  SELECT
    "lat", "lon", AVG("air") as air_avg
  FROM
    "air"
  GROUP BY
   "lat", "lon"
''')
# DataFrame()
# +------+-------+--------------------+
# | lat  | lon   | air_avg            |
# +------+-------+--------------------+
# | 75.0 | 205.0 | 259.88662671232834 |
# | 75.0 | 207.5 | 259.48268150684896 |
# | 75.0 | 230.0 | 258.9192123287667  |
# | 75.0 | 275.0 | 257.07574315068456 |
# | 75.0 | 322.5 | 250.11792123287654 |
# | 75.0 | 325.0 | 250.81590068493134 |
# | 72.5 | 205.0 | 262.74933904109537 |
# | 72.5 | 207.5 | 262.5384315068488  |
# | 72.5 | 230.0 | 260.82879452054743 |
# | 72.5 | 275.0 | 257.3063321917804  |
# +------+-------+--------------------+
# Data truncated.

# The full query is only made when we call `collect()`, or, in this case,
# `to_pandas()`.
df = result.to_pandas()
df.head()
#     lat    lon     air_avg
# 0  75.0  232.5  258.836188
# 1  75.0  247.5  257.716171
# 2  75.0  262.5  257.347959
# 3  75.0  277.5  257.671308
# 4  72.5  232.5  260.654401

Succinctly, we "pivot" Xarray Datasets (with consistent dimensions) to treat them like tables so we can run SQL queries against them.

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 make factory functions from blocks of Xarray datasets that return RecordBatchReaders. These feed into a Rust-based DataFusion TableProvider. Every chunk is uses the Arrow in memory format to translate between Python and Rust. Even still, the core of what makes this idea work is the core pivot() operation from where this project began!

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 light weight metadata change to make chunked information scannable by a DB engine (DataFusion).

What are the current limitations?

TBD, DataFusion provides a whole new world! 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 😉.

I can say that for now, the library is oriented towards making whole scans of Xarray Datasets. Common filter optimizations (even basic ones like an .sel() on core dimensions, let alone predicate push downs) are not fully implemented yet. However, these operations and more are on our roadmap.

What would a deeper integration look like?

I have a few ideas so far. One approach involves applying operations directly on Xarray Datasets. This approach is being pursued here, as xql.

Deeper still: I was thinking we could make a virtual filesystem for parquet that would internally map to Zarr. Raster-backed virtual parquet would open up integrations to numerous tools like dask, pyarrow, duckdb, and BigQuery. More thoughts on this in #4.

2025 update: Something like this is being built across a few projects! The ones I know about are:

As of writing, this project is amid integrating a rust-based DataFusion backend provided by arrow-zarr.

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 via DataFusion's catalog interface #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.

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.2.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.2.0
File Size Uploaded Provenance Actions
xarray_sql-0.2.0.tar.gz 52.2 kB Without provenance Details for xarray_sql-0.2.0.tar.gz

Built distributions (wheels)

Table of built distributions (wheels) for xarray_sql 0.2.0
File Actions
xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_x86_64.whl CPython 3.13 CPython 3.13 Linux glibc 2.28+ x86-64 Details for xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_x86_64.whl
xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_aarch64.whl CPython 3.13 CPython 3.13 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_aarch64.whl
xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_x86_64.whl CPython 3.12 CPython 3.12 Linux glibc 2.28+ x86-64 Details for xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_x86_64.whl
xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_aarch64.whl CPython 3.12 CPython 3.12 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_aarch64.whl
xarray_sql-0.2.0-cp311-cp311-win_amd64.whl CPython 3.11 CPython 3.11 Windows x86-64 Details for xarray_sql-0.2.0-cp311-cp311-win_amd64.whl
xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_x86_64.whl CPython 3.11 CPython 3.11 Linux glibc 2.28+ x86-64 Details for xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_x86_64.whl
xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_aarch64.whl CPython 3.11 CPython 3.11 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_aarch64.whl
xarray_sql-0.2.0-cp311-cp311-macosx_11_0_arm64.whl CPython 3.11 CPython 3.11 macOS 11.0+ ARM64 Details for xarray_sql-0.2.0-cp311-cp311-macosx_11_0_arm64.whl
xarray_sql-0.2.0-cp311-cp311-macosx_10_12_x86_64.whl CPython 3.11 CPython 3.11 macOS 10.12+ x86-64 Details for xarray_sql-0.2.0-cp311-cp311-macosx_10_12_x86_64.whl
xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_x86_64.whl CPython 3.10 CPython 3.10 Linux glibc 2.28+ x86-64 Details for xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_x86_64.whl
xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_aarch64.whl CPython 3.10 CPython 3.10 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_aarch64.whl
xarray_sql-0.2.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl CPython 3.8 CPython 3.8 Linux glibc 2.17+ x86-64 Details for xarray_sql-0.2.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

Total release size: 320.7 MB

Release files / xarray_sql-0.2.0.tar.gz

Download URL xarray_sql-0.2.0.tar.gz
Size 52.2 kB
Tags Source
SHA-256 checksum
How to use checksums
544cced5391eb6a4779bd66f94221d958bf44433ffc4159e3c616953820a7084
BLAKE2b-256 checksum
How to use checksums
abbc3cd98bef161fa3f3b35cc939790b035042bf857bbe7ee33a850689468a18
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp313-cp313-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_x86_64.whl
Size 27.5 MB
Tags CPython 3.13 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
a69cc51037e3d278ef6dc2b4306211113bd38702a910da785c85f363c571eb02
BLAKE2b-256 checksum
How to use checksums
de0ee09b511b7d5240b7f03805d0ad4c0c2bda2e2a7afe82e0a779c450e47581
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp313-cp313-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.0-cp313-cp313-manylinux_2_28_aarch64.whl
Size 25.5 MB
Tags CPython 3.13 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
7f2ca5c1c2ab378a2b93c898538b360557612f7095970c170f80e8ec0f0350c4
BLAKE2b-256 checksum
How to use checksums
ed968ce486dd856d61423626cf97db01a1aa230703b498e9e4c114afbe12edf3
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp312-cp312-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_x86_64.whl
Size 27.5 MB
Tags CPython 3.12 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
0285c8fd3d95e3c5434004a3d1ec4b3334a2b385c2a1653363cbccc1a33c4d85
BLAKE2b-256 checksum
How to use checksums
b72e6c0bc67484e96865e316387c3d504cf0364e8cdf0366beeec7ea157ea9a8
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp312-cp312-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.0-cp312-cp312-manylinux_2_28_aarch64.whl
Size 25.5 MB
Tags CPython 3.12 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
33fde21031700edd83a1d8f0ce6b93d3ac758571d3b9ace8c3d80c3b9f5c6a5c
BLAKE2b-256 checksum
How to use checksums
a86b04c816cc2e1e38b86d2f456938a30915ab270b4df7fba946efea7c08d130
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp311-cp311-win_amd64.whl

Download URL xarray_sql-0.2.0-cp311-cp311-win_amd64.whl
Size 27.5 MB
Tags CPython 3.11 Windows x86-64
SHA-256 checksum
How to use checksums
e9a5c59ec663244fad6ab87d60f2d82f96c315bb53624fd682b135dc476dee99
BLAKE2b-256 checksum
How to use checksums
f2a7f79df9d289427758076b85924ba04b9675e9b0284eb31b3f7143f68746bb
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp311-cp311-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_x86_64.whl
Size 27.5 MB
Tags CPython 3.11 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
03b9aa5a3343d0223579cdca80a16c5ac7f6089b42ead13d4af165c2fe823523
BLAKE2b-256 checksum
How to use checksums
2fcaf55dcde6b72b0a215f3752527c9f708b7c0e4a70032586ea6f9188a6ea78
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp311-cp311-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.0-cp311-cp311-manylinux_2_28_aarch64.whl
Size 25.5 MB
Tags CPython 3.11 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
33c7df9a19f55f5e50711723ff0781df94bdcbf87428f2a4342d3f9a7a1075d8
BLAKE2b-256 checksum
How to use checksums
c3744bd030f3da545a0aa82c0092b77ac38fffd76ec6a93869b3aaebf7b6e79e
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp311-cp311-macosx_11_0_arm64.whl

Download URL xarray_sql-0.2.0-cp311-cp311-macosx_11_0_arm64.whl
Size 24.0 MB
Tags CPython 3.11 macOS 11.0+ ARM64
SHA-256 checksum
How to use checksums
38d84a4c1cda55b0860aee83d89e510ae8f6d5e0787ad7c097ae656c5a9f658f
BLAKE2b-256 checksum
How to use checksums
30804621548c7e5c95251e95f58b328c878afd8e45ae29488c8edf59c4c217e0
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp311-cp311-macosx_10_12_x86_64.whl

Download URL xarray_sql-0.2.0-cp311-cp311-macosx_10_12_x86_64.whl
Size 25.7 MB
Tags CPython 3.11 macOS 10.12+ x86-64
SHA-256 checksum
How to use checksums
5727b1461cc1711b410aa80f878811c33a53eb0c9ce3893a8f0154ef0908f7e2
BLAKE2b-256 checksum
How to use checksums
632e4d1fcbf40e1f7eca6874324c2094967e937bfb9c05792ac36923f809bed8
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp310-cp310-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_x86_64.whl
Size 27.5 MB
Tags CPython 3.10 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
f3f0ffb8fe7d4cc59a7210b5759455b129c7a9355ed02eab958848fbc30617ed
BLAKE2b-256 checksum
How to use checksums
06a1e9783fd734ecef0fa598a269f7af7505c2b6f48e6a49be1504523c516157
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp310-cp310-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.0-cp310-cp310-manylinux_2_28_aarch64.whl
Size 25.5 MB
Tags CPython 3.10 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
f3470e62ba65a51a37ef81cbf2b592654f0083b08f7894d7c0521bf6cf75bc39
BLAKE2b-256 checksum
How to use checksums
7d77693f8f0b898a535ca8ceee0d09a1031db67ef4698152b72251b330c98b22
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

Download URL xarray_sql-0.2.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Size 31.6 MB
Tags CPython 3.8 Linux glibc 2.17+ x86-64
SHA-256 checksum
How to use checksums
69cf42abbdb921c59831533a9e30aa56212afb0c41cbb0b5dea6a09c6bfd4835
BLAKE2b-256 checksum
How to use checksums
0d31b37a005153249a25b2401ecc0eec14be1cab993550553bca3ba0ca8b3106
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","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.2.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