ClickHouse Database Skill#

Use the clickhouse-client CLI to connect to and interact with ClickHouse databases. Use the -q flag to execute SQL statements and combine with --format options to produce clean output suitable for processing. Pipe the result to jq for reliable JSON formatting.

快速使用场景#

场景 1: 查询数据（最常用）#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SELECT * FROM users LIMIT 10;" --format=JSONEachRow | jq -s '.'

场景 2: 查看表结构#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "DESCRIBE TABLE users;" --format=TSV 2>/dev/null

场景 3: 插入/更新/删除数据#

bash

# 插入
clickhouse-client -h <host> -u <user> -d <db> -q "INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');" 2>/dev/null

# 更新（需表引擎支持，如 MergeTree 家族）
clickhouse-client -h <host> -u <user> -d <db> -q "ALTER TABLE users UPDATE status=1 WHERE id=1;" 2>/dev/null

# 删除
clickhouse-client -h <host> -u <user> -d <db> -q "ALTER TABLE users DELETE WHERE id=1;" 2>/dev/null

场景 4: 统计数据报表#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SELECT COUNT(*) as total, SUM(amount) as revenue FROM orders WHERE toDate(create_time)=today();" --format=JSONEachRow | jq -s '.'

场景 5: 导出数据到文件#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SELECT * FROM users FORMAT CSV" > /tmp/users.csv 2>/dev/null

场景 6: 执行 SQL 脚本文件#

bash

clickhouse-client -h <host> -u <user> -d <db> --multiquery < script.sql 2>/dev/null

数据库连接#

基础连接#

bash

clickhouse-client -h <hostname> --port <port> -u <username> -d <database-name>

示例 (连接本地数据库):

bash

CLICKHOUSE_PASSWORD=yourpassword clickhouse-client -h 127.0.0.1 -u app_user -d app_db

从 JDBC URL 解析连接参数#

用户可能提供 JDBC URL 格式： jdbc:clickhouse://host:port/database，需要解析为 clickhouse-client 参数：

text

jdbc:clickhouse://nexus.syrinxchina.com:8123/test3
  → -h nexus.syrinxchina.com --port 8123 -d test3

bash

# 示例：从 JDBC URL 构建连接
JDBC_URL="jdbc:clickhouse://nexus.syrinxchina.com:8123/test3"
HOST=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\1/p')
PORT=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\2/p')
DB=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\3/p')
clickhouse-client -h "$HOST" --port "$PORT" -u root -d "$DB"

连接参数表#

OptionDescription-h / --hostHostname (default: localhost)--portTCP port (default: 9000, HTTP port: 8123)-u / --userUsername (default: default)--passwordPassword (default: empty)-d / --databaseDefault database (default: default)-q / --queryExecute query and exit--formatOutput format (TSV/CSV/JSON/JSONEachRow etc.)--multiqueryAllow multiple queries in one command--secureUse SSL/TLS connection--connect-timeoutConnection timeout (seconds)--send-timeoutSend data timeout (seconds)--receive-timeoutReceive data timeout (seconds)

连接示例 (完整参数):

bash

CLICKHOUSE_PASSWORD=password clickhouse-client -h 192.168.1.100 --port 9000 -u admin -d mydb --secure --connect-timeout=10 --format=JSONEachRow

使用配置文件#

创建 ~/.clickhouse-client/config.xml 简化频繁连接：

xml

<config>
  <host>127.0.0.1</host>
  <port>9000</port>
  <user>app_user</user>
  <password>yourpassword</password>
  <database>app_db</database>
  <secure>0</secure>
</config>

bash

clickhouse-client --config ~/.clickhouse-client/config.xml -q "SELECT 1;" --format=JSONEachRow

数据操作#

查询 (SELECT)#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SELECT * FROM your_table LIMIT 5;" --format=JSONEachRow | jq -s '.'

推荐格式化模式:

