clickhouse数据压缩对比

在上一篇分享中，我们介绍了ClickHouse的安装部署和简单使用。本文将介绍ClickHouse中一个非常重要的概念— 表引擎(table engine) 。如果对MySQL熟悉的话，或许你应该听说过InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引方式、锁定水平等功能，也可以称之为 表类型 。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的 表类型 。比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。本文会对ClickHouse中常见的表引擎进行介绍，主要包括以下内容： Log系列表引擎功能相对简单，主要用于快速写入小表(1百万行左右的表)，然后全部读出的场景。 即一次写入多次查询 。 该引擎适用于 一次写入，多次读取的场景 。对于处理小批数据的中间表可以使用该引擎。值得注意的是，使用大量的小表存储数据，性能会很低。 进入默认数据存储目录，查看底层数据存储形式,可以看出： TinyLog 引擎表每一列都对应的文件 当我们执行 ALTER操作 时会报错，说明该表引擎不支持ALTER操作 相比TinyLog而言，StripeLog拥有更高的查询性能（拥有.mrk标记文件，支持并行查询），同时其使用了更少的文件描述符（所有数据使用同一个文件保存）。 进入默认数据存储目录，查看底层数据存储形式 可以看出StripeLog表引擎对应的存储结构包括三个文件： Log引擎表适用于临时数据，一次性写入、测试场景。Log引擎结合了TinyLog表引擎和StripeLog表引擎的长处，是Log系列引擎中性能最高的表引擎。 进入默认数据存储目录，查看底层数据存储形式 Log引擎的存储结构包含三部分： 在所有的表引擎中，最为核心的当属MergeTree系列表引擎，这些表引擎拥有最为强大的性能和最广泛的使用场合。对于非MergeTree系列的其他引擎而言，主要用于特殊用途，场景相对有限。而MergeTree系列表引擎是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。 MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。 MergeTree作为家族系列最基础的表引擎，主要有以下特点： 查看一下数据存储格式，可以看出，存在三个分区文件夹，每一个分区文件夹内存储了对应分区的数据。 进入一个分区目录查看 可以看出，新插入的数据新生成了一个数据块，并没有与原来的分区数据在一起，我们可以执行 optimize 命令，执行合并操作 执行上面的合并操作之后，会新生成一个该分区的文件夹，原理的分区文件夹不变。 上文提到 MergeTree 表引擎无法对相同主键的数据进行去重，ClickHouse提供了ReplacingMergeTree引擎，可以针对相同主键的数据进行去重，它能够在合并分区时删除重复的数据。值得注意的是， ReplacingMergeTree 只是在一定程度上解决了数据重复问题，但是并不能完全保障数据不重复。 当我们再次向该表插入具有相同主键的数据时，观察查询数据的变化 从上面的示例中可以看出，ReplacingMergeTree是支持对数据去重的，那么是根据什么进行去重呢？答案是： ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY 。我们在看一个示例： 再次向该表中插入相同emp_id和name的数据，并执行合并操作，再观察数据 至此，我们知道了ReplacingMergeTree是支持去重的，并且是按照 ORDERBY排序键 为基准进行去重的。细心的你会发现，上面的重复数据是在一个分区内的，那么如果重复的数据不在一个分区内，会发生什么现象呢？我们再次向上面的 emp_replacingmergetree1 表插入不同分区的重复数据 ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。 在执行分区合并时，会触发删除重复数据。optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行。 ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。 如果没有设置 [ver]版本号 ，则保留同一组重复数据中的最新插入的数据； 如果设置了 [ver]版本号 ，则保留同一组重复数据中 ver字段取值最大的那一行 。 一般在数据量比较大的情况，尽量不要使用该命令。因为在海量数据场景下，执行optimize要消耗大量时间 该引擎继承了MergeTree引擎，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果。 推荐将该引擎和 MergeTree 一起使用。例如，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失数据。 如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即 GROUP BY的分组字段是确定的 ，可以使用该表引擎。 当我们再次插入具有相同emp_id,name的数据时，观察结果 要保证 PRIMARY KEY expr 指定的主键是 ORDER BY expr 指定字段的前缀，比如 这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。 用ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。 以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。 如果没有指定聚合字段，则会按照非主键的数值类型字段进行聚合 如果两行数据除了排序字段相同，其他的非聚合字段不相同，那么在聚合发生时，会保留最初的那条数据，新插入的数据对应的那个字段值会被舍弃 该表引擎继承自MergeTree，可以使用 AggregatingMergeTree 表来做增量数据统计聚合。如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。 与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。 对于AggregateFunction类型的列字段，在进行数据的写入和查询时与其他的表引擎有很大区别，在写入数据时，需要调用 <agg>-State 函数；而在查询数据时，则需要调用相应的 <agg>-Merge 函数。对于上面的建表语句而言，需要使用 sumState 函数进行数据插入 上面演示的用法非常的麻烦，其实更多的情况下，我们可以结合物化视图一起使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图。 AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。 CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。 每次需要新增数据时，写入一行sign标记为1的数据；需要删除数据时，则写入一行sign标记为-1的数据。 上面的建表语句使用CollapsingMergeTree(sign)，其中字段sign是一个Int8类型的字段 CollapsingMergeTree同样是以ORDER BY排序键作为判断数据唯一性的依据。 分数数据折叠不是实时的，需要后台进行Compaction操作，用户也可以使用手动合并命令，但是效率会很低，一般不推荐在生产环境中使用。 当进行汇总数据操作时，可以通过改变查询方式，来过滤掉被删除的数据 只有相同分区内的数据才有可能被折叠。其实，当我们修改或删除数据时，这些被修改的数据通常是在一个分区内的，所以不会产生影响。 值得注意的是：CollapsingMergeTree对于写入数据的顺序有着严格要求，否则导致无法正常折叠。 如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。 上面提到CollapsingMergeTree表引擎对于数据写入乱序的情况下，不能够实现数据折叠的效果。VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。 VersionedCollapsingMergeTree使用 version 列来实现乱序情况下的数据折叠。 可以看出：该引擎除了需要指定一个sign标识之外，还需要指定一个UInt8类型的version版本号。

可以的ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。由号称“俄罗斯 Google”的Yandex开发而来，在2016年开源，在计算引擎里算是一个后起之秀，在内存数据库领域号称是最快的。由于它有几倍于GreenPlum等引擎的性能优势，所以不少人都选择将其安装云服务器中使用。ClickHouse是一个列导向数据库，是原生的向量化执行引擎。它在大数据领域没有走Hadoop生态，而是采用Local attached storage作为存储，这样整个IO可能就没有Hadoop那一套的局限。它的系统在生产环境中可以应用到比较大的规模，因为它的线性扩展能力和可靠性保障能够原生支持shard+replication这种解决方案。它还提供了一些SQL直接接口，有比较丰富的原生client。以下是ClickHouse作为分析型数据库的特点：一. 速度快ClickHouse性能超过了市面上大部分的列式存储数据库，相比传统的数据ClickHouse要快100-1000倍，ClickHouse还是有非常大的优势。100Million 数据集:ClickHouse比Vertica约快5倍，比Hive快279倍，比MySQL快801倍。1Billion 数据集:ClickHouse比Vertica约快5倍，MySQL和Hive已经无法完成任务了。二. 功能多ClickHouse支持数据统计分析各种场景：1.支持类SQL查询；2.支持繁多库函数（例如IP转化，URL分析等，预估计算/HyperLoglog等）；3.支持数组(Array)和嵌套数据结构(Nested Data Structure)；4.支持数据库异地复制部署。三. 文艺范不理睬Hadoop生态，走自己的路。目前任何具有x86_64，AArch64或PowerPC64LE CPU架构的Linux，FreeBSD或Mac OS X上运行。而ClickHouse的缺点：1.不支持Transaction：想快就别想Transaction；2.聚合结果必须小于一台机器的内存大小：不是大问题；3.缺少完整的Update/Delete操作；4.支持有限操作系统。

