# Ibis

Source: https://www.hotdata.dev/docs/ibis
Site index: https://www.hotdata.dev/llms.txt

Use [Ibis](https://ibis-project.org/) to create on-demand databases, upload data, and query with Python expressions — get pandas or Arrow results back without writing SQL.

## Install

```bash
pip install hotdata-ibis
```

## Connect

```python
import ibis

con = ibis.hotdata.connect(
    api_url="https://api.hotdata.dev",
    token="YOUR_API_KEY",
    workspace_id="<workspace_id>",
)
```

URL-style also works:

```python
con = ibis.connect("hotdata://api.hotdata.dev/?token=...&workspace_id=<workspace_id>")
```

## Quickstart: create a database and query it

```python
import time
import pandas as pd
import ibis

con = ibis.hotdata.connect(
    api_url="https://api.hotdata.dev",
    token="YOUR_API_KEY",
    workspace_id="<workspace_id>",
)

# 1. Create a database and declare the tables you'll load.
#    Hotdata database names are not unique — create_database returns the id
#    you'll use for every subsequent operation on this database.
db_id = con.create_database("sales", schema="public", tables=["orders"])

# 2. Upload a pandas DataFrame (or PyArrow table)
df = pd.DataFrame({
    "order_id": [1, 2, 3],
    "amount": [9.99, 49.99, 5.00],
    "region": ["west", "east", "west"],
})
con.create_table("orders", df, database=(db_id, "public"), overwrite=True)

# 3. Uploads are async — wait briefly before querying
time.sleep(2)

# 4. Query with Ibis expressions
#    Managed tables are always accessed with catalog "default"
t = con.table("orders", database=("default", "public"))
result = (
    t.group_by("region")
    .agg(total=t.amount.sum())
    .order_by(ibis.desc("total"))
    .execute()  # returns a pandas DataFrame
)

# 5. Clean up
con.drop_table("orders", database=(db_id, "public"))
con.drop_database(db_id)
```

## Instant databases

Instant databases are the primary way to bring data into Hotdata with Ibis. You create a database, upload data as parquet, and query immediately.

### Create and load

```python
# Declaring table names up front is optional — see below.
# create_database returns the database id — pass that id, not the name,
# to create_table, drop_table, and drop_database.
db_id = con.create_database("analytics", schema="public", tables=["events", "users"])

# Upload from a pandas DataFrame
con.create_table("events", events_df, database=(db_id, "public"), overwrite=True)
con.create_table("users", users_df, database=(db_id, "public"), overwrite=True)

# PyArrow tables also work
import pyarrow as pa
table = pa.table({"id": [1, 2], "name": ["alice", "bob"]})
con.create_table("users", table, database=(db_id, "public"), overwrite=True)
```

Declaring table names up front is optional. `create_table` loads into a table that was never listed in `tables=` just as happily — the table, and its schema if that is new too, is created as part of the load. See [Load database table](/docs/api-reference/databases#load-database-table-from-inline-data-upload-or-query-result). Declaring the names when you create the database keeps the database's intended shape explicit, which is why the examples here do it.

### Query

When querying, use `"default"` as the catalog — that is always the SQL prefix for managed tables:

```python
t = con.table("events", database=("default", "public"))

# Ibis expression
result = (
    t.filter(t.event_type == "click")
    .group_by("user_id")
    .agg(n=t.count())
    .execute()
)

# Or raw SQL
result = con.sql(
    'SELECT user_id, COUNT(*) AS n '
    'FROM "default"."public"."events" '
    'WHERE event_type = \'click\' '
    'GROUP BY user_id'
).execute()
```

### Delete

```python
con.drop_table("events", database=(db_id, "public"))
con.drop_database(db_id)
```

### Addressing summary

| Operation | `database=` argument |
|-----------|----------------------|
| `create_table` / `drop_table` | `(database_id, schema)` — the id returned by `create_database` |
| `con.table(...)` / `con.sql(...)` when querying | `("default", schema)` |

## Query with Ibis expressions

`.execute()` returns a pandas DataFrame. Use `.to_pyarrow()` for an Arrow table or `.to_pyarrow_batches()` to stream batches:

```python
t = con.table("orders", database=("default", "public"))

# Aggregate and sort
summary = (
    t.filter(t.amount > 10)
    .group_by("region")
    .agg(total=t.amount.sum(), n=t.count())
    .order_by(ibis.desc("total"))
    .execute()
)

# Arrow output
arrow_table = t.limit(1000).to_pyarrow()

# Streaming batches
with t.to_pyarrow_batches() as reader:
    for batch in reader:
        process(batch)
```

## Raw SQL

Use `con.sql(...)` when you need Hotdata-specific syntax that Ibis doesn't model. You can chain Ibis expressions on the result:

```python
base = con.sql(
    'SELECT * FROM "default"."public"."orders"',
    dialect="postgres",
)
result = base.filter(base.amount > 10).execute()
```

## Querying data from existing sources

To work with data from your existing databases or warehouses (Postgres, Snowflake, BigQuery, etc.), pull it into an instant database first with an [ingest source](/docs/pull-data). Once ingested, the data is an ordinary managed table — query it through Ibis exactly like the instant-database examples above, using catalog `"default"`:

```python
t = con.table("orders", database=("default", "public"))
result = t.filter(t.amount > 10).execute()
```

Discover what's available:

```python
con.list_catalogs()                                  # catalogs
con.list_databases(catalog="default")                # schemas
con.list_tables(database=("default", "public"))      # tables
con.get_schema("orders", catalog="default", database="public")
```

## What's supported

| Feature | |
|---------|---|
| `create_database` / `drop_database` | yes |
| `create_table` / `drop_table` (DataFrame or Arrow upload) | yes |
| `con.table(...)` with full schema metadata | yes |
| Filter, select, join, group\_by, agg, order\_by, limit | yes |
| `con.sql(...)` raw SQL | yes |
| `.execute()` → pandas, `.to_pyarrow()`, `.to_pyarrow_batches()` | yes |
| `list_catalogs`, `list_databases`, `list_tables` | yes |
| Temporary tables | no |
| Python UDFs | no |
| INSERT / UPDATE / DELETE | no |

SQL compilation uses Ibis's Postgres dialect. Use `con.sql(...)` as a fallback for expressions that don't compile cleanly.

## See also

- [hotdata-ibis on GitHub](https://github.com/hotdata-dev/hotdata-ibis)
- [Ibis documentation](https://ibis-project.org)
- [Python SDK](/docs/python-sdk) — lower-level `hotdata` API client