ClickHouse Runner

A modular toolkit for ClickHouse data ingestion from various sources, particularly useful for running ETL jobs via Docker.

Overview

click-runner is designed for flexible query execution and data ingestion with support for different data formats and sources:

CSV Ingestion: Load data from CSV files using ClickHouse's URL engine
Parquet Ingestion: Load data from Parquet files in S3 buckets
SQL Execution: Run arbitrary SQL files against a ClickHouse database

Prerequisites

Docker
Docker Compose (for using the provided docker-compose.yml)
ClickHouse server

Project Structure

.
├── Dockerfile
├── docker-compose.yml
├── requirements.txt
├── run_queries.py                 # Main CLI entry point 
├── ingestors/                     # Ingestor modules
│   ├── __init__.py
│   ├── base.py                    # Abstract base ingestor
│   ├── csv_ingestor.py            # CSV ingestion (e.g., Ember data)
│   └── parquet_ingestor.py        # Parquet ingestion (e.g., ProbeLab data)
├── utils/                         # Utility modules
│   ├── __init__.py
│   ├── s3.py                      # S3 utilities
│   ├── db.py                      # Database utilities
│   └── date.py                    # Date utilities
└── queries/                       # SQL query files
    ├── ember/                     # Ember electricity data
    │   ├── create_ember_table.sql
    │   ├── insert_ember_data.sql
    │   └── optimize_ember_data.sql
    └── probelab/                  # ProbeLab data
        ├── probelab_agent_semvers_avg_1d.up.sql
        ├── probelab_agent_types_avg_1d.up.sql
        └── ... (other create table queries)

Environment Variables

Set the following environment variables to configure the ClickHouse connection:

CH_HOST: ClickHouse host
CH_PORT: Native ClickHouse port (default: 9000)
CH_USER: Username for authentication
CH_PASSWORD: Password for authentication
CH_DB: Database to use
CH_SECURE: Use TLS connection (True or False)
CH_VERIFY: Verify TLS certificate (True or False)

For S3 integration:

S3_ACCESS_KEY: AWS access key ID
S3_SECRET_KEY: AWS secret access key
S3_BUCKET: S3 bucket name (default: prod-use1-gnosis)
S3_REGION: AWS region (default: us-east-1)

For Ember data:

EMBER_DATA_URL: URL to the Ember CSV data

Running Modes

The system supports four primary running modes, controlled by the --ingestor parameter:

1. Query Mode (`--ingestor=query`)

Execute arbitrary SQL queries directly against ClickHouse.

Usage:

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

# Docker
docker-compose run click-runner --ingestor=query --queries=queries/file1.sql,queries/file2.sql

Environment variable alternative:

CH_QUERIES=queries/file1.sql,queries/file2.sql

Use cases:

Running administrative queries
Database maintenance
Schema updates
Custom data transformations

2. CSV Mode (`--ingestor=csv`)

Import data from CSV files using ClickHouse's URL engine. Typically used for Ember electricity data.

Usage:

# CLI
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

# Docker
docker-compose run ember-ingestor

Use cases:

Importing public datasets available as CSV files
Scheduled updates from static CSV URLs
When data source is a REST API that returns CSV

3. Parquet Mode (`--ingestor=parquet`)

Import data from Parquet files in S3 buckets, with three ingestion strategies:

Latest (--mode=latest): Import only the most recent file
Date (--mode=date): Import a file for a specific date
All (--mode=all): Import all available files

Usage:

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

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

All Files:

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=all

Using Docker:

docker-compose run probelab-agent-semvers-ingestor

Use cases:

Daily ingestion of time-series data stored as Parquet
Backfilling historical Parquet data
Importing structured data from data lakes

4. Dune Execute-Only Mode (`--ingestor=dune-execute-only`)

Trigger dedicated Dune queries through ClickHouse without saving query results into ClickHouse tables.

Usage:

