Skip to main content

Hex

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

You have two ways to connect to MotherDuck using Hex:

  • Using SQL cells with a data connection: MotherDuck is a supported data connection in Hex.
  • Using Python cells: You can use Python cells to connect to MotherDuck and query data using DuckDB.

Using SQL cells with a data connection​

To add a new data connection, head over the Data browser in a new notebook and click on Add data donnection.

Select MotherDuck as the data source and fill in the required fields. The most important is the MotherDuck token, which you can find in the MotherDuck UI.

Once done, you can use the data browser to explore the tables and columns and directly specify your data connection in your SQL cell.

hex_data_browser

hex_sql_cell

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

Using Python cells​

If you prefer programming in Python, you can use Python cells to connect to MotherDuck and start query data. You can jump directly on the Hex notebook for a quickstart.

The notebook highlight how you can query data using Python or SQL cells and display charts!

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 access 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

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