Clickhouse常见命令使用：

Clickhouse常见命令使用：

一、导入数据

1、导入制表符分隔的数据

cat /data/ZDGL/stateAnalysis/dmt_term_stateAnalysisALL202010.txt | clickhouse-client -u default --password 6lYaUiFi --query="INSERT INTO knowyou_ott_ods.dmt_term_stateAnalysisALL FORMAT TabSeparated"

cat /data/ZDGL/stateAnalysis/dmt_term_stateAnalysisALL202010.txt | clickhouse-client -u default --password 6lYaUiFi --query="INSERT INTO knowyou_ott_ods.dmt_term_stateAnalysisALL FORMAT TSV"

2、导入CSV格式数据

cat /data/ZDGL/stateAnalysis/dmt_term_stateAnalysisALL202010.csv | clickhouse-client -u default --password 6lYaUiFi --query="INSERT INTO knowyou_ott_ods.dmt_term_stateAnalysisALL FORMAT CSV"

3、指定分割符导入

cat test.csv | clickhouse-client -u user --password password --format_csv_delimiter="|" --query="INSERT INTO db.tab1 FORMAT CSV"

4、加入最大分区数导入

cat /data/ZDGL/inventoryTurnover/dmt_term_inventoryTurnover202010.txt | clickhouse-client -u default --password 6lYaUiFi --max_partitions_per_insert_block 10000 --query="INSERT INTO knowyou_ott_ods.dmt_term_inventoryTurnover FORMAT TabSeparated"

5、跳过错误行导入

cat /data/erqi/zhuangwei/company/edw_sp_grid.txt | clickhouse-client -u default --password 6lYaUiFi --input_format_allow_errors_num=1 --input_format_allow_errors_ratio=0.1 --query="INSERT INTO knowyou_ott_ods.edw_sp_grid FORMAT TabSeparated"

二、导出数据

1、以CSV分隔符导出

clickhouse-client -u default --password 6lYaUiFi --query="select * from knowyou_ott_ods.dmt_ott_withduser limit 30 FORMAT CSV" >dmt_ott_withduser.csv

2、以制表符导出

clickhouse-client -u default --password 6lYaUiFi --query="select * from knowyou_ott_ods.dmt_ott_withduser limit 30 FORMAT TSV" >dmt_ott_withduser.txt

用的是centos版本，命令如下：

速度还挺快的顺利安装完毕。

更改下数据存储目录，便于测试大数据：

注意下，test目录需要更改下权限，简单点 *** 作：

建表必须有一个date字段默认用于分区，还有其他很多标引擎，这里面用的是MergeTree，最常用。

