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.1

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.1
File Size Uploaded Provenance Actions
xarray_sql-0.2.1.tar.gz 66.3 kB Without provenance Details for xarray_sql-0.2.1.tar.gz

Built distributions (wheels)

Table of built distributions (wheels) for xarray_sql 0.2.1
File Actions
xarray_sql-0.2.1-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.1-cp313-cp313-manylinux_2_28_x86_64.whl
xarray_sql-0.2.1-cp313-cp313-manylinux_2_28_aarch64.whl CPython 3.13 CPython 3.13 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.1-cp313-cp313-manylinux_2_28_aarch64.whl
xarray_sql-0.2.1-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.1-cp312-cp312-manylinux_2_28_x86_64.whl
xarray_sql-0.2.1-cp312-cp312-manylinux_2_28_aarch64.whl CPython 3.12 CPython 3.12 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.1-cp312-cp312-manylinux_2_28_aarch64.whl
xarray_sql-0.2.1-cp311-cp311-win_amd64.whl CPython 3.11 CPython 3.11 Windows x86-64 Details for xarray_sql-0.2.1-cp311-cp311-win_amd64.whl
xarray_sql-0.2.1-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.1-cp311-cp311-manylinux_2_28_x86_64.whl
xarray_sql-0.2.1-cp311-cp311-manylinux_2_28_aarch64.whl CPython 3.11 CPython 3.11 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.1-cp311-cp311-manylinux_2_28_aarch64.whl
xarray_sql-0.2.1-cp311-cp311-macosx_11_0_arm64.whl CPython 3.11 CPython 3.11 macOS 11.0+ ARM64 Details for xarray_sql-0.2.1-cp311-cp311-macosx_11_0_arm64.whl
xarray_sql-0.2.1-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.1-cp311-cp311-macosx_10_12_x86_64.whl
xarray_sql-0.2.1-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.1-cp310-cp310-manylinux_2_28_x86_64.whl
xarray_sql-0.2.1-cp310-cp310-manylinux_2_28_aarch64.whl CPython 3.10 CPython 3.10 Linux glibc 2.28+ ARM64 Details for xarray_sql-0.2.1-cp310-cp310-manylinux_2_28_aarch64.whl
xarray_sql-0.2.1-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.1-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

Total release size: 191.5 MB

Release files / xarray_sql-0.2.1.tar.gz

Download URL xarray_sql-0.2.1.tar.gz
Size 66.3 kB
Tags Source
SHA-256 checksum
How to use checksums
e22e478c4f82aba72f5961b14083424799fdc28df2d7b4024757b85350b814a0
BLAKE2b-256 checksum
How to use checksums
4928ed7a4d17c35727eb2e654ed7212985f0649458644f3b4b886a95feb6ac5a
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp313-cp313-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.1-cp313-cp313-manylinux_2_28_x86_64.whl
Size 16.5 MB
Tags CPython 3.13 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
8c29cb0fde004ba3fa3db1340bb07119e0b1bc38b0b07269de485d47f4a4e055
BLAKE2b-256 checksum
How to use checksums
9faa463aceca2e363e5687fec0f39dbd81f0601331e69bcc4e7829e7d957bc32
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp313-cp313-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.1-cp313-cp313-manylinux_2_28_aarch64.whl
Size 15.2 MB
Tags CPython 3.13 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
798f6cb0b6e9a0762e70f4c31898c0e7baea65e35bbbdf85d52d0bab8c69dc49
BLAKE2b-256 checksum
How to use checksums
5c01feb7f31397dcc02b3c07af19298c7425df29542a20f65d2ee80bfff9eaa0
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp312-cp312-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.1-cp312-cp312-manylinux_2_28_x86_64.whl
Size 16.5 MB
Tags CPython 3.12 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
c104a9f47c870e4147ddf522389e34e5b552e3d51ba065201778c4a1776fa1b3
BLAKE2b-256 checksum
How to use checksums
222a8edb2ec55b223064a6f691c6afe5e919b9ed5f98973e3da6b584035cd769
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp312-cp312-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.1-cp312-cp312-manylinux_2_28_aarch64.whl
Size 15.2 MB
Tags CPython 3.12 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
29d2dc75746b86c482562a44335adff399fcc55a88294b076dd6151306fe4078
BLAKE2b-256 checksum
How to use checksums
6ae2cbc6fea2638a53966b7f6b2f9e400eae51896d18142b5b488229b712d3e7
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp311-cp311-win_amd64.whl

