ClickHouse客户端和HTTP API：连接与首次查询

ClickHouse 客户端：我是如何在博彩项目中与控制台和 HTTP API 交上朋友的

第一次碰壁

记得安装 ClickHouse 后，我兴冲冲地输入 clickhouse-client，结果报错：Code: 210. DB::NetException: Connection refused (localhost:9000)。原来服务器只监听 127.0.0.1，而我想从另一台机器连接。花了一小时谷歌、编辑 config.xml、重启——才明白客户端有 --host 和 --port 参数。

ClickHouse 提供两种通信方式：原生客户端（供人类和脚本使用）和 HTTP API（供其他一切使用）。我每天两者都用。下面是你真正需要的一切，以及我踩过的坑。

连接方式：从简单到规范

方式 1：简单粗暴（仅限本地）

clickhouse-client

这仅在服务器同一台机器且未修改端口 9000 时有效。生产环境没人这么干。

方式 2：专业版：远程访问参数

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

从生产中学到的： 如果 bash 历史记录开启，绝不要在命令行传递密码。改用配置文件。

方式 3：规范版：配置文件

创建 ~/.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>

通过环境变量设置密码：

export CLICKHOUSE_PASSWORD="StrongPass123"
clickhouse-client

为什么更安全： 密码不会出现在 ps aux 或历史记录中。生产环境中，曾有开发人员运行 clickhouse-client --password secret，一小时内所有人都能在编排器日志中看到密码。

方式 4：HTTP 连接（CI/CD 的替代方案）

对于自动化，我经常使用 HTTP：

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

交互模式 vs 批处理模式：何时使用哪种

交互模式（供人类使用）

clickhouse-client

优点：自动补全（按 Tab 键）、命令历史、多行查询。缺点：不适合脚本。

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

我的小窍门： 在交互模式下，快捷键如 \l（列出数据库）、\d（列出表）、\c betting（切换数据库）都有效。不是所有人都知道，但能节省大量时间。

批处理模式（用于脚本和 Cron）

# 单条命令
clickhouse-client --query "SELECT count() FROM betting.bets"

# 从文件读取
clickhouse-client --queries-file /path/to/analytics.sql

# 多行 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

让我吃亏的地方： 在批处理模式下，始终以分号结束查询。没有分号，命令不会执行，但也不会报错——只是挂起。我们曾花了一小时调试 cron 作业。

HTTP API：curl 是你最好的朋友

HTTP 接口运行在 8123 端口。它非常适合微服务、仪表盘和任何语言的脚本。

GET 请求：简单快速

# 最简单的查询
curl "http://localhost:8123/?query=SELECT+version()"

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

# 带数据库参数
curl "http://localhost:8123/?database=betting&query=SELECT+count()+FROM+bets"

POST 请求：用于大查询和数据插入

# 长查询通过 POST（无 URL 长度限制）
curl -X POST "http://localhost:8123/" \
  -d "SELECT user_id, sum(amount) FROM betting.bets GROUP BY user_id"

# 通过 POST 插入数据
curl -X POST "http://localhost:8123/?query=INSERT+INTO+betting.bets+FORMAT+CSV" \
  --data-binary @bets_data.csv

响应格式：根据任务选择

ClickHouse 可以多种格式返回数据。我都试过——以下是你真正需要的：

# Pretty——供人类阅读（可读但格式字符多）
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=Pretty"

# JSON——用于 API（随处可解析）
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSON"

# JSONEachRow——逐行处理（内存高效）
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=JSONEachRow"

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

# TabSeparated——管道到其他工具（grep, awk）
curl "http://localhost:8123/?query=SELECT+user_id,amount+FROM+bets+LIMIT+3&default_format=TSV"

实际案例： 我们将聚合数据发送到 Telegram 机器人。使用 JSONEachRow，在 Python 中用一行 response.json() 解析，然后格式化为消息。

为博彩平台创建数据库

CREATE DATABASE IF NOT EXISTS betting;

并立即切换：

clickhouse-client --database betting

或在客户端内部：

USE betting;

第一张表：来自真实项目的投注模式

在我的投注分析生产项目中，表结构如下：

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

为什么这样设计：

• LowCardinality 用于运动项目——足球、篮球、网球。它们重复数千次，压缩成字典。
• Enum8 用于结果——只有三个值，占用 1 字节而不是字符串。
• DateTime64(3)——毫秒级精度对实时投注分析很重要。

常见新手错误： 忘记指定 ENGINE = MergeTree()。否则 ClickHouse 会创建 TinyLog 引擎的表（仅用于测试），无法分区且不支持复制。生产环境中，向这样的表插入 1000 万行会崩溃。

插入测试数据

单条记录

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

多条记录（批量插入）

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

使用 numbers() 生成测试数据

对于负载测试，我经常即时生成一百万条记录：

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

重要提示： 在配置不错的服务器上，此插入需要 5-10 秒。ClickHouse 针对此类批量操作进行了优化，但在弱虚拟机上可能需要一分钟。

投注上下文中的基本 SELECT 查询

WHERE——过滤

-- 特定用户最近一小时的投注
SELECT *
FROM betting.bets
WHERE user_id = 1001 
  AND created_at >= now() - INTERVAL 1 HOUR;

-- 赔率大于 2.0 的赢注
SELECT user_id, amount, odds, amount * odds AS payout
FROM betting.bets
WHERE outcome = 'win' AND odds > 2.0;

ORDER BY——排序

-- 今天最大的投注
SELECT user_id, amount, created_at
FROM betting.bets
WHERE created_at >= today()
ORDER BY amount DESC
LIMIT 10;

-- 某用户最近 5 条投注
SELECT created_at, sport, amount, odds, outcome
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 5;

GROUP BY——分析

-- 本周各运动项目的赔付
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;

组合条件

-- 一天内投注超过 10 次的用户
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;

实际案例：发现可疑模式

以下是我们欺诈检测系统中的真实查询：

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;

此查询用于发现机器人：那些每小时投注超过 100 次且金额几乎相同的用户。在 PostgreSQL 中处理 5000 万条记录永远无法完成。ClickHouse 在 0.6 秒内返回结果。

导出数据：当需要交给业务部门时

# 导出为 CSV 给市场部
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

# 导出为 JSON 给其他服务的 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

常见错误及解决方案

错误：Code: 102. DB::NetException: Connection refused

原因： 主机或端口错误，或服务器未监听外部连接。

解决方案： 检查 netstat -tulpn | grep clickhouse。如果没有 0.0.0.0:9000，编辑 config.xml：

<listen_host>0.0.0.0</listen_host>

错误：Code: 81. DB::Exception: Database betting doesn't exist

原因： 未创建数据库或未指定 --database。

解决方案： CREATE DATABASE IF NOT EXISTS betting; 或使用 --database betting 连接。

错误：Code: 62. DB::Exception: Syntax error: failed at position 1

原因： 在批处理模式下忘记分号。

解决方案： 使用 --query 时始终在查询末尾加上 ;。

下一步

现在你知道了如何以任何方式连接 ClickHouse、创建表、插入数据和运行查询。下一篇文章我们将深入高级分析：窗口函数、数组、聚合和物化视图。

所有示例在 ClickHouse 24.8 上测试通过。如果某些功能不工作，首先检查版本：SELECT version(); 这已经救了我无数次。

---

← 上一篇: Docker中的ClickHouse：如何不再担忧，两分钟启动分析
→ 下一篇: ClickHouse：博彩分析完整数据类型参考（我踩过的坑）

— Editorial Team