# DuckDBGAM


GAM that reads data directly from DuckDB via SQL.


Usage

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


[DuckDBGAM](DuckDBGAM.md#whittaker.DuckDBGAM) is a SQL-native variant of [BigGAM](BigGAM.md#whittaker.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](BigGAM.md#whittaker.BigGAM)) consume to fit the model. Use [DuckDBGAM](DuckDBGAM.md#whittaker.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()](Gaussian.md#whittaker.Gaussian), [Binomial()](Binomial.md#whittaker.Binomial), [Poisson()](Poisson.md#whittaker.Poisson), or [Gamma()](Gamma.md#whittaker.Gamma). Defaults to [Gaussian()](Gaussian.md#whittaker.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](BigGAM.md#whittaker.BigGAM.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


``` python
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:


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


    80


## Attributes

| Name | Description |
|----|----|
| [chunk_size](#chunk_size) | Arrow batch size used when streaming from DuckDB. |
| [n_rows](#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) | Fit the GAM by reading data from DuckDB. |
| [fit_query()](#fit_query) | Fit the GAM from an explicit SQL query. |

------------------------------------------------------------------------


### fit()


Fit the GAM by reading data from DuckDB.


Usage

``` python
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](BigGAM.md#whittaker.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()](DuckDBGAM.md#whittaker.DuckDBGAM.fit_query) is a thin, more explicit alias for this method.

------------------------------------------------------------------------


### fit_query()


Fit the GAM from an explicit SQL query.


Usage

``` python
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](CausalGAM.md#whittaker.CausalGAM.method), and `select`.


#### Returns


`DuckDBGAM`  
Returns `self` for method chaining.