• --format=JSONEachRow：每行一个 JSON 对象，适合多行结果
• --format=JSON：单个 JSON 对象包裹所有结果
• --format=TSV：制表符分隔，适合简单输出

插入 (INSERT)#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "INSERT INTO users (name, email) VALUES ('New User', 'new@example.com');" 2>/dev/null

更新 (ALTER UPDATE)#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "ALTER TABLE users UPDATE status = 'active' WHERE signup_date < '2026-01-01';" 2>/dev/null

删除 (ALTER DELETE)#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "ALTER TABLE sessions DELETE WHERE last_activity < subtractDays(now(), 30);" 2>/dev/null

高级查询与 JSON 输出#

统计摘要查询#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "
SELECT JSON_OBJECT(
  'total_users', (SELECT COUNT(*) FROM users),
  'active_users', (SELECT COUNT(*) FROM users WHERE status = 'active'),
  'avg_posts', (SELECT AVG(post_count) FROM user_stats)
) AS report;
" --format=JSON | jq .

通用 JSON 输出模式#

单行结果：

bash

clickhouse-client ... -q "SELECT JSON_OBJECT('key1', column1, 'key2', column2) FROM ..." --format=JSON | jq .

多行结果：

bash

clickhouse-client ... -q "SELECT id, name FROM users LIMIT 5" --format=JSONEachRow | jq -s '.'

批量执行 SQL 文件#

bash

clickhouse-client -h <host> -u <user> -d <db> --multiquery < script.sql 2>/dev/null

批量导入 CSV：

bash

clickhouse-client -h <host> -u <user> -d <db> -q "INSERT INTO my_table FORMAT CSV" < data.csv 2>/dev/null

错误处理#

常见错误码#

Error CodeMeaningSolution516Authentication failed检查用户名/密码是否正确81Unknown database检查数据库名是否存在210Can't connect to ClickHouse检查 ClickHouse 服务是否启动，端口是否开放60Table doesn't exist检查表名拼写是否正确

超时配置#

bash

clickhouse-client -h <host> -u <user> -d <db> --connect-timeout=5 --send-timeout=30 --receive-timeout=30 -q "SELECT * FROM large_table;" --format=TSV

连接测试模式#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SELECT 1 AS connected;" --format=TSV 2>&1 | grep -q "connected" && echo "连接成功" || echo "连接失败"

数据库与表操作#

创建数据库#

bash

clickhouse-client -h <host> -u <user> -q "CREATE DATABASE IF NOT EXISTS new_db ENGINE = Atomic;" --format=TSV

列出所有数据库#

bash

clickhouse-client -h <host> -u <user> -q "SHOW DATABASES;" --format=TSV

列出表#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SHOW TABLES;" --format=TSV

查看表结构#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "DESCRIBE TABLE users;" --format=TSV

查看索引#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SHOW INDEXES FROM users;" --format=TSV

查看建表语句#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "SHOW CREATE TABLE users;" --format=TSV

DESCRIBE TABLE 详解#

查看单表结构#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "DESCRIBE TABLE users;" --format=TSV

输出字段说明：

字段说明name列名type数据类型（String、Int64、DateTime 等）default_type默认值类型default_expression默认值表达式comment字段注释codec_expression压缩算法ttl_expressionTTL 表达式

格式化输出为 JSON#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "
SELECT JSON_ARRAYAGG(JSON_OBJECT(
  'column', name,
  'type', type,
  'default_type', default_type,
  'default_value', default_expression,
  'comment', comment,
  'ttl', ttl_expression
)) AS columns
FROM system.columns
WHERE database = '<database>' AND table = '<table>'
ORDER BY position;" --format=JSON | jq .

快速查看主键和分区键#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "
SELECT name, type, is_in_primary_key, is_in_partition_key
FROM system.columns
WHERE database = '<database>'
  AND table = '<table>'
  AND (is_in_primary_key = 1 OR is_in_partition_key = 1)
ORDER BY is_in_primary_key DESC, position;" --format=JSONEachRow | jq -s '.'

