# Query with Pandas, Polars, and Ibis

Use DuckDB's Python client and the [DuckDB Altertable extension](https://github.com/altertable-ai/duckdb-altertable) to query Altertable tables with Pandas, Polars, or [Ibis](https://ibis-project.org/). 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](#query-with-ibis-over-http) 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](/docs/lakehouse/query/duckdb.md).

## Query into Pandas

```bash
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

```bash
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

Register a local DataFrame with DuckDB to use it in the same SQL query as an attached Altertable table:

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

```bash
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](/docs/lakehouse/query/duckdb.md).

## Query with Ibis over HTTP

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

```bash
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.