clickhouse支持与多种存储引擎集成，可以从集成的引擎里面读取消息，然后写到真正的数据存储表里。 clickhouse批量写入的性能比较好，我们的业务场景下会大批量的产生数据，如果使用clickhouse-jdbc去写的，写入时机和每批次写入的数量不好把控，最终选择了先将消息写入kafka，然后由clickhouse从kafka消费数据，clickhouse server消费到数据之后写入真正的数据表。 clickhouse集成kafka引擎见官方文档： https://clickhouse.com/docs/zh/engines/table-engines/integrations/kafka/ 下面的介绍会与官方文档有重复，然后补充一些集成过程中遇到的坑。 下面介绍clickhouse与kafka集成的步骤，clickhouse版本是22.1.3.7 必要参数 可选参数 关于必选参数中的kafka_format参数，参见Formats部分，format具体解释如下 https://clickhouse.com/docs/zh/interfaces/formats/ 。 JSONEachRow, JSONStringsEachRow, JSONCompactEachRow, JSONCompactStringsEachRow 这几种格式，ClickHouse会将行输出为用换行符分隔的JSON值，这些输出数据作为一个整体时，由于没有分隔符(,)因而不是有效的JSON文档。 官方文档给了一些示例。 由于我的真实的数据表，有一个字段是json类型的字符串，但是一开始设置kafka_format的类型为JSONEachRow时，从kafka消费数据会报错，所以kafka_format格式设置成了JSONAsString,具体的错误后面贴出来。 创建kafka引擎表，用于从kafka消费数据 由于我的数据结构里有嵌套json，如果使用JSONEachRow，有个字段是json类型的字符串，带转义字符，导致clickhouse解析失败，没找到解决办法，所以使用了JSONAsString格式。 一个简单的MergeTree引擎的表，其中content是json格式的字符串。 创建的物化视图用于把从kafka消费到的数据，写到真实的数据表里，在这个例子里，msg_json_source从kafka消费到数据，然后通过物化视图msg_json_source_consumer将消费到的数据写到真实的数据表msg_target中。 由于从kafka消费到的数据就是一个json字符串，在这里使用JSONExtractString等json字段提取工具，提取msg里的字段，比如biz,sender_id,content等字段。 status_time原本计划用DatTime64类型的，但是这个时间格式有坑，最终选择了使用UInt64存毫秒级时间戳，具体的问题下面再介绍。 在clickhouse创建好3张表之后(kafka引擎表，真实数据表，物化视图表)，往kafka发消息 本地安装一个简易的kafka服务端，然后创建topic 创建好topic之后，使用Java客户端往kafka发消息，使用confluent client发也可以。 添加kafka依赖 实体类,使用fastjson的@JSONField注解，实体类转字符串的时候，将驼峰转换为下划线 测试类 最终发送完，我们查看一下clickhouse里的数据表的数据,可以发现我们发送到kakfa里的数据，已经成功的消费，并且写入到真实的数据表里了。 当时测试环境部署的版本是21.9，但是这个版本有问题，不推荐安装，建议直接部署22以上的clickhouse 我一开始就是使用的JSONEachRow格式，但是我的消息体里还有嵌套的json，类似下面这种格式，里面有个字段还是个json，转行成字符串带转义字符。 然后消息体的string字符串贴一条在这里 然后clickhouse解析消息体报错，当时的错找不到了，现在复现不出来了，非常的难顶。。。。 后来因为赶版本的原因把kafka_format换成了JSONAsString。 clickhouse是支持DateTime64格式的，可以到毫秒级，但是实际使用过程中却有些坑在， 首先是有的客户端解析毫秒字符串有问题，其次是使用JSONExtract*的方法，会有差异，再然后是jdbc查询的时候，也会导致时间查询有问题。 拿毫秒时间戳和秒级时间戳做试验，clickhouse-server版本是22.3.1.1 把上面的kafka引擎表拿出来改一下 其中status_time这个字段的类型改成DateTime64(3, "Asia/Shanghai")，使用JSONExtractUInt提取时间，看下效果 首先发条数据，数据内容如下 传入的是毫秒级时间戳，然后数据表存储的时候就变成了2282年 然后如果传入秒级的时间戳，真实的数据是这样 clickhouse存储的时候看着时间正常了，但是毫秒丢失了 然后修改一下物化视图的字段提取方式，之前是 JSONExtractUInt(msg,"status_time") as status_time，现在改成使用 JSONExtractString(msg,"status_time") as status_time提取时间 会发现时间类型又正常了。 这一条数据内容如下 最终使用JSONExtractString提取毫秒时间戳，得到了正确的DateTime64的时间，非常的神奇 最终我决定来了个釜底抽薪的方法，时间直接用UInt64存，因为我发送出去的数据是毫秒级时间戳，最终存时间戳，查询时间范围的时候直接用long类型的数据between好了。 这也是无奈之举，万一哪天server更新版本，导致时间出现问题，那就完蛋了，希望后面时间可以稳定一点吧。

ClickHouse支持多种方式的数据压缩：LZ4和ZSTD。 关于压缩算法的测试，见 这篇文章 。简而言之，LZ4在速度上会更快，但是压缩率较低，ZSTD正好相反。尽管ZSTD比LZ4慢，但是相比传统的压缩方式Zlib，无论是在压缩效率还是速度上，都可以作为Zlib的替代品。 下面我们对比一下这两种压缩方式。压缩测试所用的表（lineorder）结构和数据来自 这里 。未压缩的数据集是680GB。 把上述数据加载到ClickHouse后，默认的LZ4压缩算法下，数据容量是184G（压缩到27%），而ZSTD达到了135GB（压缩到20%）。 如果想要使用ZSTD压缩方式，修改为如下配置即可： 压缩比率对比 压缩后的查询性能如何，我们来跑如下查询看看： 为了保持客观，查询测试会跑两次，第一次是冷数据请求，这次的数据没有被操作系统缓存，第二次是热数据情求，这次的数据已经被操作系统的内存缓存了。 LZ4的性能如下： ZSTD性能如下： 冷数据查询情况下，两者区别不大，因为消耗在IO方面的时间，远大于消耗在数据解压缩上面的时间。 热数据请求下，LZ4会更快，此时IO代价小，数据解压缩成为性能瓶颈。 综上所述，默认的LZ4压缩方式，会给我们提供更快的执行效率，但是同时需要占用较多的磁盘容量。 ClickHouse抛开高效的SQL执行效率，数据压缩比率也是一个非常喜人的地方。使用Hadoop Node低配置服务器，再加上ClickHouse优秀的压缩性能，单机容量轻松可达几十T，推荐直接使用默认的LZ4压缩方式，用可以接受的少量空间来换查询执行效率的提升。

