Skip to main content

CBT API

CBT API is a generic REST API generator for ClickHouse databases managed with CBT (ClickHouse Build Tool). It automatically generates OpenAPI specifications and fully functional REST APIs from your ClickHouse table schemas.

Overview​

CBT API eliminates the manual work of building REST APIs for ClickHouse data by automatically:

  • Discovering Tables: Queries ClickHouse to find tables matching configured prefixes
  • Generating Protobuf Definitions: Creates type-safe Protocol Buffer schemas from table structures
  • Creating OpenAPI Specs: Generates complete OpenAPI 3.0 specifications with all endpoints
  • Building Server Implementation: Produces a fully functional Go REST API server with query builders

Key Features​

Automatic API Generation​

Point CBT API at your ClickHouse database and get a complete REST API:

  • No manual endpoint configuration required
  • Type-safe query builders generated from schemas
  • Pagination, filtering, and sorting built-in
  • OpenAPI specification auto-generated

Rich Query Capabilities​

Every table gets comprehensive filtering options:

  • Scalar operators: eq, ne, lt, lte, gt, gte, in_values, not_in_values
  • String operators: contains, starts_with, ends_with, like, not_like
  • Nullable operators: is_null, is_not_null
  • Map operators: has_key, not_has_key, has_any_key, has_all_keys

Developer-Friendly​

  • REST-friendly URL parameters with underscore notation
  • Swagger UI included for interactive API exploration
  • Prometheus metrics for observability
  • Health check endpoints for monitoring
  • Optional OpenTelemetry tracing support

How It Works​

Generation Pipeline​

  1. Table Discovery: Queries ClickHouse system.tables to find tables matching configured prefixes
  2. Proto Generation: Uses clickhouse-proto-gen to create Protocol Buffer definitions
  3. OpenAPI Generation: Creates OpenAPI spec from proto annotations with flattened filter parameters
  4. Server Generation: Generates complete server implementation with query builders

Request Flow​

HTTP Request
  ↓
Router (validates params)
  ↓
Handler (maps HTTP → Proto)
  ↓
Query Builder (generates SQL)
  ↓
ClickHouse Execution
  ↓
Result Scanning
  ↓
JSON Response

API Examples​

Basic Queries​

# List blocks
GET /api/v1/fct_block

# Filter by slot
GET /api/v1/fct_block?slot_eq=1000

# Range query
GET /api/v1/fct_block?slot_gte=1000&slot_lte=2000

# String search
GET /api/v1/fct_block?meta_client_name_contains=lighthouse

Advanced Filtering​

# List filters (comma-separated)
GET /api/v1/fct_block?meta_client_name_in_values=lighthouse,prysm,teku

# Multiple conditions (AND logic)
GET /api/v1/fct_block?slot_gte=1000&slot_lte=2000&meta_client_name_eq=lighthouse

# Null checks
GET /api/v1/fct_block?optional_field_is_not_null=true

Pagination​

# Custom page size
GET /api/v1/fct_block?page_size=100

# Next page
GET /api/v1/fct_block?page_size=100&page_token=offset_100

# Default: 100 items per page, max: 10000

Sorting​

# Sort by field (ascending)
GET /api/v1/fct_block?order_by=slot

# Sort descending
GET /api/v1/fct_block?order_by=slot&order_desc=true

Configuration​

Table Discovery​

Control which tables are exposed via the API:

clickhouse:
  dsn: "https://user:password@host:443"
  database: "mainnet"

  # Table discovery for proto generation
  discovery:
    prefixes:
      - fct        # Discover fact tables
      - dim        # Discover dimension tables
    exclude:
      - "*_test"   # Exclude test tables
      - "*_tmp"    # Exclude temporary tables

API Exposure​

Fine-tune which tables get REST endpoints:

api:
  enable: true
  base_path: "/api/v1"
  # Only tables with these prefixes will be exposed
  expose_prefixes:
    - fct  # Only expose fact tables via API

Use Cases​

Public Data Access​

Expose ClickHouse data to the community:

  • Provide REST APIs for your Ethereum datasets
  • Enable developers to query data without ClickHouse access
  • Automatic pagination and filtering for large datasets

Internal Dashboards​

Power internal analytics tools:

  • Generate APIs for Grafana or custom dashboards
  • Type-safe data access with Protocol Buffers
  • Consistent query interface across teams

Multi-Network Deployment​

Serve data from multiple Ethereum networks:

  • Generate APIs for mainnet, testnets, and devnets
  • Consistent API structure across all networks
  • Use with lab-backend for routing

Integration with ethPandaOps Stack​

CBT API is used throughout the ethPandaOps infrastructure:

  • The Lab: Powers the REST APIs serving transformed data from CBT
  • Public Datasets: Exposes Xatu data to the community via REST APIs
  • Multi-Network: Generates separate APIs for each Ethereum network (mainnet, sepolia, holesky, etc.)

API Endpoints​

All generated APIs include:

  • API endpoints: /api/v1/* (all discovered tables)
  • Swagger UI: /docs/ (interactive API explorer)
  • OpenAPI spec: /openapi.yaml (machine-readable specification)
  • Health check: /health (liveness probe)
  • Metrics: /metrics (Prometheus format)

Generic & Reusable​

CBT API is completely generic and works with any ClickHouse database. Simply point it at your ClickHouse instance, configure table prefixes, and generate a complete REST API with type-safe query capabilities.

Resources​

  • CBT: Transform data in ClickHouse before exposing via API
  • ClickHouse Proto Gen: Underlying tool for schema generation (used internally)
  • The Lab: Visualize data served by CBT API
  • Xatu: Collect data that can be served via CBT API

Community​

Need help or want to contribute?