Hacktoberfest 2026: as issues que os mantenedores marcaram para outubro, abertas e boas para iniciantes. Ver issues do Hacktoberfest

Support a mutable/appendable table for in-SQL iterative state (e.g. optimizer trajectories)

Aberta
  • Algo errado?
#194 1 comentário 0 reações 0 responsáveis Ver no GitHub

Mantenedores costumam responder em até 1 dia

Ninguém assumiu esta issue ainda.

  • #206 de @JHON12091986 — fechado sem integrar

Avaliação

Dificuldade
5/5
Tempo estimado
Mais de uma semana
Facilidade para iniciantes
25/100
Tipo de issue
Funcionalidade
Clareza
Claramente especificada
Status de atividade
Estagnada
Stack de tecnologia
apache, python, sql
Domínio
databases

Direção de pesquisa

The issue points to the _table_schemas() path and the autograd Substrait rewrite, but does not name their files; start by locating where schemas come from _registered_datasets and how rewrite tables are registered. The proposed direction is a separate mutable scratch table exposed from Python, with schema support in the rewrite. Done means SQL can append optimizer state without rebuilding xarray history; the issue recommends deferring this work.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

Motivation

The autograd work (#192) makes it possible to run gradient descent in SQL — the gradient of a loss is AVG(grad(loss, θ)), and an update is θ_new = θ - lr * AVG(grad(loss, θ)). A natural, SQL-native way to express the optimizer is a parameter table that grows one row per step, so the whole training trajectory becomes a relation you can query (e.g. the loss curve in a single GROUP BY). See the discussion in https://github.com/alxmrs/xarray-sql/pull/192#discussion_r3488049015.

Today that "append a new generation" step can't happen in SQL. The tables registered via XarrayContext.from_dataset are backed by a read-only, lazy Arrow stream provider (LazyArrowStreamTable), so there is no INSERT INTO. The demo works around it by rebuilding the whole history as an xarray Dataset and re-registering it each step:

def register_params():
    ctx.deregister_table("params")
    ds = xr.Dataset({"a": (("step",), a_hist), "b": (("step",), b_hist)},
                    coords={"step": steps})
    ctx.from_dataset("params", ds, chunks={"step": len(steps)})

It's correct and fine for a demo, but it's the one un-SQL-native part of an otherwise fully-relational training loop, and it re-materializes O(history) each step.

What we'd want

Some way to do, in SQL:

INSERT INTO params
SELECT :next_step,
       cur.a - lr * AVG(grad(loss, a)),
       cur.b - lr * AVG(grad(loss, b))
FROM d CROSS JOIN (SELECT a, b FROM params WHERE step = :k) cur
GROUP BY cur.a, cur.b;

so the optimizer's state lives in the engine and the Python side only drives iteration (or, eventually, doesn't — see "recursive CTE" below).

Design tension (why "xarray-backed append" specifically is awkward)

The obvious framing — "make xarray tables appendable" — fights the data model:

  • xarray/Zarr tables model immutable, dense, gridded arrays. A SQL row is one cell (coords…, values…). "Appending a row" to a gridded array isn't an INSERT; it's growing/concatenating along a coordinate dimension (a rechunk/xr.concat), which only makes sense for some tables (a 1-D params(step)) and not others (a 3-D (time, lat, lon) field).
  • The real need that surfaced is a small mutable scratch table for iteration state, which is conceptually separate from the large immutable data tables we read from Zarr.

So I'd frame this as "support mutable state tables," not "make xarray reads mutable."

Options

  1. Mutable in-memory scratch table (recommended if we do anything). Register a DataFusion-native mutable table (e.g. a MemTable, which already supports INSERT INTO in DataFusion) for iteration state, kept separate from xarray-backed tables. Work needed: expose a way to create/register such a table from Python, and make the autograd Substrait rewrite aware of it (it currently resolves table schemas from _registered_datasets, so a non-xarray table would need to be included in _table_schemas() / registered as an empty table on the rewrite side).
  2. Appendable xarray/Zarr-backed provider. A TableProvider that supports insert_into by appending along a dimension (or writing to a Zarr store). More work, and only natural for 1-D / append-along-one-dim tables; risks implying mutability for gridded data where it doesn't belong.
  3. Recursive CTE for the whole loop. The most ambitious "fully in SQL" answer: express N steps as WITH RECURSIVE. Blocked today — DataFusion's recursive-CTE support is limited (aggregates in the recursive term), and recursive CTEs + scalar subqueries don't round-trip through Substrait yet (the demo already hit "Cannot convert <subquery> to Substrait"). Tracking-only for now.

Do we think we should implement this?

My recommendation: not a priority, and not as "appendable xarray tables." Reasons:

  • The current re-registration workaround is correct and adequate for the demo; the per-step re-materialization cost is small for the state tables this is about.
  • "Append to xarray-backed tables" cuts against the immutable-gridded-array model that is the library's core value; baking mutability into that provider adds surface area and conceptual confusion for little gain.
  • If/when we want in-engine iteration, the clean path is a separate mutable scratch table (option 1), not mutating the data tables — and even that is arguably premature until there's a use case beyond the demo.

Filing this mainly to capture the idea and the design reasoning so the trade-off is on record. Happy to be argued out of it — option 1 is genuinely appealing for the "differential database" direction (a training loop whose state is a table), and a recursive-CTE loop (option 3) would be the dream once the engine/Substrait support lands.

Context: came out of building the autograd feature (#192) and its gradient-descent / MNIST-MLP-in-SQL demos.

Linguagem predominante
Python
Estrelas
142
Forks
19
Merge médio
1d 6h
PRs com merge (30d)
10

Preparar o ambiente

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de xqlsystems/xarray-sql

Todas as issues de xqlsystems/xarray-sql

Issues semelhantes

Mais issues de Python

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.