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 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. Ultimately, the initial insight of the pivot() function -- that any ndarray can be translated into a 2D table -- underlies this performant query mechanism.

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

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:

2026 update: A colleague and I are experimenting with native Zarr RDBMS engines. Check out:

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.

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

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

Built distributions (wheels)

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

Total release size: 191.4 MB

Release files / xarray_sql-0.2.2.tar.gz

Download URL xarray_sql-0.2.2.tar.gz
Size 75.7 kB
Tags Source
SHA-256 checksum
How to use checksums
ee906ec14f75f85435b5537480dadc6fbfb5ea2818b9d8c3fdc6740f1bcfabef
BLAKE2b-256 checksum
How to use checksums
35efb1aa99a948f254a87bd38826bb8886d7f28fbc42aa9362345dd46d2cd2cf
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp313-cp313-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.2-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
e49ee95dd049435edf56e2feffb60ef40e42722b4d315a2676d7fd1cc2f92905
BLAKE2b-256 checksum
How to use checksums
670dd7a7fa9a6a46246daf219b11a561f52ca34f70023f7965fd62aab20f9417
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp313-cp313-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.2-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
5263fd717e19363b652bc44b180755613f7d4208673201d337b5fe6aa5f0aa00
BLAKE2b-256 checksum
How to use checksums
216cf6ee0422d52242385cd63e734e6f2914c9f92f8badc708349596c992e2dc
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp312-cp312-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.2-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
b6e4ff0ad752d72212806d7de2cd07bd8dd4a19e5388310ad3a45e265d67357a
BLAKE2b-256 checksum
How to use checksums
03978d8485337df8461d0a9f24f7a804bae25c60a5348891f37a7d465998c4cd
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp312-cp312-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.2-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
04b15d3bd5c09ebd45ee4bc28fe76b7cc6d1975363fd2eb477746ca15650735f
BLAKE2b-256 checksum
How to use checksums
871bfc0376f33147a090e9a8d1ca5b23f154674a8316b2aa9900c4a7787a8982
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp311-cp311-win_amd64.whl

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

Download URL xarray_sql-0.2.2-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
cbad50b62655134446e7e0ff1ecbeb807deb12b6832e82f64b564d7b6f336367
BLAKE2b-256 checksum
How to use checksums
994c7a23a10bae3dff71af3e7f9dfaa99140753c18a0013609fd8c7031507331
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp311-cp311-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.2-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
97d912b6f4b16fd10400daaadf90102cd21254845fe3a77a4dd9365805595a9c
BLAKE2b-256 checksum
How to use checksums
cde7f4362304684bb9b9c9fc05134b97caf97c7ec3ff94983ca5ad5ba5857635
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp311-cp311-macosx_11_0_arm64.whl

Download URL xarray_sql-0.2.2-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
4387330a09013183413063e7ca6474708804ba022aa51a93a76abfe6947a6343
BLAKE2b-256 checksum
How to use checksums
6d765c43d735c274192b75d806c289691c04f843a845e8ed9254037662c36f4f
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp311-cp311-macosx_10_12_x86_64.whl

Download URL xarray_sql-0.2.2-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
aafe586088104487947a61f6ecd3fd08f00df431bbfd863ad919686b626afc4c
BLAKE2b-256 checksum
How to use checksums
398fb7539e522b7dfa20af71ac80f3562b6c4e361abcedd001eaf6a1c24c8fda
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp310-cp310-manylinux_2_28_x86_64.whl

Download URL xarray_sql-0.2.2-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
48702d6efd0758ee1580cee920d5feabee5659e0fafe4ea43f3185673225e30d
BLAKE2b-256 checksum
How to use checksums
053d42787cebb0695a17e5fd92bb1517380d367bc0a0818c692e160ea8342d08
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp310-cp310-manylinux_2_28_aarch64.whl

Download URL xarray_sql-0.2.2-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
6833a1bb51f25b65ec487c6fb572b4c27563f4cd4972111486e95e075ef7f277
BLAKE2b-256 checksum
How to use checksums
29c66b6fb0527b0eb90ee0b8be214238d1b86dbcd7dbd18dd45c5e68ced09f82
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

Download URL xarray_sql-0.2.2-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
2ce4bab735c87e2d426d26f4f53f9315d996b585665b76ecdb70171f9b2e4209
BLAKE2b-256 checksum
How to use checksums
643b34ee63bd47d9d75b4069373f22ecbe477c362462a2c30a786bccbc372a2f
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.11.2 {"installer":{"name":"uv","version":"0.11.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.2

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