SQL Guide

Query the tables available to your ClickHouse tier for advanced analytics and complex queries.

Getting Started

Already have credentials? Jump to Connection Methods. New to Probalytics? Create ClickHouse credentials in app.probalytics.io → ClickHouse Credentials section. See Quickstart for setup. Connection Details

Host: clickhouse.probalytics.io
Port: 9440 (Secure TCP) or 8443 (Secure HTTPS)
Database: probalytics
Authentication: Username and password from dashboard

All connections are secured with TLS encryption. Use the --secure flag for CLI or enable TLS/SSL in your client.

Quick Exploration

SQL Workspace (Browser)

The fastest way to explore and test queries. Visit app.probalytics.io/sql and start querying immediately—no setup required.

Large result sets will crash your browser. Keep queries focused and use LIMIT to restrict results. For bulk exports or large analytics, use programmatic connections instead.

Connection Methods

For production applications and large-scale analysis, use one of these programmatic connections:

Native ClickHouse driver for best performance.Install: pip install clickhouse-driver

from clickhouse_driver import Client

client = Client(
    host='clickhouse.probalytics.io',
    port=9440,
    user='YOUR_USERNAME',
    password='YOUR_PASSWORD',
    database='probalytics',
    secure=True
)

# Test connection
result = client.execute('SELECT count() FROM markets;')
print(f"Total markets: {result[0][0]}")

HTTP-based client, simpler setup.Install: pip install clickhouse-connect

import clickhouse_connect

client = clickhouse_connect.get_client(
    host='clickhouse.probalytics.io',
    port=8443,
    username='YOUR_USERNAME',
    password='YOUR_PASSWORD',
    database='probalytics'
)

# Test connection
result = client.query('SELECT count() FROM markets;')
print(f"Total markets: {result.first_item}")

Install: npm install @clickhouse/client

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: 'https://clickhouse.probalytics.io:8443',
  username: 'YOUR_USERNAME',
  password: 'YOUR_PASSWORD',
  database: 'probalytics'
});

// Test connection
const result = await client.query({
  query: 'SELECT count() FROM markets;',
  format: 'JSONEachRow'
});

const data = await result.json();
console.log(`Total markets: ${data[0].count}`);

Install: go get github.com/ClickHouse/clickhouse-go/v2

package main

import (
  "fmt"
  "github.com/ClickHouse/clickhouse-go/v2"
  "context"
  "crypto/tls"
)

func main() {
  conn, _ := clickhouse.Open(&clickhouse.Options{
    Addr: []string{"clickhouse.probalytics.io:9440"},
    Auth: clickhouse.Auth{
      Username: "YOUR_USERNAME",
      Password: "YOUR_PASSWORD",
    },
    TLS: &tls.Config{},
    Settings: clickhouse.Settings{
      "max_execution_time": 300,
    },
  })

  var count uint64
  err := conn.QueryRow(context.Background(),
    "SELECT count() FROM markets;").Scan(&count)
  fmt.Printf("Total markets: %d\n", count)
}

Quick exploration and ad-hoc queries.Install: ClickHouse Docs

clickhouse client --host=clickhouse.probalytics.io --secure --port=9440 --user=YOUR_USERNAME --password=YOUR_PASSWORD --database=probalytics

Or run a query directly:

clickhouse client \
  --host clickhouse.probalytics.io \
  --secure \
  --port 9440 \
  --user YOUR_USERNAME \
  --password YOUR_PASSWORD \
  --database probalytics \
  --query "SELECT count() FROM markets;"

Exploring the Database

List All Tables

SHOW TABLES FROM probalytics;

Describe Table Structure

DESCRIBE TABLE probalytics.markets;
DESCRIBE TABLE probalytics.fills;
DESCRIBE TABLE probalytics.orderbook_snapshots;

Test Your Connection

SELECT
    'markets' as table_name,
    count() as row_count
FROM markets
UNION ALL
SELECT
    'fills' as table_name,
    count() as row_count
FROM fills
UNION ALL
SELECT
    'orderbook_snapshots' as table_name,
    count() as row_count
