Hex
Hex notebooks run SQL against a data connection, or Python with any library. Pick the path by where your data already is.
| You have | Use |
|---|---|
| No warehouse yet | DuckDB in a Python cell |
| A MotherDuck account | A MotherDuck connection |
| Trino, Starburst or Athena over the bucket or Iceberg table | A Trino or Athena connection, with the tables from Trino |
| BigQuery | A BigQuery connection, with the tables from BigQuery |
| Records loaded into Postgres | A Postgres connection, with the table from PostgreSQL |
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.
-
In the project's Secrets, add
S2B_KEY_IDandS2B_SECRETwith a read-only storage key. -
In a Python cell:
import duckdbcon = 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 ASSELECT * 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_usdFROM records WHERE signal = 'event' AND date >= current_date - 7GROUP BY ALL ORDER BY events DESC""").df() -
eventsis 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
- Follow MotherDuck to create views over your data in a MotherDuck database.
- In Hex, add a MotherDuck data connection with a read-scoped MotherDuck token.
- 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.