v0.0.9

What's Changed

Edited the table creation process to add the columns in the initial SQL instructions instead of using ALTER TABLE statements.

This allows to add automatic columns in the sorting key:

CREATE TABLE IF NOT EXISTS BlockStats  (
    transaction_traces Int64,
    trace_calls  Int64,
    uaw  Array(String)
)
ENGINE = ReplacingMergeTree()
ORDER BY (block_id, chain)

v0.0.8

Updated substreams sink entity change package

v0.0.7

What's Changed

• Added Prometheus metrics
• Added support for GraphQL schemas as a ClickHouse schema definition
• Added GET /blocks endpoint to monitor inserted blocks
• Added cursors query endpoint
• Added cursors table
• Added cors
• Added Argon2 auth
• Added support for remote schema urls

Minor changes

• Included external files in the built archive
• Fixed pipeline actions
• Fixed arrays in entity changes
• Updated documentation

v0.0.6

• Improved insertion performance
• Added id to every record
• Split block metadata into another dimension table
• Exposed more configuration fields to the user

v0.0.5

• Made AUTH_KEY optional
• Added defaults to every environment variable (except for security keys)
• Exposed every environment variable to the CLI
• Added columns final_block and entity_id
• Updated docs

v0.0.3

Initial preview release to try out the sink with external packages.

Substreams ClickHouse Sink CLI

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

Pre-built binaries

• Linux

Installation

Globally via npm

$ npm i -g substreams-sink-clickhouse

Environment variables

# ./.env

PUBLIC_KEY=... # Provided by substreams-sink-webhook

DB_HOST=http://127.0.0.1:8123
DB_NAME=clickhouse_sink
DB_USERNAME=default
DB_PASSWORD=

AUTH_KEY=... # Generate in Node via `require(crypto).randomBytes(64).toString('base64')`

SCHEMA_URL=... # Optional

Usage

Endpoint summary available at http://localhost:3000.

substreams-clickhouse-sink --help

Schema initialization

Initializes the database according to a SQL file. See example file.

It can also be done by uploading a .sql file on http://localhost:3000.

curl --location --request POST 'http://localhost:3000/schema' --header 'Authorization: Bearer <AUTH_KEY>' --header 'Content-Type: application/json' --data-raw '<SQL_INSTRUCTIONS>'

Sink

Serves an endpoint to receive Substreams data from substreams-sink-webhook.

Endpoints are detailed on http://localhost:3000.

substreams-clickhouse-sink
# or
bun start

Optional flags

Flags	Arguments	Default	Description
-v, --verbose	-	"pretty"	Enables logs. Add "json" to change the output format
-p, --port	<port>	3000	Selects the port to serve the sink
-s, --schema-url	[schema-url]	SCHEMA_URL in .env	URL to a .sql file to execute before starting the sink

Example SQL file

Click to expand

CREATE TABLE IF NOT EXISTS contracts (
    address FixedString(40),
    name Nullable(String),
    symbol Nullable(String),
    decimals Nullable(UInt8)
)
ENGINE = ReplacingMergeTree()
ORDER BY (address)

Database structure

Every table created by the user is extended by metadata associated with the received information. The added fields are:

Field	Type
timestamp	DateTime('UTC')
block_number	UInt32
block_id	FixedString(64)
chain	LowCardinality(String)
module_hash	FixedString(40)

An index is added to the tuple (chain, module_hash).

A dimension table also contains the following fields. It has one record per executed substreams.

Field	Type
module_hash	FixedString(40)
module_name	String
type	String