export DUNE_EXECUTE_ONLY_QUERY_IDS="1234567,2345678,3456789"
export CH_QUERY_VAR_DUNE_API_KEY="your-dune-api-key"

python run_queries.py --ingestor=dune-execute-only

Using Docker:

docker-compose run --rm dune-execute-only-daily-ingestor

Use cases:

Refreshing Dune result caches for a separate set of queries
Scheduling Dune query executions independently from data ingestion
Keeping execute-only query IDs separate from the existing Dune ingestion queries

Common Parameters

All modes share these common parameters:

--host: ClickHouse host (default: from CH_HOST env var)
--port: ClickHouse port (default: from CH_PORT env var)
--user: ClickHouse user (default: from CH_USER env var)
--password: ClickHouse password (default: from CH_PASSWORD env var)
--db: ClickHouse database (default: from CH_DB env var)
--secure: Use TLS connection (default: from CH_SECURE env var)
--verify: Verify TLS certificate (default: from CH_VERIFY env var)
--skip-table-creation: Skip table creation steps (optional flag)

Docker Compose Services

The docker-compose.yml file includes several predefined services:

click-runner: Generic service that can run in any mode
ember-ingestor: Specialized for Ember CSV data
probelab-agent-semvers-ingestor: Example for one ProbeLab Parquet dataset
dune-execute-only-daily-ingestor: Triggers dedicated Dune queries without ingesting their results

Setting Up Cron Jobs

To run data ingestion as a daily cron job, you can use the provided Docker containers:

# Example crontab entry for daily Ember data update at 2 AM
0 2 * * * cd /path/to/click-runner && docker-compose run --rm ember-ingestor

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

For convenience, you can use the included cron_setup.sh script to automatically create these cron jobs:

chmod +x cron_setup.sh
sudo ./cron_setup.sh

Adding New Data Sources

1. Adding a New CSV Data Source

Create table definition SQL file: queries/new_source/create_table.sql
Create insert SQL file: queries/new_source/insert_data.sql
(Optional) Create optimization SQL file: queries/new_source/optimize.sql
Add environment variable for data URL: NEW_SOURCE_URL

Run:

python run_queries.py --ingestor=csv \
  --create-table-sql=queries/new_source/create_table.sql \
  --insert-sql=queries/new_source/insert_data.sql

2. Adding a New Parquet Data Source

Create table definition SQL file: queries/new_source/new_source_table.up.sql

Run:

python run_queries.py --ingestor=parquet \
  --create-table-sql=queries/new_source/new_source_table.up.sql \
  --s3-path=assets/new_source_data/{{DATE}}.parquet \
  --table-name=database.new_source_table \
  --mode=latest

3. Adding to Docker Compose

For ease of use, add a new service to docker-compose.yml:

new-source-ingestor:
  build:
    context: .
    dockerfile: Dockerfile
  container_name: new-source-ingestor
  volumes:
    - ./queries:/app/queries
  environment:
    CH_HOST: ${CH_DB_HOST}
    CH_PORT: ${CH_NATIVE_PORT}
    CH_USER: ${CH_USER}
    CH_PASSWORD: ${CH_PASSWORD}
    CH_DB: ${CH_DB}
    CH_SECURE: ${CH_SECURE}
    CH_VERIFY: "False"
    CH_QUERY_VAR_NEW_SOURCE_URL: ${NEW_SOURCE_URL:-}
    CH_QUERY_VAR_S3_ACCESS_KEY: ${S3_ACCESS_KEY:-}
    CH_QUERY_VAR_S3_SECRET_KEY: ${S3_SECRET_KEY:-}
    CH_QUERY_VAR_S3_BUCKET: ${S3_BUCKET:-}
    CH_QUERY_VAR_S3_REGION: ${S3_REGION:-}
  command: >
    --ingestor=parquet
    --create-table-sql=queries/new_source/new_source_table.up.sql
    --s3-path=assets/new_source_data/{{DATE}}.parquet
    --table-name=database.new_source_table
    --mode=latest

