建表,排序,索引及注意事項(xiàng)
https://clickhouse.tech/docs/zh/operations/table_engines/mergetree/
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
我們以 (CounterID, Date)
以主鍵莉炉。排序好的索引的圖示會(huì)是下面這樣:
如果指定查詢?nèi)缦拢?/p>
-
CounterID in ('a', 'h')
躏结,服務(wù)器會(huì)讀取標(biāo)記號(hào)在[0, 3)
和[6, 8)
區(qū)間中的數(shù)據(jù)湃累。 -
CounterID IN ('a', 'h') AND Date = 3
栅干,服務(wù)器會(huì)讀取標(biāo)記號(hào)在[1, 3)
和[7, 8)
區(qū)間中的數(shù)據(jù)。 -
Date = 3
宏榕,服務(wù)器會(huì)讀取標(biāo)記號(hào)在[1, 10]
區(qū)間中的數(shù)據(jù)拓诸。
上面例子可以看出使用索引通常會(huì)比全表描述要高效。
稀疏索引會(huì)引起額外的數(shù)據(jù)讀取担扑。當(dāng)讀取主鍵單個(gè)區(qū)間范圍的數(shù)據(jù)時(shí)恰响,每個(gè)數(shù)據(jù)塊中最多會(huì)多讀 index_granularity * 2
行額外的數(shù)據(jù)。大部分情況下涌献,當(dāng) index_granularity = 8192
時(shí)胚宦,ClickHouse的性能并不會(huì)降級(jí)。
稀疏索引讓你能操作有巨量行的表燕垃。因?yàn)檫@些索引是常駐內(nèi)存(RAM)的枢劝。
ClickHouse 不要求主鍵惟一。所以卜壕,你可以插入多條具有相同主鍵的行您旁。
主鍵的選擇
https://clickhouse.tech/docs/zh/operations/table_engines/mergetree/#zhu-jian-de-xuan-ze
主鍵中列的數(shù)量并沒(méi)有明確的限制。依據(jù)數(shù)據(jù)結(jié)構(gòu)轴捎,你應(yīng)該讓主鍵包含多些或少些列鹤盒。這樣可以:
-
改善索引的性能。
如果當(dāng)前主鍵是
(a, b)
侦副,然后加入另一個(gè)c
列侦锯,滿足下面條件時(shí),則可以改善性能: - 有帶有c
列條件的查詢秦驯。 - 很長(zhǎng)的數(shù)據(jù)范圍(index_granularity
的數(shù)倍)里(a, b)
都是相同的值尺碰,并且這種的情況很普遍。換言之译隘,就是加入另一列后亲桥,可以讓你的查詢略過(guò)很長(zhǎng)的數(shù)據(jù)范圍。 -
改善數(shù)據(jù)壓縮固耘。
ClickHouse 以主鍵排序片段數(shù)據(jù)题篷,所以,數(shù)據(jù)的一致性越高厅目,壓縮越好悼凑。
-
CollapsingMergeTree 和 SummingMergeTree 引擎里偿枕,數(shù)據(jù)合并時(shí)璧瞬,會(huì)有額外的處理邏輯户辫。
在這種情況下,指定一個(gè)跟主鍵不同的 排序鍵 也是有意義的嗤锉。
長(zhǎng)的主鍵會(huì)對(duì)插入性能和內(nèi)存消耗有負(fù)面影響渔欢,但主鍵中額外的列并不影響 SELECT
查詢的性能。
選擇跟排序鍵不一樣主鍵
指定一個(gè)跟排序鍵(用于排序數(shù)據(jù)片段中行的表達(dá)式) 不一樣的主鍵(用于計(jì)算寫到索引文件的每個(gè)標(biāo)記值的表達(dá)式)是可以的瘟忱。 這種情況下奥额,主鍵表達(dá)式元組必須是排序鍵表達(dá)式元組的一個(gè)前綴。
分區(qū)
https://clickhouse.tech/docs/zh/operations/table_engines/custom_partitioning_key/
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;
分區(qū)鍵也可以是表達(dá)式元組(類似 [主鍵]例如:
ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));
上例中访诱,我們?cè)O(shè)置按一周內(nèi)的事件類型分區(qū)垫挨。
新數(shù)據(jù)插入到表中時(shí),這些數(shù)據(jù)會(huì)存儲(chǔ)為按主鍵排序的新片段(塊)触菜。插入后 10-15 分鐘九榔,同一分區(qū)的各個(gè)片段會(huì)合并為一整個(gè)片段。
可以通過(guò) [system.parts]表查看表片段和分區(qū)信息涡相。例如哲泊,假設(shè)我們有一個(gè) visits
表,按月分區(qū)催蝗。對(duì) system.parts
表執(zhí)行 SELECT
:
SELECT
partition,
name,
active
FROM system.parts
WHERE table = 'visits'
──partition─┬─name───────────┬─active─┐
│ 201901 │ 201901_1_3_1 │ 0 │
│ 201901 │ 201901_1_9_2 │ 1 │
│ 201901 │ 201901_8_8_0 │ 0 │
│ 201901 │ 201901_9_9_0 │ 0 │
│ 201902 │ 201902_4_6_1 │ 1 │
│ 201902 │ 201902_10_10_0 │ 1 │
│ 201902 │ 201902_11_11_0 │ 1 │
└───────────┴────────────────┴────────┘
partition
列存儲(chǔ)分區(qū)的名稱切威。此示例中有兩個(gè)分區(qū):201901
和 201902
。在 [ALTER ... PARTITION]語(yǔ)句中你可以使用該列值來(lái)指定分區(qū)名稱丙号。
name
列為分區(qū)中數(shù)據(jù)片段的名稱先朦。在 [ALTER ATTACH PART] 語(yǔ)句中你可以使用此列值中來(lái)指定片段名稱。
這里我們拆解下第一部分的名稱:201901_1_3_1
:
-
201901
是分區(qū)名稱犬缨。 -
1
是數(shù)據(jù)塊的最小編號(hào)喳魏。 -
3
是數(shù)據(jù)塊的最大編號(hào)。 -
1
是塊級(jí)別(即在由塊組成的合并樹中遍尺,該塊在樹中的深度)截酷。
注意
舊類型表的片段名稱為:20190117_20190123_2_2_0
(最小日期 - 最大日期 - 最小塊編號(hào) - 最大塊編號(hào) - 塊級(jí)別)。
active
列為片段狀態(tài)乾戏。1
激活狀態(tài)迂苛;0
非激活狀態(tài)。非激活片段是那些在合并到較大片段之后剩余的源數(shù)據(jù)片段鼓择。損壞的數(shù)據(jù)片段也表示為非活動(dòng)狀態(tài)三幻。
正如在示例中所看到的,同一分區(qū)中有幾個(gè)獨(dú)立的片段(例如呐能,201901_1_3_1
和201901_1_9_2
)念搬。這意味著這些片段尚未合并抑堡。ClickHouse 大約在插入后15分鐘定期報(bào)告合并操作,合并插入的數(shù)據(jù)片段朗徊。此外首妖,你也可以使用 OPTIMIZE 語(yǔ)句直接執(zhí)行合并逗噩。例:
OPTIMIZE TABLE visits PARTITION 201902;
┌─partition─┬─name───────────┬─active─┐
│ 201901 │ 201901_1_3_1 │ 0 │
│ 201901 │ 201901_1_9_2 │ 1 │
│ 201901 │ 201901_8_8_0 │ 0 │
│ 201901 │ 201901_9_9_0 │ 0 │
│ 201902 │ 201902_4_6_1 │ 0 │
│ 201902 │ 201902_4_11_2 │ 1 │
│ 201902 │ 201902_10_10_0 │ 0 │
│ 201902 │ 201902_11_11_0 │ 0 │
└───────────┴────────────────┴────────┘
非激活片段會(huì)在合并后的10分鐘左右刪除出吹。
查看片段和分區(qū)信息的另一種方法是進(jìn)入表的目錄:/var/lib/clickhouse/data/<database>/<table>/
。例如:
/var/lib/clickhouse/data/default/visits$ ls -l
total 40
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 1 16:48 201901_1_3_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201901_1_9_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 15:52 201901_8_8_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 15:52 201901_9_9_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201902_10_10_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201902_11_11_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:19 201902_4_11_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 12:09 201902_4_6_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 1 16:48 detached
- 物理存儲(chǔ)
[root@izm5eja0h0h0p04ol7ijrpz 20200319_10_10_0]# pwd
/var/lib/clickhouse/data/logs/logs/20200319_10_10_0
[root@izm5eja0h0h0p04ol7ijrpz 20200319_10_10_0]# ll
total 104
-rw-r----- 1 clickhouse clickhouse 831 Mar 19 23:01 checksums.txt
-rw-r----- 1 clickhouse clickhouse 234 Mar 19 23:01 columns.txt
-rw-r----- 1 clickhouse clickhouse 34 Mar 19 23:01 content.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 content.mrk2
-rw-r----- 1 clickhouse clickhouse 1 Mar 19 23:01 count.txt
-rw-r----- 1 clickhouse clickhouse 30 Mar 19 23:01 create_time.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 create_time.mrk2
-rw-r----- 1 clickhouse clickhouse 38 Mar 19 23:01 ip.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 ip.mrk2
-rw-r----- 1 clickhouse clickhouse 32 Mar 19 23:01 level.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 level.mrk2
-rw-r----- 1 clickhouse clickhouse 34 Mar 19 23:01 log_file_name.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 log_file_name.mrk2
-rw-r----- 1 clickhouse clickhouse 8 Mar 19 23:01 minmax_partition_date.idx
-rw-r----- 1 clickhouse clickhouse 4 Mar 19 23:01 partition.dat
-rw-r----- 1 clickhouse clickhouse 30 Mar 19 23:01 partition_date.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 partition_date.mrk2
-rw-r----- 1 clickhouse clickhouse 34 Mar 19 23:01 primary.idx
-rw-r----- 1 clickhouse clickhouse 34 Mar 19 23:01 service_name.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 service_name.mrk2
-rw-r----- 1 clickhouse clickhouse 33 Mar 19 23:01 thread_name.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 thread_name.mrk2
-rw-r----- 1 clickhouse clickhouse 30 Mar 19 23:01 timestamp.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 timestamp.mrk2
-rw-r----- 1 clickhouse clickhouse 27 Mar 19 23:01 trace_id.bin
-rw-r----- 1 clickhouse clickhouse 48 Mar 19 23:01 trace_id.mrk2
文件夾 '201901_1_1_0'励负,'201901_1_7_1' 等是片段的目錄温亲。每個(gè)片段都與一個(gè)對(duì)應(yīng)的分區(qū)相關(guān)棚壁,并且只包含這個(gè)月的數(shù)據(jù)(本例中的表按月分區(qū))。
detached
目錄存放著使用 DETACH 語(yǔ)句從表中分離的片段栈虚。損壞的片段也會(huì)移到該目錄袖外,而不是刪除。服務(wù)器不使用detached
目錄中的片段魂务÷椋可以隨時(shí)添加,刪除或修改此目錄中的數(shù)據(jù) – 在運(yùn)行 ATTACH 語(yǔ)句前头镊,服務(wù)器不會(huì)感知到蚣驼。
注意
在操作服務(wù)器時(shí),你不能手動(dòng)更改文件系統(tǒng)上的片段集或其數(shù)據(jù)相艇,因?yàn)榉?wù)器不會(huì)感知到這些修改颖杏。對(duì)于非復(fù)制表,可以在服務(wù)器停止時(shí)執(zhí)行這些操作坛芽,但不建議這樣做留储。對(duì)于復(fù)制表,在任何情況下都不要更改片段文件咙轩。
注意
那些有相同分區(qū)表達(dá)式值的數(shù)據(jù)片段才會(huì)合并获讳。這意味著 你不應(yīng)該用太精細(xì)的分區(qū)方案(超過(guò)一千個(gè)分區(qū))。否則活喊,會(huì)因?yàn)槲募到y(tǒng)中的文件數(shù)量和需要找開的文件描述符過(guò)多丐膝,導(dǎo)致 SELECT 查詢效率不佳。
彈性支持
- click house elastic (不支持彈性架構(gòu)) https://clickhouse.yandex/docs/en/development/architecture
ClickHouse 集群由獨(dú)立的分片組成钾菊,每一個(gè)分片由多個(gè)副本組成帅矗。集群不是彈性的,因此在添加新的分片后煞烫,數(shù)據(jù)不會(huì)自動(dòng)在分片之間重新平衡浑此。相反,集群負(fù)載將變得不均衡滞详。該實(shí)現(xiàn)為你提供了更多控制凛俱,對(duì)于相對(duì)較小的集群紊馏,例如只有數(shù)十個(gè)節(jié)點(diǎn)的集群來(lái)說(shuō)是很好的。但是對(duì)于我們?cè)谏a(chǎn)中使用的具有數(shù)百個(gè)節(jié)點(diǎn)的集群來(lái)說(shuō)蒲犬,這種方法成為一個(gè)重大缺陷朱监。我們應(yīng)該實(shí)現(xiàn)一個(gè)表引擎,使得該引擎能夠跨集群擴(kuò)展數(shù)據(jù)暖哨,同時(shí)具有動(dòng)態(tài)復(fù)制的區(qū)域赌朋,這些區(qū)域能夠在集群之間自動(dòng)拆分和平衡。
The ClickHouse cluster consists of independent shards, and each shard consists of replicas. The cluster is not elastic, so after adding a new shard, data is not rebalanced between shards automatically. Instead, the cluster load will be uneven. This implementation gives you more control, and it is fine for relatively small clusters such as tens of nodes. But for clusters with hundreds of nodes that we are using in production, this approach becomes a significant drawback. We should implement a table engine that will span its data across the cluster with dynamically replicated regions that could be split and balanced between clusters automatically.
表結(jié)構(gòu)設(shè)計(jì)參考
CREATE TABLE logs.logs (
ip String,
log_file_name String,
service_name String,
thread_name String,
trace_id String,
timestamp DateTime,
partition_date UInt32,
level String,
content String,
create_time DateTime
) ENGINE = MergeTree() PARTITION BY partition_date
ORDER BY (create_time,service_name, trace_id)
SETTINGS index_granularity = 8192
Distributed(cluster_name, db_name, table_name[, sharding_key])
sharding_key=rand()隨機(jī)寫
CREATE TABLE logs.logs_view
(
ip String,
log_file_name String,
service_name String,
thread_name String,
trace_id String,
timestamp DateTime,
partition_date UInt32,
level String,
content String,
create_time DateTime
)ENGINE =Distributed(log_collector,logs, logs, rand());
- 新建數(shù)據(jù)
insert into logs.logs(ip,log_file_name,service_name,thread_name,partition_date,level,content,create_time,timestamp)values('192.168.1.1','log.log','sparrow','thread',20200319,'level','content','2020-03-19 22:59:00','2020-03-19 22:58:00') ;
刪除分區(qū) delete parition
https://clickhouse.tech/docs/zh/query_language/alter/#alter_manipulations-with-partitions
ALTER TABLE testdb.testtable DROP PARTITION '201907'
性能及使用建議
性能測(cè)試對(duì)比
https://clickhouse.tech/benchmark.html
https://clickhouse.yandex/docs/en/introduction/performance/
數(shù)據(jù)的寫入性能
我們建議每次寫入不少于1000行的批量寫入篇裁,或每秒不超過(guò)一個(gè)寫入請(qǐng)求。當(dāng)使用tab-separated格式將一份數(shù)據(jù)寫入到MergeTree表中時(shí)赡若,寫入速度大約為50到200MB/s达布。如果您寫入的數(shù)據(jù)每行為1Kb,那么寫入的速度為50逾冬,000到200黍聂,000行每秒。如果您的行更小身腻,那么寫入速度將更高产还。為了提高寫入性能,您可以使用多個(gè)INSERT進(jìn)行并行寫入嘀趟,這將帶來(lái)線性的性能提升脐区。
常用命令行
clickhouse-client -h 192.168.1.195 -u root --password=123456
clickhouse-client -m
clickhouse-client --multiline //開啟多行模式
create database logs
show databases;
select * from system.clusters; //查詢集群狀態(tài)
安裝配置
- click house config.xml
<?xml version="1.0"?>
<yandex>
<logger>
<level>trace</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>10</count>
</logger>
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<interserver_http_port>9009</interserver_http_port>
<interserver_http_host>my-host-name</interserver_http_host>
<listen_host>0.0.0.0</listen_host>
<max_connections>4096</max_connections>
<keep_alive_timeout>3000</keep_alive_timeout>
<max_concurrent_queries>1000</max_concurrent_queries>
<max_table_size_to_drop>0</max_table_size_to_drop>
<uncompressed_cache_size>21474836480</uncompressed_cache_size>
<mark_cache_size>25769803776</mark_cache_size>
<path>/data/clickhouse/</path>
<tmp_path>/data/clickhouse/tmp/</tmp_path>
<user_files_path>/data/clickhouse/user_files/</user_files_path>
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<default_database>default</default_database>
<remote_servers incl="clickhouse_remote_servers"/>
<zookeeper incl="zookeeper-servers" optional="true" />
<macros incl="macros" optional="true" />
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<format_schema_path>/data/clickhouse/format_schemas/</format_schema_path>
<include_from>/etc/clickhouse-server/metrika.xml</include_from>
</yandex>
metrika.xml
<yandex>
<clickhouse_remote_servers>
<log_collector>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>192.168.2.254</host>
<port>9000</port>
<user>root</user>
<password>123456</password>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>122.168.2.255</host>
<port>9000</port>
<user>log_collector</user>
<password>123456</password>
</replica>
</shard>
</log_collector>
</clickhouse_remote_servers>
<zookeeper-servers>
<node index="1">
<host>192.168.209.78</host>
<port>2181</port>
</node>
<node index="2">
<host>192.168.192.166</host>
<port>2181</port>
</node>
<node index="3">
<host>192.168.209.79</host>
<port>2181</port>
</node>
<node index="4">
<host>192.168.192.179</host>
<port>2181</port>
</node>
<node index="5">
<host>192.168.209.92</host>
<port>2181</port>
</node>
<node index="6">
<host>192.168.209.94</host>
<port>2181</port>
</node>
<node index="7">
<host>192.168.192.184</host>
<port>2181</port>
</node>
</zookeeper-servers>
<macros>
<replica>logs_01</replica>
</macros>
<networks>
<ip>::/0</ip>
</networks>
<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>
users.xml
<?xml version="1.0"?>
<yandex>
<profiles>
<default>
<max_memory_usage>25769803776</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>random</load_balancing>
</default>
<readonly>
<readonly>1</readonly>
<max_memory_usage>25769803776</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>random</load_balancing>
</readonly>
</profiles>
<users>
<default>
<password></password>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</default>
<log_collector>
<password>123456</password>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
<allow_databases>
<database>logs</database>
</allow_databases>
</log_collector>
<!-- Example of user with readonly access. -->
<readonly>
<password></password>
<networks incl="networks" replace="replace">
<ip>::1</ip>
<ip>127.0.0.1</ip>
</networks>
<profile>readonly</profile>
<quota>default</quota>
</readonly>
</users>
<quotas>
<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>
</yandex>
服務(wù)啟動(dòng)
sudo systemctl start
clickhouse-server.service