ClickHouse client and HTTP API: connection and first queries

ClickHouse Client: How I Befriended the Console and HTTP API in a Gambling Project

My First Knock on a Closed Door

I remember after installing ClickHouse, I happily typed clickhouse-client and got an error: Code: 210. DB::NetException: Connection refused (localhost:9000). Turns out the server was only listening on 127.0.0.1, and I was trying to connect from another machine. An hour of Googling, editing config.xml, restarting — and only then did I learn that the client has --host and --port flags.

ClickHouse offers two ways to communicate: the native client (for humans and scripts) and the HTTP API (for everything else). I use both every day. Below is everything you really need, plus the pitfalls I've stepped on.

Connection Methods: From Simple to Proper

Method 1. Naive (localhost only)

clickhouse-client

This only works if you're on the same machine as the server and haven't changed port 9000. No one does this in production.

Method 2. Professional: Flags for Remote Access

clickhouse-client \
  --host analytics.prod.company.com \
  --port 9000 \
  --user analyst \
  --password 'StrongPass123' \
  --database betting

What I learned from production: never pass a password on the command line if bash history is enabled. Instead, use a config file.

Method 3. Proper: Config File

Create ~/.clickhouse-client/config.xml:

<config>
    <host>clickhouse.prod.internal</host>
    <port>9000</port>
    <user>analyst</user>
    <password>${CLICKHOUSE_PASSWORD}</password>
    <database>betting</database>
    <history_file>/home/user/.clickhouse-client-history</history_file>
</config>

Password via environment variable:

export CLICKHOUSE_PASSWORD="StrongPass123"
clickhouse-client

Why this is safer: the password won't end up in ps aux or history. In production, we had a case where a developer ran clickhouse-client --password secret, and within an hour everyone could see the password in the orchestrator logs.

Method 4. HTTP Connection (Alternative for CI/CD)

For automation, I often use HTTP:

curl -u analyst:StrongPass123 \
  "http://clickhouse.prod.internal:8123/?query=SELECT+1"

Interactive vs Batch: When to Use What

Interactive Mode (for Humans)

clickhouse-client

Pros: autocompletion (press Tab), command history, multi-line queries. Cons: not for scripts.

:) SELECT user_id, sum(amount) FROM bets GROUP BY user_id LIMIT 5;

My life hack: in interactive mode, shortcuts like \l (list databases), \d (list tables), \c betting (switch database) work. Not everyone knows this, but it saves a ton of time.

Batch Mode (for Scripts and Cron)

# Single command
clickhouse-client --query "SELECT count() FROM betting.bets"

# From file
clickhouse-client --queries-file /path/to/analytics.sql

# Multi-line via heredoc
clickhouse-client <<SQL
SELECT 
    toDate(created_at) AS day,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY day
ORDER BY day;
SQL

What burned me: in batch mode, always end your query with a semicolon. Without it, the command won't execute, but it won't show an error either — it just hangs. We wasted an hour debugging cron jobs.

HTTP API: curl Is Your Best Friend

The HTTP interface runs on port 8123. It's perfect for microservices, dashboards, and scripts in any language.

GET Requests: Simple and Fast

# Simplest query
curl "http://localhost:8123/?query=SELECT+version()"

# With authentication
curl -u user:pass "http://localhost:8123/?query=SELECT+count()+FROM+betting.bets"

# With database parameter
curl "http://localhost:8123/?database=betting&query=SELECT+count()+FROM+bets"

POST Requests: For Large Queries and Data Insertion

# Long query via POST (no URL length limit)
curl -X POST "http://localhost:8123/" \
  -d "SELECT user_id, sum(amount) FROM betting.bets GROUP BY user_id"

# Insert data via POST
curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
  --data-binary @bets_data.csv

Response Formats: Choose for Your Task

ClickHouse can return data in many formats. I've tried them all — here's what you really need:

# Pretty — for humans (readable but lots of formatting characters)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=Pretty"

# JSON — for APIs (parsed everywhere)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSON"

# JSONEachRow — for line-by-line processing (memory efficient)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSONEachRow"

# CSV — for export to Excel/Google Sheets
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=CSV"

# TabSeparated — for piping to other tools (grep, awk)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=TSV"

Real-life example: We send aggregates to a Telegram bot. We use JSONEachRow, parse it in Python with a single response.json(), and format it into a message.

Creating a Database for a Betting Platform

CREATE DATABASE IF NOT EXISTS betting;

And switch to it immediately:

clickhouse-client --database betting

Or inside the client:

USE betting;

First Table: Bet Schema from a Real Project

In my production project for bet analytics, the table looks like this:

CREATE TABLE betting.bets
(
    user_id     UInt64,
    created_at  DateTime64(3),
    amount      Decimal(18, 2),
    odds        Float64,
    sport       LowCardinality(String),
    outcome     Enum8('win' = 1, 'loss' = 2, 'void' = 3),
    event_id    UInt64,
    bet_type    String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);

Why this way:

• LowCardinality for sport — football, basketball, tennis. They repeat thousands of times, compressed into a dictionary.
• Enum8 for outcome — only three values, takes 1 byte instead of a string.
• DateTime64(3) — milliseconds matter for live bet analytics.

Common beginner mistake: forgetting to specify ENGINE = MergeTree(). Without it, ClickHouse creates a table with the TinyLog engine (test only), which cannot be partitioned and doesn't support replication. In production, inserting 10 million rows into such a table will kill it.