Supporting a New File Format

If you need to support a new file format beyond CSV and Parquet:

Create a new ingestor class that extends BaseIngestor in ingestors/new_format_ingestor.py
Implement the ingest() method and any format-specific methods
Update run_queries.py to recognize the new ingestor type

Example: Adding Support for Avro Files

If you wanted to add support for Avro files, you would:

Update requirements.txt to include Avro-related packages
Create ingestors/avro_ingestor.py extending BaseIngestor
Implement the specialized logic for Avro ingestion
Update run_queries.py to support --ingestor=avro
Create sample Avro ingestion Docker Compose services

Advanced Usage

Variable Substitution in SQL

SQL files can use variable placeholders with the {{VARIABLE_NAME}} syntax. These 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')

Skip Table Creation

If tables already exist, you can skip the table creation step:

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

Running Multiple Ingestors

For complex workflows, you can chain multiple ingestors:

# First run ember ingestor
docker-compose run --rm ember-ingestor

# Then run probelab ingestor
docker-compose run --rm probelab-agent-semvers-ingestor

External prices standbein (DefiLlama + CoinGecko)

Phase 1 parallel feed for cross-check / future hub fallback. Does not change int_execution_token_prices_daily priority logic. Allowlist: config/external_prices_tokens.yml.

Mode	What it does
`backfill`	Daily history into `crawlers_data.defillama_prices` / `coingecko_prices`. DefiLlama: `~365d`, or all history with `--external-prices-defillama-full-history` (pages `/chart` backwards; it caps a request at 500 points). CoinGecko: `365d` max on the free tier — for full history use `scripts/full_history_coingecko_prices.py`
`daily`	One settled day, `00:00 UTC` (default: yesterday). DefiLlama `/prices/historical` batched; CoinGecko `/coins/{id}/history` per token

Every row is the price at 00:00 UTC of its block_date — i.e. the close of the previous day. Both sources are anchored to that same boundary, which is what makes them comparable (median disagreement 0.043% across 34 tokens on one day).

Two things that are easy to get wrong here, both guarded in the code:

Neither daily source may use a "current price" endpoint. Those return a live tick, which stamps an intraday price under a whole-day block_date — the basis then depends on when cron happened to run.
DefiLlama answers a 00:00 request with the last tick before it (e.g. 23:59:04 the previous day). Taking block_date from the returned timestamp lands every row a day early.

Both tables are plain MergeTree and do not dedupe, so every write path inserts first and then prunes superseded rows — re-running any mode is safe.

Env vars

ClickHouse: usual CH_* / compose CH_DB_HOST, etc.
COINGECKO_API_KEY — Demo key. Effectively required for daily: it makes one /history call per token, and the keyless tier throttles hard enough that a run cannot finish. Also accepted as CH_QUERY_VAR_COINGECKO_API_KEY
EXTERNAL_PRICES_SOURCE — defillama | coingecko | both (default both)
EXTERNAL_PRICES_MODE — daily | backfill
EXTERNAL_PRICES_DAILY_LAG_DAYS — which UTC day daily writes, days back from today (default 1). 0 is also correct when the job runs well after midnight
EXTERNAL_PRICES_DEFILLAMA_FULL_HISTORY — backfill pages back to each token's first price instead of --external-prices-chart-span-days
EXTERNAL_PRICES_DATABASE — target DB (default crawlers_data; use playground_max for Max-dev)

CLI

# One-time history (Max-dev: write to playground_max — no CREATE on crawlers_data)
python run_queries.py --ingestor=external-prices \
  --external-prices-source=both --external-prices-mode=backfill \
  --external-prices-database=playground_max \
  --external-prices-tokens-config=config/external_prices_tokens.yml

