Data Visualisation Tools With DuckDB & MotherDuck - Hex/Preset

Check Your DuckDB Version

MotherDuck requires a recent version of DuckDB to function correctly. Verify the version in your Hex notebook by running a simple Python cell.

Copy code
# Check the installed DuckDB version in the Hex environment
import duckdb
print(duckdb.__version__)

You can always find the currently supported DuckDB version in our documentation. As of this writing, you're good to go!

Securely Store Your MotherDuck Token

Never hardcode credentials. Hex has a built-in secret manager that makes this easy.

1. Navigate to the "Secrets" tab in the left-hand sidebar of your Hex project.
2. Click "Add secret" and create a new secret named motherduck_token.
3. Paste your MotherDuck service token into the value field. You can generate a token from your MotherDuck profile page.

Hex will now make this secret available as a Python variable within your notebook.

Connect to MotherDuck

With your token securely stored, connecting is simple. Use a Python cell to load the secret as an environment variable. The DuckDB client will automatically detect it for authentication.

Copy code
import os
import duckdb

# Hex injects the secret as a Python variable named after the secret
os.environ['MOTHERDUCK_TOKEN'] = motherduck_token 

# Connect to MotherDuck using the 'md:' prefix
# The client automatically uses the MOTHERDUCK_TOKEN environment variable
con = duckdb.connect(database='md:')

The md: connection string tells the DuckDB client to connect to your MotherDuck account instead of a local file.

Run a Query and Create a Visualization

Now for the fun part. We'll query the sample_data database, which is a shared database available to all MotherDuck users. It's filled with useful datasets like the NYC Taxi data.

We'll find the peak taxi pickup hours throughout the week.

In a new Python cell, run this SQL query and load the results into a pandas DataFrame.

Copy code
# Query the NYC taxi dataset to find peak hours
peak_hours_df = con.sql("""
    SELECT 
        dayname(tpep_pickup_datetime) AS day_of_week, 
        strftime('%H', tpep_pickup_datetime) AS hour_of_day, 
        COUNT(*) AS trip_count
    FROM sample_data.nyc.taxi
    GROUP BY day_of_week, hour_of_day
    ORDER BY day_of_week, hour_of_day;
""").to_df()

# Display the DataFrame
peak_hours_df

With your peak_hours_df DataFrame ready, add a "Chart" cell in Hex. Configure it as a bar chart with hour_of_day on the X-axis, trip_count on the Y-axis, and use day_of_week to group the data.

In just a few clicks, you have a visualization showing taxi trip patterns across the week.

Hex excels at notebook-style analysis. But if you need a more traditional BI dashboard, Apache Superset is your answer.

Add a New Database Connection

In your Preset workspace, navigate to Settings in the top-right menu and select Database Connections. Click the + Database button to add a new source.

Configure the MotherDuck Connection

From the list of supported databases, select MotherDuck. Preset presents a simple form for your connection details.

• Display Name: Give your connection a memorable name, like "My MotherDuck."
• Database Name (Optional): You can specify a single database to connect to (e.g., md:my_db). However, you can leave this field blank to query across all databases your token can access with a single connection.
• Access Token: Paste your MotherDuck service token here.

Test and Connect

Click the Test Connection button to verify your credentials. If you see a "Connection looks good!" message, click Connect to save the data source.

Build a Dashboard

With MotherDuck connected, you can follow Preset's intuitive workflow:

1. Explore in SQL Lab: Go to the SQL Lab and write the same query we used in Hex to find the peak NYC taxi hours. Run the query to see the results.
2. Create a Dataset: From the query results, click Save dataset to make your query's output a reusable data source for charts.
3. Build a Chart: Create a new chart, selecting your newly saved dataset. Configure it as a bar chart just as you did in Hex.
4. Add to Dashboard: Save the chart and add it to a new or existing dashboard. You can now resize the chart, add other visualizations, and share the dashboard with your team.