DuckDBGAM

GAM that reads data directly from DuckDB via SQL.

Usage

DuckDBGAM(
    formula,
    family=None,
    *,
    n_discrete=200,
    chunk_size=100000,
)

DuckDBGAM is a SQL-native variant of BigGAM for fitting a GAM without first loading the source data into pandas or polars. fit() accepts either a bare table/view name or an arbitrary SELECT query — anything expressible in SQL, including joins, filters, aggregations, and window functions — and DuckDB does the work of producing the resulting rows. Internally, _stream_as_dict reads those rows through DuckDB’s Arrow batch interface (conn.sql(query).to_arrow_reader(batch_size=chunk_size)), concatenating chunk_size-row Arrow batches into the column-oriented dict that build_discretized_model_matrix and bam_fit (the same discretized-basis machinery used by BigGAM) consume to fit the model. Use DuckDBGAM whenever the training data already lives in DuckDB, in Parquet/CSV files DuckDB can scan directly, or in a view/query that would be expensive to materialize by hand before fitting.

Parameters

formula: str or Formula: Model formula (same syntax as ~whittaker.gam.GAM), e.g. "y ~ s(x1) + s(x2)". The left-hand side names the response column that must be selectable from source; the right-hand side lists smooth terms, linear terms, and interactions in mgcv-style syntax.
family: Family = None: Response distribution family, e.g. Gaussian(), Binomial(), Poisson(), or Gamma(). Defaults to Gaussian() (identity link).
n_discrete: int = 200: Number of discretization grid points per covariate used when building the discretized model matrix (see build_discretized_model_matrix). Larger values give a more accurate approximation to the exact basis evaluation at the cost of a larger grid_size * p term in memory and compute. Defaults to 200, which is adequate for most smooths.
chunk_size: int = 100000: Number of rows per Arrow batch fetched from DuckDB while streaming (see Notes below). Larger values reduce Python-level batch-processing overhead but increase peak memory per batch; smaller values do the opposite. Defaults to 100_000.

Notes

Memory usage during fitting is bounded by O(n d + grid_size p)* rather than O(n p), where n is the row count, d the number of raw covariates, p the number of basis functions, and grid_size the discretization resolution (n_discrete). The n d term comes from streaming the raw columns rather than an n x p design matrix; the grid_size * p term comes from evaluating the basis only at the discretization grid. _stream_as_dict is what keeps the raw data at O(n d) rather than materializing a full n x p matrix twice as conn.sql(...).df() would: it pulls one chunk_size-row Arrow batch at a time from to_arrow_reader and appends each batch’s columns to a list, so DuckDB never has to build (and Python never has to hold) more than one batch’s worth of Arrow data plus the columns accumulated so far.

Examples

import duckdb
import numpy as np

import whittaker as wt
from whittaker.duckdb import DuckDBGAM

conn = duckdb.connect()
rng = np.random.default_rng(0)
conn.execute(
    "CREATE TABLE data AS "
    "SELECT i AS id, (i / 100.0) AS x, "
    "sin(2 * pi() * i / 100.0) + ? * random() AS y "
    "FROM range(200) AS t(i)",
    [0.2],
)

model = DuckDBGAM("y ~ s(x)").fit("data", conn)
print(model.summary())

GAM fit summary
============================================================
Formula:    y ~ s(x)
Family:     Gaussian(link='identity')
Inference:  REML
Observations: 0
Coefficients: 10

Parametric coefficients:
  Term                       Estimate    Std.Err    t value    p-value
  ------------------------ ---------- ---------- ---------- ----------
  (Intercept)                  0.1030     0.0000      0.000          1

Approximate significance of smooth terms:
  Term                        EDF Ref.df     Chi.sq    p-value
  ------------------------ ------ ------ ---------- ----------
  s(x)                       8.99      9  29134.077    < 1e-16

Total EDF:  9.99
Scale est:  0.003447
Deviance:   0.6549
Null dev:   101.0945
Dev. expl:  99.4%
GCV score:  0.003628
AIC:        -556.51
BIC:        -523.57

A query can be used directly in place of a table name, e.g. to filter or join before fitting:

model2 = DuckDBGAM("y ~ s(x)").fit_query(
    "SELECT x, y FROM data WHERE x < 0.8", conn
)
print(model2.n_rows)

Attributes

Name	Description
chunk_size	Arrow batch size used when streaming from DuckDB.
n_rows	Total number of rows in the DuckDB source used by the most recent fit.

chunk_size

Arrow batch size used when streaming from DuckDB.

chunk_size: int

This is the chunk_size value passed to __init__: the number of rows per Arrow batch that _stream_as_dict requests from conn.sql(query).to_arrow_reader(batch_size=...) while reading fit()’s data source.

n_rows

Total number of rows in the DuckDB source used by the most recent fit.

n_rows: int

Populated by fit() via _count_rows, which runs a SELECT COUNT(*) against the normalized source query before streaming the data. Remains 0 until fit() has been called at least once.

Methods

Name	Description
fit()	Fit the GAM by reading data from DuckDB.
fit_query()	Fit the GAM from an explicit SQL query.

fit()

Fit the GAM by reading data from DuckDB.

Usage

Source

fit(
    source,
    conn,
    *,
    smoothing_params=None,
    method="fREML",
    select=False,
)

Data is streamed in Arrow batches of chunk_size rows (see _stream_as_dict), then discretized and fit using the BigGAM approach (build_discretized_model_matrix + bam_fit). The full design matrix is never materialized.

Parameters

source: str: DuckDB table or view name, or a full SELECT query. A bare name such as "my_table" is normalized by _normalize_source into "SELECT * FROM my_table"; a string that already starts with SELECT (case-insensitive) is used as-is, e.g. "SELECT * FROM my_table WHERE year > 2020". Any query DuckDB can execute is accepted, including joins, aggregations, and window functions, as long as its output columns cover every variable referenced by formula.
conn: duckdb.DuckDBPyConnection: A live DuckDB connection (as returned by duckdb.connect()) against which data is resolved. The connection must remain open for the duration of fit(); it is not closed or modified by this method beyond issuing read queries.
smoothing_params: list of float = None: Fixed smoothing parameters. If None, selected automatically.
method: str = "fREML": Smoothing selection method: "fREML" (default), "REML", "ML", or "GCV".
select: bool = False: If True, enable double-penalty variable selection.

Returns

DuckDBGAM: Returns self for method chaining.

Notes

When data is unambiguously a SQL query rather than a table/view name, fit_query() is a thin, more explicit alias for this method.

fit_query()

Fit the GAM from an explicit SQL query.

Usage

Source

fit_query(
    query,
    conn,
    **kwargs,
)

Equivalent to calling fit(query, conn, ...) directly; this method exists purely to document intent at the call site when source is unambiguously a SQL query (e.g. one with a WHERE, JOIN, or aggregation) rather than a bare table or view name.

Parameters

query: str: A full SELECT query, e.g. "SELECT * FROM my_table WHERE year > 2020". Its output columns must cover every variable referenced by formula.
conn: duckdb.DuckDBPyConnection: A live DuckDB connection against which query is executed.
**kwargs: Additional keyword arguments forwarded to fit(): smoothing_params, method, and select.

Returns

DuckDBGAM: Returns self for method chaining.