Skip to content

Commit 059af80

Browse files
alxmrsclaude
andcommitted
Address review: NaT, large tables, tables registered again, now()
- A write must keep NaT in place, as it keeps NaN: a table reads both as NULL, from a validity bitmap made when it's registered. - Keep the plan's copy only where copying is faster than planning. Copying a plan copies its in-memory tables, so over a 36 MB table a call took 45 ms against ctx.sql's 1 ms; it now plans again. - Every call checks that the tables the query reads, through any views, are still the ones it was prepared with, on either path. Context.deregister_table forgets the Dataset, so a table replaced by any means counts as registered again. - Say that now() is evaluated when the query is prepared. - Shorten PreparedQuery's docstring to what a caller needs; how plans are copied moves to a comment. Co-Authored-By: Claude Opus 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01NyN7idACeDMh94qrnFMu6h
1 parent df52375 commit 059af80

3 files changed

Lines changed: 144 additions & 50 deletions

File tree

‎docs/performance.md‎

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -193,10 +193,16 @@ for weights in trajectory: # Datasets shaped like w's
193193
rows = step(w=weights) # a pyarrow.Table
194194
```
195195

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.
196+
On a join of two 1,024-row tables, a call takes 0.41 ms, and `ctx.sql`
197+
takes 0.99 ms (one target partition). A write must keep the table's
198+
shape, dtypes, coordinates, and NaN and NaT positions. Functions such as
199+
`now()` are evaluated once, when the query is prepared.
200+
201+
The savings are for small tables. Each call runs a copy of the plan,
202+
which copies the in-memory tables it reads. Where that's slower than
203+
planning, as it is over tables of more than about a megabyte, a call
204+
plans the query again and runs as fast as `ctx.sql`. So does a call of
205+
a plan over a lazy table (Dask or a lazily opened file).
200206

201207
## Round-trip faster with ORDER BY
202208

‎tests/test_prepare.py‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
"""Tests for queries planned once and run many times (`Context.prepare`)."""
22

3+
import time
4+
35
import numpy as np
46
import pytest
57
import xarray as xr
@@ -135,3 +137,56 @@ def test_a_write_needs_the_table_the_query_was_planned_with(ctx):
135137
ctx.from_dataset("a", _a(np.ones((4, 3))))
136138
with pytest.raises(ValueError, match="registered again"):
137139
query(a=_a(np.ones((4, 3))))
140+
141+
142+
def _times(*days):
143+
t = np.array(
144+
[np.datetime64("NaT") if d is None else np.datetime64(d) for d in days],
145+
dtype="datetime64[ns]",
146+
)
147+
return xr.Dataset({"t": ("i", t)}, coords={"i": np.arange(len(t))})
148+
149+
150+
def test_a_write_must_keep_nat_in_place():
151+
ctx = Context()
152+
ctx.from_dataset("a", _times("2000-01-01", None, "2000-01-03"))
153+
query = ctx.prepare("SELECT COUNT(t) AS n, MIN(t) AS m FROM a")
154+
with pytest.raises(ValueError, match="NaT"):
155+
query(a=_times(None, "2000-01-02", "2000-01-03"))
156+
rows = query(a=_times("2000-01-04", None, "2000-01-02")).to_pydict()
157+
assert rows["n"] == [2]
158+
assert str(rows["m"][0]) == "2000-01-02 00:00:00"
159+
160+
161+
def test_a_query_reads_its_tables_as_they_were_prepared():
162+
pytest.importorskip("dask")
163+
ctx = Context()
164+
ctx.from_dataset("a", _a(np.ones((4, 3))).chunk({"i": 2}))
165+
query = ctx.prepare("SELECT SUM(v) AS s FROM a")
166+
ctx.deregister_table("a")
167+
ctx.from_dataset("a", _a(np.full((4, 3), 2.0)))
168+
with pytest.raises(ValueError, match="registered again"):
169+
query()
170+
171+
172+
def test_a_table_replaced_by_datafusion_counts_as_registered_again(ctx):
173+
query = ctx.prepare(JOIN)
174+
ctx.deregister_table("a")
175+
ctx.register_record_batches("a", [ctx.sql("SELECT * FROM b").collect()])
176+
with pytest.raises(ValueError, match="registered again"):
177+
query()
178+
179+
180+
def test_a_table_the_query_doesnt_read_can_be_registered_again(ctx):
181+
query = ctx.prepare("SELECT SUM(v) AS s FROM a")
182+
want = query()
183+
ctx.deregister_table("b")
184+
ctx.from_dataset("b", _b(np.ones((4, 3))))
185+
assert query().equals(want)
186+
187+
188+
def test_now_is_the_time_the_query_was_prepared(ctx):
189+
query = ctx.prepare("SELECT now() AS t")
190+
first = query()
191+
time.sleep(0.01)
192+
assert query().equals(first)

‎xarray_sql/sql.py‎

Lines changed: 79 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,13 @@
1+
import time
12
import uuid
23

34
import numpy as np
45
import pyarrow as pa
56
import xarray as xr
67
from datafusion import SessionConfig, SessionContext
78
from datafusion.catalog import Schema
8-
from datafusion.plan import ExecutionPlan
9+
from datafusion.expr import TableScan
10+
from datafusion.plan import ExecutionPlan, LogicalPlan
911
from types import ModuleType
1012

1113
from . import cftime as cft
@@ -242,6 +244,12 @@ def sql(self, query: str, *args, **kwargs) -> XarrayDataFrame:
242244
"""
243245
return self._wrap(super().sql(query, *args, **kwargs))
244246

247+
def deregister_table(self, name: str) -> None:
248+
"""Remove a table from the session, and forget the Dataset
249+
registered under its name."""
250+
super().deregister_table(name)
251+
self._registered_datasets.pop(name, None)
252+
245253
def prepare(self, query: str) -> "PreparedQuery":
246254
"""Plan a query once, to run it many times.
247255
@@ -261,7 +269,7 @@ def prepare(self, query: str) -> "PreparedQuery":
261269
rows = step(w=weights)
262270
263271
See [PreparedQuery][xarray_sql.PreparedQuery] for what a call
264-
can write, and which plans are run again rather than planned again.
272+
can write.
265273
266274
Args:
267275
query: A SQL query string.
@@ -311,15 +319,17 @@ def _write(self, writes: dict[str, xr.Dataset]) -> None:
311319
"the new Dataset instead, and prepare the query again"
312320
)
313321
values = new.transpose(*old.dims).values
314-
# A table works out which values are NULL (NaN) when it is
315-
# registered, so a write must keep its NaNs in place.
316-
if values.dtype.kind in "fc" and not np.array_equal(
317-
np.isnan(values), np.isnan(target)
322+
# A table works out which values are NULL (NaN or NaT) when
323+
# it is registered, so a write must keep them in place.
324+
missing = _missing(values)
325+
if missing is not None and not np.array_equal(
326+
missing, _missing(target)
318327
):
319328
raise ValueError(
320-
f"the new values of {name}.{var} have NaN in other "
321-
"places; xarray-sql reads a NaN as NULL, so register "
322-
"the new Dataset instead, and prepare the query again"
329+
f"the new values of {name}.{var} have NaN or NaT in "
330+
"other places; xarray-sql reads them as NULL, so "
331+
"register the new Dataset instead, and prepare the "
332+
"query again"
323333
)
324334
copies.append((registered[var], ds[var]))
325335
# Assigning into a registered DataArray writes into its array, which
@@ -335,47 +345,48 @@ def _wrap(self, inner) -> XarrayDataFrame:
335345
class PreparedQuery:
336346
"""A query planned once by [Context.prepare][xarray_sql.Context.prepare].
337347
338-
Call it to run the query, and get its rows as a ``pyarrow.Table``
339-
([to_dataset][xarray_sql.to_dataset] turns them back into a Dataset).
340-
``query()`` runs it on the values its tables hold now. ``query(w=ds)``
341-
first writes ``ds``'s values into the table ``w``.
342-
343-
A write goes into the arrays of the Dataset registered as ``w``, as
344-
``w_ds["v"][...] = ds["v"]`` would, matching dims by name. That Dataset changes
345-
too, and so does what any other query of the table returns. The new
346-
Dataset must have the registered one's data variables, dtypes, dims and
347-
coordinates. Its NaNs must be in the same places too, since xarray-sql
348-
reads a NaN as NULL, and a table works out which values are NULL when it
349-
is registered. To change anything else, register the new Dataset, and
350-
prepare the query again: a prepared query reads the tables that were
351-
registered when it was planned, and refuses to write into a name that
352-
has been registered again since.
353-
354-
A plan keeps state from a run: a hash join, for one, keeps the table it
355-
built. So each call runs a fresh copy of the plan, decoded from its
356-
serialized form, which is much cheaper than planning. Some plans can't
357-
be copied that way: a plan that reads a lazy table (a Dask array or a
358-
lazily opened file), or one nested too deep to decode. These are
359-
planned again on every call, so they return the right rows, without
360-
the savings.
348+
``query()`` runs it on the values its tables hold now, and returns its
349+
rows as a ``pyarrow.Table``. ``query(w=ds)`` first writes ``ds``'s
350+
values into the Dataset registered as ``w``, as
351+
``w_ds["v"][...] = ds["v"]`` would, so other queries of ``w`` see them
352+
too. ``ds`` must match ``w_ds`` in all but its values: the same data
353+
variables, dtypes, dims and coordinates, and NaN or NaT in the same
354+
places, since xarray-sql reads them as NULL.
355+
356+
To change anything else, register the new Dataset and prepare the
357+
query again. A call raises if a table the query reads has been
358+
registered again since it was prepared. Functions such as ``now()``
359+
are evaluated once, when the query is prepared.
361360
"""
362361

363362
def __init__(self, ctx: Context, query: str):
364363
self._ctx = ctx
365364
self._query = query
366-
# The Datasets a call may write into: those registered now.
367-
self._registered = dict(ctx._registered_datasets)
368-
self._plan: ExecutionPlan | None = None
365+
start = time.perf_counter()
369366
df = SessionContext.sql(ctx, query)
370-
self._schema = df.schema()
371367
plan = df.execution_plan()
368+
planning = time.perf_counter() - start
369+
self._schema = df.schema()
370+
# The Datasets the query reads, as they are registered now.
371+
self._registered = {
372+
name: ctx._registered_datasets[name]
373+
for name in _tables_read(df.logical_plan())
374+
if name in ctx._registered_datasets
375+
}
376+
# A plan keeps state from a run (a hash join keeps the table it
377+
# built), so each call runs a fresh copy of it, decoded from its
378+
# serialized form. Copying a plan copies its in-memory tables, so
379+
# over large ones it's slower than planning again; a plan that reads
380+
# a lazy table, or is nested deeper than protobuf decodes, can't be
381+
# copied at all. Either way, each call plans the query again.
382+
self._plan: ExecutionPlan | None = None
383+
start = time.perf_counter()
372384
try:
373385
_copy(ctx, plan)
374386
except Exception: # noqa: BLE001 - DataFusion raises a bare Exception
375-
# It reads a table the plan's serialized form can't hold, or is
376-
# nested deeper than protobuf decodes.
377387
return
378-
self._plan = plan
388+
if time.perf_counter() - start < planning:
389+
self._plan = plan
379390

380391
def __call__(self, **tables: xr.Dataset) -> pa.Table:
381392
"""Run the query, after writing each Dataset given into the table
@@ -390,19 +401,18 @@ def __call__(self, **tables: xr.Dataset) -> pa.Table:
390401
for name in tables:
391402
if name not in self._registered:
392403
raise ValueError(
393-
f"no Dataset was registered as {name!r} when the query "
394-
"was prepared"
404+
f"the query reads no Dataset registered as {name!r} "
405+
"when it was prepared"
395406
)
396-
now = self._ctx._registered_datasets.get(name)
397-
if now is not self._registered[name]:
407+
for name, registered in self._registered.items():
408+
if self._ctx._registered_datasets.get(name) is not registered:
398409
raise ValueError(
399410
f"{name!r} has been registered again since the query was "
400-
"prepared, and the query reads the table it was planned "
401-
"with; prepare the query again"
411+
"prepared; prepare the query again"
402412
)
403413
self._ctx._write(tables)
404414
if self._plan is None:
405-
return self._ctx.sql(self._query).to_arrow_table()
415+
return SessionContext.sql(self._ctx, self._query).to_arrow_table()
406416
plan = _copy(self._ctx, self._plan)
407417
batches = [
408418
batch.to_pyarrow()
@@ -421,5 +431,28 @@ def _copy(ctx: SessionContext, plan: ExecutionPlan) -> ExecutionPlan:
421431
return ExecutionPlan.from_bytes(ctx, plan.to_bytes())
422432

423433

434+
def _tables_read(plan: LogicalPlan) -> set[str]:
435+
"""The names of the tables ``plan`` scans, through any views."""
436+
names = set()
437+
stack = [plan]
438+
while stack:
439+
node = stack.pop()
440+
variant = node.to_variant()
441+
if isinstance(variant, TableScan):
442+
names.add(variant.table_name())
443+
stack.extend(node.inputs())
444+
return names
445+
446+
447+
def _missing(values: np.ndarray) -> np.ndarray | None:
448+
"""Where ``values`` holds NaN or NaT, which xarray-sql reads as NULL, or
449+
``None`` for a dtype that holds neither."""
450+
if values.dtype.kind in "fc":
451+
return np.isnan(values)
452+
if values.dtype.kind in "mM":
453+
return np.isnat(values)
454+
return None
455+
456+
424457
XarrayContext = Context
425458
"""The original name of [Context][xarray_sql.Context], kept for compatibility."""

0 commit comments

Comments
 (0)