FROM orderbook_snapshots;

Understanding ReplacingMergeTree

Tables use the ReplacingMergeTree engine for efficient updates and deduplication. How it works:

Each table’s ORDER BY tuple defines row identity for replacement
For duplicate identity keys, the greatest indexed_at version wins
Background merges consolidate duplicates asynchronously
Queries may return duplicates until merges complete

The FINAL Keyword Use FINAL to force immediate deduplication:

-- May include duplicates (fast)
SELECT count() FROM markets;

-- Guaranteed deduplicated (slower)
SELECT count() FROM markets FINAL;

FINAL triggers synchronous deduplication which is significantly slower. Use it only when you need guaranteed unique results. For most analytics, skip FINAL and rely on background merges.

Access tiers

Your tier determines which datasets a ClickHouse credential can query. Query quotas and resource limits are applied separately from REST and can vary by credential. Contact support@probalytics.io if you need the effective limits for a credential.

Paid credentials

Feature	Starter	Pro	Orderbook	Custom
Tables	markets, fills	markets, fills	markets, fills, orderbooks	markets, fills, orderbooks
Retained history	All	All	All	All

Trial credentials

Trials receive full market metadata but normally only the last seven days of fills and, when the tier permits it, orderbook snapshots. A per-account offer can differ from the normal trial window. “All retained history” means the complete period Probalytics currently stores for that dataset. See the Datasets overview for coverage details.

Feature	Starter trial	Pro trial	Orderbook trial	Custom trial
Tables	markets, fills	markets, fills	markets, fills, orderbooks	markets, fills, orderbooks

Your tier is set when creating ClickHouse credentials in the dashboard.

Available Tables

markets

Primary table for prediction market metadata.

Available to: All tiers
Records: One per market across platforms
Key fields: platform, title, description, outcomes, status, market_type
See Tables reference for complete schema

fills

Individual trade fill records on each market.

Available to: All tiers
Records: One per trade executed
Key fields: market_id, size, price, normalized_price, taker_side, taker_id, maker_id, fee, timestamp
Note: Partitioned monthly by timestamp for performance
See Tables reference for complete schema

orderbook_snapshots

Orderbook depth snapshots per market outcome, written when the book state changes.

Available to: Orderbook and Custom ClickHouse tiers
Records: Full bid/ask states per outcome; multiple ordered states can share one timestamp
Key fields: market_id, outcome, bids, asks, timestamp, hash, state, continuity, path_index
Note: Partitioned daily by timestamp; use continuity boundaries when reconstructing time series
Data range: Polymarket available from November 20, 2025 (high-quality snapshots from February 17, 2026). Kalshi available from May 8, 2026.
See Tables reference for complete schema

Next Steps

Tables Reference

Complete field definitions and data types

Common Queries

Ready-to-run query examples

SQL Tips

Performance optimization strategies

Getting Started

Datasets

Access Methods

REST API

Reference

Support

Getting Started

Quick Exploration

SQL Workspace (Browser)

Connection Methods

Exploring the Database

List All Tables

Describe Table Structure

Test Your Connection

Understanding ReplacingMergeTree

Access tiers

Paid credentials

Trial credentials

Available Tables

markets

fills

orderbook_snapshots

Next Steps

Tables Reference

Common Queries

SQL Tips

​Getting Started

​Quick Exploration

​SQL Workspace (Browser)

​Connection Methods

​Exploring the Database

​List All Tables

​Describe Table Structure

​Test Your Connection

​Understanding ReplacingMergeTree

​Access tiers

​Paid credentials

​Trial credentials

​Available Tables

​markets

​fills

​orderbook_snapshots

​Next Steps

Tables Reference

Common Queries

SQL Tips

Getting Started

Quick Exploration

SQL Workspace (Browser)

Connection Methods

Exploring the Database

List All Tables

Describe Table Structure

Test Your Connection

Understanding ReplacingMergeTree

Access tiers

Paid credentials

Trial credentials

Available Tables

markets

fills

orderbook_snapshots

Next Steps