Export from Notion to DuckDB

CloudQuery is an open-source data integration platform that allows you to export data from any source to any destination.

The CloudQuery Notion plugin allows you to sync data from Notion to any destination, including DuckDB. It's free, open source, requires no account, and takes only minutes to get started.

Ready? Let's dive right in!

Step 1. Install the CloudQuery CLI

The CloudQuery CLI is a command-line tool that runs the sync. It supports MacOS, Linux and Windows.

brew install cloudquery/tap/cloudquery

Step 2. Configure the Notion source plugin

Create a configuration file for the Notion plugin and set up authentication.

Configuration

Create a file called notion.yaml and add the following contents:

kind: source
spec:
  name: "notion"
  path: "cloudquery/notion"
  version: "v1.0.2"
  destinations:
    - "postgresql"
  spec:
    bearer_token: "${NOTION_SECRET_KEY}"

This example uses environment variable expansion to read the token from an NOTION_SECRET_KEY environment variable. You can also hardcode the value in the configuration file, but this is not advised for production settings.

Fine-tune this configuration to match your needs. For more information, see the Notion Plugin ↗ page in the docs.

Authentication

In Order for CloudQuery to sync resources from your Notion setup, you will need to create a notion integration key and export the Token in NOTION_SECRET_KEY environment variable. How to create the notion integration key? see here (opens in a new tab). Make sure to give proper Content Capabilities and User Capabilities from capabilities settings. Also give your integration page permissions see here (opens in a new tab). Only pages and databases with permission will able to sync.

export NOTION_SECRET_KEY=<your_notion_integration_key>

Step 3. Configure the DuckDB destination plugin

Create a configuration file for the DuckDB plugin and set up authentication.

Configuration

Create a file called duckdb.yaml and add the following contents:

This is a basic configuration that will save all your sync resources to /path/to/example.db.

kind: destination
spec:
  name: duckdb
  path: cloudquery/duckdb
  version: "v5.0.3"
  write_mode: "overwrite-delete-stale"
  spec:
    connection_string: /path/to/example.db
    # Optional parameters
    # batch_size: 1000
    # batch_size_bytes: 4194304 # 4 MiB
    # debug: false

After running cloudquery sync, you can explore the data locally with the DuckDB CLI: duckdb /path/to/example.db.

The default write_mode is overwrite-delete-stale, but the plugin also supports overwrite or append. Note that overwrite and overwrite-delete-stale modes do not support atomic updates: to update a resource, it is first deleted and then re-inserted. This is due to a current lack of support in DuckDB for upserting list-type columns. If this is an issue for you, consider using the append mode instead. You may then perform a manual cleanup of stale resources after the sync completes.

Note that this plugin does currently not support Windows. See this issue (opens in a new tab) for more details.

Fine-tune this configuration to match your needs. For more information, see the DuckDB Plugin ↗ page in the docs.

Step 4. Start the Sync

Run the following command in your terminal to start the sync

cloudquery sync notion.yaml duckdb.yaml

And away we go! 🚀 The sync will run until completion, fetching all selected tables from Notion. Any errors will be logged to a file called cloudquery.log.

Export from Notion to DuckDB

Step 1. Install the CloudQuery CLI

Step 2. Configure the Notion source plugin

Configuration

Authentication

Step 3. Configure the DuckDB destination plugin

Configuration

Step 4. Start the Sync

Further Reading