Export from Simple Analytics to DuckDB

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

The CloudQuery Simple Analytics plugin allows you to sync data from Simple Analytics 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 Simple Analytics source plugin

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

Simple Analytics is a partner plugin, which means that it is maintained by the Simple Analytics team. Create a file called simpleanalytics.yaml, then copy the example and follow the instructions in the Simple Analytics Plugin Documentation ↗ to fit your needs.

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: "v2.0.1"
  write_mode: "overwrite-delete-stale"
  spec:
    connection_string: /path/to/example.db

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 simpleanalytics.yaml duckdb.yaml

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