clickhouse客户端工具之clickhouse-client快速入门实战
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
目录
一.clickhouse-client进行数据导入导出
1.clickhouse-client工作模式
交互式使用:
即输入一条指令执行一条，然后在输入一条，以此类推，需要手动交互。
非交互式模式:
使用"--query"参数或将参数发送到标准的stdin即标准输入。
参考链接:
https://clickhouse.com/docs/zh/interfaces/cli
2.批处理模式数据导入
参考链接:
https://clickhouse.com/docs/zh/interfaces/cli#batch-mode
1.创建测试表
node-exporter42 :) CREATE DATABASE IF NOT EXISTS jasonyin;
node-exporter42 :) USE jasonyin;
node-exporter42 :) CREATE TABLE demo(id UInt8,text String,created DateTime) ENGINE=TinyLog;
node-exporter42 :) SHOW TABLES;
node-exporter42 :) SHOW CREATE TABLE demo;
2.通过echo程序非交互式导入数据
[root@node-exporter42 ~]# echo -n "101,'尹正杰','2003-05-19 08:30:00'" | clickhouse-client --password yinzhengjie --database=jasonyin --query="INSERT INTO demo FORMAT CSV"
[root@node-exporter42 ~]# echo -n "102,'杰哥讲运维','2025-05-20 10:30:00'" | clickhouse-client --password yinzhengjie --database=jasonyin --query="INSERT INTO demo FORMAT CSV"
3.验证数据
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie -m
ClickHouse client version 25.3.3.42 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.3.3.
Warnings:
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
* Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.
node-exporter42 :) SELECT * FROM jasonyin.demo;
SELECT *
FROM jasonyin.demo
Query id: 020a6557-4266-4252-932f-ad921c4ffc92
┌──id─┬─text─────────┬─────────────created─┐
1. │ 101 │ '尹正杰' │ 2003-05-19 08:30:00 │
2. │ 102 │ '杰哥讲运维' │ 2025-05-20 10:30:00 │
└─────┴──────────────┴─────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
node-exporter42 :)
4.通过cat程序非交互式导入数据
[root@node-exporter42 ~]# cat << EOF | clickhouse-client --password yinzhengjie --database=jasonyin --query="INSERT INTO demo FORMAT CSV";
103,'博客地址: https://www.cnblogs.com/yinzhengjie','2015-08-31 12:00:00'
104,'B站地址: https://www.bilibili.com/','2025-05-20 13:14:00'
EOF
5.再次验证数据
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie -m
ClickHouse client version 25.3.3.42 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.3.3.
Warnings:
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
* Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.
node-exporter42 :) SELECT * FROM jasonyin.demo;
SELECT *
FROM jasonyin.demo
Query id: b8326b5f-778a-4284-8e18-4df07b668910
┌──id─┬─text────────────────────────────────────────────┬─────────────created─┐
1. │ 101 │ '尹正杰' │ 2003-05-19 08:30:00 │
2. │ 102 │ '杰哥讲运维' │ 2025-05-20 10:30:00 │
3. │ 103 │ '博客地址: https://www.cnblogs.com/yinzhengjie' │ 2015-08-31 12:00:00 │
4. │ 104 │ 'B站地址: https://www.bilibili.com/' │ 2025-05-20 13:14:00 │
└─────┴─────────────────────────────────────────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.002 sec.
node-exporter42 :)
3.导出数据并指定数据格式
参考链接:
https://clickhouse.com/docs/zh/interfaces/formats#formats-overview
1.导出格式为TabSeparated【各字段使用空格键进行分割】
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM jasonyin.demo FORMAT TabSeparated" > /tmp/demo.tsv
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# cat /tmp/demo.tsv
101 \'尹正杰\' 2003-05-19 08:30:00
102 \'杰哥讲运维\' 2025-05-20 10:30:00
103 \'博客地址: https://www.cnblogs.com/yinzhengjie\' 2015-08-31 12:00:00
104 \'B站地址: https://www.bilibili.com/\' 2025-05-20 13:14:00
[root@node-exporter42 ~]#
2.导出格式为CSV【很明显，如果不指定格式，则默认为'CSV'格式哟~早期的版本默认格式为'TabSeparated'】
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM jasonyin.demo FORMAT CSV" > /tmp/table-demo.csv
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM jasonyin.demo" > /tmp/demo.csv
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# cat /tmp/table-demo.csv
101,"'尹正杰'","2003-05-19 08:30:00"
102,"'杰哥讲运维'","2025-05-20 10:30:00"
103,"'博客地址: https://www.cnblogs.com/yinzhengjie'","2015-08-31 12:00:00"
104,"'B站地址: https://www.bilibili.com/'","2025-05-20 13:14:00"
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# cat /tmp/demo.csv
101,"'尹正杰'","2003-05-19 08:30:00"
102,"'杰哥讲运维'","2025-05-20 10:30:00"
103,"'博客地址: https://www.cnblogs.com/yinzhengjie'","2015-08-31 12:00:00"
104,"'B站地址: https://www.bilibili.com/'","2025-05-20 13:14:00"
[root@node-exporter42 ~]#
3.导出json格式数据
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM jasonyin.demo FORMAT JSON" > /tmp/demo.json
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# cat /tmp/demo.json
{
"meta":
[
{
"name": "id",
"type": "UInt8"
},
{
"name": "text",
"type": "String"
},
{
"name": "created",
"type": "DateTime"
}
],
"data":
[
{
"id": 101,
"text": "'尹正杰'",
"created": "2003-05-19 08:30:00"
},
{
"id": 102,
"text": "'杰哥讲运维'",
"created": "2025-05-20 10:30:00"
},
{
"id": 103,
"text": "'博客地址: https:\/\/www.cnblogs.com\/yinzhengjie'",
"created": "2015-08-31 12:00:00"
},
{
"id": 104,
"text": "'B站地址: https:\/\/www.bilibili.com\/'",
"created": "2025-05-20 13:14:00"
}
],
"rows": 4,
"statistics":
{
"elapsed": 0.00160984,
"rows_read": 4,
"bytes_read": 174
}
}
[root@node-exporter42 ~]#
二.clickhouse-client客户端执行SQL语句
1.支持同时执行多条SQL语句
[root@node-exporter42 ~]# clickhouse-client --multiquery --password yinzhengjie --query="SELECT id FROM jasonyin.demo;SELECT text FROM jasonyin.demo;"
101
102
103
104
\'尹正杰\'
\'杰哥讲运维\'
\'博客地址: https://www.cnblogs.com/yinzhengjie\'
\'B站地址: https://www.bilibili.com/\'
[root@node-exporter42 ~]#
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# clickhouse-client -m --password yinzhengjie --query="SELECT id FROM jasonyin.demo;SELECT text FROM jasonyin.demo;"
101
102
103
104
\'尹正杰\'
\'杰哥讲运维\'
\'博客地址: https://www.cnblogs.com/yinzhengjie\'
\'B站地址: https://www.bilibili.com/\'
[root@node-exporter42 ~]#
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT id FROM jasonyin.demo;SELECT text FROM jasonyin.demo;"
101
102
103
104
\'尹正杰\'
\'杰哥讲运维\'
\'博客地址: https://www.cnblogs.com/yinzhengjie\'
\'B站地址: https://www.bilibili.com/\'
[root@node-exporter42 ~]#
2.在查询中指定变量参数
1.查询id大于103的数据
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database jasonyin --query "SELECT * FROM demo WHERE id > 103"
104 \'B站地址: https://www.bilibili.com/\' 2025-05-20 13:14:00
[root@node-exporter42 ~]#
2.可以使用"--param_myid"表示定义一个myid的变量，在后面的SQL语句中可以引用该变量
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database jasonyin --param_myid=103 --query "SELECT * FROM demo WHERE id > {myid:UInt8}"
104 \'B站地址: https://www.bilibili.com/\' 2025-05-20 13:14:00
[root@node-exporter42 ~]#
3.clickhouse-client查找配置文件顺序
- A.通过"--config"指定配置文件;
[root@node-exporter42 ~]# systemctl cat clickhouse-server.service | grep ExecStart
ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=%t/%p/%p.pid
[root@node-exporter42 ~]#
- B."./clickhouse-client.xml"
- C."~/.clickhouse-client/config.xml"
- D."/etc/clickhouse-client/config.xml"
[root@node-exporter42 ~]# ll /etc/clickhouse-client/config.xml
-rw-r--r-- 1 root root 4881 Apr 18 05:51 /etc/clickhouse-client/config.xml
[root@node-exporter42 ~]#
4.clickhouse-client的历史记录命令
[root@node-exporter42 ~]# ll ~/.clickhouse-client-history
-rw------- 1 root root 23531 Jun 9 22:29 /root/.clickhouse-client-history
[root@node-exporter42 ~]#
[root@node-exporter42 ~]# tail ~/.clickhouse-client-history
### 2025-06-09 22:27:36.312
drop table demo;
### 2025-06-09 22:28:03.332
CREATE TABLE demo(id UInt8,text String,created DateTime) ENGINE=TinyLog;
### 2025-06-09 22:28:17.412
select * from demo;
### 2025-06-09 22:28:22.584
show tables;
### 2025-06-09 22:29:01.883
SELECT * FROM jasonyin.demo;
[root@node-exporter42 ~]#
本文来自博客园，作者：尹正杰，转载请注明原文链接：https://www.cnblogs.com/yinzhengjie/p/18935506，个人微信: "JasonYin2020"（添加时请备注来源及意图备注，有偿付费）
当你的才华还撑不起你的野心的时候，你就应该静下心来学习。当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练。问问自己，想要怎样的人生。
浙公网安备 33010602011771号