Substreams ClickHouse Sink

substreams-sink-clickhouse is a tool to pipe in data from the blockchain into a ClickHouse database.

Table of contents

• Executable binaries
• Features
• Quickstart
• .env
• CLI

Executable binaries

See releases

$ ./substreams-sink-clickhouse

Features

See detailed list of features.

• Support for entity changes
• Support for database changes
• Table initialization
  • SQL schemas
  • GraphQL schemas
  • Materialized View
• Automatic block metadata
• Serveless
• Webhooks Ed25519 signatures
• No data loss

Quickstart

Every request can also be executed via the online UI.

1. Associate Webhook Ed25519 public keys from substreams-sink-webhook.

   $ echo "PUBLIC_KEY=<PK1>,<PK2>,..." >> .env

2. Initialize the database (set database credentials in environment)

   $ ./substream-sink-clickhouse
   $ curl --location 'localhost:3000/health' # --> OK
   $ curl --location --request PUT "localhost:3000/init" # --> OK

3. Create TABLE for your EntityChanges Substreams (more details on table initialization)

   $ curl --location --request PUT 'localhost:3000/schema/sql?schema-url=<url>'

   OR

   $ curl --location --request PUT "localhost:3000/schema/sql" \
       --header "Content-Type: text/plain" \
       --data "CREATE TABLE foo () ENGINE=MergeTree ORDER BY();"

Environment

Create a .env file in the root of the project.

$ cp .env.example .env

# ClickHouse DB (optional)
HOST=http://127.0.0.1:8123
USERNAME=default
DATABASE=default
PASSWORD=

# Webhook Ed25519 public key (comma separated for multiple) (optional)
# https://github.com/pinax-network/substreams-sink-webhook
PUBLIC_KEY=

# Sink HTTP server (optional)
PORT=3000
HOSTNAME=0.0.0.0
VERBOSE=true

CLI

Each field in environment can be overriden when starting the sink.

$ ./substreams-sink-clickhouse --help

Substreams Clickhouse Sink

Options:
  -V, --version            output the version number
  -v, --verbose <boolean>  Enable verbose logging (choices: "true", "false", default: "true", env: VERBOSE)
  -p, --port <number>      Sink HTTP server port (default: "3000", env: PORT)
  --hostname <string>      Sink HTTP server hostname (default: "0.0.0.0", env: HOSTNAME)
  --public-key <string>   Webhook Ed25519 public key (comma separated for multiple) (env: PUBLIC_KEY)
  --host <string>          Clickhouse DB hostname (default: "http://localhost:8123", env: HOST)
  --username <string>      Clickhouse DB username (default: "default", env: USERNAME)
  --password <string>      Clickhouse DB password (default: "", env: PASSWORD)
  --database <string>      Clickhouse DB database (default: "default", env: DATABASE)
  -h, --help               display help for command

Database structure

See detailed documentation

Development

$ bun install
$ bun dev