最近遇到给ClickHouse灌注测试数据的场景。方法多种多样,此处提供一种思路。
[root@VM-16-48-centos ~]# cat a.csv
2,liudehua
2,chenguanxi
3,bh8ank
4,bh8ank
5,bh8ank
6,bh8ank
7,bh8ank
8,bh8ank
[root@VM-16-48-centos ~]#
现有csv文件,内容格式如上。
192.168.16.16 :) create database DDD on cluster default_cluster;
CREATE DATABASE DDD ON CLUSTER default_cluster
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 192.168.16.15 │ 9000 │ 0 │ │ 1 │ 0 │
│ 192.168.16.16 │ 9000 │ 0 │ │ 0 │ 0 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
2 rows in set. Elapsed: 0.112 sec.
192.168.16.16 :) CREATE TABLE DDD.test (id int,name varchar(255)) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/test', '{replica}')ORDER BY(id);
CREATE TABLE DDD.test
(
`id` int,
`name` varchar(255)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/test', '{replica}')
ORDER BY id
Ok.
0 rows in set. Elapsed: 0.031 sec.
192.168.16.16 :)
192.168.16.16 :)
[root@VM-16-48-centos ~]# cat a.csv | clickhouse-client --host=172.16.0.72 --port=9000 --user=yourusername --password=yourpassword --query="INSERT INTO DDD.test FORMAT CSV"
[root@VM-16-48-centos ~]#
192.168.16.15 :)
192.168.16.15 :)
192.168.16.15 :)
192.168.16.15 :)
192.168.16.15 :) select * from DDD.test;
SELECT *
FROM DDD.test
┌─id─┬─name───────┐
│ 2 │ liudehua │
│ 2 │ chenguanxi │
│ 3 │ bh8ank │
│ 4 │ bh8ank │
│ 5 │ bh8ank │
│ 6 │ bh8ank │
│ 7 │ bh8ank │
│ 8 │ bh8ank │
└────┴────────────┘
8 rows in set. Elapsed: 0.006 sec.
192.168.16.15 :)