# Daily spot
python run_queries.py --ingestor=external-prices \
  --external-prices-source=both --external-prices-mode=daily \
  --external-prices-database=playground_max \
  --external-prices-tokens-config=config/external_prices_tokens.yml

dbt source schema (must match ingest DB):

# PowerShell
$env:DBT_EXTERNAL_PRICES_SCHEMA = "playground_max"

Docker Compose

docker-compose run --rm external-prices-backfill-ingestor
docker-compose run --rm external-prices-daily-ingestor

dbt (after ingest) — target pg_max; set DBT_EXTERNAL_PRICES_SCHEMA=playground_max so staging reads the tables you just wrote:

dbt run -s stg_crawlers_data__defillama_prices stg_crawlers_data__coingecko_prices \
  int_execution_token_prices_external_daily int_execution_token_prices_compare \
  --target pg_max

Hub Phase 2a: int_execution_token_prices_daily picks off-chain as DefiLlama (confidence >= 0.9) > CoinGecko > Dune at priority 3 (below native / backedfi). Keep int_execution_token_prices_compare for QA. K8s CronJob + prod crawlers_data writes are still deferred (dev uses playground_max).

HOPR (network dashboard + blokli)

Two ingestors feeding four tables in {{HOPR_DATABASE}} (crawlers_data in prod). Consumed by dbt-cerebro's stg_crawlers_data__hopr_* staging views, fct_hopr_network_health_daily and int_hopr_nodes.

Ingestor	Tables	Coverage
`hopr_network_ingestor.py`	`hopr_network_nodes`, `hopr_network_online_hourly`	dufour only — the prober was never ported to jura/v4
`hopr_blokli_ingestor.py`	`hopr_blokli_nodes`, `hopr_blokli_network_snapshot`	jura + rotsee only — blokli does not serve dufour; rotsee is a testnet

The two feeds are mirror images and neither covers both networks. That is upstream reality, not a gap to close.

These tables must exist in crawlers_data before the dbt side runs in prod

The four tables live only in a dev database until an ingestor run creates them in crawlers_data (both ingestors issue CREATE TABLE IF NOT EXISTS, so a single run of each is enough — the cron does not have to be deployed yet).

dbt-cerebro's HOPR models read them through stg_crawlers_data__hopr_*, and a ClickHouse view over a missing table fails to CREATE rather than returning nothing. So if the tables are absent the first production dbt run errors on those models instead of building them empty. Nothing is live yet — the whole HOPR set is still development-only — but this is the thing to clear before it ships.

Run the ingestors against prod (or deploy the cron), then let the dbt side run there. Either order of the two ingestors is fine; they share nothing.

Running them

Both are classes invoked through run_queries.py — they have no __main__, so python -m ingestors.hopr_network_ingestor imports the module and exits silently without ingesting anything.

python run_queries.py --ingestor=hopr-network --hopr-database=playground_max
python run_queries.py --ingestor=hopr-blokli --hopr-blokli-networks=jura,rotsee --hopr-database=playground_max

In prod, drop both overrides: --hopr-database defaults to crawlers_data, and --hopr-blokli-networks defaults to jura alone. Adding rotsee is a dev convenience — it is a testnet whose ticket price and balances are orders of magnitude away from production. dbt flags it as is_testnet so it cannot be summed in by accident, but there is no reason to carry it in prod.

There is no docker-compose service for either ingestor yet — whoever deploys will add one, or a K8s CronJob alongside the other daily ingestors.

Deploying the schedule

Run once a day. --hopr-network-mode no longer changes what is fetched, so there is no wrong mode to pick — that was deliberate, see below.

All four tables are ReplacingMergeTree(ingested_at) and every dbt staging view over them reads FINAL, so re-runs, catch-ups and overlapping runs are all safe. Running more than once a day is harmless; running less loses data permanently.

Two properties that decide the cadence:

