-
Notifications
You must be signed in to change notification settings - Fork 59
/
Copy pathclickhouse.txt
305 lines (288 loc) · 11.6 KB
/
clickhouse.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
clickhouse
https://clickhouse.tech/docs/zh/getting-started/tutorial/
https://zhuanlan.zhihu.com/p/34669883
https://github.com/vinitk95/clickhouse-cluster
https://developer.aliyun.com/article/762461
https://packages.clickhouse.com/rpm/stable
wget https://packages.clickhouse.com/rpm/stable/clickhouse-client-22.4.2.1.noarch.rpm
wget https://packages.clickhouse.com/rpm/stable/clickhouse-common-static-22.4.2.1.x86_64.rpm
wget https://packages.clickhouse.com/rpm/stable/clickhouse-common-static-dbg-22.4.2.1.x86_64.rpm
wget https://packages.clickhouse.com/rpm/stable/clickhouse-server-22.4.2.1.noarch.rpm
2 备份数据目录和配置文件
# mkdir /data/backup && cp /data/clickhouse /data/backup
# mkdir /tmp/clickhouse && cp /etc/clickhouse* /tmp/clickhouse
3 进入到下载目录，执行命令
rpm -Uvh *.rpm
说明1：目录下，最好只有刚才的下载的四个rpm
说明2：如果报错的话，一般是依赖的问题，可自行检索。
4 恢复编辑配置文件
mv /etc/clickhouse-server/config.xml.rpmorig /etc/clickhouse-server/config.xml
mv /etc/clickhouse-server/users.xml.rpmorig /etc/clickhouse-server/users.xml
也可以先执行下面 5 启动服务，测试完成之后再根据需要编辑配置
vim /etc/clickhouse-server/config.xml
vim /etc/clickhouse-server/users.xml
5 启动服务
service clickhouse-server start
6 查看升级后版本，再使用 clickhouse-client 连接测试
$ clickhouse-server --version
ClickHouse server version 22.4.2.1 (official build).
$ clickhouse-client --version
ClickHouse client version 22.4.2.1 (official build).
$ clickhouse-client -h 127.0.0.1
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.
# ARM (AArch64) build works on Amazon Graviton, Oracle Cloud, Huawei Cloud ARM machines.
wget 'https://builds.clickhouse.com/master/aarch64/clickhouse'
chmod a+x ./clickhouse
sudo ./clickhouse install
docker pull clickhouse/clickhouse-server
docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server
docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -v /path/to/your/config.xml:/etc/clickhouse-server/config.xml clickhouse/clickhouse-server
容器需要公开用于HTTP通信的8123端口和用于主机间通信的9000端口。
docker run -it --rm --link some-clickhouse-server:clickhouse-server clickhouse/clickhouse-client --host clickhouse-server
docker exec -it some-clickhouse-server bash
cd etc/clickhouse-server
user.xml
<access_management>1</access_management>
CREATE USER IF NOT EXISTS user1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'pass1'
CREATE DATABASE IF NOT EXISTS db1
SHOW DATABASES
GRANT ALL PRIVILEGES ON db1.* TO user1
docker run -it --rm --link some-clickhouse-server:clickhouse-server clickhouse/clickhouse-client --host clickhouse-server -u user1 --password pass1
:) SHOW GRANTS
:) SHOW DATABASES
:) USE db1
:) CREATE TABLE names (
id UInt64,
name String,
created DateTime
) ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id;
:) SHOW TABLES
CREATE DATABASE docker;
CREATE TABLE docker.docker (x Int32) ENGINE = Log;
CREATE TABLE IF NOT EXISTS example (
country_code FixedString(2),
os_id UInt8,
browser_id UInt8,
categories Array(Int16),
action_day Date,
action_time DateTime
) engine=Memory
yum install -y yum-utils
yum-config-manager --add-repo http://repo.red-soft.biz/repos/clickhouse/repo/clickhouse-el7.repo
yum install -y clickhouse-server clickhouse-client clickhouse-server-common clickhouse-compressor
CREATE TABLE ontime_local (FlightDate Date,Year UInt16) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);
insert into ontime_local (FlightDate,Year) values('2001-10-12',2001);
select count(*) from ontime_local;
vi /etc/rc.d/init.d/clickhouse-server
CLICKHOUSE_LOGDIR=/data/clickhouse/log
CLICKHOUSE_DATADIR_OLD=/data/clickhouse/data_old
CLICKHOUSE_CONFIG=/data/clickhouse/config.xml
CLICKHOUSE_DATADIR_OLD为兼容老版本，新版本已经弃用。
vi /data/clickhouse/config.xml
<?xml version="1.0"?>
<yandex>
<logger>
<level>trace</level>
<log>/data/clickhouse/log/server.log</log>
<errorlog>/data/clickhouse/log/error.log</errorlog>
<size>1000M</size>
<count>10</count>
</logger>
<!-- For HTTPS over native protocol. -->
<!--
<https_port>8443</https_port>
-->
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<!-- Port for communication between replicas. Used for data exchange. -->
<interserver_http_port>9009</interserver_http_port>
<!-- 本机域名 -->
<interserver_http_host>node1.jikelab.com</interserver_http_host>
<!-- 监听IP -->
<listen_host>0.0.0.0</listen_host>
<!-- 最大连接数 -->
<max_connections>64</max_connections>
<!-- 没搞懂的参数 -->
<keep_alive_timeout>3</keep_alive_timeout>
<!-- 最大并发查询数 -->
<max_concurrent_queries>16</max_concurrent_queries>
<!-- 单位是B -->
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
<mark_cache_size>10737418240</mark_cache_size>
<!-- 存储路径 -->
<path>/data/clickhouse/</path>
<tmp_path>/data/clickhouse/tmp/</tmp_path>
<!-- user配置 -->
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<log_queries>1</log_queries>
<default_database>default</default_database>
<!-- Configuration of clusters that could be used in Distributed tables.
https://clickhouse.com/docs/en/table_engines/distributed/
-->
<remote_servers incl="clickhouse_remote_servers" />
<zookeeper incl="zookeeper-servers" optional="true" />
<!-- Substitutions for parameters of replicated tables.
Optional. If you don't use replicated tables, you could omit that.
See https://clickhouse.com/docs/en/table_engines/replication/#creating-replicated-tables
-->
<macros incl="macros" optional="true" />
<!-- Reloading interval for embedded dictionaries, in seconds. Default: 3600. -->
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<!-- 控制大表的删除 -->
<max_table_size_to_drop>0</max_table_size_to_drop>
<!-- 集群配置文件 -->
<include_from>/data/clickhouse/metrika.xml</include_from>
</yandex>
需要注意interserver_http_host配置每个主机都不同，，其他配置保持一致，建议使用域名映射，方便数据入库的时候轮训写入各节点均衡数据。
修改集群的配置文件metrika.xml
vi /data/clickhouse/metrika.xml
<?xml version="1.0"?>
<yandex>
<!-- 集群配置 -->
<clickhouse_remote_servers>
<bip_ck_cluster>
<!-- 数据分片1 -->
<shard>
<internal_replication>false</internal_replication>
<replica>
<host>node1.jikelab.com</host>
<port>9000</port>
<user>default</user>
<password>6lYaUiFi</password>
</replica>
</shard>
<!-- 数据分片2 -->
<shard>
<internal_replication>false</internal_replication>
<replica>
<host>node2.jikelab.com</host>
<port>9000</port>
<user>default</user>
<password>6lYaUiFi</password>
</replica>
</shard>
<!-- 数据分片3 -->
<shard>
<internal_replication>false</internal_replication>
<replica>
<host>node3.jikelab.com</host>
<port>9000</port>
<user>default</user>
<password>6lYaUiFi</password>
</replica>
</shard>
</bip_ck_cluster>
</clickhouse_remote_servers>
<!-- 本节点副本名称，创建复制表时有用，每个节点不同，整个集群唯一，建议使用主机名） -->
<macros>
<replica>node1</replica>
</macros>
<!-- 监听网络 -->
<networks>
<ip>::/0</ip>
</networks>
<!-- ZK集群 -->
<zookeeper-servers>
<node index="1">
<host>192.168.0.245</host>
<port>2181</port>
</node>
<node index="2">
<host>192.168.0.246</host>
<port>2181</port>
</node>
<node index="3">
<host>192.168.0.247</host>
<port>2181</port>
</node>
</zookeeper-servers>
<!-- 数据压缩算法 -->
<clickhouse_compression>
<case>
<min_part_size>10000000000</min_part_size>
<min_part_size_ratio>0.01</min_part_size_ratio>
<method>lz4</method>
</case>
</clickhouse_compression>
</yandex>
关键的是zookeeper和macros(此配置，每个节点必须不一致，在集群需保持唯一性，建议使用主机名或递增字符串)配置，集群中clickhouse_remote_servers配置涉及数据分片,如果使用了分布式表，需要在集群的配置文件里，增加分片的用户名密码，关于配置文件我们需要单独一节介绍剖析配置
修改users.xml配置，设置权限、负载和配额。
vi /data/clickhouse/users.xml
<?xml version="1.0"?>
<yandex>
<profiles>
<!-- 读写用户设置 -->
<default>
<max_memory_usage>10000000000</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>random</load_balancing>
</default>
<!-- 只写用户设置 -->
<readonly>
<max_memory_usage>10000000000</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>random</load_balancing>
<readonly>1</readonly>
</readonly>
</profiles>
<!-- 配额 -->
<quotas>
<!-- Name of quota. -->
<default>
<interval>
<duration>3600</duration>
<queries>0</queries>
<errors>0</errors>
<result_rows>0</result_rows>
<read_rows>0</read_rows>
<execution_time>0</execution_time>
</interval>
</default>
</quotas>
<users>
<!-- 读写用户 -->
<default>
<password_sha256_hex>967f3bf355dddfabfca1c9f5cab39352b2ec1cd0b05f9e1e6b8f629705fe7d6e</password_sha256_hex>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</default>
<!-- 只读用户 -->
<ck>
<password_sha256_hex>967f3bf355dddfabfca1c9f5cab39352b2ec1cd0b05f9e1e6b8f629705fe7d6e</password_sha256_hex>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>readonly</profile>
<quota>default</quota>
</ck>
</users>
</yandex>
[4] 创建相关目录
mkdir -p /data/clickhouse/log
mkdir -p /data/clickhouse
chown -R clickhouse:clickhouse /data/clickhouse
[5] 启动集群
service clickhouse-server start
lsof -i :8123
lsof -i :9000
clickhouse-client -h 127.0.0.1 -d default -m -u default --password 6lYaUiFi
:) use system;
:) select * from clusters;
创建clickhouse表和库
CREATE TABLE ontime_local (FlightDate Date,Year UInt16) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);
CREATE TABLE ontime_all AS ontime_local ENGINE = Distributed(bip_ck_cluster, default, ontime_local, rand());
insert into ontime_local (FlightDate,Year)values('2001-10-12',2001);
insert into ontime_local (FlightDate,Year)values('2002-10-12',2002);
insert into ontime_local (FlightDate,Year)values('2003-10-12',2003);
insert into ontime_all (FlightDate,Year)values('2003-10-12',2008);
select count(*) from ontime_all;