物化视图在数据层面做指标大宽表有着举足轻重的作用，分布式物化视图是对物化视图存储的数据进行分布式读取。 之前我们有一个介绍过物化视图的文章，详情请点击：clickhouse物化视图的应用，这里我们已经介绍过物化视图是什么，如何使用。 下面我们这里来介绍一下分布式物化视图的使用。我们这里使用的是分布式clickhouse集群。版本是：20.3.10.75，下面我们就来详解分布式物化视图在clickhouse的使用。 1：首先我们还是来建立三个表。 2：分别在不同的节点插入数据，我这里有两个节点，我们每个节点插入2条数据。 节点1如下： 节点2如下： 3：插入完数据之后，我们去每个节点查询，因为我们需要读所有的数据，则我们需要建一下分布式表来读数据。下面是建分布式表的语句。 建立好上面的分布式表之后就能读集群所有节点的数据了。我这里贴一下user表的所有数据。 4：上面是基础的数据表，这里我们开始建物化视图表。下面的sql是把用户表，用户信息表，绑定表进行组合成大宽表，下面的脚本我们是在每个节点上存了一份快照，实际业务中我们是写数据到一个节点，不会一份数据存多份。我这里做例子就这么使用。 5：上面的物化视图表我们建立好了，下面我们在物化视图表上建分布式表。 好了，到这里我们已经可以通过物化视图分布式表读每个节点的物化视图了，业务中我们基于物化视图来做大宽表，读取物化视图分布式表是非常常见的。我之前记得之前clickhouse物化视图在微信的应用这篇文章也是比较类似。 总结 ：

下文以 常见ClickHouse集群部署架构 中 方案四 的部署架构为例。 准备Docker环境： 其中，主配置文件 conf/zoo.cfg的关键配置项： ClickHouse最重要的配置有clickhouse_remote_servers、zookeeper和macros。 主配置文件，设置成可加载其他相关配置文件。 用于配置集群、数据副本、Zookeeper等。如果使用 方案一 ，此处配置需修改。 宏定义，后面创建本地表时会引用到。比如： 查看数据表： 主副本的分布表： 副副本的分布表： node21节点主副本的本地表： node23节点副副本的本地表：

不能。数据覆盖是数据恢复行业的专业词汇，指的是在我们删除数据后，如果之后又有其他数据对其原有的部分或全部存储空间进行占据，则称之为覆盖。一旦出现数据覆盖情况是无法对数据进行恢复的。clickhouse称为列式储存，可以通过数据能否恢复来看能否覆盖数据。通过资料查找可以发现数据是可以恢复的，也就是说数据并不会被覆盖。当储存文件里的内容删除掉时，恢复原文件里面的内容也没有办法找回；当原文件里的内容还存在时，恢复原文件可以看到里面的内容。

https://clickhouse.tech/docs/en/sql-reference/statements/create/view/# 物化视图可以理解为一个预聚合触发器，数据在控制好触发的汇聚条件，几乎是实时的 物化视图会存储一份计算好的聚合数据，是一种空间换时间的绝妙方法，对集群的稳定性和很重要。 物化视图的建立有两种方法 1，使用TO关键字（ 推荐使用 ），可以控制TTL，不能使用POPULATE 例： 2，使用默认表 此方案建议是数据量小的表，因为无法控制TTL，后期数据运维不方便。默认存储表在clickhouse中是 .inner_id.uuid 值作为表名 例： 1，物化视图是一种空间换时间的预聚合方式，聚合后的数据将存储在新表中，一般于SummingMergeTree，AggregatingMergeTree等聚合引擎一起使用。 2，物化视图因为是写入触发器，所以as select只对每批次的insert data有效果，所以即使是where条件也是对这批写入数据起效果（ https://clickhouse.tech/docs/en/sql-reference/statements/create/view/#materialized ） 4，POPULATE关键字，不建议使用，会把原始表中的已存在数据全部物化一遍，老数据的同步，建议直接insert到mv中 5，多表join生成物化视图，左表插入数据时才更新 6，源表数据的改变不会影响物化视图，如update, delete, drop partition

clickhouse不可以替代hadoop。Hadoop生态圈的技术繁多，HDFS一直用来保存底层数据，地位牢固。Hbase作为一款Nosql也是Hadoop生态圈的核心组件，它海量的存储能力，优秀的随机读写能力，能够处理一些HDFS不足的地方。Apache Kudu是Cloudera Manager公司16年发布的新型分布式存储系统，结合CDH和Impala使用可以同时解决随机读写和sql化数据分析的问题。分别弥补HDFS静态存储和Hbase Nosql的不足。Clickhouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)，能够使用SQL查询实时生成分析数据报告。它同样拥有优秀的数据存储能力。

一般情况下，如果不是主动使用systemctl stop clickhouse-server 停止clickhouse 而是使用kill -9 pid关闭clickhouse，或者异常奔溃，那么如果一切正常的情况下clickhouse server 10s检测进程，自动重启。 登录机器cat /etc/cron.d/clickhouse-server */10 * * * * root (which service > /dev/null 2>&1 && (service clickhouse-server condstart ||:)) || /etc/init.d/clickhouse-server condstart > /dev/null 2>&1 默认会10s检测一下服务进程是否正常，否则重启，检测时间可以调。/etc/init.d/clickhouse-server 在执行分布式DDL的时候出现这个问题一般是有一个节点处于假死状态，但是节点又没有完全奔溃，一般报错如下 Code: 159. DB::Exception: Received from xxxxx:29000. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000xxxxxxx is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 1 unfinished hosts (0 of them are currently active), they are going to execute the query in background. distributed_ddl_task_timeout 执行超过了默认的180s。 首先检查异常节点机器网络，磁盘等信息，然后检查ck状态。一般都是磁盘满了或者网络问题，很少有zk集群出问题。处理方式的话都是清理磁盘和修复网络。 Code: 458, e.displayText() = DB::ErrnoException: Cannot unlink file /data2/clickhouse/store/488/488da1e0-a9ee-4191-8376-0daaa4e0314d/format_version.txt, errno: 2, strerror: No such file or directory (version 21.3.4.25 (official build)) clickhouse 集群在建分布式表的时候出现 clickhouse zookeeper All connection tries failed 如果配置没啥问题，zk和ck集群也没啥问题，重启下zk即可恢复 查询出现AST is too big. Maximum: 500000 程序报错AST is too big. Maximum: 500000，语法树元素个数超过限制错误，说明查询sql很长很复杂，一般情况不会有，要木优化sql，要木修改集群配置 在user.xml 添加 <max_ast_elements>10000000</max_ast_elements> <max_expanded_ast_elements>10000000</max_expanded_ast_elements> 报错 DB::Exception: Replica xxxxx already exists 。 CK会对同一个block保证重复插入的insert的幂等性，会检测重复，默认会去重，使用 insert_deduplicate 配置。如果不需要去重则可以使用 SET insert_deduplicate=0 ，但不推荐这样做。 查询超过了限制的时间（60s）,需要优化sql，或者做预聚合 一次写入的分区数超过100，一般情况下不会出现一次写操作写100个分区的情况，解决方法1：查看写入的数据是否异常，为啥会写100个分区，一般是按时间分区，是不是时间解析错误了。解决方案2：在user.xml配置文件中添加<max_partitions_per_insert_block>配置项