含义：create_date 时间字段默认用于分区，如果不写这个字段，这个MergeTree里面不能有其他值了(见下面），（src_ip,dst_ip,src_port,dst_port,timestamp）构成主键。

如果不必须定义主键,可以用其他字段得到分区字段。

粗略看了下，我们的数据压缩的后的数据占原来数据的大小为17%。

比较基础比较详细

https://www.cnblogs.com/shengyang17/p/10894490.html#_label3_0_0_3

*** 作函数

https://www.cnblogs.com/pengpenghuhu/p/14546305.html

手册：

https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/reference/topk/

本文基于ClickHouse 20.8.5.45版本编写， *** 作系统使用的是CentOS 7.5，主要介绍MergeTree表引擎的存储结构以及索引过程。

刚刚创建的表只在数据目录下生成了一个名为 test_merge_tree 文件夹(具体路径为data/default/test_merge_tree)，并没有任何数据，接下来往该表里面插入一条数据，看看会生成哪些文件。

在test_merge_tree目录下使用tree命令可以看到刚刚的那条命令生成了一个名为 200002_1_1_0 的文件夹。

在介绍这些文件之前先介绍一下200002_1_1_0这个目录的命名规则

当分区发生合并时，新的分区目录名称命名规则将会在接下来介绍，这里不做详述。

在介绍这部分之前，需要先将min_compress_block_size配置改小，以方便分析mrk2和bin文件，其默认值为65535。

修改方法为在 users.xml 文件的 profiles 里面增加以下配置

修改完后重启clickhouse-server服务，然后再用以下命令查看是否修改成功

刚刚已经插入了一条数据，但是那一条数据不具有代表性，所以这次决定多插入几条数据再来分析。

上面这条命令产生了个新的分区目录 200002_2_2_0 ，此目录下的文件前面已经讲过，现在重点分析以下几个文件的存储格式

MergeTree表会按照主键字段生成primary.idx，用于加快表查询。前面创建表时使用的是(Id, Name)两个字段作为主键，所以每隔index_granularity行数据就会取(Id, Name)的值作为索引值，由于index_granularity被设置为2，所以每隔两行数据就会生成一个索引。也就是说会使用(3,'Lisa'), (6,'Meimei'), (31,'vincent')作为索引值。

这里我只介绍第一个索引(3,'Lisa')的存储格式，剩下的可以自己去梳理。Id是UInt64类型的，所以使用8字节来存储。从上图可以看出前8个字节为0x03，以小端模式来存储，接下来我们可以看到其它文件都是以小端模式来存储。Name是String类型，属于变长字段，所以会先使用1个字节来描述String的长度，由于Lisa的长度是4，所以第9个字节为0x04，再接下来就是Lisa的ASCII码。

mrk2文件格式比较固定，primary.idx文件中的每个索引在此文件中都有一个对应的Mark，Mark的格式如下图所示：

通过primary.idx中的索引寻找mrk2文件中对应的Mark非常简单，如果要寻找第n(从0开始)个index，则对应的Mark在mrk2文件中的偏移为n*24，从这个偏移处开始读取24 Bytes即可得到相应的Mark。

bin文件由若干个Block组成，由上图可知Id.bin文件中包含两个Block。每个Block主要由头部的Checksum以及若干个Granule组成，Block的格式如下图所示：

每个Block都会包含若干个Granule，具体有多少个Granule是由参数min_compress_block_size控制，每次Block中写完一个Granule的数据时，它会检查当前Block Size是否大于等于min_compress_block_size，如果满足则会把当前Block进行压缩然后写到磁盘中，不满足会继续等待下一个Granule。结合上面的INSERT语句，当插入第一个Granule(3, 4)时，数据的的size为16，由于16 <24所以会等第二个Granule，当插入第二个Granule(6, 12)后数据的size为32，由于32 >24所以会把(3, 4, 6, 12)压缩放到第一个Block里面。最后面的那个31由于是最后一条数据，就放到第二个Block里面。

partition.dat文件里面存放的是分区表达式的值，该分区表达式生成的值为200002，UInt32类型，转换成16进制就是0x00030d42。

minmax文件里面存放的是该分区里分区字段的最小最大值。分区字段Birthday的类型为Date，其底层由UInt16实现，存的是从1970年1月1号到现在所经过的天数。通过上面的INSERT语句我们可以知道Birthday的最小值为2000-02-03，最大值为2000-02-08。这两个时间转换成天数分别为10990和10995，再转换成16进制就是0x2aee和0x2af3。

属于同一个分区的不同目录，ClickHouse会在分区目录创建后的一段时间自动进行合并，合并之后会生成一个全新的目录，以前老的分区目录不会立马删除，而是在合并后过一段时间再删除。新的分区目录名称遵循以下规则：

所以上面的两个分区目录200002_1_1_0和200002_2_2_0在过一段时间后最终会变成一个新的分区目录200002_1_2_1。由此可见如果你频繁插入数据会产生很多分区目录，在合并的时候会占用很多资源。所以最好一次插入很多条数据，尽量降低插入的频率。

通过上面的介绍相信大家已经对ClickHouse的索引结构有所了解，接下来用一张图简要描述Id字段的索引过程。

其它列的索引过程类似，这里就不一一赘述了，有兴趣的朋友可以自己去研究。

本文通过一个简单的例子来分析ClickHouse的存储结构，整个逻辑力求简洁明了，希望通过本文能够让喜欢ClickHouse的朋友对它的索引有个清晰的认识。

---