Skip to main content

Hex

Hex notebooks run SQL against a data connection, or Python with any library. Pick the path by where your data already is.

You haveUse
No warehouse yetDuckDB in a Python cell
A MotherDuck accountA MotherDuck connection
Trino, Starburst or Athena over the bucket or Iceberg tableA Trino or Athena connection, with the tables from Trino
BigQueryA BigQuery connection, with the tables from BigQuery
Records loaded into PostgresA Postgres connection, with the table from PostgreSQL
note

Steps that happen in Hex follow Hex's documentation. The SQL is the same SQL we run against our own sample data.

DuckDB in a Python cell​

No connection to set up. DuckDB runs inside the notebook kernel and reads your bucket or table directly.

  1. In the project's Secrets, add S2B_KEY_ID and S2B_SECRET with a read-only storage key.

  2. In a Python cell:

    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')",
    [S2B_KEY_ID, 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()
  3. events is a pandas DataFrame. Chart it with a chart cell, or query it from a SQL cell with Hex's dataframe SQL.

For a table destination, replace the view with the ATTACH and view for your catalog from DuckDB. If import duckdb fails, run !pip install duckdb in a cell first.

Filter by date before pulling records into the notebook: everything a cell returns is held in the kernel's memory.

MotherDuck connection​

  1. Follow MotherDuck to create views over your data in a MotherDuck database.
  2. In Hex, add a MotherDuck data connection with a read-scoped MotherDuck token.
  3. Query the views from SQL cells. The example queries in DuckDB work unchanged.

Use this path when several people share the same views, or when queries are too large for the notebook kernel.

Warehouse connections​

If your team already queries Send2Bucket data through Trino, Athena, BigQuery or Postgres, add that engine as a Hex data connection and query the same tables. Workspace connections are managed by Hex admins, so analysts do not need storage credentials.