ClickHouse学习笔记

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, ...]

复制代码

ON CLUSTER cluster：指定分片，可用于建分布式表。
ENGINE - 引擎名和参数（必填）。
PARTITION BY — 分区键（可选）。

参数为Date类型，默认要天分区；要按月分区，可以使用表达式toYYYYMM(date_column)

ORDER BY — 表的排序键（可选）。

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。

PRIMARY KEY - 主键（可选），如果要设成跟排序键不相同。

通常主键默认和排序（ORDER BY）字段相同，不需另外指定。

SAMPLE BY — 用于抽样的表达式（可选）。

如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

SETTINGS — 影响 MergeTree 性能的额外参数（可选）：

控制合并树的其他参数设置（可选）。index_granularity——索引的粒度。索引“标记”之间的数据行数。默认值：8192。

新版CLickHouse不建议使用以下方式建表：

ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

4.1.2. ReplacingMergeTree

该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = ReplacingMergeTree([ver])  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。

合并的时候，ReplacingMergeTree 从所有具有相同主键的行中选择一行留下：如果 ver 列未指定，选择最后一条。如果 ver 列已指定，选择 ver 值最大的版本。

4.1.3. SummingMergeTree

该引擎继承自 MergeTree。区别在于，当合并SummingMergeTree表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = SummingMergeTree([columns])  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

columns - 包含了将要被汇总的列的列名的元组。可选参数。所选的列必须是数值类型，并且不可位于主键中。

如果没有指定 columns，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。

4.1.4. AggregatingMergeTree

该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将相同主键的所有行（在一个数据片段内）替换为单个存储一系列聚合函数状态的行。

可以使用 AggregatingMergeTree 表来做增量数据统计聚合，包括物化视图的数据聚合。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = AggregatingMergeTree()  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

4.1.5. CollapsingMergeTree

该引擎继承于 MergeTree，并在数据块合并算法中添加了折叠行的逻辑。

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。

该引擎可以显著的降低存储量并提高 SELECT 查询效率。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = CollapsingMergeTree(sign)  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

sign—类型列的名称：1是“状态”行，-1是“取消”行。

列数据类型 — Int8。

4.1.6. VersionedCollapsingMergeTree

该引擎继承了MergeTree，并将折叠行的逻辑添加到合并数据部分的算法中。版本化的collapsingmergetree与collapsingmergetree具有相同的用途，但使用了不同的折叠算法，允许在多个线程中以任意顺序插入数据。尤其是，“版本”列有助于正确折叠行，即使它们是按错误的顺序插入的。相反，collapsingmergetree只允许严格连续插入。

允许快速写入不断变化的对象状态。

删除背景中的旧对象状态。这大大减少了存储容量。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = VersionedCollapsingMergeTree(sign, version)  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

sign—类型的列的名称：1是“状态”行，-1是“取消”行。

列数据类型应为Int8

version—具有对象状态版本的列的名称。

列数据类型应为Uint*。

4.1.7. GraphiteMergeTree

该引擎继承于 MergeTree，用于细化和聚合/平均（汇总）Graphite Data。

如果不需要汇总，您可以使用任何Clickhouse表引擎来存储Graphite Data，但是如果需要汇总，请使用GraphiteMergeTree。该引擎减少了存储容量，并提高了Graphite Data的效率。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
Path String,  
Time DateTime,  
Value <Numeric_type>,  
Version <Numeric_type>  
...  
) ENGINE = GraphiteMergeTree(config_section)  
[PARTITION BY expr]  
[ORDER BY expr]  
[SAMPLE BY expr]  
[SETTINGS name=value, ...]

复制代码

config_section—配置文件中配置项的名称，

4.1.8. 数据副本

只有 MergeTree 系列里的表可支持副本：

ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergeTree
ReplicatedGraphiteMergeTree

副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复制表和非复制表。

副本不依赖分片。每个分片有它自己的独立副本。

4.2. Log Family

这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的。

这系列的引擎有：

主要特点：

数据存储在磁盘上。
写入时将数据追加在文件末尾。
不支持突变操作。
不支持索引。意味着 SELECT 在范围查询时效率不高。
非原子地写入数据。

如果某些事情破坏了写操作，例如服务器的异常关闭，你将会得到一张包含了损坏数据的表。

4.2.1. StripeLog

