Querying data

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.

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 for the full API.

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. 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 is a standard pyiceberg catalog, so the rest of the pyiceberg 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:

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. Narrow in the scan itself, which also lets Iceberg skip whole files: selected_fields picks columns, row_filter picks rows, limit caps the count.

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:

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 in the Data Catalog show which snapshot is current.

Write a table

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

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

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:

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.

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

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.

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, 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.