1，查询强烈要求带上分区键过滤和主键过滤，如 where day = today() and itime = now()。 2，建表的时候，选择合适的分区键和排序键是优化的关键。 3，如果不允许重复主键(且不要求去重时效性)，建议使用表类型：ReplicatedReplacingMergeTree 建表语句可参考 https://clickhouse.yandex/docs/en/operations/table_engines/replacingmergetree/ ,注意只能保证单节点的数据不重复，无法保证集群的。 4，如果要对某一列过滤，且该列非partition key和orderby key, 且该列过滤前后数据量差异较大，建议使用prewhere clause过滤。参考： https://clickhouse.yandex/docs/en/query_language/select/#prewhere-clause 。 5，日期和时间使用Date, DateTime类型，不要用String类型。 6，建表时，强烈建议低基数(基数小于10000)且类型为String的列，使用 LowCardinality 特性，例如国家(country)，操作系统(os)皆可用LowCardinality。查询效益提高可以40~50%，具体参考 https://altinity.com/blog/2019/3/27/low-cardinality 。 7，为了使复杂查询尽量本地完成，提前减小数据量和网络传输，加快查询速度，创建分布式表时，尽量按照主键hash分shard。例如欲加快select count(distinct uid) from table_all group by country, os的查询速度. 创建分布式表table_all时，shard key为cityHash64(country, os)，hash函数参考 https://clickhouse.tech/docs/en/sql-reference/functions/hash-functions/ 。 8，计算不同维度组合的指标值时，用with rollup或with cube替代union all子句。 9，建表时，请遵守命名规范：分布式表名 = 本地表名 + 后缀"_all"。 select请直接操作分布式表。 10，官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID） 11，稀疏索引不同于mysql的B+树，不存在最左的原则，所以在ck查询的时候，where条件中，基数较大的列（即区分度较高的列）在前，基数较小的列（区分度较低的列）在后。 12，多表Join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较 13，多维分析, 查询列不宜过多, 过滤条件带上分区筛选 (select dim1, dim2, agg1(xxx), agg2(xxx) from table where xxxx group by dim1, dim2 ) 14，禁止SELECT *, 不能拉取原始数据!!!! (clickhouse不是数据仓库, 纯粹是拉原始表数据的查询应该禁止,如 select a, b, c, f, e, country from xxx ) 分区键和排序键理论上不能修改，在建表建库的时候尽量考虑清楚 。 0，事实表必须分区，分区粒度根据业务特点决定，不宜过粗或过细。我们当前都是按天分区，按小时、周、月分区也比较常见（系统表中的query_log、trace_log表默认就是按月分区的）。 1，分区键能过滤大量数据，分区键建议使用toYYYYMMDD()按天分区，如果数据量很少，100w左右，建议使用toYYYYMM()按月分区，过多的分区会占用大量的资源，会对集群的稳定性造成很大的影响。 2，分区键必须使用date和datetime字段，避免string类型的分区键 3，每个sql必须要用分区键，否则会导致大量的数据被读取，到了集群的内存限制直接拒绝 4，排序键也是一个非常重要的过滤条件，考虑到ck是OLAP 库，排序键默认也是ck的主键，loap库建议分区键要使用基数比较少的字段，比如country就比timestramp要好。 5，不要使用过长的分区键，主键 。 6，CK的索引非MySQL的B树索引，而是类似Kafka log风格的稀疏索引，故不用考虑最左原则，但是建议基数较大的列（即区分度较高的列）在前，基数较小的列（区分度较低的列）在后。另外，基数特别大的列（如订单ID等）不建议直接用作索引。 分区数过多会导致一些致命的集群问题。 不建议分区数粒度过细，不建议分区数过多 ，经验来看，10亿数据建议1-10个分区差不多了，当然需要参考你的硬件资源如何。 1，select 查询性能降低，分区数过多会导致打开大量文件句柄，影响集群。 2，分区数过多会导致集群重启变慢，zk压力变大，insert变慢等问题。 https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key/

是。where别名是clickhouse。ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。它是由俄罗斯公司Yandex于2016年6月15日开源的一个项目，简称为CH。

clickhouse-copier是官方出的用来同步数据的工具，依赖zk来满足跨集群同步数据的场景。 假设我们要从cluster1[IP1,IP2,IP3]集群中拷贝table_dis到cluster2[IP4,IP5]中。table_dis是distributed table，对应的mergetree表为table_local （1）zk.xml 创建zk.xml文件，用于copy时候使用。 （2）schema.xml 用括号标注的变量需要根据实际情况更换。 在IP1中执行： clickhouse-copier copier --daemon --config zk.xml --task-path /[ZK-CLICKHOUSE-PATH/NODE] --base-dir /[PATH] --task-upload-force true --task-file schema.xml 测试在3台的集群1000w条的数据，写入2台的集群中，耗时在30s

NineData GUI 是一款基于 ClickHouse 数据库的 GUI 工具，可以帮助用户更方便地进行数据管理和查询操作。下面是使用 NineData GUI 创建 ClickHouse 数据表的步骤：打开 NineData GUI，并连接到 ClickHouse 数据库。在导航栏中选择 “Table Editor”（数据表编辑器）选项卡。点击 “Create Table” 按钮创建新的数据表。在弹出的窗口中，输入数据表的名称和字段信息。例如：CREATE TABLE IF NOT EXISTS test_table (id UInt32,name String,age UInt8) ENGINE = MergeTree() PRIMARY KEY id在这个例子中，我们创建了一个名为 test_table 的数据表，包含三个字段：id、name 和 age。其中，id 的数据类型是 UInt32，name 的数据类型是 String，age 的数据类型是 UInt8。我们还指定了 MergeTree 引擎和 id 字段为主键。点击 “Create Table” 按钮，保存新的数据表。通过以上步骤，您就可以使用 NineData GUI 创建 ClickHouse 数据表了。请注意，NineData GUI 还支持其他的数据表管理和查询操作，可以根据您的需要进行使用。

会使查询变慢。根据查询相关公开信息显示，clickhouse使用虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢，可以关闭虚拟内存。内存是计算机的重要部件，也称内存储器和主存储器，它用于暂时存放CPU中的运算数据，以及与硬盘等外部存储器交换的数据。

相信用过Clickhouse的MergeTree引擎的同学都有听说过稀疏索引以及设置过"8192"这个参数，但是官网的案例说明比较晦涩，我一开始也是理解得云里雾里。后面是看到Alexey Milovidov写的一篇介绍，才算是理解了其实的奥秘。把我所了解到的分享给大家，希望对大家也有帮助。 从官网的Demo开始。官网给的介绍案例是以(CounterID、Date)这2个键来建立索引，可以看到一对的(CounterID、Date)间隔地生成了一个Marks，例如(a,1),(a,2)；根据Marks又生成了相应的Marks numbers。那么"8192"这个index_granularity参数又是用来做什么的呢？大家可以看下(a,1),(a,2)这2个索引之间，间隔了好几个数据，即： （1）index_granularity这个参数规定了数据按照索引规定排序以后，间隔多少行会建立一个索引的Marks，即索引值 （2）稀疏索引的意义即是Clickhouse不对所以的列都建立索引（相比较Mysql的B树索引会为每行都建立），而是间隔index_granularity列才建立一个。 （3）Marks与Marks number均被保存在内存中，利于查询的时候快速检索。 clickhouse针对每一列都进行了分别存储，并生成了.bin以及.mrk两个文件。bin文件存储了真正的列的值（内部又设计列的压缩），mrk文件记录了Mark numbers对应这个列的offset。以官网例子为例，Marks numbers为3对应了CounterID取值为[b,c,d,e]4个字符，查询命中Marks numbers=3时，通过CounterID的mrk文件就可以知道这4个字符在CounterID的bin文件中存储的offset，提高查询性能。 （1）虽然是稀疏索引，但是如果索引中的列过多，则根据索引来划分数据会更稀疏，建立的索引也需要更多，影响写入性能，也会增加内存的使用 （2）相比普通的B树索引，稀疏索引需要的内存更少，但是可能导致需要扫描的行数比实际的多（以官网demo为例，例如查询(e,1)命中第3个索引，则需要扫描{index_granularity}行的数据，但是其实内部(e,1)的数据只占了少部分，带来了无效扫描） （3）官网推荐是不需要去改"8192"这个值。我个人认为是除非你要做为索引的这个列的值分布非常非常集中，可能几w行数据才可能变化一个取值，否则无需去做调大去建立更稀疏的索引，不过如果这个列这个集中的分布，也不大适合作为索引；如果要调小这个值，是会带来索引列增加，但是同样也会带来内存使用增加、写入性能受影响。 （4）有2个列组合做组合索引，一个值比较稀疏、一个值比较集中，要选稀疏的值放在第一位。只能选择一个列做单索引，如果有2个备选的值，要选比较稀疏的。 ClickHouse Primary Keys

