ClickHouse client a HTTP API: připojení a první dotazy

ClickHouse client: jak jsem se skamarádil s konzolí a HTTP API v gaming projektu

Můj první ťuk na zavřené dveře

Pamatuji si, jak jsem po instalaci ClickHouse šťastně napsal clickhouse-client a dostal chybu: Code: 210. DB::NetException: Connection refused (localhost:9000). Ukázalo se, že server poslouchá jen na 127.0.0.1, ale já se snažil připojit z jiného stroje. Hodina googlení, úprava config.xml, restart — a teprve tehdy jsem zjistil, že klient má přepínače --host a --port.

ClickHouse nabízí dva způsoby komunikace: nativního klienta (pro lidi a skripty) a HTTP API (pro všechno ostatní). Oba používám každý den. Níže je vše, co skutečně potřebujete, plus hrábě, na které jsem šlapal.

Způsoby připojení: od jednoduchého ke správnému

Způsob 1. Naivní (pouze pro localhost)

clickhouse-client

To funguje, jen pokud jste na stejném stroji jako server a nezměnili jste port 9000. V produkci to nikdo nedělá.

Způsob 2. Profesionální: přepínače pro vzdálený přístup

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

Co jsem si odnesl z produkce: nikdy nepředávejte heslo v příkazovém řádku, pokud je zapnutá historie bash. Místo toho použijte konfigurační soubor.

Způsob 3. Správný: konfigurační soubor

Vytvoříme ~/.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>

Heslo přes proměnnou prostředí:

export CLICKHOUSE_PASSWORD="StrongPass123"
clickhouse-client

Proč je to bezpečnější: heslo se nedostane do ps aux ani historie. V produkci jsme měli případ, kdy vývojář spustil clickhouse-client --password secret a za hodinu všichni viděli heslo v logu orchestátoru.

Způsob 4. Připojení přes HTTP (alternativa pro CI/CD)

Pro automatizaci často používám HTTP:

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

Interaktivní vs batch: kdy co použít

Interaktivní režim (pro lidi)

clickhouse-client

Výhody: automatické doplňování (stiskněte Tab), historie příkazů, víceřádkové dotazy. Nevýhody: není pro skripty.

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

Můj lifehack: v interaktivním režimu fungují zkratky \l (seznam DB), \d (seznam tabulek), \c betting (změna DB). Ne každý to ví, ale šetří to spoustu času.

Batch režim (pro skripty a cron)

# Jeden příkaz
clickhouse-client --query "SELECT count() FROM betting.bets"

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

# Víceřádkový přes 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

Na čem jsem se spálil: v batch režimu ukončete dotaz středníkem. Bez něj se příkaz neprovede, ale ani neukáže chybu — jen zamrzne. Ztratili jsme hodinu laděním cron úloh.

HTTP API: curl je váš nejlepší přítel

HTTP rozhraní běží na portu 8123. Je ideální pro mikroslužby, dashboardy a skripty v libovolném jazyce.

GET dotazy: jednoduše a rychle

# Nejjednodušší dotaz
curl "http://localhost:8123/?query=SELECT+version()"

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

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

POST dotazy: pro velké dotazy a vkládání dat

# Dlouhý dotaz přes POST (bez limitu na délku URL)
curl -X POST "http://localhost:8123/" \
  -d "SELECT user_id, sum(amount) FROM betting.bets GROUP BY user_id"

# Vkládání dat přes POST
curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
  --data-binary @bets_data.csv

Formáty odpovědí: vyberte podle úkolu

ClickHouse umí vracet data v mnoha formátech. Vyzkoušel jsem všechny — zde je to, co skutečně potřebujete:

# Pretty — pro člověka (čitelné, ale mnoho řídicích znaků)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=Pretty"

# JSON — pro API (parsovatelné všude)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSON"

# JSONEachRow — pro řádkové zpracování (úsporné na paměť)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSONEachRow"

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

# TabSeparated — pro předání jiným nástrojům (grep, awk)
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=TSV"

Příklad z praxe: Posíláme agregace do telegram bota. Používáme JSONEachRow, parsujeme v Pythonu jedním řádkem response.json() a formátujeme do zprávy.

Vytvoření databáze pro sázkovou platformu

