Skip to content

Commit c0c4f7c

Browse files
alxmrsclaude
andcommitted
Plan a query once and run it many times: Context.prepare
A training loop runs the same query on every step, and over small tables DataFusion's planning takes longer than running. Context.prepare plans a query once; calling the PreparedQuery runs that plan on the tables' current values, after writing any Datasets given into their tables in place. On a join of two 1,024-row tables a call takes 0.33 ms, against 0.91 ms for ctx.sql. A plan keeps state from a run (a hash join keeps its build side, a repartition its channels), and datafusion-python can't reset it (#283). Each call runs a copy decoded from the plan's serialized form. A plan that can't be serialized (a lazy table) or decoded (nested too deep), and a query that calls grad (xqlsystems/ddx#202), are planned again on each call. A write must keep its NaNs in place (#284). Co-Authored-By: Claude Opus 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01NyN7idACeDMh94qrnFMu6h
1 parent 86d5c00 commit c0c4f7c

4 files changed

Lines changed: 323 additions & 3 deletions

File tree

‎docs/performance.md‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,27 @@ it or nothing happens:
177177
ctx.sql("CREATE OR REPLACE TABLE grid_cube AS ...").collect()
178178
```
179179

180+
## Plan once: prepare a query you run many times
181+
182+
DataFusion plans a query each time it runs. For a query over small
183+
tables, as in a training loop, planning can take longer than running.
184+
`Context.prepare` plans the query once. A call runs that plan on the
185+
tables' current values, and can first write new values into a table
186+
in place:
187+
188+
```python
189+
step = ctx.prepare(
190+
"SELECT x.i, SUM(x.v * w.v) AS y FROM x JOIN w ON x.j = w.j GROUP BY x.i"
191+
)
192+
for weights in trajectory: # Datasets shaped like w's
193+
rows = step(w=weights) # a pyarrow.Table
194+
```
195+
196+
On a join of two 1,024-row tables, a call takes 0.33 ms, and `ctx.sql`
197+
takes 0.91 ms (one target partition). A write must keep the table's
198+
shape, dtypes, coordinates and NaN positions. A plan over a lazy table
199+
(Dask or a lazily opened file) is planned again on each call for now.
200+
180201
## Round-trip faster with ORDER BY
181202

182203
Results that arrive **grid-ordered** — sorted by the dimension columns,

‎tests/test_prepare.py‎

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,119 @@
1+
"""Tests for queries planned once and run many times (`Context.prepare`)."""
2+
3+
import numpy as np
4+
import pytest
5+
import xarray as xr
6+
from datafusion import SessionConfig
7+
8+
from xarray_sql import Context
9+
10+
# The build side of the join, `a`, is what a reused hash join would keep.
11+
JOIN = """
12+
SELECT b.i, SUM(t.v2 * b.w) AS s
13+
FROM (SELECT i, j, 2 * v AS v2 FROM a) t JOIN b ON t.j = b.j
14+
GROUP BY b.i ORDER BY b.i
15+
"""
16+
17+
18+
def _a(v):
19+
return xr.Dataset(
20+
{"v": (("i", "j"), v)},
21+
coords={"i": np.arange(v.shape[0]), "j": np.arange(v.shape[1])},
22+
)
23+
24+
25+
def _b(w):
26+
return xr.Dataset(
27+
{"w": (("i", "j"), w)},
28+
coords={"i": np.arange(w.shape[0]), "j": np.arange(w.shape[1])},
29+
)
30+
31+
32+
@pytest.fixture
33+
def ctx():
34+
rng = np.random.default_rng(0)
35+
ctx = Context()
36+
ctx.from_dataset("a", _a(rng.normal(size=(4, 3))))
37+
ctx.from_dataset("b", _b(rng.normal(size=(4, 3))))
38+
return ctx
39+
40+
41+
def test_a_prepared_query_returns_the_query_rows(ctx):
42+
query = ctx.prepare(JOIN)
43+
want = ctx.sql(JOIN).to_arrow_table()
44+
assert query().equals(want)
45+
assert query().equals(want)
46+
47+
48+
def test_a_call_reads_the_values_it_writes(ctx):
49+
query = ctx.prepare(JOIN)
50+
rng = np.random.default_rng(1)
51+
for _ in range(3):
52+
a, b = _a(rng.normal(size=(4, 3))), _b(rng.normal(size=(4, 3)))
53+
rows = query(a=a, b=b)
54+
fresh = Context()
55+
fresh.from_dataset("a", a)
56+
fresh.from_dataset("b", b)
57+
assert rows.equals(fresh.sql(JOIN).to_arrow_table())
58+
59+
60+
def test_a_write_changes_the_registered_dataset():
61+
v = np.zeros((4, 3))
62+
ctx = Context()
63+
ctx.from_dataset("a", _a(v))
64+
query = ctx.prepare("SELECT SUM(v) AS s FROM a")
65+
assert query(a=_a(np.ones((4, 3)))).column("s").to_pylist() == [12.0]
66+
assert (v == 1).all()
67+
assert ctx.sql("SELECT SUM(v) AS s FROM a").to_pydict() == {"s": [12.0]}
68+
69+
70+
def test_a_plan_with_several_partitions_runs_again():
71+
ctx = Context(SessionConfig().with_target_partitions(4))
72+
n = 64
73+
ctx.from_dataset("a", _a(np.ones((n, 3))), chunks={"i": n // 4})
74+
ctx.from_dataset("b", _b(np.ones((n, 3))), chunks={"i": n // 4})
75+
query = ctx.prepare(
76+
"SELECT a.i, SUM(v * w) AS s FROM a JOIN b ON a.i = b.i AND a.j = b.j "
77+
"GROUP BY a.i"
78+
)
79+
for scale in (1.0, 2.0):
80+
rows = query(a=_a(np.full((n, 3), scale)))
81+
assert sorted(rows.column("s").to_pylist()) == [3 * scale] * n
82+
83+
84+
def test_a_lazy_table_is_planned_on_each_call():
85+
pytest.importorskip("dask")
86+
ctx = Context()
87+
ctx.from_dataset("a", _a(np.ones((4, 3))).chunk({"i": 2}))
88+
query = ctx.prepare("SELECT SUM(v) AS s FROM a")
89+
assert query().column("s").to_pylist() == [12.0]
90+
assert query().column("s").to_pylist() == [12.0]
91+
with pytest.raises(ValueError, match="NumPy"):
92+
query(a=_a(np.ones((4, 3))))
93+
94+
95+
@pytest.mark.parametrize(
96+
"new, match",
97+
[
98+
(_a(np.ones((4, 2))), "coordinates"),
99+
(_a(np.ones((4, 3), dtype=np.float32)), "float32"),
100+
(_a(np.ones((4, 3))).rename({"v": "u"}), "data variables"),
101+
(_a(np.full((4, 3), np.nan)), "NaN"),
102+
],
103+
)
104+
def test_a_write_must_keep_the_table_shape(ctx, new, match):
105+
query = ctx.prepare(JOIN)
106+
want = query()
107+
with pytest.raises(ValueError, match=match):
108+
query(a=new)
109+
assert query().equals(want)
110+
111+
112+
def test_a_write_needs_the_table_the_query_was_planned_with(ctx):
113+
query = ctx.prepare(JOIN)
114+
with pytest.raises(ValueError, match="no Dataset"):
115+
query(c=_a(np.ones((4, 3))))
116+
ctx.deregister_table("a")
117+
ctx.from_dataset("a", _a(np.ones((4, 3))))
118+
with pytest.raises(ValueError, match="registered again"):
119+
query(a=_a(np.ones((4, 3))))

‎xarray_sql/__init__.py‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
__all__ = [
1515
"cftime",
1616
"Context",
17+
"PreparedQuery",
1718
"XarrayContext",
1819
"FlightSQLServer",
1920
"read_xarray_table",
@@ -28,17 +29,18 @@
2829
]
2930

3031
try:
31-
from .sql import Context, XarrayContext
32+
from .sql import Context, PreparedQuery, XarrayContext
3233
except ModuleNotFoundError as e:
3334
# DataFusion's Python bindings have no wheels for some platforms
3435
# (e.g. Pyodide); every other engine adapter still works there.
3536
if e.name != "datafusion":
3637
raise
3738
__all__.remove("Context")
39+
__all__.remove("PreparedQuery")
3840
__all__.remove("XarrayContext")
3941

4042
def __getattr__(name: str):
41-
if name in ("Context", "XarrayContext"):
43+
if name in ("Context", "PreparedQuery", "XarrayContext"):
4244
raise AttributeError(
4345
f"{name} requires the datafusion package, which is "
4446
"not installed."

‎xarray_sql/sql.py‎

Lines changed: 179 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,11 @@
11
import uuid
22

3+
import numpy as np
34
import pyarrow as pa
45
import xarray as xr
56
from datafusion import SessionConfig, SessionContext
67
from datafusion.catalog import Schema
8+
from datafusion.plan import ExecutionPlan
79
from types import ModuleType
810

911
from . import cftime as cft
@@ -238,9 +240,185 @@ def sql(self, query: str, *args, **kwargs) -> XarrayDataFrame:
238240
Returns:
239241
An [XarrayDataFrame][xarray_sql.ds.XarrayDataFrame] wrapping the DataFusion DataFrame.
240242
"""
241-
inner = super().sql(query, *args, **kwargs)
243+
return self._wrap(super().sql(query, *args, **kwargs))
244+
245+
def prepare(self, query: str) -> "PreparedQuery":
246+
"""Plan a query once, to run it many times.
247+
248+
A training loop runs the same query on every step, and only the
249+
values in its tables change. DataFusion plans a query each time it
250+
runs, and for a query over small tables, planning can take far
251+
longer than running. ``prepare`` plans the query once, and calling
252+
the result runs that plan, as ``jax.jit`` traces a function once
253+
and runs the compiled function after that. A call can first write
254+
new values into the query's tables::
255+
256+
step = ctx.prepare(
257+
"SELECT x.i, SUM(x.v * w.v) AS y "
258+
"FROM x JOIN w ON x.j = w.j GROUP BY x.i"
259+
)
260+
for weights in trajectory: # Datasets shaped like w's
261+
rows = step(w=weights)
262+
263+
See [PreparedQuery][xarray_sql.PreparedQuery] for what a call
264+
can write, and which plans are run again rather than planned again.
265+
266+
Args:
267+
query: A SQL query string.
268+
269+
Returns:
270+
A [PreparedQuery][xarray_sql.PreparedQuery]; call it to run
271+
the query.
272+
"""
273+
return PreparedQuery(self, query)
274+
275+
def _write(self, writes: dict[str, xr.Dataset]) -> None:
276+
"""Write each Dataset's values into the arrays of the Dataset
277+
registered under its name, all or none of them."""
278+
copies = []
279+
for name, ds in writes.items():
280+
registered = self._registered_datasets[name]
281+
if set(ds.data_vars) != set(registered.data_vars):
282+
raise ValueError(
283+
f"{name!r} has the data variables "
284+
f"{sorted(registered.data_vars)}, and the new Dataset "
285+
f"has {sorted(ds.data_vars)}"
286+
)
287+
for dim in registered.dims:
288+
if dim not in ds.dims or not ds[dim].equals(registered[dim]):
289+
raise ValueError(
290+
f"the new Dataset for {name!r} has other coordinates "
291+
f"along {dim!r}; register it again, and prepare the "
292+
"query again"
293+
)
294+
for var in registered.data_vars:
295+
old, new = registered[var].variable, ds[var].variable
296+
if new.dims != old.dims or new.dtype != old.dtype:
297+
raise ValueError(
298+
f"{name}.{var} is {old.dtype} over {old.dims}, and "
299+
f"the new values are {new.dtype} over {new.dims}"
300+
)
301+
target = old.data
302+
# A table shares a C-contiguous NumPy array's memory; any
303+
# other array, it read or copied when it was registered.
304+
if not (
305+
isinstance(target, np.ndarray)
306+
and target.flags.c_contiguous
307+
and target.flags.writeable
308+
):
309+
raise ValueError(
310+
f"{name}.{var} isn't a writable, C-contiguous NumPy "
311+
"array, which its table would share; register the "
312+
"new Dataset instead, and prepare the query again"
313+
)
314+
values = np.asarray(new.values)
315+
# A table works out which values are NULL (NaN) when it is
316+
# registered, so a write must keep its NaNs in place.
317+
if values.dtype.kind in "fc" and not np.array_equal(
318+
np.isnan(values), np.isnan(target)
319+
):
320+
raise ValueError(
321+
f"the new values of {name}.{var} have NaN in other "
322+
"places; xarray-sql reads a NaN as NULL, so register "
323+
"the new Dataset instead, and prepare the query again"
324+
)
325+
copies.append((target, values))
326+
for target, values in copies:
327+
np.copyto(target, values)
328+
329+
def _wrap(self, inner) -> XarrayDataFrame:
330+
"""Wrap a DataFusion DataFrame so it can round-trip to xarray."""
242331
return XarrayDataFrame(inner, templates=self._registered_datasets)
243332

244333

334+
class PreparedQuery:
335+
"""A query planned once by [Context.prepare][xarray_sql.Context.prepare].
336+
337+
Call it to run the query, and get its rows as a ``pyarrow.Table``
338+
([to_dataset][xarray_sql.to_dataset] turns them back into a Dataset).
339+
``query()`` runs it on the values its tables hold now. ``query(w=ds)``
340+
first writes ``ds``'s values into the table ``w``.
341+
342+
A write goes into the arrays of the Dataset registered as ``w``, as
343+
``w_ds["v"].values[...] = ds["v"].values`` would. That Dataset changes
344+
too, and so does what any other query of the table returns. The new
345+
Dataset must have the registered one's data variables, dtypes, dims and
346+
coordinates. Its NaNs must be in the same places too, since xarray-sql
347+
reads a NaN as NULL, and a table works out which values are NULL when it
348+
is registered. To change anything else, register the new Dataset, and
349+
prepare the query again: a prepared query reads the tables that were
350+
registered when it was planned, and refuses to write into a name that
351+
has been registered again since.
352+
353+
A plan keeps state from a run: a hash join, for one, keeps the table it
354+
built. So each call runs a fresh copy of the plan, decoded from its
355+
serialized form, which is much cheaper than planning. Some plans can't
356+
be copied that way: a plan that reads a lazy table (a Dask array or a
357+
lazily opened file), or one nested too deep to decode. These are
358+
planned again on every call, so they return the right rows, without
359+
the savings.
360+
"""
361+
362+
def __init__(self, ctx: Context, query: str):
363+
self._ctx = ctx
364+
self._query = query
365+
# The Datasets a call may write into: those registered now.
366+
self._registered = dict(ctx._registered_datasets)
367+
self._plan: ExecutionPlan | None = None
368+
df = SessionContext.sql(ctx, query)
369+
self._schema = df.schema()
370+
plan = df.execution_plan()
371+
try:
372+
_copy(ctx, plan)
373+
except Exception: # noqa: BLE001 - DataFusion raises a bare Exception
374+
# It reads a table the plan's serialized form can't hold, or is
375+
# nested deeper than protobuf decodes.
376+
return
377+
self._plan = plan
378+
379+
def __call__(self, **tables: xr.Dataset) -> pa.Table:
380+
"""Run the query, after writing each Dataset given into the table
381+
of its name.
382+
383+
Args:
384+
**tables: A Dataset for each table to write new values into.
385+
386+
Returns:
387+
The query's rows.
388+
"""
389+
for name in tables:
390+
if name not in self._registered:
391+
raise ValueError(
392+
f"no Dataset was registered as {name!r} when the query "
393+
"was prepared"
394+
)
395+
now = self._ctx._registered_datasets.get(name)
396+
if now is not self._registered[name]:
397+
raise ValueError(
398+
f"{name!r} has been registered again since the query was "
399+
"prepared, and the query reads the table it was planned "
400+
"with; prepare the query again"
401+
)
402+
self._ctx._write(tables)
403+
if self._plan is None:
404+
return self._ctx.sql(self._query).to_arrow_table()
405+
plan = _copy(self._ctx, self._plan)
406+
batches = [
407+
batch.to_pyarrow()
408+
for partition in range(plan.partition_count)
409+
for batch in self._ctx.execute(plan, partition)
410+
]
411+
return pa.Table.from_batches(batches, self._schema)
412+
413+
414+
def _copy(ctx: SessionContext, plan: ExecutionPlan) -> ExecutionPlan:
415+
"""A copy of ``plan`` with none of its state from a run, reading its
416+
in-memory tables' values as they are now."""
417+
# TODO(#283): reset the plan's state with datafusion-python's binding of
418+
# reset_plan_states, once it has one, rather than decode it again.
419+
# `plan` itself never runs, so it has no state to leave behind.
420+
return ExecutionPlan.from_bytes(ctx, plan.to_bytes())
421+
422+
245423
XarrayContext = Context
246424
"""The original name of [Context][xarray_sql.Context], kept for compatibility."""

0 commit comments

Comments
 (0)