ClickHouse Has a Free API You Should Know About

ClickHouse is the fastest open-source analytics database — and its HTTP API lets you query billions of rows in milliseconds.

HTTP API — Query via curl

# Query
curl 'http://localhost:8123/' --data-binary \
  "SELECT count() FROM events WHERE timestamp > now() - INTERVAL 1 DAY"

# With format
curl 'http://localhost:8123/?default_format=JSON' --data-binary \
  "SELECT event_type, count() as cnt FROM events GROUP BY event_type ORDER BY cnt DESC LIMIT 10"

# Insert data
curl 'http://localhost:8123/' --data-binary \
  "INSERT INTO events (timestamp, event_type, user_id) VALUES (now(), 'pageview', 'u123')"

ClickHouse Cloud API

# Manage services
curl -X GET 'https://api.clickhouse.cloud/v1/organizations/ORG_ID/services' \
  -H "Authorization: Basic $(echo -n 'KEY_ID:KEY_SECRET' | base64)"

# Create service
curl -X POST 'https://api.clickhouse.cloud/v1/organizations/ORG_ID/services' \
  -d '{"name": "analytics", "provider": "aws", "region": "us-east-1", "tier": "development"}'

JavaScript Client

import { createClient } from '@clickhouse/client'

const client = createClient({
  url: 'http://localhost:8123',
  username: 'default',
  database: 'analytics'
})

// Query with streaming
const result = await client.query({
  query: `
    SELECT 
      toDate(timestamp) as date,
      count() as events,
      uniqExact(user_id) as users
    FROM events
    WHERE timestamp > now() - INTERVAL 30 DAY
    GROUP BY date
    ORDER BY date
  `,
  format: 'JSONEachRow'
})

const rows = await result.json()

// Insert batch
await client.insert({
  table: 'events',
  values: [
    { timestamp: new Date(), event_type: 'signup', user_id: 'u456' },
    { timestamp: new Date(), event_type: 'purchase', user_id: 'u789' }
  ],
  format: 'JSONEachRow'
})

Materialized Views — Real-Time Aggregation

-- Automatically aggregate on insert
CREATE MATERIALIZED VIEW hourly_stats
ENGINE = SummingMergeTree()
ORDER BY (date, hour, event_type)
AS SELECT
  toDate(timestamp) as date,
  toHour(timestamp) as hour,
  event_type,
  count() as count,
  uniqExact(user_id) as unique_users
FROM events
GROUP BY date, hour, event_type

Real-World Use Case

A SaaS company was running analytics queries on PostgreSQL. Simple dashboard query on 10M rows: 12 seconds. They migrated to ClickHouse: same query, same data, 47 milliseconds. Their analytics dashboard went from "go get coffee" to "instant." Monthly cost: $0 (self-hosted on a $20 VPS handling 100M events/day).

ClickHouse makes you question why you ever used a row database for analytics.

---

Build Smarter Data Pipelines

Need to scrape websites, extract APIs, or automate data collection? Check out my ready-to-use scrapers on Apify — no coding required.

Custom scraping solution? Email me at [e-mail] — fast turnaround, fair prices.