Skip to main content

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 haveURIDriver package
Bucket objects, read in placeduckdb:////<path>/s2b.duckdbduckdb-engine
A MotherDuck databaseduckdb:///md:<database>?motherduck_token=<token>duckdb-engine
Trino, Starbursttrino://<user>:<password>@<host>:443/<catalog>trino
Records loaded into PostgreSQLpostgresql://<user>:<password>@<host>:5432/<database>psycopg2 (in the Docker image)
BigQuerybigquery://<project_id>sqlalchemy-bigquery
ClickHouseclickhousedb://<user>:<password>@<host>:8443/<database>?secure=trueclickhouse-connect
note

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.

  1. Create the file once, with the records view from DuckDB:

    duckdb s2b.duckdb < records_view.sql

    records_view.sql holds the INSTALL httpfs, CREATE SECRET and CREATE VIEW records statements. The secret only exists for this session, which is fine: DuckDB stores the view, not the credentials.

  2. Copy s2b.duckdb to the same path on every Superset web and worker host, and add a database with the URI duckdb:////<path>/s2b.duckdb.

  3. 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_only lets several Superset processes open the file at once. For S3-compatible storage, add "s3_endpoint": "<endpoint>" and "s3_url_style": "path" to config.

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 SELECT on s2b_records only.
  • 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://... with clickhouse-connect. Use port 8443 and ?secure=true for 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, CREATE and 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.