在你需要写入许多小数据量（小于一百万行）的表的场景下使用这个引擎。

复制代码

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = StripeLog

复制代码

4.2.2. TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。

并发数据访问不受任何限制：如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常,如果同时写入多个查询中的表，则数据将被破坏。

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。换句话说，此引擎适用于相对较小的表（建议最多1,000,000行）。如果您有许多小表，则使用此表引擎是适合的，因为它比Log引擎更简单（需要打开的文件更少）。当您拥有大量小表时，可能会导致性能低下，但在可能已经在其它 DBMS 时使用过，则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。

4.2.3. Log

Log与TinyLog的不同之处在于，"标记" 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。Log 引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log 引擎适用于临时数据，write-once 表以及测试或演示目的。

4.3. Distributed

分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。分布式引擎参数：服务器配置文件中的集群名，远程数据库名，远程表名，数据分片键（可选）。

示例：

Distributed(logs, default, hits[, sharding_key])

将会从位于“logs”集群中 default.hits 表所有服务器上读取数据。远程服务器不仅用于读取数据，还会对尽可能数据做部分处理。例如，对于使用 GROUP BY 的查询，数据首先在远程服务器聚合，之后返回聚合函数的中间状态给查询请求的服务器。再在请求的服务器上进一步汇总数据。

4.4. Memory

Memory 引擎以未压缩的形式将数据存储在RAM中。数据完全以读取时获得的形式存储。换句话说，从这张表中读取是很轻松的。并发数据访问是同步的。锁范围小：读写操作不会相互阻塞。不支持索引。阅读是并行化的。在简单查询上达到最大生产率（超过10 GB /秒），因为没有磁盘读取，不需要解压缩或反序列化数据。（值得注意的是，在许多情况下，与 MergeTree 引擎的性能几乎一样高）。重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。但是，它可用于测试，以及在相对较少的行（最多约100,000,000）上需要最高性能的查询。

5. SQL语法

SELECT查询

复制代码

SELECT [DISTINCT] expr_list  
[FROM [db.]table | (subquery) | table_function] [FINAL]  
[SAMPLE sample_coeff]  
[ARRAY JOIN ...]  
[GLOBAL] ANY|ALINNER|LEFT JOIN (subquery)|table USING columns_list  
[PREWHERE expr]  
[WHERE expr]  
[GROUP BY expr_list] [WITH TOTALS]  
[HAVING expr]  
[ORDER BY expr_list]  
[LIMIT [n, ]m]  
[UNION ALL ...]  
[INTO OUTFILE filename]  
[FORMAT format]  
[LIMIT n BY columns]

复制代码

语法与关系型数据库MYSQL基本一致。

除了SELECT之后的表达式列表(expr_list)，所有的子句都是可选的。

INSERT写入

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

语法与关系型数据库MYSQL基本一致。

remote数据同步

INSERT INTO [db.][tablename] SELECT * FROM remote(ip,db.tablename[,username[,password])

mysql表同步

CREATE TABLE tmp ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

CREATE创建

复制代码

CREATE DATABASE [IF NOT EXISTS] db_name;  
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],  
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],  
...  
) ENGINE = engine

复制代码

ALTER修改

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

DROP 删库/表

DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]   #删除数据库
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]  #删除表

ALTER修改

ALTER TABLE [db.]table DELETE WHERE filter_expr...   #删除数据
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE ...  #修改数据

TRUNCATE 清表

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

6. 实践

ClickHouse在galaxy-0-10至galaxy-0-14五台物理机上有安装部署，本次实践是基于此测试集群进行操作。

分片配置文件路径：/etc/metrika.xml

里面有配置分片名称（cluster_3shards_1replicas）及信息，若要创建分布式库/表，则在语句末尾加上ON CLUSTER cluster_3shards_1replicas即可。

登陆ClickHouse通过SQL也可查看分片信息：

配置文件路径：/etc/clickhouse-server/config.xml
日志文件路径：/var/log/clickhouse-server/
建表信息路径：/data/clickhouse/metadata/
分区数据路径：/data/clickhouse/data/

下面以进行分布式操作为例（单机操作省略[ON CLUSTER cluster_3shards_1replicas]即可）：

6.1. 连接ClickHouse

clickhouse-client -m -u [username] -h [ip] --password [password] --port [port]

username：用户名
password：密码
ip：服务器IP
port：端口
-m：允许多行查询

