ClickHouse Has a Free Analytics API That Queries Billions of Rows in Milliseconds

ClickHouse is a column-oriented database that processes analytical queries on billions of rows in milliseconds. Free to self-host with a powerful HTTP API.

Setup

docker run -d -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

HTTP API

# Create table
curl "http://localhost:8123/" -d "
  CREATE TABLE events (
    timestamp DateTime,
    event_type String,
    user_id UInt64,
    properties String,
    revenue Float64
  ) ENGINE = MergeTree()
  ORDER BY (timestamp, event_type)
"

# Insert data
curl "http://localhost:8123/" -d "
  INSERT INTO events VALUES
  ('2026-03-29 10:00:00', 'purchase', 1, '{"product":"pro"}', 49.99),
  ('2026-03-29 10:01:00', 'signup', 2, '{}', 0)
"

# Query — aggregation on millions of rows
curl "http://localhost:8123/" -d "
  SELECT
    toStartOfHour(timestamp) AS hour,
    event_type,
    count() AS events,
    sum(revenue) AS total_revenue
  FROM events
  WHERE timestamp >= now() - INTERVAL 24 HOUR
  GROUP BY hour, event_type
  ORDER BY hour DESC
  FORMAT JSON
"

Node.js Client

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

const client = createClient({ url: 'http://localhost:8123' });

// Insert batch
await client.insert({
  table: 'events',
  values: events.map(e => ({
    timestamp: e.ts,
    event_type: e.type,
    user_id: e.userId,
    properties: JSON.stringify(e.props),
    revenue: e.revenue || 0
  })),
  format: 'JSONEachRow'
});

// Query with streaming
const result = await client.query({
  query: `SELECT event_type, count() as cnt FROM events GROUP BY event_type ORDER BY cnt DESC`,
  format: 'JSONEachRow'
});
const data = await result.json();

Why ClickHouse Over PostgreSQL for Analytics

Query	PostgreSQL (1B rows)	ClickHouse (1B rows)
COUNT GROUP BY	45 seconds	0.3 seconds
SUM with filter	60 seconds	0.5 seconds
Full table scan	120 seconds	2 seconds

Why This Matters

• Blazing fast: Columnar storage optimized for aggregations
• SQL compatible: Standard SQL with extensions
• Compression: 10-40x data compression ratio
• Free: Open source, self-host anywhere

---

Need custom analytics tools or data pipeline automation? I build developer tools. Check out my web scraping actors on Apify or reach out at [e-mail] for custom solutions.