Download URL xarray_sql-0.2.1-cp311-cp311-win_amd64.whl
Size 16.1 MB
Tags CPython 3.11 Windows x86-64
SHA-256 checksum
How to use checksums
a36b3051502088c1c38f793172e2d4f9a64cfa8e82cc2b0d315bbcb6076b0250
BLAKE2b-256 checksum
How to use checksums
564b86132302d4b2e25f8e5e566fa54cea02653771a431ab022f98ab9650ed3c
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp311-cp311-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.1-cp311-cp311-manylinux_2_28_x86_64.whl
Size 16.5 MB
Tags CPython 3.11 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
d4b4e9f711f17b363186278a16c23e50cc36ae3ac42f84d6dfdbc8fd9b7ca7f4
BLAKE2b-256 checksum
How to use checksums
ddfe117b3f88bee277c69aa374f011ddf8c91e727d055d90dbdf2a89312fd75e
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp311-cp311-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.1-cp311-cp311-manylinux_2_28_aarch64.whl
Size 15.2 MB
Tags CPython 3.11 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
89bd6609508c06b37bb7a0e07a0fa40af344b45720a8fbc61a3e5316ebb3581f
BLAKE2b-256 checksum
How to use checksums
8cf45ba1df40adff4e8a522399102b7376e78699766e7c2bceea10c696f3c716
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp311-cp311-macosx_11_0_arm64.whl

Download URL xarray_sql-0.2.1-cp311-cp311-macosx_11_0_arm64.whl
Size 14.3 MB
Tags CPython 3.11 macOS 11.0+ ARM64
SHA-256 checksum
How to use checksums
7ae0036ff3d6603a298b1720c67126d6760bdefc503e2477b67d6fdaca824d59
BLAKE2b-256 checksum
How to use checksums
f66ecac69f96cc77f477860244ee909b07ceea7286cff03d49136ee224a3fce9
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp311-cp311-macosx_10_12_x86_64.whl

Download URL xarray_sql-0.2.1-cp311-cp311-macosx_10_12_x86_64.whl
Size 15.2 MB
Tags CPython 3.11 macOS 10.12+ x86-64
SHA-256 checksum
How to use checksums
b3cc94a33523cfff2ab6a4fe1ad8896049cf1cd5de4b88b51d58d6d48f98b38a
BLAKE2b-256 checksum
How to use checksums
b938d276a7606fd5403b0ecb126fc7b593dd6c2a37d47d08133b25e0bae7039c
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp310-cp310-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.1-cp310-cp310-manylinux_2_28_x86_64.whl
Size 16.5 MB
Tags CPython 3.10 Linux glibc 2.28+ x86-64
SHA-256 checksum
How to use checksums
415ac4d40e0cf831487e598789372bc923063ae11822e921091c1d96930a1e34
BLAKE2b-256 checksum
How to use checksums
3c0348eda787b85b8a675d5453ed609704854ee154b444678dbf7ce59d1a56a3
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp310-cp310-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.1-cp310-cp310-manylinux_2_28_aarch64.whl
Size 15.2 MB
Tags CPython 3.10 Linux glibc 2.28+ ARM64
SHA-256 checksum
How to use checksums
50933d56794bebed88bdafbfd0b441d639f65ec75b90305fdee14f8162fa8cc4
BLAKE2b-256 checksum
How to use checksums
01ec96355fa2cb6e86ce2f9e54795a4cf3e233dff75c96132d6cad12d0409fd2
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

Download URL xarray_sql-0.2.1-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Size 18.8 MB
Tags CPython 3.8 Linux glibc 2.17+ x86-64
SHA-256 checksum
How to use checksums
c2c2db687a514132bf669721b455ce4f159249a098f31ebf5b6802646f90cf5b
BLAKE2b-256 checksum
How to use checksums
13ffb82082d564300dec752afbedd2e54933b20441f543c8f3ff8d5119fbfbcc
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.10.9 {"installer":{"name":"uv","version":"0.10.9","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.1

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