Apache Superset
Apache Superset connects to a database through a SQLAlchemy URI, which you enter under Settings > Database Connections > + Database. Pick the engine that already reads your Send2Bucket data and install its Python driver where Superset runs.
| You have | URI | Driver package |
|---|---|---|
| Bucket objects, read in place | duckdb:////<path>/s2b.duckdb | duckdb-engine |
| A MotherDuck database | duckdb:///md:<database>?motherduck_token=<token> | duckdb-engine |
| Trino, Starburst | trino://<user>:<password>@<host>:443/<catalog> | trino |
| Records loaded into PostgreSQL | postgresql://<user>:<password>@<host>:5432/<database> | psycopg2 (in the Docker image) |
| BigQuery | bigquery://<project_id> | sqlalchemy-bigquery |
| ClickHouse | clickhousedb://<user>:<password>@<host>:8443/<database>?secure=true | clickhouse-connect |
We ran the DuckDB, Trino and PostgreSQL URIs, with the engine parameters below, through SQLAlchemy in Python, as Superset does, but not inside Superset itself. The BigQuery and ClickHouse URIs and the Superset menus follow Superset's database documentation.
For a Docker deployment, add driver packages to docker/requirements-local.txt and rebuild, or install them into the image you run.
DuckDB
DuckDB inside Superset reads your bucket directly, with no warehouse in between. Each Superset process opens its own DuckDB connection, so views must live in a database file, not in memory.
-
Create the file once, with the
recordsview from DuckDB:duckdb s2b.duckdb < records_view.sqlrecords_view.sqlholds theINSTALL httpfs,CREATE SECRETandCREATE VIEW recordsstatements. The secret only exists for this session, which is fine: DuckDB stores the view, not the credentials. -
Copy
s2b.duckdbto the same path on every Superset web and worker host, and add a database with the URIduckdb:////<path>/s2b.duckdb. -
Under Advanced > Other > Engine Parameters, open the file read-only and pass a read-only storage key:
{"connect_args": {"read_only": true,"config": {"s3_region": "<region>","s3_access_key_id": "<access_key_id>","s3_secret_access_key": "<secret_access_key>"}}}read_onlylets several Superset processes open the file at once. For S3-compatible storage, add"s3_endpoint": "<endpoint>"and"s3_url_style": "path"toconfig.
The records view then appears as a table. The DuckDB example queries run in SQL Lab as written; we ran them through this exact connection.
For a dataset that charts well, save a query such as this as a virtual dataset (SQL Lab > Save > Save dataset):
SELECT received_at,
date,
data->>'$.event' AS event,
(data->>'$.cents')::INTEGER AS cents
FROM records
WHERE signal = 'event'
Then chart count(*) and sum(cents) / 100 by event over received_at, with a time range filter on received_at. Add a filter on date too: DuckDB skips whole days of objects for a date filter, not for a received_at one.
MotherDuck
If your views live in MotherDuck, use duckdb:///md:<database>?motherduck_token=<token> with a read-scoped token. Nothing needs copying to the Superset hosts, and the bucket key stays in MotherDuck.
Trino and Starburst
Use trino://<user>:<password>@<host>:443/<catalog>, for example with catalog iceberg for an Iceberg destination or hive for bucket objects, set up as in Trino. A password in the URI is sent with basic authentication, which Trino accepts only over HTTPS. Give the user read access only.
Schemas and tables appear under the connection. payload is text, so the queries in Trino: Example queries extract fields with json_extract_scalar or json_value.
PostgreSQL, BigQuery and ClickHouse
- PostgreSQL: load records as in PostgreSQL and connect as a role with
SELECTons2b_recordsonly. - BigQuery:
bigquery://<project_id>, with a service account key uploaded in the connection form. The account needs the BigQuery Data Viewer and BigQuery Job User roles. Query the views from BigQuery. - ClickHouse:
clickhousedb://...withclickhouse-connect. Use port 8443 and?secure=truefor ClickHouse Cloud, or 8123 without TLS inside your network. Query the tables from ClickHouse.
Notes
- Superset leaves Allow DML off for new databases, so SQL Lab rejects
INSERT,CREATEand similar statements. Keep it off, and use read-only users and keys as well. - Chart results are cached for the database's cache timeout, or Superset's default, and only if a cache backend is configured. Set it no lower than how often new data arrives: about a minute for in-place reads, your load schedule for loaded tables.
- A DuckDB file connection reads your bucket from the Superset host on every uncached query. Keep its time range filters narrow, or use a table destination for long ranges.