click-runner¶

click-runner is a modular Python toolkit for loading data into ClickHouse from various external sources. It supports SQL query execution, CSV ingestion via ClickHouse URL engine, Parquet ingestion from S3 buckets, Google Drive CSV imports, and dedicated API ingestors for Mixpanel, CoW Protocol, Snapshot, and Discourse.

Purpose¶

Not all data in the Gnosis Analytics pipeline comes from blockchain nodes. click-runner handles the ingestion of external datasets:

• Ember -- global electricity generation data used for ESG carbon footprint calculations
• ProbeLab -- daily peer-to-peer network metrics (agent versions, peer distributions, crawl statistics)
• Governance -- Snapshot proposals, votes, and followers plus Discourse forum topics, posts, and users (feeds the Governance Explorer mini-app)
• Mixpanel -- product-analytics events and user profiles for Gnosis App and Gnosis Pay
• CoW Protocol -- open orders and trade fees from the CoW API
• Gnosis Pay on Celo -- card transfers and wallet events (crawlers_data.celo_gpay_*)
• Google Drive -- ad-hoc CSV datasets shared via Drive
• Administrative queries -- schema migrations, data maintenance, and custom SQL operations

Ingestion Modes¶

click-runner selects an ingestor via the --ingestor parameter: query, csv, parquet, gdrive, dune-execute-only, mixpanel, mixpanel-profiles, cow, snapshot, or forum.

Query Mode¶

Executes arbitrary SQL files against ClickHouse. Used for administrative tasks, schema updates, and custom transformations.

python run_queries.py --ingestor=query \
  --queries=queries/file1.sql,queries/file2.sql

CSV Mode¶

Imports data from CSV files using ClickHouse's built-in URL engine. The typical workflow involves three SQL files: table creation, data insertion (reading from a remote CSV URL), and optional optimization.

python run_queries.py --ingestor=csv \
  --create-table-sql=queries/ember/create_ember_table.sql \
  --insert-sql=queries/ember/insert_ember_data.sql \
  --optimize-sql=queries/ember/optimize_ember_data.sql

Parquet Mode¶

Imports data from Parquet files stored in S3 buckets. Supports three sub-modes:

# Import latest file
python run_queries.py --ingestor=parquet \
  --create-table-sql=queries/probelab/probelab_agent_semvers_avg_1d.up.sql \
  --s3-path=assets/agent_semvers_avg_1d_data/{{DATE}}.parquet \
  --table-name=crawlers_data.probelab_agent_semvers_avg_1d \
  --mode=latest

# Import file for a specific date
python run_queries.py --ingestor=parquet \
  --create-table-sql=queries/probelab/probelab_agent_semvers_avg_1d.up.sql \
  --s3-path=assets/agent_semvers_avg_1d_data/{{DATE}}.parquet \
  --table-name=crawlers_data.probelab_agent_semvers_avg_1d \
  --mode=date --date=2025-04-13

Google Drive Mode¶

Imports a CSV file shared on Google Drive by its file ID.

python run_queries.py --ingestor=gdrive \
  --create-table-sql=queries/new_source/create_table.sql \
  --table-name=crawlers_data.my_table \
  --file-id=<drive-file-id> --max-rows=1000000

Mixpanel Modes¶

Two ingestors cover Mixpanel: mixpanel exports raw events and mixpanel-profiles exports user profiles. Both write to the database configured via MIXPANEL_DATABASE (mixpanel_raw_events, mixpanel_raw_profiles, plus an ingestion-state watermark table).

# Daily incremental event export
python run_queries.py --ingestor=mixpanel --mixpanel-mode=daily --mixpanel-region=EU

# Historical backfill for a date range
python run_queries.py --ingestor=mixpanel --mixpanel-mode=backfill \
  --mixpanel-from-date=2026-01-01 --mixpanel-to-date=2026-06-30

--mixpanel-region selects the data-residency region (US, EU, IN); --mixpanel-event-filter restricts the export to a JSON array of event names.

CoW Mode¶

Fetches open orders and trade fees from the CoW Protocol API for owner addresses read from a source table (--cow-source-table, e.g. dbt.int_execution_cow_trades). Tables land in the database configured via COW_DATABASE.

Snapshot Mode (Governance)¶

Ingests Snapshot Hub GraphQL data — space metadata, proposals, votes, and followers — into the database configured via GOVERNANCE_DATABASE (snapshot_space, snapshot_proposals, snapshot_votes, snapshot_follows).

# Daily: refresh open + recently-closed proposals
python run_queries.py --ingestor=snapshot --snapshot-mode=daily --snapshot-vote-refresh-days=5

