Skip to main content

Hex

Hex a software platform for collaborative data science and analytics using Python, SQL and no-code.

In this small guide, we will show you how to connect Hex to MotherDuck and display your first chart. You can jump directly on the Hex notebook for a quickstart. Hex notebook supports multiple kind of cells, including SQL, Python and no-code cells.

You can query MotherDuck using both Python and SQL cells! We'll showcase this in our quick guide, along with creating some data visualizations.

Storing your MotherDuck token​

The first step is to safely store your MotherDuck token. You can do this by creating a new secret in Hex.

Hex secrets

Let's add your MotherDuck service token under the name motherduck_token.

Hex secrets2

Once done, add the next Python cell to export as environment variable your motherduck_token. This will be detected by SQL/Python processes when authenticating to MotherDuck.

# Passing the secrets as environment variable for Python/SQL cell auth
# Fill in your token as a Hex project secret https://learn.hex.tech/docs/environment-configuration/environment-views#secret
import os
os.environ["motherduck_token"] = motherduck_token

Using Python cells​

Connecting to MotherDuck​

Connecting to MotherDuck is straightforward as DuckDB is already pre-installed in the Hex environment!

Add a Python cell and run the following code:

Hex add cell

import duckdb 

# Connect to MotherDuck using Python
conn = duckdb.connect(f'md:')

Query some data and display a chart​

We can now easily query some data based on the sample_data database. We will run a simple query and return it as a pandas dataframe in order to display it as a chart. This database is auto-attached to any MotherDuck user, so you can query it directly.

Add another Python cell and run the following code:

# Query sample_data database and convert it to a pandas dataframe for dataviz
peak_hours = conn.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()

Now we can display the chart using the Visualization cell. Add a new Visualization cell, type Chart and select the dataframe we just created peak_hours.

Hex chart

Finally, play with the parameters to obtain the following chart which gives you a weekly view of the peak hours in New York City for the yellow cabs.

Hex chart peak hours

Using SQL cells​

Connecting to MotherDuck​

Add a new SQL cell and select Dataframes to be able to query the current Dataframes in scope. This is needed as we don't setup a traditional SQL connection using Hex. Instead, we authenticate to MotherDuck using SQL commands. hex_sql

Finally, use the ATTACH command to connect to MotherDuck.

-- connect to MotherDuck using DuckDB SQL command Attach, motherduck_token should be set as environment variable
ATTACH 'md:';

Query some data​

Add another cell and run the same query we ran in a Python cell :

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;

This produces both a table and a Dataframe, which you can utilize in the same manner as we previously demonstrated with Python to generate data visualizations.

hex_sql_result