Skip to content

Instantly share code, notes, and snippets.

@AndreasAlbertQC
Last active September 26, 2025 09:31
Show Gist options
  • Select an option

  • Save AndreasAlbertQC/8c5b5a6356634e01e7fb4b3682e9b4c4 to your computer and use it in GitHub Desktop.

Select an option

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

Polars IO plugin for interacting with duckdb / ducklake

This gist demonstrates how to use a polars IO plugin to communicate lazily with DuckDB. Basic polars predicates are pushed down to SQL.

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚ 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)
# 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