ClickHouse安装配置
一.安装官网介绍安装
https://clickhouse.com/#quick-start
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client
sudo /etc/init.d/clickhouse-server start
clickhouse-client # or "clickhouse-client --password" if you set up a password.
记得需要修改用户组
[root@Linux121 clickhouse-server]# chown -R clickhouse: '/var/run/clickhouse-server/'
集群方式配置
修改配置文件/etc/clickhouse-server/config.xml
<listen_host>::</listen_host>
<zookeeper>
<node>
<host>Linux121</host>
<port>2181</port>
</node>
<node>
<host>Linux122</host>
<port>2181</port>
</node>
<node>
<host>Linux123</host>
<port>2181</port>
</node>
</zookeeper>
<macros>
<shard>01</shard>
<replica>Linux121</replica>
</macros>
<perftest_3shards_1replicas>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux121</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux122</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux123</host>
<port>9000</port>
</replica>
</shard>
</perftest_3shards_1replicas>
启动服务
sudo /etc/init.d/clickhouse-server start
登录客户端
clickhouse-client
[root@Linux121 default]# clickhouse-client -m
ClickHouse client version 22.3.3.44 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.3 revision 54455.
Linux121 :)
简单使用同sql
Linux121 :) show databases;
SHOW DATABASES
Query id: ba56b55d-914d-4e2d-aabc-b9202f5123b0
┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default │
│ information_schema │
│ system │
└────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
Linux121 :) use default;
USE default
Query id: 50a692c7-2173-478e-9bb6-7f17a5db2fe9
Ok.
0 rows in set. Elapsed: 0.003 sec.
Linux121 :) show tables;
SHOW TABLES
Query id: 23711594-34b4-4aea-974e-0b9625b7418f
Ok.
0 rows in set. Elapsed: 0.005 sec.
Linux121 :) use system
;
USE system
Query id: af13d001-195a-4349-8abd-f07f3fbca779
Ok.
0 rows in set. Elapsed: 0.001 sec.
Linux121 :) show tables;
SHOW TABLES
Query id: 5211c6cd-a7c8-4c8a-88a7-84316916f742
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_inserts │
│ asynchronous_metric_log │
│ asynchronous_metrics │
│ build_options │
│ clusters │
│ collations │
│ columns │
│ contributors │
│ current_roles │
│ data_skipping_indices │
│ data_type_families │
│ databases │
│ detached_parts │
│ dictionaries │
│ disks │
│ distributed_ddl_queue │
│ distribution_queue │
│ enabled_roles │
│ errors │
│ events │
│ formats │
│ functions │
│ grants │
│ graphite_retentions │
│ licenses │
│ macros │
│ merge_tree_settings │
│ merges │
│ metric_log │
│ metrics │
│ models │
│ mutations │
│ numbers │
│ numbers_mt │
│ one │
│ part_moves_between_shards │
│ parts │
│ parts_columns │
│ privileges │
│ processes │
│ projection_parts │
│ projection_parts_columns │
│ query_log │
│ query_thread_log │
│ quota_limits │
│ quota_usage │
│ quotas │
│ quotas_usage │
│ replicas │
│ replicated_fetches │
│ replicated_merge_tree_settings │
│ replication_queue │
│ rocksdb │
│ role_grants │
│ roles │
│ row_policies │
│ session_log │
│ settings │
│ settings_profile_elements │
│ settings_profiles │
│ stack_trace │
│ storage_policies │
│ table_engines │
│ table_functions │
│ tables │
│ time_zones │
│ trace_log │
│ user_directories │
│ users │
│ warnings │
│ zeros │
│ zeros_mt │
│ zookeeper │
└────────────────────────────────┘
74 rows in set. Elapsed: 0.004 sec.
Linux121 :)
通过查询系统表可以知道目前是以集群方式运行
Linux121 :) select * from system.clusters;
SELECT *
FROM system.clusters
Query id: 15fb5c2d-fde3-4b1f-a01c-9958ed7822cb
┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address───┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─slowdowns_count─┬─estimated_recovery_time─┐
│ perftest_3shards_1replicas │ 1 │ 1 │ 1 │ Linux121 │ 172.16.131.129 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │
│ perftest_3shards_1replicas │ 2 │ 1 │ 1 │ Linux122 │ 172.16.131.130 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │
│ perftest_3shards_1replicas │ 3 │ 1 │ 1 │ Linux123 │ 172.16.131.131 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │
└────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────┴─────────────────────────┘
3 rows in set. Elapsed: 0.003 sec.
二.ReplicatedMergeTree表引擎
实现副本机制的配置
配置路径/etc/clickhouse-server/config.xml
<zookeeper>
<node>
<host>Linux121</host>
<port>2181</port>
</node>
<node>
<host>Linux122</host>
<port>2181</port>
</node>
<node>
<host>Linux123</host>
<port>2181</port>
</node>
</zookeeper>
副本机制案例
创建该引擎相关的表
Linux121节点创建
create table a1(
id String,
price Float64,
create_time DateTime)
engine=ReplicatedMergeTree('/clickhouse/tables/01/a1','Linux121')
partition by toYYYYMM(create_time)
order by id;
Linux122节点创建
create table a1(
id String,
price Float64,
create_time DateTime)
engine=ReplicatedMergeTree('/clickhouse/tables/01/a1','Linux122')
partition by toYYYYMM(create_time)
order by id;
向Linux121该表插入一条数据
insert into table a1 values('A001',100,'2020-08-20 08:00:00');
我们发现Linux122节点也同时插入了该条数据
三.分片
配置路径/etc/clickhouse-server/config.xml
配置节点
<remote_servers>
<perftest_3shards_1replicas>//集群名字
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux121</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux122</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>Linux123</host>
<port>9000</port>
</replica>
</shard>
</perftest_3shards_1replicas>
</remote_servers>
对应每个节点下指明当前分片信息
比如Linux121节点
<macros>
<shard>01</shard>
<replica>Linux121</replica>
</macros>
比如Linux122节点
<macros>
<shard>02</shard>
<replica>Linux122</replica>
</macros>
其他类似
配置后不需要重启server
通过查询系统表可以看出当前节点分片信息
Linux122 :) select * from system.macros;
SELECT *
FROM system.macros
Query id: 91d1e00c-f915-4003-81bc-3cfe52db734f
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.3 revision 54455.
┌─macro───┬─substitution─┐
│ replica │ Linux122 │
│ shard │ 02 │
└─────────┴──────────────┘
2 rows in set. Elapsed: 0.166 sec.
Linux121 :) select * from system.macros;
SELECT *
FROM system.macros
Query id: 10b1a06f-73fa-4ac2-9b27-8797fd0fee5b
┌─macro───┬─substitution─┐
│ replica │ Linux121 │
│ shard │ 01 │
└─────────┴──────────────┘
2 rows in set. Elapsed: 0.069 sec.
另一种查询方式
Linux122 :) select * from remote('Linux122:9000','system','macros','default');
SELECT *
FROM remote('Linux122:9000', 'system', 'macros', 'default')
Query id: 0895f290-a21c-4193-8c97-943606f9b6a7
┌─macro───┬─substitution─┐
│ replica │ Linux122 │
│ shard │ 02 │
└─────────┴──────────────┘
2 rows in set. Elapsed: 0.624 sec.
创建分布式表案例
Linux121 :) create table clustertable on cluster perftest_3shards_1replicas (id UInt32) engine=ReplicatedMergeTree('/clickhouse/tables/{shard}/clustertable','{replica}') order by id;
CREATE TABLE clustertable ON CLUSTER perftest_3shards_1replicas
(
`id` UInt32
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clustertable', '{replica}')
ORDER BY id
Query id: bc24c0c7-2f24-472c-a82b-99ff1e2ca7b5
┌─host─────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ Linux123 │ 9000 │ 0 │ │ 2 │ 0 │
│ Linux121 │ 9000 │ 0 │ │ 1 │ 0 │
│ Linux122 │ 9000 │ 0 │ │ 0 │ 0 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
3 rows in set. Elapsed: 0.563 sec.
我们发现在每个节点对应数据库下有同样表被创建