# Full backfill of all proposals and votes
python run_queries.py --ingestor=snapshot --snapshot-mode=backfill

Forum Mode (Governance)¶

Crawls the Discourse forum JSON API into forum_categories, forum_topics, forum_posts, and forum_users in the same governance database. Daily mode processes topics bumped since the stored watermark; backfill crawls everything (bounded by --forum-max-pages, 30 topics per page).

python run_queries.py --ingestor=forum --forum-mode=daily

Dune Execute-Only Mode¶

Triggers execution of dedicated Dune queries without ingesting results (used to refresh Dune-side materializations):

python run_queries.py --ingestor=dune-execute-only \
  --dune-execute-only-query-ids=123456,234567

Configuration¶

ClickHouse Connection¶

S3 Integration¶

Data Source URLs¶

Variable Substitution in SQL¶

SQL files support {{VARIABLE_NAME}} placeholders that are replaced with values from environment variables prefixed with CH_QUERY_VAR_. For example:

• Environment variable: CH_QUERY_VAR_EMBER_DATA_URL=https://example.com/data.csv
• In SQL: FROM url('{{EMBER_DATA_URL}}', 'CSV')

Docker Compose Services¶

The docker-compose.yml provides preconfigured services:

# Run the Ember ingestor
docker-compose run --rm ember-ingestor

# Run the ProbeLab ingestor
docker-compose run --rm probelab-agent-semvers-ingestor

# Run arbitrary queries
docker-compose run click-runner --ingestor=query \
  --queries=queries/file1.sql

Scheduling¶

click-runner services are typically scheduled as daily cron jobs:

# Daily Ember data update at 2 AM
0 2 * * * cd /path/to/click-runner && docker-compose run --rm ember-ingestor

# Daily ProbeLab data update at 3 AM
0 3 * * * cd /path/to/click-runner && docker-compose run --rm probelab-agent-semvers-ingestor

A convenience script cron_setup.sh is included to automatically create these cron entries.

Adding New Data Sources¶

New CSV Source¶

1. Create SQL files in queries/new_source/:
   • create_table.sql -- table schema definition
   • insert_data.sql -- INSERT using ClickHouse URL engine
   • optimize.sql (optional) -- post-load optimization
2. Set the data URL as an environment variable
3. Run with --ingestor=csv

New Parquet Source¶

1. Create a table definition SQL file in queries/new_source/
2. Run with --ingestor=parquet, specifying the S3 path pattern with {{DATE}} placeholder
3. Add a Docker Compose service for convenient scheduling

New File Format¶

Extend the framework by creating a new ingestor class that inherits from BaseIngestor in ingestors/:

1. Create ingestors/new_format_ingestor.py extending BaseIngestor
2. Implement the ingest() method
3. Register the new ingestor type in run_queries.py

Project Structure¶

click-runner/
├── run_queries.py          # Main CLI entry point
├── ingestors/
│   ├── base.py             # Abstract base ingestor
│   ├── csv_ingestor.py     # CSV ingestion logic
│   ├── parquet_ingestor.py # Parquet/S3 ingestion logic
│   ├── gdrive_ingestor.py  # Google Drive CSV imports
│   ├── mixpanel_ingestor.py            # Mixpanel raw events
│   ├── mixpanel_profiles_ingestor.py   # Mixpanel user profiles
│   ├── cow_ingestor.py     # CoW API orders + fees
│   ├── snapshot_ingestor.py# Snapshot governance data
│   └── forum_ingestor.py   # Discourse forum data
├── utils/
│   ├── s3.py               # S3 file discovery utilities
│   ├── db.py               # ClickHouse connection helpers
│   └── date.py             # Date parsing utilities
├── queries/
│   ├── ember/              # Ember electricity data SQL
│   ├── probelab/           # ProbeLab metrics SQL
│   ├── governance/         # Snapshot + forum table DDL
│   ├── celo_gpay/          # Gnosis Pay on Celo tables
│   ├── mixpanel/           # Mixpanel raw event tables
│   ├── mixpanel_profiles/  # Mixpanel profile tables
│   ├── cow/                # CoW API tables
│   └── dune/               # Dune imports
├── Dockerfile
└── docker-compose.yml

ClickHouse Table Schemas¶

The legacy CSV/Parquet sources below land in the crawlers_data database. The newer API ingestors write to env-configured databases instead: governance tables via GOVERNANCE_DATABASE, Mixpanel tables via MIXPANEL_DATABASE, and CoW tables via COW_DATABASE; Celo Gnosis Pay tables land in crawlers_data.celo_gpay_*.