rmoff's random ramblings
about talks

Exporting Notebooks from DuckDB UI

Published Mar 19, 2025 by in DuckDB at https://rmoff.net/2025/03/19/exporting-notebooks-from-duckdb-ui/

DuckDB added a very cool UI last week and I’ve been using it as my primary interface to DuckDB since.

One thing that bothered me was that the SQL I was writing in the notebooks wasn’t exportable. Since DuckDB uses DuckDB in the background for storing notebooks, getting the SQL out is easy enough.

Here’s a rough-and-readyscript that will dump it out to a series of .sql files, one per notebook. It only exports the SQL; any other data like useDatabase and isActive is not included.

#!/bin/bash
#
# @rmoff / 2025-03-19

# Create a temporary file for the JSON output
temp_file=$(mktemp)

# Run the DuckDB query and save the output to the temp file
duckdb -json ~/.duckdb/extension_data/ui/ui.db \
        -c 'select title,json from ui.main.notebook_versions where expires is null' \
        | grep -v "^Run Time" > "$temp_file"

# Create a directory with timestamp for the output
timestamp=$(date +"%Y%m%d_%H%M%S")
output_dir="DuckDB_notebooks_${timestamp}"
mkdir -p "$output_dir"

# Process the JSON output
jq -c '.[]' "$temp_file" | while read -r item; do

    # Extract title
    title=$(echo "$item" | jq -r '.title')

    # Create a sanitized filename from the title
    # Replace spaces with underscores and remove special characters
    filename=$(echo "$title" | tr ' ' '_' | tr -cd 'a-zA-Z0-9_-').sql

    # Extract all queries from the notebook JSON and save them to the file
    # Credit to Hayley Jane Wakenshaw for the Duck ASCII art :)
    echo "$item" | jq -r '.json' | jq -r '.cells[] | "\n--           _      _      _\n--         >(.)__ <(.)__ =(.)__\n--          (___/  (___/  (___/ \n-- °º¤ø,¸¸,ø¤º°`°º¤ø,¸,ø¤°º¤ø,¸¸,ø¤º°`°º¤ø,¸\n\n" + .query' > "$output_dir/${filename}"

    echo "Created file: $filename"
done

# Clean up the temporary file
rm "$temp_file"

echo "All notebook queries have been saved to individual files in folder $output_dir."
echo ""
echo "To create a new gist run: gh gist create --desc \"DuckDB UI Notebook export $timestamp\" $output_dir/*.sql"

In use it looks like this:

Created file: Load_historical_data.sql
Created file: Load_latest_data.sql
Created file: Buildadd_to_main_tables.sql
Created file: Join_and_Analyse.sql
Created file: Untitled_Notebook.sql
All notebook queries have been saved to individual files in folder DuckDB_notebooks_20250319_170611.

To create a new gist run: gh gist create DuckDB_notebooks_20250319_170611/*.sql

I use it to then upload the SQL to gist (the GitHub CLI tool gh is awesome, install it if you haven’t already!) with:

❯ gh gist create --desc "DuckDB UI Notebook export 20250319_170851" DuckDB_notebooks_20250319_170851/*.sql
- Creating gist with multiple files
✓ Created secret gist Buildadd_to_main_tables.sql
https://gist.github.com/rmoff/7c681529754a74d7e8f6bff31e069d5b
DuckDB UI Notebook export to gist

For other DuckDB UI notebook export ideas, see here and here

Robin Moffatt

Robin Moffatt works on the DevRel team at Confluent. He likes writing about himself in the third person, eating good breakfasts, and drinking good beer.

Story logo

© 2025