Inserting Test Data

Single Record

INSERT INTO betting.bets (user_id, created_at, amount, odds, sport, outcome, event_id, bet_type)
VALUES (1001, now(), 50.00, 2.1, 'football', 'win', 50001, 'single');

Multiple Records (Batch Insert)

INSERT INTO betting.bets VALUES
(1002, now() - INTERVAL 1 HOUR, 100.00, 1.8, 'basketball', 'loss', 50002, 'single'),
(1003, now() - INTERVAL 2 HOUR, 200.00, 3.0, 'tennis', 'win', 50003, 'express'),
(1001, now() - INTERVAL 30 MINUTE, 75.00, 2.5, 'football', 'void', 50001, 'single');

Generating Test Data with numbers()

For load testing, I often generate a million records on the fly:

INSERT INTO betting.bets
SELECT 
    number % 10000 AS user_id,
    now() - INTERVAL (number % 86400) SECOND,
    (number % 1000) / 10 + 10,
    1.5 + (number % 200) / 100,
    arrayElement(['football', 'basketball', 'tennis', 'hockey'], (number % 4) + 1),
    CAST((number % 3) + 1 AS Enum8('win' = 1, 'loss' = 2, 'void' = 3)),
    number,
    'single'
FROM numbers(1000000);

Important note: This insert will take 5-10 seconds on a decent server. ClickHouse is optimized for such bulk operations, but on a weak VM it might take a minute.

Basic SELECT Queries in the Context of Bets

WHERE — Filtering

-- Bets of a specific user in the last hour
SELECT *
FROM betting.bets
WHERE user_id = 1001 
  AND created_at >= now() - INTERVAL 1 HOUR;

-- Winning bets with odds greater than 2.0
SELECT user_id, amount, odds, amount * odds AS payout
FROM betting.bets
WHERE outcome = 'win' AND odds > 2.0;

ORDER BY — Sorting

-- Largest bets today
SELECT user_id, amount, created_at
FROM betting.bets
WHERE created_at >= today()
ORDER BY amount DESC
LIMIT 10;

-- Last 5 bets of a user
SELECT created_at, sport, amount, odds, outcome
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 5;

GROUP BY — Analytics

-- Payouts by sport for the week
SELECT 
    sport,
    count() AS total_bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_payout,
    round(total_payout / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY sport
ORDER BY total_bets DESC;

Combining Conditions

-- Users who placed more than 10 bets in a day
SELECT 
    user_id,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM betting.bets
WHERE created_at >= today()
GROUP BY user_id
HAVING bets_count > 10
ORDER BY total_amount DESC;

Practical Case: Finding Suspicious Patterns

Here's a real query from our fraud detection system:

WITH hourly_bets AS (
    SELECT 
        user_id,
        toStartOfHour(created_at) AS hour,
        count() AS bets_per_hour,
        avg(amount) AS avg_bet
    FROM betting.bets
    WHERE created_at >= now() - INTERVAL 3 HOUR
    GROUP BY user_id, hour
)
SELECT 
    user_id,
    max(bets_per_hour) AS max_rate,
    avg(avg_bet) AS typical_bet,
    stddevPop(avg_bet) AS bet_variance
FROM hourly_bets
GROUP BY user_id
HAVING max_rate > 100 AND bet_variance < 0.5;

This query finds bots: those who bet more than 100 times per hour with almost identical amounts. In PostgreSQL with 50 million records, it would never finish. ClickHouse returns the answer in 0.6 seconds.

Exporting Data: When You Need to Give It to the Business

# Export to CSV for the marketing department
clickhouse-client --query "
    SELECT user_id, sum(amount) AS total_bet, count() AS bet_count
    FROM betting.bets
    WHERE created_at >= '2024-01-01'
    GROUP BY user_id
    ORDER BY total_bet DESC
    LIMIT 1000
" --format CSV > top_users.csv

# Export to JSON for another service's API
curl "http://localhost:8123/?query=SELECT+user_id,sum(amount)+FROM+betting.bets+GROUP+BY+user_id+LIMIT+10&default_format=JSON" \
  -o top_users.json

Common Errors and Their Solutions

Error: Code: 102. DB::NetException: Connection refused

Cause: Wrong host or port, or the server is not listening for external connections.

Solution: Check netstat -tulpn | grep clickhouse. If there's no 0.0.0.0:9000, edit config.xml:

<listen_host>0.0.0.0</listen_host>

Error: Code: 81. DB::Exception: Database betting doesn't exist

Cause: Didn't create the database or didn't specify --database.

Solution: CREATE DATABASE IF NOT EXISTS betting; or connect with --database betting.

Error: Code: 62. DB::Exception: Syntax error: failed at position 1

Cause: Forgot the semicolon in batch mode.

Solution: Always put ; at the end of the query when using --query.

What's Next?

Now you know how to connect to ClickHouse in any way, create tables, insert data, and run queries. In the next article, we'll dive into advanced analytics: window functions, arrays, aggregations, and materialized views.

All examples tested on ClickHouse 24.8. If something doesn't work, first check the version: SELECT version(); This has saved me hundreds of times.

---

← Previous: ClickHouse in Docker: How I Stopped Worrying and Launched Analytics in 2 Minutes
→ Next: ClickHouse: Complete Data Type Reference for Betting Analytics (What I Got Burned On)

— Editorial Team