1准备测试用虚拟机clickhouse安装只有一个必须条件：Linux，x86_64和SSE 4.2。可以使用下面这个指令看下支不支持你的系统grep -q sse4_2 / proc / cpuinfo &&回显“支持SSE 4.2” || 回显“不支持SSE 4.2”下面采用的是ubuntu18.04系统，因为官方中默认是ubuntu，由于是测试所以就没有使用centos。首先准备了3台虚拟机进行测试（实际上clickhouse没有要求用几台，如果你是搭着玩玩，甚至都可以用一台也可以工作或使用docker，我这里主要是为了以后要做演示做的）；配置是CPU 1CORE，RAM 1G-----------------------------vms001 192.168.56.11vms002 192.168.56.12vms003 192.168.56.13------------------------------clickhouse安装及配置3台虚拟机ip信息2下载并安装clickhouse服务器端和客户端安装clickhouse有多种方法：如果您的服务器连接不上外网，那么会比较麻烦，需要自己手工去官网下载安装包（http://repo.yandex.ru/clickhouse/deb/stable/main/）同样针对centos也有相应的这些包的只是叫rpm包（https://packagecloud.io/Altinity/clickhouse）。一共下载下面几个包：＃基础包clickhouse-common-static_18.14.17_amd64.deb clickhouse-server-base_18.14.17_amd64.deb clickhouse-server-common_18.14.17_all.deb clickhouse-compressor_1.1.54318_amd64.deb ＃密码clickhouse-client_18.14.17_all .deb clickhouse-server_18.14.17_all.deb ＃选装包（都是测试调试用的）clickhouse-test_18.14.17_all.deb clickhouse-common-static-dbg_18.14.17_amd64.deb＃可选项sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4 ＃获取并设置安装包源echo“ deb http://repo.yandex.ru/clickhouse/deb/stable/ main /” | sudo tee /etc/apt/sources.list.d/clickhouse.list＃更新包sudo apt-get update ＃安装clickhouse-server clickhouse-client sudo apt-get install -y clickhouse-server clickhouse-client只要跑完上面的命令，这样clickhouse就算安装好了。clickhouse安装及配置完整的安装过程3 clickhouse配置文件说明在上面的安装完后，接下来就可以开始启动服务了。＃启动clickhouse-serversudo服务clickhouse-服务器启动在启动之后通过ps -ef | grep clickhouse可以发现他就使用了一个配置文件clickhouse安装及配置Clickhouse服务clickhouse-server使用的入口配置文件只有一个config.xml下面我们进入配置文件中心看下都有一些文件（/ etc / clickhouse-server /）：config-preprocessed.xml（这个是动态生成的，这可以不用重启服务也能实时生效配置文件）config.xml（主要的配置文件控制未来的很多子配置文件，如users.xml，metrika.xml）users-preprocessed.xml（这个是动态生成的，这可以不用重启服务也能实时实现配置文件）users.xml（主要是配置用户信息的）metrika.xml（这个文件是后来手工创建的，主要是将include_from的例程的配置文件分离到这里来，提高config.xml文件的扭曲性，我采用调整路径到当前/ etc / clickhouse-server /下方便些）4配置文件修改1.为了配置文件统一管理，需要添加如下副本（从到统一的配置文件中调整include_，因为替换的路径是/etc/metrika.xml）<include_from> /etc/clickhouse-server/metrica.xml </ include_from>2.创建metrica.xml，将合并信息调整到metrica.xml文件中，而原来的config.xml中的积累信息需要做删除与调整。clickhouse安装及配置调整config.xml的体现信息在新建的metrica.xml中需要配置相应的充分信息，由于我使用是3台服务器，所以我需要配置3个副本，mycluster是重新命名，下面有3个shard，没有副本。<yandex> <clickhouse_remote_servers> <mycluster> <shard> <replica> <host> 192.168.56.11 </ host> <port> 9000 </ port> </ replica> </ shard> <shard> <replica> <host> 192.168.56.12 </ host> <port> 9000 </ port> </ replica> </ shard> <shard> <replica> <host> 192.168.56.13 </ host> <port> 9000 </ port> </ replica > </ shard> </ mycluster> </ clickhouse_remote_servers> </ yandex>这样就配置完毕了。5启动服务和使用客户端工具连接clickhouse在3台服务器中执行启动服务：服务Clickhouse-服务器启动在任何一台服务器上执行客户端工具命令：clickhouse-clientroot @ vms001：u301c＃clickhouse-client ClickHouse客户端版本18.14.17。连接到本地主机：9000。已连接到ClickHouse服务器版本18.14.17修订版54409。vms001 :)显示数据库；SHOW DATABASES ┌─name────┐ ││默认│系统│ └─────────┘ 在一套2行。耗时：0.002秒。vms001 :)使用系统；使用系统确定。设置0行。耗时：0.001秒。vms001 :)显示表格；SHOW TABLES ┌─name───────────────────────────┐ │aggregate_function_combinators│ ││asynchronous_metrics │build_options│ ││群││排序│列││data_type_families│ │数据库│ │字典│ │活动│ │格式│ │功能│ │graphite_retentions│ │宏│ │merge_tree_settings│ │合并│ │指标│ │型号│ │突变│ │号│ │numbers_mt│ │一个│ │件│ │parts_columns│ │过程│ │副本│ │replication_queue│ │设置│ │table_engines│ │table_functions│ │表│ └──────────────────────── ────────┘一组31行。耗时：0.004秒。vms001 :)在执行查看生成的表select * from system.clusters就可以看到看到的信息了，系统信息全部在表system中；clickhouse安装及配置发挥上的3个例程整个clickhouse就这么简单的安装完成了，只是没有做副本以及高可用。

数据丢失。clickhouse插入分布式表没反应是在ES中比较常见的写Rejected导致数据丢失、写入延迟等问题，在ClickHouse中不容易发生。查询速度快，官方宣称数据在pagecache中，单服务器查询速率大约在2-30GB/s。

droppartitionclickhouse是异步么是异步的。它是计算机程序中的一个命令。据相关资料查询显示该命令是异步执行的，可以通过查看表system.mutations来查看命令的是否执行。clickhouse默认是不支持实时删除表中的数据，数据的删除通常是异步进行。

