This gist demonstrates how to use a polars IO plugin to communicate lazily with DuckDB. Basic polars predicates are pushed down to SQL.
Last active
September 26, 2025 09:31
-
-
Save AndreasAlbertQC/8c5b5a6356634e01e7fb4b3682e9b4c4 to your computer and use it in GitHub Desktop.
Polars IO plugin for interacting with duckdb / ducklake with basic projection and predicate pushdown
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| βββββββββββββββ¬βββββββββββββββββββββββββββββ¬βββββββββββββββββ¬ββββββββββββββββββββββββββββββββββ | |
| β snapshot_id β snapshot_time β schema_version β changes β | |
| β int64 β timestamp with time zone β int64 β map(varchar, varchar[]) β | |
| βββββββββββββββΌβββββββββββββββββββββββββββββΌβββββββββββββββββΌββββββββββββββββββββββββββββββββββ€ | |
| β 0 β 2025-06-05 17:47:29.706+02 β 0 β {schemas_created=[main]} β | |
| β 1 β 2025-06-05 17:47:29.722+02 β 1 β {tables_created=[main.mytable]} β | |
| β 2 β 2025-06-05 17:47:29.723+02 β 1 β {tables_inserted_into=[1]} β | |
| β 3 β 2025-06-05 17:47:29.734+02 β 1 β {tables_inserted_into=[1]} β | |
| βββββββββββββββ΄βββββββββββββββββββββββββββββ΄βββββββββββββββββ΄ββββββββββββββββββββββββββββββββββ | |
| DEBUG:__main__:select * from mytable AT (VERSION => 1) | |
| DEBUG:__main__:select * from mytable AT (VERSION => 2) | |
| DEBUG:__main__:select * from mytable AT (VERSION => 3) | |
| DEBUG:__main__:select * from mytable AT (VERSION => 3) where (x < 2) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Author: AndreasAlbertQC | |
| # Date: 2025-06-05 | |
| import json | |
| import logging | |
| import polars as pl | |
| from polars.io.plugins import register_io_source | |
| from typing import Iterator | |
| import duckdb | |
| from pathlib import Path | |
| logger = logging.getLogger(__name__) | |
| def main(): | |
| logging.basicConfig(level=logging.DEBUG) | |
| # Create a new lake on every script run | |
| # For demo purposes only | |
| ducklake_file = Path("/tmp/test.ducklake") | |
| ducklake_file.unlink(missing_ok=True) | |
| with duckdb.connect() as con: | |
| # Set up duck lake | |
| # Creates version 0 with empty schema | |
| con.sql(f""" | |
| INSTALL ducklake; | |
| ATTACH 'ducklake:{ducklake_file}' AS my_ducklake; | |
| USE my_ducklake; | |
| """) | |
| # Version 1: empty table created | |
| con.sql("create table mytable (x int, y string)") | |
| # Version 2: Insert | |
| con.sql("insert into mytable values (1, 'abc'), (2, 'def')") | |
| # Version 3: Another insert | |
| con.sql("insert into mytable values (3, 'ghi'), (4, 'jkl')") | |
| # Show snapshots | |
| print(con.sql("SELECT * FROM my_ducklake.snapshots()")) | |
| # Version 1 has 0 rows | |
| lf_1 = duckdb_source(con, "mytable", extra="AT (VERSION => 1)") | |
| assert len(lf_1.collect()) == 0 | |
| # Version 2 has 2 rows | |
| lf_2 = duckdb_source(con, "mytable", extra="AT (VERSION => 2)") | |
| assert len(lf_2.collect()) == 2 | |
| # Version 3 has 4 rows | |
| lf_2 = duckdb_source(con, "mytable", extra="AT (VERSION => 3)") | |
| assert len(lf_2.collect()) == 4 | |
| # Show that filtering works in SQL | |
| lf_2.filter(pl.col("x")<2).collect() | |
| def duckdb_source(connection: duckdb.DuckDBPyConnection, table: str, extra: str = "") -> pl.LazyFrame: | |
| """ | |
| A polars IO plugin for DuckDB. | |
| """ | |
| query = f"select * from {table} {extra} limit 0" | |
| schema = connection.sql( | |
| query | |
| ).pl().schema | |
| def source_generator( | |
| with_columns: list[str] | None, | |
| predicate: pl.Expr | None, | |
| n_rows: int | None, | |
| batch_size: int | None, | |
| ) -> Iterator[pl.DataFrame]: | |
| if with_columns is None: | |
| cols = "*" | |
| else : | |
| cols = ",".join(with_columns) | |
| query = f"select {cols} from {table} {extra}" | |
| if predicate is not None: | |
| query += f" where {_predicate_to_sql(predicate)}" | |
| if n_rows is not None: | |
| query += f" limit {n_rows}" | |
| logger.debug(query) | |
| yield connection.sql(query).pl() | |
| return register_io_source(io_source=source_generator, schema=schema) | |
| # ---------------------------- Implementation --------------------------------------# | |
| def _predicate_to_sql(predicate: pl.Expr): | |
| """Convert a polars predicate to a SQL expression""" | |
| tree = _into_tree(predicate) | |
| return _pl_tree_to_sql(tree) | |
| def _into_tree(expr:pl.Expr): | |
| """Obtain a tree representation of the expression""" | |
| # I feel like there should be a better way of doing this, | |
| # but I don't know it :) | |
| return json.loads(expr.meta.serialize(format="json")) | |
| def _pl_operation_to_sql(op: str) -> str: | |
| """Translate a polars operation string to SQL""" | |
| match op: | |
| case "Lt": | |
| return "<" | |
| case "LtEq": | |
| return "<=" | |
| case "Gt": | |
| return ">" | |
| case "GtEq": | |
| return ">=" | |
| case "Eq": | |
| return "=" | |
| case "Modulus": | |
| return "%" | |
| case "And": | |
| return "AND" | |
| case _: | |
| raise NotImplementedError(op) | |
| def _pl_tree_to_sql(tree: dict): | |
| """Convert a polars expression tree to a SQL statement""" | |
| [node_type] = tree.keys() | |
| subtree = tree[node_type] | |
| match node_type: | |
| case "BinaryExpr": | |
| return ("(" | |
| + " ".join((_pl_tree_to_sql(subtree['left']), _pl_operation_to_sql(subtree['op']), _pl_tree_to_sql(subtree['right']))) | |
| + ")") | |
| case "Column": | |
| return subtree | |
| case "Literal": | |
| return _pl_tree_to_sql(subtree) | |
| case "Dyn": | |
| return _pl_tree_to_sql(subtree) | |
| case "Int": | |
| return str(subtree) | |
| case "Scalar": | |
| match subtree['dtype']: | |
| case "Int32": | |
| return str(subtree['value']['Int32']) | |
| case "String": | |
| return "'{}'".format(subtree['value']['StringOwned']) | |
| case _: | |
| raise NotImplementedError(node_type) | |
| if __name__ == "__main__": | |
| main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment