Jupyter
Jupyter notebooks run a Python kernel, so DuckDB and PyIceberg run inside the notebook and read your bucket or table directly. Nothing is copied first and there is no connection to configure in Jupyter itself. The same steps work in JupyterLab, Jupyter Notebook, Google Colab and VS Code; the last two differ only in where credentials go.
| You have | Use |
|---|---|
| Bucket objects, or an Iceberg or DuckLake table | DuckDB in a cell |
| You prefer SQL cells to Python strings | JupySQL on the same DuckDB connection |
| An Iceberg table, and you want Arrow or pandas without SQL | PyIceberg |
| BigQuery external tables over your bucket, in Colab | The %%bigquery magic |
We ran the DuckDB, JupySQL and PyIceberg cells on this page in a notebook executed with jupyter nbconvert --execute, against our own S3-compatible bucket and Iceberg REST catalog. The Google Colab and VS Code sections follow Colab's BigQuery notebook, the BigQuery magics reference and VS Code's documentation and were not run by us.
Install
Install the packages into the environment the kernel runs in. From a notebook cell, %pip installs into the kernel's own environment:
%pip install duckdb jupysql 'pyiceberg[pyarrow]' pandas
Restart the kernel after installing. Use DuckDB 2.0 or later if you read DuckLake tables.
Credentials
Keep keys out of the notebook file. Export them before you start Jupyter, and read them with os.environ in the notebook:
export S2B_KEY_ID='<access_key_id>'
export S2B_SECRET='<secret_access_key>'
jupyter lab
Use a read-only storage key, not the one Send2Bucket writes with. A kernel only sees variables that were set when the Jupyter server started, so restart the server after you change them.
DuckDB in a cell
import os
import duckdb
con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
con.execute(
"CREATE SECRET s2b_bucket (TYPE s3, KEY_ID ?, SECRET ?, REGION 'us-east-1')",
[os.environ["S2B_KEY_ID"], os.environ["S2B_SECRET"]],
)
con.sql("""
CREATE VIEW records AS
SELECT * FROM read_json(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited', hive_partitioning = true,
columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
resource: 'JSON', scope: 'JSON', data: 'JSON'})
""")
events = con.sql("""
SELECT data->>'$.event' AS event, count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records
WHERE signal = 'event' AND date >= current_date - 7
GROUP BY ALL ORDER BY events DESC
""").df()
events
.df() returns a pandas DataFrame, which the notebook renders as a table and which any plotting library takes as input.
For Google Cloud Storage, S3-compatible storage, or a table destination, swap the secret and view for the ones in DuckDB: the Iceberg and DuckLake sections end with the same records view, so the cells after it do not change. The example queries run unchanged in con.sql(...).
DuckDB keeps the view in memory. After a kernel restart, run the setup cell again.
SQL cells with JupySQL
JupySQL adds %sql and %%sql magics. Point it at the DuckDB connection from the previous cell, so SQL cells see the records view:
%load_ext sql
%sql con --alias s2b
Then write plain SQL in a cell:
%%sql
SELECT data->>'$.event' AS event, count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records WHERE signal = 'event'
GROUP BY ALL ORDER BY events DESC
To keep a result, name a variable with <<, then convert it in a Python cell:
%%sql by_signal <<
SELECT signal, count(*) AS records FROM records GROUP BY ALL ORDER BY records DESC
by_signal.DataFrame()
PyIceberg
For an Iceberg table, PyIceberg loads the table through the catalog and returns Arrow. DuckDB can then query the Arrow table by its variable name:
import os
import duckdb
from pyiceberg.catalog import load_catalog
catalog = load_catalog(
"s2b",
type="rest",
uri="<catalog_uri>",
warehouse="<warehouse>",
token=os.environ["S2B_CATALOG_TOKEN"],
**{
"s3.access-key-id": os.environ["S2B_KEY_ID"],
"s3.secret-access-key": os.environ["S2B_SECRET"],
"s3.region": "<region>",
},
)
table = catalog.load_table("<namespace>.<table>")
rows = table.scan(selected_fields=("signal_type", "ingested_at", "payload")).to_arrow()
duckdb.sql("""
SELECT payload::JSON->>'$.data.event' AS event, count(*) AS events,
sum((payload::JSON->>'$.data.cents')::INTEGER) / 100 AS revenue_usd
FROM rows WHERE signal_type = 'event'
GROUP BY ALL ORDER BY events DESC
""").df()
For S3-compatible storage add "s3.endpoint", and for S3 Tables or Supabase Analytics Buckets use the catalog properties in Python. to_arrow() reads the whole scan into memory; pass a row_filter on ingested_at to read less.
Run a notebook on a schedule
nbconvert runs every cell without a browser and saves the outputs, which turns a notebook into a scheduled report:
jupyter nbconvert --to notebook --execute report.ipynb --output report-latest.ipynb
Use --to html to publish the result as a page. Set the credential variables in the job's environment as above.
Google Colab
Colab runs the same cells, with two differences.
Credentials. Add S2B_KEY_ID and S2B_SECRET in the Secrets panel (the key icon in the left sidebar) and turn on notebook access for each. Read them with userdata instead of os.environ:
from google.colab import userdata
key_id, secret = userdata.get("S2B_KEY_ID"), userdata.get("S2B_SECRET")
con.execute(
"CREATE SECRET s2b_bucket (TYPE s3, KEY_ID ?, SECRET ?, REGION 'us-east-1')",
[key_id, secret],
)
Secrets belong to your Google account and are not included when you share the notebook. Colab's preinstalled DuckDB can lag behind; run %pip install --upgrade duckdb first if you need a newer version.
BigQuery. If your data is in Google Cloud Storage and you set up the s2b.records view from BigQuery, query it from Colab without storage keys. Sign in, then use the %%bigquery cell magic, which saves the result as a pandas DataFrame:
from google.colab import auth
auth.authenticate_user()
%%bigquery by_signal --project <project_id>
SELECT signal, COUNT(*) AS records
FROM s2b.records
WHERE date = '2026-11-19'
GROUP BY signal
ORDER BY records DESC
If Colab reports that the magic is not found, run %load_ext bigquery_magics first. Every %%bigquery cell is a BigQuery job, billed by bytes scanned, so filter on date.
VS Code
VS Code runs .ipynb files with the Jupyter extension, on the Python environment you pick as the kernel. Install the packages into that environment.
The Python extension loads a .env file from the workspace folder into the kernel's environment, so the os.environ cells above work unchanged:
S2B_KEY_ID=<access_key_id>
S2B_SECRET=<secret_access_key>
Add .env to .gitignore. After editing it, restart the kernel; if a value still does not appear, reload the window. The python.envFile setting points at a different file.
Notes
- Everything a cell returns is held in the kernel's memory. Filter on
date(bucket objects) oringested_at(tables) before calling.df()orto_arrow(). - A saved
.ipynbfile contains cell outputs, including query results. Clear outputs before committing a notebook that shows customer data. - For queries too large for one machine, or views shared with a team, use MotherDuck from the notebook, or connect to a warehouse from the querying guides.