Skip to main content

Intro

ClickHouse is an open-source column-oriented database that stores data for the Xatu platform and provides fast analytics capabilities.

Overviewโ€‹

Xatu uses ClickHouse as a primary storage and analytics engine for the large volumes of data collected from the Ethereum network. ClickHouse's columnar storage format and query optimization make it ideal for the time-series and event data that Xatu processes.

Key benefits of using ClickHouse with Xatu include:

  • High Query Performance: Optimized for analytical queries over large datasets
  • Efficient Storage: Columnar storage with compression for network data
  • Real-time Analytics: Process incoming Ethereum network data in real-time
  • Scalability: Ability to handle petabytes of Ethereum network data

Accessโ€‹

Currently access is restricted. Please contact us if you need access.

Available Endpoints

๐ŸŒ
Mainnet Endpoint
Primary endpoint for production networks with stable, long-term data retention.
Supported Networks:
MainnetHoodiSepoliaHolesky
https://clickhouse.xatu.ethpandaops.io
๐Ÿงช
Experimental Endpoint
Specialized endpoint for ephemeral networks, devnets, and experimental data collection.
Supported Networks:
DevnetsExperimental Networks
https://clickhouse.xatu-experimental.ethpandaops.io

Using Curlโ€‹

Replace CLICKHOUSE_USER and CLICKHOUSE_PASSWORD with your credentials.

Curl Example:

# Using endpoint: clickhouse.xatu.ethpandaops.io
echo """
    SELECT
        *
    FROM beacon_api_eth_v1_events_block
    WHERE
        event_date_time > NOW() - INTERVAL '1 day'
        AND meta_network_name = 'mainnet'
    LIMIT 5
    FORMAT JSON
""" | curl "https://clickhouse.xatu.ethpandaops.io" -u "$CLICKHOUSE_USER:$CLICKHOUSE_PASSWORD" --data-binary @- | jq

Using Jupyter Notebooksโ€‹

Install dependencies:

pip install --quiet jupysql clickhouse_sqlalchemy

Create a new notebook:

Python Example:

ENDPOINT = "clickhouse.xatu.ethpandaops.io"
CLICKHOUSE_USER = "your-username"
CLICKHOUSE_PASSWORD = "your-password"

# Set up the connection string
%sql clickhouse+http://{{CLICKHOUSE_USER}}:{{CLICKHOUSE_PASSWORD}}@{{ENDPOINT}}:443/default?protocol=https
# Query example - select 10 rows from block arrival events table
%sql SELECT * FROM beacon_api_eth_v1_events_block WHERE event_date_time > NOW() - INTERVAL '1 HOUR' LIMIT 10;

Available Tablesโ€‹

The Xatu ClickHouse instance provides several tables for different types of Ethereum network data. For a complete list of tables and their schemas, please see the Schema Documentation.