search

crate / sqlalchemy-cratedb

SQLAlchemy dialect for CrateDB.
https://cratedb.com/docs/sqlalchemy-cratedb/
Apache License 2.0
3 stars 2 forks source link

Add `table_kwargs` context manager to make pandas/Dask support CrateDB's special SQL DDL options #139

Closed amotl closed 2 months ago

amotl commented 2 months ago

Problem

In certain cases where SQLAlchemy orchestration is implemented within a framework, like at this spot ^1 in pandas' SQLTable._create_table_setup, it is not easily possible to forward SQLAlchemy dialect options at table creation time.

Idea

Unlock SQLAlchemy ORM's __table_args__ on the pandas/Dask to_sql() interface, in order to support CrateDB's special SQL DDL options.

Solution

In order to augment the SQL DDL statement to make it honor database-specific dialect options, the only way to work around the unfortunate situation is by monkey-patching the call to sa.Table() at runtime, relaying additional dialect options through corresponding keyword arguments in their original <dialect>_<kwarg> format ^2.

Synopsis

Using a context manager incantation like with table_kwargs(crate_partitioned_by="time") will render a PARTITIONED BY ("time") SQL clause, without touching the call site of sa.Table(...).

from sqlalchemy_cratedb.support import table_kwargs

# Load data into database, using Dask.
ddf = dd.from_pandas(df, npartitions=npartitions)
with table_kwargs(crate_partitioned_by="time"):
    return ddf.to_sql(
        tablename,
        uri=dburi,
        index=index,
        chunksize=chunksize,
        if_exists=if_exists,
        method=method,
        parallel=True,
    )

Documentation

Preview: https://sqlalchemy-cratedb--139.org.readthedocs.build/support.html#context-manager-table-kwargs

References

Backlog