原理是：既支持分区（纵向扩展，利用多线程原理），也支持分片（横向扩展，利用分布式原理）。

ClickHouse不要求主键唯一，所以您可以插入多条具有相同主键的行，确保去重成功。每次批量写入，一定要做一批去重。去重语句如下：optimizetablemytableNamefinal。但是查询可以做到去重，达到目的。

string str="2010-04-12 16:00:00"； DateTime dt=new DateTime(); if(DateTime.TryParse(str,out dt)) { //将dt作为有效日期进行操作 } else { //错误提示 }

你好请问是问clickhouse不做raid吗？clickhouse不做raid。因为是clickhouse从19.15开始，MergeTree实现了自定义存储策略的功能是JBOD策略：这种策略适合服务器挂多磁盘但没做raid的场景。

pip install clickhouse pip install clickhouse_driver from clickhouse_driver import Client clickhouse_user = "name" clickhouse_pwd = "pass" clickhouse_host_sq = "ip" clickhouse_database = "db" begin_time="2019-05-06" end_time="2019-05-12" client = Client(host=clickhouse_host_sq,user=clickhouse_user , database=clickhouse_database, password=clickhouse_pwd) api_interface_sql = "select accountName,count(*) as count,sum(backendTime) as sum from logs " "where date>="{}" and date<="{}" and appName = "service_si_new" and backendName != "" group by accountName order by count desc limit 0,10" .format(begin_time,end_time) try: a=client.execute(api_interface_sql) print(a) except Exception as e: print(e)

GitHub地址 GitHub DBM是ClickHouse可视化数据工具。它基于ClickHouse原生Http请求构建,支持大量ClickHouse工作,主要支持以下功能点: 我们看一下它的全景! 编辑器配置

对表的操作的完整文档，请参看 ClickHouse 官方文档： https://clickhouse.com/docs/en/ 。 从集群中同步地删除表 这样能避免频繁重建表时的「Table columns structure in ZooKeeper is different from local table structure」错误。 清除表中所有数据

这两个单词的话，你可以在英语词典上面看一下想去的。

Date类型转String 与 String转Date类型，这个类型在jsp/servlet中要手动转换，而在struts2 中会自动转换SimpleDateFormat 是一个以与语言环境有关的方式来格式化和解析日期的具体类。它允许进行格式化（日期 -> 文本）、解析（文本 -> 日期）和规范化http://www.cnblogs.com/android-html5/archive/2012/05/12/2533652.html详细可以参考这个，有图解教程，希望可以帮到你

具体操作步骤：1、准备Spark程序目录结构。2、编辑build.sbt配置文件添加依赖。3、创建WriteToCk.scala数据写入程序文件。4、编译打包。5、运行。参数说明：your-user-name：目标ClickHouse集群中创建的数据库账号名。your-pasword：数据库账号名对应的密码。your-url：目标ClickHouse集群地址。/your/path/to/test/data/a.txt：要导入的数据文件的路径，包含文件地址和文件名。说明文件中的数据及schema，需要与ClickHouse中目标表的结构保持一致。your-table-name：ClickHouse集群中的目标表名称。

可以将clickhouse改为Nullable。可以将非空类型改成Nullable，String，但是要注意Nullable字段不允许用于orderby。clickhouse启动的时候总是无法绑定端口才会报错canotunlinkfile，只要修改成Nullable即可。

系统bug，网络问题。1、系统bug是clickhouse软件系统出现了问题导致执行doinst.sh报错非法指令，等待官方修复即可。2、网络问题是自身设备连接的网络出现较大波动，导致clickhouse软件执行doinst.sh报错非法指令，更换网络重新打开即可。

在执行一个较为复杂的SQL聚合的时候，报错了： 【报错】 DB::Exception: Allocator: Cannot mmap 64.00 MiB., errno: 12, strerror: Cannot allocate memory. 可见是内存不够了（CK虽然是分布式存储但是集中计算） 一个办法是修改SQL，比如说用临时表之类的，但是那多麻烦啊。我决定先用swap内存试试。 我是跑完了SQL以后查看的，所以used是474 . 不是0。 嗯，SQL正常执行。 还有一点需要指出，虽然CK是分布式存储，但是在执行聚合运算的时候，仍然是在单机上，所以会比较消耗内存。 如果想增加空间，先关闭： sudo swapoff /swapfile 后续步骤跟之前一样即可。

这个是ClickHouse保护大表被误删的动作，有两个方法可以解除这个限制。 意思是删除大于50G的表都会提示无法删除，设置为0的就不会报告警提示。 sudo touch /data/clickhouse/flags/force_drop_table && sudo chmod 666 /data/clickhouse/flags/force_drop_table sudo -u clickhouse touch /var/lib/clickhouse/flags/force_restore_data CK会从另外一个备份中恢复数据。这里是CK自带的故障恢复机制，前提是使用复制表（Replicated开头），本质是告诉CK，强制重建数据。 问题分析： 启动时，检查本地文件系统中的数据集是否与预期的数据集（ZooKeeper中信息）一致。如果存在轻微的不一致，系统会通过与副本同步数据来解决，如果系统检测到损坏的数据片段（如文件大小错误）或无法识别的片段（写入文件系统但未记录在ZooKeeper中的部分），则会把它们移动到 ‘detached" 子目录（相当于逻辑删除），然后再从其他备份中去恢复这个数据片段。 但是注意这里是有一个安全机制的，即CK判断你损坏的片段大于一定的值（max_suspicious_broken_parts，对应源码图二中的逻辑），即“本地数据集与预期数据的差异太大”，CK将会拒绝帮你自动修复，并抛出异常、阻塞启动，这个时候你就必须手动执行恢复。 通过查询配置得到，max_suspicious_broken_parts参数的默认值是10。

目前Clickhouse的备份方式有以下几种： clickhouse中，数据文件大小为 900M，实际导出会远大于900M，语句 20G左右【测试时没执行完成】 需要先建好表，因此最好备份下metadata数据 语法： 该操作为指定分区创建一个本地备份。 如果 PARTITION 语句省略，该操作会一次性为所有分区创建备份。整个备份过程不需要停止服务 默认位置：/var/lib/clickhouse/shadow/,若没执行过则还不存在此文件,会生成在设置的数据路径下 查看备份 因为 /shadow/ 目录下次备份时候需要清空，因此将备份迁移到指定路径 vi /etc/clickhouse-backup/config.yml 备份语法： clickhouse-backup create [-t, --tables=<db>.<table>] <backup_name> 恢复语法： clickhouse-backup restore 备份名称 当前测试恢复数据的版本是 20.5.4.40，备份是直接存在 metadata 信息的 当前备份 由于clickhouse-backup 当前版本不会备份metadata,因此自己复制一份metadata数据

数据存储使用clickhouse在批量插入的时候报错，报错提示信息如上所示，原因是： 插入String类型的列中包含了汉字，clickhouse对于汉字的存储有问题，将汉字在存储时转换为unicode就可以了，网上查不到，方便大家

首先，您需要添加官方存储库： 然后运行命令安装： 日志文件将输出在/var/log/clickhouse-server/文件夹。 如果服务器没有启动，检查/etc/clickhouse-server/config.xml中的配置。 更改目录/etc/clickhouse-server： 更改目录/var/log/clickhouse-server： 这个是数据存储的位置,再config配置文件中,该路径也需要设置属主 修改时区为 : Asia/Shanghai 对外开放连接：取消<listen_host>::</listen_host> 注释 后台启动 ClickHouse支持访问限制设置。它们位于users.xml文件(与config.xml同级目录)。 默认情况下，允许default用户从任何地方访问，不需要密码

如果将ClickHouse作为Log或Metrics这种具有明显时序特征数据的存储和分析引擎，那就需要考虑这些数据的生命周期管理，即设置数据的老化机制，如是否需要根据时间划分数据存储等级、设定数据的保留时长等。 ClickHouse支持从分区、列、表等粒度对数据进行管理，如分区数据的迁移、删除，列、表的TTL设置。 热数据一般为最近几天或几周的数据，访问频率最高，通常采用SSD作为存储介质，达到存储时长后，会自动迁移至温数据区；而温数据一般为数月之内的数据，访问频次相对较低，通常采用普通机械盘作为存储介质。 对于Log或Metrics类型数据使用时间字段作为分区键和排序键，ClickHouse虽然没有提供基于时间的数据自动迁移（目前只提供基于分区大小的数据自动迁移），但可以利用其卷划分机制。 磁盘配置参数（disks标签） 策略配置参数（policies标签） 目前只能通过ALTER查询语句移动分区： 1）按照上述方式启动ClickHouse实例，并配置存储策略 查看磁盘配置： 查看策略配置： 2）创建一张MergeTree表用于测试 注意：需指定storage_policy，否则数据会写入默认存储路径中 3）写入测试数据 写入第一批数据，会创建一个分区目录： 写入第二批数据，会创建一个新的分区目录： 如果触发合并操作，会生成一个新分区目录： 注意：由多个disk组成的volume，每当生成一个新数据分区时，会依照disk定义的顺序，依次轮询写入 4）迁移数据分区到温数据区 注意：合并后尚未被清理的数据分区无法移动 可以通过物化视图同步MergeTree表中的数据到SummingMergeTree或AggregatingMergeTree表引擎中，利用既定的聚合条件得到趋势数据并存储。 注意：此处可以指定partition_id进行数据分区删除，如果partition_id是all，则无法直接删除。 在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除该列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。 1）列级别TTL 2）表级别TTL 注意：ClickHouse没有提供删除TTL声明的方法，但提供了控制全局TTL合并任务的启停方法：

是异步的。clickhousedroppartition是计算机程序中的一个命令。据相关资料查询显示该命令是异步执行的，可以通过查看表system.mutations来查看命令的是否执行。clickhouse默认是不支持实时删除表中的数据，数据的删除通常是异步进行。

部署为3个节点的集群，数据无副本。单机则不需要配置metrika.xml文件即可。 主要配置服务端口、ip、文件存储目录，系统配置、zk配置等参数。本文不涉及zk配置。并且开启query_log，方便后期做监控。 文件中注释很详细，根据需要配置即可。 集群配置、压缩算法配置。本示例集群名为default_cluster，可定义多个。名称自定义，创建分布式表时指定对应的集群名称实现灵活使用数据。 结构资料： 密码可以以明文或SHA256（十六进制格式）指定 不建议使用明文； 结果的第一行是密码。 第二行是相应的SHA256哈希 官方配置文档 介绍很详细，在实际使中还需要自己优化。自己就踩过不少坑，以后有机会和大家分享。load_balancing指定用于分布式查询处理的副本选择算法

原因在于 ： 将 clickhouse-jdbc-0.1.54.jar 放到了 jre/lib/ext 目录下，但是其相关的依赖没有放进去

副本文件损坏。1、ClickHouse是俄罗斯的Yandex于2016年开源的用于在线分析处理查询(OLAP:OnlineAnalyticalProcessing)MPP架构的列式存储数据库。2、clickhouse软件的副本文件发生损坏，在配置完后导致系统本身的文件也损坏，需要用户从新下载软件和副本再从新配置即可。

问题要从面试或者大数据场景下最常见的一个算法说起，问题是这样的，假如有几十亿个unsigned int类型的数据，要求去重或者计算总共有多少不重复的数据？最简单的办法就是直接利用一个HashMap，进行去重。但是这里面有个内存使用量的问题，几十亿个元素，即使不考虑HashMap本身实现所用到的数据结果，单单key本身，假如每个unsigned int占用4个字节，简单算一下的话，这里都需要几十GB的内存占用，因此，这里就引出了BItMap。 BItMap的思想非常简单，就是用一个bit表示一个二元的状态，比如有或者没有，存在或者不存在，用bit本身的位置信息，对应不同的数据。比如针对上面的问题，我们可以开辟一个2^32 bit的内存空间，每一个bit存储一个unsigned int类型的数据，有就是1，没有就是0，总共需要存储unsigned int类型的最大范围个数据，也就是2^32 个数据，这个2^32其实就是所谓的基数。如下图所示： 假如存在数字8，那就把对应的第8位的值赋为1。上图插入的数据为1、3、7、8。接着依次把所有的数据遍历然后更新这个BitMap。这样我们就可以得到最终结果。 假如上面的问题变成了对几十亿个URL做判断，那应该怎么去做呢？URL没有办法和BitMap的位置关系对应上，所以，我们需要加一层哈希，把每个URL经过哈希运算得到一个整数，然后对应上BitMap。如下图所示： 但是有哈希，肯定会存在碰撞，如果BitMap基数（也就是长度）比较小，那碰撞的概率就大，如果基数比较大，那占用的空间又会比较多。Bloom Filter的思想就是引入多个哈希函数来解决冲突的问题。也就是说对每个URL，经过多个哈希函数的运算，得到多个值，每个数值对应的BitMap的对应的位置都赋值为1。这个两个URL经过多个哈希函数结果还是一样的概率就大大降低。 但是由于依然存在冲突的可能性（其实冲突就是来源于我们BitMap的长度小于了数据量的基数，这也就是牺牲了准确性换来了空间使用的减少），所以Bloom Filter 存在假阳性的概率，不适用于任何要求 100% 准确率的场景，也就是说Bloom Filter 只能用来判无，不能用来判有。比如一个URL经过多次哈希运算之后，发现对应的BitMap的位置都已经是1了，那也不能说明，这个URL之前存在过了，也有可能是哈希冲突的结果。但是一个URL经过多次哈希运算之后，发现对应的BitMap的位置不是都是1，那当前URL之前一定是没有存在过的。 可以看到，Bloom Filter 引入多次哈希，在查询效率和插入效率不变的情况下，用较少空间的BitMap解决大数据量的判断问题。 大部分情况下仅仅做有无的判断是不能满足使用需求的，我们还是需要真正意义上的BitMap（可以方便的用来做交并等计算），但是最好可以在基数比较大的时候，依然可以占用相对比较小的空间。这就是RoaringBitMap所要实现的。 简单来说RoaringBitMap是BitMap的一种带索引的复杂BitMap数据结构。以32位的RoaringBitMap为例，首先划分2^16 个空间（Container），每个Container内部都是一个大小为2^16 bit的BitMap，总的内存使用量还是2^32 = 512Mb。这样的话和普通的BitMap是没有区别的，而RoaringBitMap的创新之处在于每个Container内的BitMap是在没有使用到的情况下是可以不分配内存空间的。这样可以大大减小内存的使用量。 （这个图片是Roaring Bitmaps: Implementation of an Optimized Software Library 论文原图） 要将一个4个字节的数据插入RoaringBitMap，首先要用数据的高16位，找到对应的Container，然后用数据的低16在Container中插入。 在每个Container内部，RoaringBitMap不是简单的用BitMap来进行数据的存储，而是把Container的类型划分为几种，不同的Container用来存储不同情况的数据。 当2个字节（4个字节的原数据，低16位用来插入具体的Container中）的数据，总的个数小于4096个的时候，当前Container使用 array Container。为什么是4096个呢？4096*2B=8Kb，而一个Container如果是bitmap的结构的话，最多也就是2^16bit=8Kb的空间。所以这里当数据个数小于4096使用array Container会更节省空间。当然这里名字为array Container，实际上是链表结构，不需要最开始就初始化4096个short int的数组。 当array Container存储的数到4096个的时候（也就是使用内存到8Kb的时候），array Container会转换为bitmap container，bitmap container就是一个2^16 bit普通的bitmap，可以存储2^16 = 65536个数据。这个8Kb还有一个好处，是可以放到L1 Cache中，加快计算。 这个严格的说，只是一种数据压缩存储方法的实现。其压缩原理是对于连续的数字只记录初始数字以及连续的长度，比如有一串数字 12,13,14,15,16 那么经过压缩后便只剩下12,5。从压缩原理我们也可以看出，这种算法对于数据的紧凑程度非常敏感，连续程度越高压缩率也越高。当然也可以实现其他的压缩方法。 RoaringBitMap其核心就在于加了一层索引，利用复杂的数据结构换取了空间上的效率。需要注意的是这里并没有增加计算的复杂度，其出色的数据结构让其在做交并计算的时候性能也毫不逊色。 ClickHouse中有bloom_filter类型的Skipping indexs，可以方便的用来过滤数据。 ClickHouse实现了大量的BitMap的函数，用来操作BitMap。ClickHouse中的BitMap在32位的时候用的是Set实现的，大于32位的时候也是使用RoaringBitMap实现的。我们这里不看具体的函数，我们来看一个典型的使用场景。 最常见的一个场景是根据标签来进行用户的圈选。常见的解决办法是有一张用户标签表，比如 要查询标签tag1="xx"和tag2="xx"的用户需要执行SQL： 但是由于不可能对每个tag列构建一级索引，所以这条SQL执行的效率并不高。可选的一种方式是先构建关于标签的BitMap数据结果，然后进行查询： （1） 创建tag的bitmap表： （2）写入数据 （3）查询 如果有多张tag表，进行交并计算（要比普通的用户表进行JOIN或者IN计算要高效很多）：

常用的clickhouse时间函数 获取未来时间的函数 获取过去时间： 计算两个不同时间在不同时间单位下的差值： 字符串转日期 提取单独的年月日等等 指定维度的开始 格式化

数据分析需要学习以下几点：一、统计学。二、编程能力。三、数据库。四、数据仓库。五、数据分析方法。六、数据分析工具。想要成为数据分析师应该重点学习以下两点：1.python、SQL、R语言这些都是最基础的工具，python都是最好的数据入门语言，而R语言倾向于统计分析、绘图等，SQL是数据库。既然是数据分析，平时更多的时间就是与数据分析打交道，数据采集、数据清洗、数据可视化等一系列数据分析工作都需要上面的工具来完成。2.业务能力数据分析师存在的意义就是通过数据分析来帮助企业实现业务增长，所以业务能力也是必须。企业的产品、用户、所处的市场环境以及企业的员工等都是必须要掌握的内容，通过这些内容建立帮助企业建立具体的业务指标、辅助企业进行运营决策等。当然这些都是数据分析师最基本也是各位想转行的小伙伴需要重点学习的内容，以后想要有更好的发展，还需要学习更多的技能，例如企业管理，人工智能等。关于数据分析师的学习可以到CDA数据分析认证中心看看。全球CDA持证者秉承着先进商业数据分析的新理念，遵循着《CDA职业道德和行为准则》新规范，发挥着自身数据专业能力，推动科技创新进步，助力经济持续发展。

配置文件：user.xml 核心配置3部分： - profile配置，最大内存、负载方式等（没有特别关注，可见官方文档） - 配额设置，单个用户最大能用的资源多少（没有特别关注，可见官方文档） - 用户设置，包括用户名和密码 密码有2种，一种是明文，一种是写sha256sum的Hash值 官方不建议直接写明文密码 我们的config文件: 下图定义了两组设置，名字不同 第二组增加了readonly选项下图定义了2个用户，为了方便测试，用了同一个用户名 ck用户是read模式如何生成密码权限验证 默认用户登陆（可以不用指定用户名） CH用户登陆

ClickHouse作为一个来自俄罗斯的开源大数据产品非常的有名，去年9月份，ClickHouse团队独立，成立了自己的公司。ClickHouse是近年来备受关注的开源列式数据库，主要用于数据分析（OLAP）领域，目前国内社区火热，各个大厂纷纷跟进大规模使用。国内云计算的领导厂商阿里云率先推出了自己的ClickHouse托管产品，产品首页地址为云数据库ClickHouse。

本文基于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的朋友对它的索引有个清晰的认识。

删除字典表的时候，使用的语句应该是： DETACH Dictionary dmp_log.ods_product; 由于同事错误了使用了命令，导致重新创建字典表不成功，错误的命令如下（当成表来操作了）： DETACH TABLE dmp_log.ods_product; 重新创建字典表报如下错误： SQL 错误 [387]: ClickHouse exception, code: 387, host: 172.30.125.92, port: 8124; Code: 387, e.displayText() = DB::Exception: Dictionary dmp_log.ods_product already exists. (version 20.8.6.6 (official build)) 由于把字典表当成表卸载了。然后再加载的时候，又报语法错误。 ATTACH TABLE dmp_log.ods_product; 解决的方法： 总体思路：既然当成表来DETACH了，那么再当成表来ATTACH即可。 1、ClickHouse metadata的目录 /data/clickhouse/metadata/dmp_log 找到误操作的表信息ods_product。 2、由于ods_product是字典表，语法与建表语句不一致。 所以需要将ods_product文件内容，修改为普通的建表语句，让ClickHouse能加载进去。 3、重启ClickHouse 3、执行删表语句 drop table ods.ods_product 4、新建字典表 CREATE DICTIONARY dmp_log.ods_product...

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

从无符号整型（UInt8、UInt32、UInt64等）array构造bitmap 将bitmap转成整型array 返回bitmap中，range_start到range_end区间内（不包含renge_end）的子集bitmap对象 返回bitmap中，从range_start开始的cardinality_limit个元素组成的子集bitmap对象 判断指定bitmap中是否存在e元素 bitmap1中是否包含bitmap2中的元素，只要有一个相同的元素，就返回1，否则返回0. bitmap1中是否全部包含bitmap2中的元素，全部包含就返回1，否则返回0. 返回bitmap的基数 将bitmap中的元素进行转换，将存在于from_array的元素，一次转换成to_array的对应元素。 上面的例子中，依次将bitmap中，5转成2，999（不存在）转成888，2转成20。因为就bitmap中不存在999，所以新bitmap没有888；因为将5转成2，又将2转成20，所以新bitmap中去掉了5和2元素，新加了20元素 求两个bitmap的交集 求两个bitmap的并集 求两个bitmap的异或 求bitmap1与bitmap2的与非