CREATE DATABASE IF NOT EXISTS betting;

A hned se na ni přepneme:

clickhouse-client --database betting

Nebo již uvnitř klienta:

USE betting;

První tabulka: schéma sázek z reálného projektu

V mém produkčním projektu pro analýzu sázek vypadá tabulka takto:

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);

Proč takto:

• LowCardinality pro sport — fotbal, basketbal, tenis. Opakují se tisíckrát, komprimují se do slovníku.
• Enum8 pro outcome — jen tři hodnoty, zabírá 1 bajt místo řetězce.
• DateTime64(3) — milisekundy jsou důležité pro analýzu live sázek.

Častá chyba začátečníků: zapomenou uvést ENGINE = MergeTree(). Bez toho ClickHouse vytvoří tabulku s enginem TinyLog (jen pro testy), který nelze partitionovat a nepodporuje replikaci. V produkci při vkládání 10 milionů řádků taková tabulka zemře.

Vkládání testovacích dat

Jeden záznam

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');

Mnoho záznamů (dávkové vkládání)

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');

Generování testovacích dat pomocí numbers()

Pro zátěžové testování často generuji milion záznamů za běhu:

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);

Důležité upozornění: toto vkládání zabere 5-10 sekund na normálním serveru. ClickHouse je optimalizován pro takové bulk operace, ale na slabé virtuálce může trvat minutu.

Základní SELECT dotazy v kontextu sázek

WHERE — filtrování

-- Sázky konkrétního uživatele za poslední hodinu
SELECT *
FROM betting.bets
WHERE user_id = 1001 
  AND created_at >= now() - INTERVAL 1 HOUR;

-- Výherní sázky s kurzem větším než 2.0
SELECT user_id, amount, odds, amount * odds AS payout
FROM betting.bets
WHERE outcome = 'win' AND odds > 2.0;

ORDER BY — řazení

-- Největší sázky dnes
SELECT user_id, amount, created_at
FROM betting.bets
WHERE created_at >= today()
ORDER BY amount DESC
LIMIT 10;

-- Posledních 5 sázek uživatele
SELECT created_at, sport, amount, odds, outcome
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 5;

GROUP BY — analýza

-- Výplaty podle sportů za týden
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;

Kombinace podmínek

-- Uživatelé, kteří provedli více než 10 sázek za den
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;

Praktický případ: hledání podezřelých vzorců

Toto je skutečný dotaz z našeho systému detekce podvodů:

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;

Tento dotaz najde boty: ty, kteří sází více než 100krát za hodinu a s téměř stejnou částkou. V PostgreSQL s 50 miliony záznamů by se nikdy neprovedl. ClickHouse vrátí odpověď za 0,6 sekundy.

Export dat: když potřebujete předat businessu

# Export do CSV pro marketingové oddělení
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 do JSON pro API jiné služby
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

Typické chyby a jejich řešení

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

Příčina: Špatný host nebo port, nebo server neposlouchá externí připojení.

Řešení: Zkontrolujte netstat -tulpn | grep clickhouse. Pokud není 0.0.0.0:9000, upravte config.xml:

<listen_host>0.0.0.0</listen_host>

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

Příčina: Nevytvořili jste DB nebo neuvedli --database.

Řešení: CREATE DATABASE IF NOT EXISTS betting; nebo se připojte s přepínačem --database betting.

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

Příčina: Zapomněli jste středník v batch režimu.

Řešení: Vždy dávejte ; na konec dotazu, pokud používáte --query.

Co dál?

Nyní umíte připojit k ClickHouse libovolným způsobem, vytvářet tabulky, vkládat data a provádět dotazy. V příštím článku rozebereme pokročilou analýzu: okenní funkce, pole, agregace a materializované pohledy.

Všechny příklady byly testovány na ClickHouse 24.8. Pokud něco nefunguje — nejprve zkontrolujte verzi: SELECT version(); Tohle mě zachránilo stokrát.

---

← Předchozí: ClickHouse v Dockeru: jak jsem přestal mít strach a spustil analytiku za 2 minuty
→ Další: ClickHouse: úplný přehled datových typů pro analýzu sázek (na čem jsem se spálil)

— Editorial Team