# Querying data (/notebooks/querying-data)

Read and write Iceberg tables from a notebook using the Databaas helpers.

Read Iceberg tables into Arrow, pandas or Polars, look at earlier versions of a table, and write
tables of your own, all under your own login. For the first ten rows end to end, see the
[quickstart](/getting-started/quickstart).

## Load the catalog

`get_catalog` from `databaas-utils` returns a pyiceberg catalog scoped to *your* grants. It fetches a
token for you, so there are no credentials to configure. See the [databaas-utils documentation](https://wolkwork.github.io/databaas-utils/) for the
full API.

```python title="Load the catalog and a table"
from databaas_utils.notebook import get_catalog

catalog = get_catalog()

catalog.list_namespaces()
catalog.list_tables("<namespace>")

table = catalog.load_table("<namespace>.<table>")
```

Use the warehouse, namespace and table name from the Data Catalog breadcrumb — see [browsing the
catalog](/catalog/browsing). With no argument you get your deployment's default warehouse; for
another one, name it: `get_catalog(warehouse="<warehouse>")`. Identifiers take a dotted string
(`"sales.orders"`) or a tuple (`("sales", "orders")`).

You only see and read what you have been granted. An empty `list_namespaces()` means you have no data
grants yet, and a table you have no grant for raises a permission error — the same boundary the
catalog preview enforces. See [data permissions](/catalog/data-permissions).

`catalog` is a standard pyiceberg catalog, so the rest of the [pyiceberg
API](https://py.iceberg.apache.org/api/) works on it, within your grants.

## Scan rows

`table.scan()` is lazy: nothing is read until you convert it. Convert to whichever library you work
in:

```python title="First rows"
scan = table.scan(limit=10)

scan.to_arrow()    # pyarrow.Table
scan.to_pandas()   # pandas.DataFrame
scan.to_polars()   # polars.DataFrame
```

Everything you convert loads into your session's memory, so an unbounded scan of a large table
exhausts it — see [session resources](/notebooks/sessions#resources). Narrow in the scan itself, which
also lets Iceberg skip whole files: `selected_fields` picks columns, `row_filter` picks rows, `limit`
caps the count.

```python title="A narrowed scan"
scan = table.scan(
    selected_fields=("account_id", "amount_eur", "channel"),
    row_filter="channel = 'mobile' AND amount_eur > 100",
    limit=100_000,
)

scan.count()                       # rows that match, without loading them
arrow_table = scan.to_arrow()
```

For a result that does not fit in memory, `scan.to_arrow_batch_reader()` streams it in batches.

## Read an earlier version

Every write to an Iceberg table creates a snapshot, and you can read the table as it was at any of
them:

```python title="Time travel"
for entry in table.history():
    print(entry.snapshot_id, entry.timestamp_ms)

first = table.history()[0].snapshot_id
table.scan(snapshot_id=first).to_pandas()
```

`timestamp_ms` is milliseconds since the Unix epoch. The [table details](/catalog/table-details) in
the Data Catalog show which snapshot is current.

## Write a table

<Callout type="warn" title="This is the real lakehouse">
  Writes go to the same tables dashboards and pipelines use, not to a scratch copy. Reading needs a
  **Select** grant; writing to a table needs **Modify** on it, and creating a namespace or table
  needs **Create** on the warehouse or namespace you create it in. See
  [data permissions](/catalog/data-permissions#make-a-grant). Keep experiments in a namespace of your own, not one a
  pipeline owns — an append there lands in that pipeline's data. If you are not sure what you may
  write, ask your platform administrator.
</Callout>

Build a pyarrow table, create the namespace and table if they do not exist yet, then append:

```python title="Create a table and append rows"
import pyarrow as pa

rows = pa.Table.from_pylist([
    {"city": "Amsterdam", "temperature": 18.4},
    {"city": "De Bilt", "temperature": 17.9},
])

catalog.create_namespace_if_not_exists("sandbox")
table = catalog.create_table_if_not_exists("sandbox.hello", schema=rows.schema)
table.append(rows)

table.scan().to_pandas()
```

The table takes its schema from `rows.schema`. Later writes must match that schema.

To replace data instead of adding to it, use `overwrite`. With no filter it replaces the whole table;
with `overwrite_filter` it replaces only the matching rows:

```python title="Replace all rows, or some"
table.overwrite(rows)

table.overwrite(
    pa.Table.from_pylist([{"city": "Amsterdam", "temperature": 19.2}]),
    overwrite_filter="city = 'Amsterdam'",
)
```

Each write is a new snapshot, so the rows you replaced are still readable through [an earlier
version](#read-an-earlier-version).

`catalog.drop_table("<namespace>.<table>")` removes a table from the catalog; `catalog.purge_table(...)`
also deletes its data files, and cannot be undone.

## Query a slice with DuckDB

Register an Arrow table as a DuckDB view and query it with SQL:

```python title="SQL over an Arrow slice"
import duckdb

con = duckdb.connect()
con.register("tx", arrow_table)

con.sql("""
    SELECT channel, count(*) AS n
    FROM tx
    GROUP BY channel
    ORDER BY n DESC
""").show()
```

This runs entirely in your notebook, so it only sees the rows you scanned in. See the [DuckDB
documentation](https://duckdb.org/docs/).

## When to switch to SQL Lab

Notebooks pull data to your code — right for exploration and tables that fit in your session. When a
table is bigger than your session, or you want a query to run next to the data and return only the
result, use [SQL Lab](/dashboards/sql-lab), on Trino.

## Your catalog does not go stale

Unlike a plain pyiceberg catalog, `catalog` here asks for a fresh token on every request instead of
holding the one it was built with. You can keep the same `catalog` open for as long as your notebook
runs; there is no need to call `get_catalog()` again partway through.

The one thing that can still expire is your sign-in itself. If a call fails saying the hub could not
refresh your login, sign out of Notebooks and sign back in — a sign-in stays refreshable for about 30
days of use.