6.2. 创建数据库

创建分布式测试数据库test

CREATE DATABASE test ON CLUSTER cluster_3shards_1replicas;

6.3. 创建表

以深圳通原始数据为例，在每个分片上创建表szt_data

复制代码

CREATE TABLE test.szt_data ON CLUSTER cluster_3shards_1replicas  
(  
id String,   
card_id String,   
deal_time String,   
trade_type String,   
trade_sum Int16,   
trade_value Int16,   
terminal_code String,   
com_line String,   
line_station String,   
car_gate String,   
flag String,   
finish_time Date  
)  
ENGINE = MergeTree()  
PARTITION BY finish_time  
ORDER BY (card_id, terminal_code)  
SETTINGS index_granularity = 8192

复制代码

以结算日期（Date型finish_time）按天进行分区，以卡号和设备编号进行排序，使用默认索引粒度8192。

toYYYYMM(EventDate)：按月分区
toMonday(EventDate)：按周分区
toDate(EventDate)：按天分区
PARTITION BY cloumn_name：指定列分区（不建议以非日期类型作为分区）

对于分区可进行如下操作：

ALTER TABLE [db.][tablename] DROP PARTITION [partition]  #删除分区  
ALTER TABLE [db.][tablename] DETACH PARTITION [partition]#下线分区  
ALTER TABLE [db.][tablename] ATTACH PARTITION [partition]#恢复分区  
ALTER TABLE [db.][tablename] FREEZE PARTITION [partition]#备份分区

创建分布式表all_szt_data

CREATE TABLE test.all_szt_data 
ON CLUSTER cluster_3shards_1replicas AS test.szt_data 
ENGINE = Distributed(cluster_3shards_1replicas, test, szt_data, rand())

cluster_3shards_1replicas：配置文件中集群名称
test：数据库名称
szt_data：表名
rand()：分片方式，随机

6.4. 删除库/表

语法同mysql，若要删除创建的分布式库/表，在末尾加上ON CLUSTER cluster_3shards_1replicas即可。

DROP DATABASE test;   #删除数据库
DROP TABLE szt_data;   #删除表
DROP TABLE all_szt_data;   #删除分布式表

6.5. 数据导入

上面建表时分别在各机器上创建了本地表（szt_data）和分布式表（all_szt_data），数据导入时可指定导入哪台机器上或者导入分布式表，若导入分布式表数据会随机分到各分片上。

命令行导入

clickhouse-client -u [username] -h [ip] --password [password] --port [port] --query="INSERT INTO [tablename] FORMAT CSV" < [filepath]  
cat [filepath] | clickhouse-client -u [username] -h [ip] --password [password] --port [port] --query="INSERT INTO [tablename] FORMAT CSV"

同时可在clickhouse-client命令后面添加以下选项：

--format_csv_delimiter：指定分隔符。上面命令中文件是以逗号分割
--input_format_allow_errors_num：容错数量

其他选项可输入命令clickhouse-client --help查看。

6.6. 数据修改

语法与普通关系型数据库稍微有些区别，由于分布式表并不存储数据，所以无法直接对分布式表进行修改，对于本地表中数据指定记录的修改，可用以下方式：

ALTER TABLE test.szt_data UPDATE trade_sum=1000 WHERE id='20181016094339_020001045';  #修改数据
ALTER TABLE test.szt_data DELETE WHERE id='20181016094339_020001045';  #删除数据

6.7. 查询

语法同普通关系型数据库

SELECT * FROM test.szt_data LIMIT 10;   #查询本地表
SELECT * FROM test.all_szt_data LIMIT 10;   #查询分布式表

查看分片信息

SELECT * FROM system.clusters;

查看分区信息

SELECT partition, name, active FROM system.parts WHERE table = 'szt_data';

对应存储目录下的文件：

从目录结构可看出，ClickHouse为列式存储，每个数据文件夹下均存储了以列为单位的文件。

走到华灯初上

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
ClickHouse学习笔记

目录1.概述2.应用场景3.数据类型4.表引擎4.1.MergeTreeFamily4.1.1.MergeTree4.1.2.ReplacingMergeTree4.1.3.SummingMergeTree4.1.4.AggregatingMergeTree4.1.5.CollapsingMergeTree4.1.6.Versio...
复制链接

扫一扫

专栏目录