EXPLAIN 查询分析（重要！）#

分析 SELECT 查询执行计划：

bash

clickhouse-client -h <host> -u <user> -d <db> -q "EXPLAIN SELECT * FROM users WHERE phone = '13800138000';" --format=JSONEachRow | jq -s '.'

输出关键字段说明：

字段说明Expression表达式计算Filter过滤条件ReadFromStorage存储读取方式PrimaryKey主键使用情况Partition分区过滤情况Files涉及文件数Rows预计扫描行数（越小越好）

优化要点：

• 确认分区键被有效使用
• 避免全表扫描（Full scan）
• 检查主键是否命中

EXPLAIN ANALYZE（ClickHouse 21.1+）#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "EXPLAIN ANALYZE SELECT * FROM users WHERE phone = '13800138000';" --format=JSONEachRow | jq -s '.'

比 EXPLAIN 更详细，包含 实际运行时间、 实际扫描行数 和 执行步骤耗时。

查看表大小和行数#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "
SELECT
  table AS table_name,
  total_rows AS rows,
  formatReadableSize(total_bytes) AS total_size,
  formatReadableSize(data_bytes) AS data_size,
  formatReadableSize(index_bytes) AS index_size
FROM system.tables
WHERE database = '<database>'
ORDER BY total_bytes DESC;" --format=JSONEachRow | jq -s '.'

查看数据库中所有表的基本信息#

bash

clickhouse-client -h <host> -u <user> -d <db> -q "
SELECT
  name AS table,
  engine AS table_engine,
  total_rows AS rows,
  formatReadableSize(total_bytes) AS size,
  comment AS table_comment
FROM system.tables
WHERE database = '<database>'
  AND engine NOT LIKE '%View%'
ORDER BY total_bytes DESC;" --format=JSONEachRow | jq -s '.'

环境变量配置#

bash

export CLICKHOUSE_PASSWORD="yourpassword"
export CLICKHOUSE_HOST="127.0.0.1"
export CLICKHOUSE_USER="app_user"
export CLICKHOUSE_DB="app_db"

clickhouse-client -h "$CLICKHOUSE_HOST" -u "$CLICKHOUSE_USER" -d "$CLICKHOUSE_DB" --password="$CLICKHOUSE_PASSWORD" -q "SELECT 1;" --format=JSONEachRow

完整示例脚本#

bash

#!/bin/bash
# 查询用户统计数据（带错误处理）

DB_HOST="${CLICKHOUSE_HOST:-127.0.0.1}"
DB_USER="${CLICKHOUSE_USER:-app_user}"
DB_PASS="${CLICKHOUSE_PASSWORD:-}"
DB_NAME="${CLICKHOUSE_DB:-app_db}"

QUERY="
SELECT JSON_OBJECT(
  'timestamp', now(),
  'summary', JSON_OBJECT(
    'total_users', (SELECT COUNT(*) FROM users),
    'active_users', (SELECT COUNT(*) FROM users WHERE status = 'active'),
    'new_today', (SELECT COUNT(*) FROM users WHERE toDate(created_at) = today())
  )
) AS report;
"

clickhouse-client -h "$DB_HOST" -u "$DB_USER" --password="$DB_PASS" -d "$DB_NAME" -q "$QUERY" --format=JSON 2>&1 | jq .

安全建议#

1. 禁止 在命令行中直接写密码（进程列表可见）
2. 使用 CLICKHOUSE_PASSWORD 环境变量或配置文件
3. 生产环境 强制 使用 SSL ( --secure 选项)
4. 配置文件权限设置为 chmod 600 ~/.clickhouse-client/config.xml
5. 查询操作使用只读账号
6. 避免使用默认端口和默认用户名/密码

重要提示: 使用 --format 参数指定输出格式（如 TSV/JSON/JSONEachRow）确保 clickhouse-client 输出纯净数据，是生成有效 JSON 的前提。