hopr_network_nodes, hopr_blokli_nodes and hopr_blokli_network_snapshot are forward-only daily snapshots. They are queried for today; there is no historical endpoint. A day the job does not run is a hole that can never be filled. This is the whole argument for deploying the schedule promptly.
hopr_network_online_hourly is the opposite: the API returns the entire series from 2023-09 on every call, so it self-heals. Any run repairs every earlier gap.

That second table used to be fetched only under --mode backfill. "Returns everything each time" was read as "run it once", but new hours keep accruing, so a daily schedule left the series frozen at the last manual backfill while the job kept reporting success — silent staleness with nothing failing. It is now fetched on every run regardless of mode. Do not re-gate it on backfill.

It matters because that series is the only multi-year HOPR history that exists, and it is the sole source of "how many nodes are actually online" — as opposed to how many ever registered on-chain, which is cumulative, never expires, and currently overstates the live network several-fold.

Also needed: ip_crawler

Node geography comes from ipinfo, populated by the separate ip_crawler repo via python -m src.crawler --source hopr. That is a one-shot command with no schedule of its own, and it reads int_hopr_nodes for the IP list, so it must run after dbt.

It needs a recurring run, otherwise newly-announced IPs stay unenriched (visibly — int_hopr_nodes.geo_source reports unenriched rather than pretending). It is cheap to repeat: the query pre-filters against ipinfo, so a re-run only costs API calls for genuinely new IPs.

Order: click-runner → dbt → ip_crawler → dbt (to pick up the new geo).

Troubleshooting

Common Issues

S3 Access Denied:
- Verify S3 credentials
- Check bucket permissions
ClickHouse Connection Failure:
- Verify ClickHouse connection details
- Check network connectivity
Invalid SQL Syntax:
- Inspect SQL files for errors
- Use ClickHouse client directly to test queries
External prices HTTP 429:
- Slow down / re-run; set COINGECKO_API_KEY (Demo) for higher CoinGecko limits
- DefiLlama backfill already sleeps between chart calls

Logs

By default, logs are output to stdout/stderr. Docker Compose captures these logs.

To view logs:

docker-compose logs click-runner

When using cron jobs, logs are saved to the logs/ directory with dated filenames.

License

This project is licensed under the MIT License.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
config		config
docs/lessons		docs/lessons
ingestors		ingestors
queries		queries
scripts		scripts
utils		utils
.env.example		.env.example
.gitignore		.gitignore
AGENTS.md		AGENTS.md
CLAUDE.md		CLAUDE.md
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
__init__.py		__init__.py
docker-compose.yml		docker-compose.yml
observability.py		observability.py
requirements.txt		requirements.txt
run_queries.py		run_queries.py

Uh oh!

Latest commit

History

Folders and files

Repository files navigation

ClickHouse Runner

Overview

Prerequisites

Project Structure

Environment Variables

Running Modes

1. Query Mode (--ingestor=query)

2. CSV Mode (--ingestor=csv)

3. Parquet Mode (--ingestor=parquet)

4. Dune Execute-Only Mode (--ingestor=dune-execute-only)

Common Parameters

Docker Compose Services

Setting Up Cron Jobs

Adding New Data Sources

1. Adding a New CSV Data Source

2. Adding a New Parquet Data Source

3. Adding to Docker Compose

Supporting a New File Format

Example: Adding Support for Avro Files

Advanced Usage

Variable Substitution in SQL

Skip Table Creation

Running Multiple Ingestors

External prices standbein (DefiLlama + CoinGecko)

HOPR (network dashboard + blokli)

These tables must exist in crawlers_data before the dbt side runs in prod

Running them

Deploying the schedule

Also needed: ip_crawler

Troubleshooting

Common Issues

Logs

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages

1. Query Mode (`--ingestor=query`)

2. CSV Mode (`--ingestor=csv`)

3. Parquet Mode (`--ingestor=parquet`)

4. Dune Execute-Only Mode (`--ingestor=dune-execute-only`)