Pandas, Polars, and Ibis

Query with Pandas, Polars, and Ibis

Use DuckDB's Python client and the DuckDB Altertable extension to query Altertable tables with Pandas, Polars, or Ibis. DuckDB executes SQL against the attached Altertable catalog, then returns results in the dataframe format you choose.

The Altertable community extension requires DuckDB 1.5.4 or later. For Ibis queries over HTTP, use the Python lakehouse client instead.

Connect DuckDB to Altertable

Load the community extension and attach an Altertable catalog:

Python

import os

import duckdb

con = duckdb.connect()
con.execute("INSTALL altertable FROM community")
con.execute("LOAD altertable")

con.execute(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)

The connection uses the LAKEHOUSE_USERNAME, LAKEHOUSE_PASSWORD, and LAKEHOUSE_CATALOG environment variables. For other connection options and DuckDB secrets, see Query with DuckDB.

Query into Pandas

Terminal

pip install 'duckdb==1.5.4' pandas

Call .df() on the result of a DuckDB query to return a Pandas DataFrame:

Python

events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").df()

print(events)

Query into Polars

Terminal

pip install 'duckdb==1.5.4' polars pyarrow

pyarrow is required for DuckDB's Polars integration.

Call .pl() to return a Polars DataFrame:

Python

events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").pl()

print(events)

Join remote tables with local DataFrames

Python

import polars as pl

customers = pl.DataFrame(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

con.register("customers_df", customers)
result = con.sql("""
    SELECT
        c.customer_id,
        c.segment,
        COUNT(*) AS event_count
    FROM customers_df AS c
    JOIN remote.main.events AS e
        ON e.customer_id = c.customer_id
    GROUP BY c.customer_id, c.segment
    ORDER BY event_count DESC
""").pl()

The same registration pattern works with Pandas DataFrames.

Query with Ibis

Terminal

pip install 'ibis-framework[duckdb]' 'duckdb==1.5.4'

Install pandas or polars pyarrow as well if you plan to call .to_pandas() or .to_polars().

Create an Ibis connection with the same attach workflow:

Python

import os

import ibis

con = ibis.duckdb.connect()
con.raw_sql("INSTALL altertable FROM community")
con.raw_sql("LOAD altertable")
con.raw_sql(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)

Reference a remote table, filter and sort with Ibis expressions, then materialize the result:

Python

events = con.table("events", database="remote.main")
activated = events.filter(events.event_name == "activated").order_by("event_id")

activated.to_pandas()

Return a Polars DataFrame instead:

Python

activated.to_polars()

Use sql() when you prefer to write SQL directly:

Python

events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""")

events.to_pandas()

Join a local memtable to an attached Altertable table in the same expression:

Python

customers = ibis.memtable(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

events = con.table("events", database="remote.main")
result = (
    customers.join(events, "customer_id")
    .group_by("customer_id", "segment")
    .aggregate(event_count=events.event_id.count())
    .order_by(ibis.desc("event_count"))
)

result.to_polars()

For the SQL-only workflow, including attachment options and direct table access, see Query with DuckDB.

Query with Ibis over HTTP

The Python lakehouse client also provides an optional Ibis backend. Install the Ibis extra:

Terminal

pip install 'altertable-lakehouse[ibis]'

Connect directly to Altertable without loading the DuckDB extension:

Python

import os

import ibis

con = ibis.altertable.connect(
    username=os.environ["LAKEHOUSE_USERNAME"],
    password=os.environ["LAKEHOUSE_PASSWORD"],
    catalog=os.environ["LAKEHOUSE_CATALOG"],
    database="main",
)

try:
    events = con.table("events")
    counts = events.group_by("event_name").aggregate(event_count=events.count())
    print(counts.execute())
finally:
    con.disconnect()

catalog selects the Altertable catalog; database selects its schema. Expressions execute remotely through the HTTP query API, and results are buffered in memory. Filter and aggregate before calling execute() to limit the result size.

This backend is for reading data. Use the lakehouse client's upload and ingestion methods for writes; local memtable joins and Python UDFs are not supported. For local DataFrame joins, use the DuckDB workflow above.