clickhouse知识点

基本介绍：

概念：

一个快速开源的OLAP数据库管理系统，它是面向列的，允许使用SQL查询实时生成分析报告。

背景知识：

历史olap：

定时（通常是T+1 或者 T+H）将 HBase 数据写成静态的文件（如：Parquet）导入到 OLAP 引擎（如：HDFS，比较常见的是Impala操作Hive）。
这一架构能满足既需要随机读写，又可以支持 OLAP 分析的场景
缺点：
    架构复杂。从架构上看，数据在 HBase、消息队列、HDFS 间流转，涉及环节太多，运维成本很高。并且每个环节需要保证高可用，都需要维护多个副本，存储空间也有一定的浪费。
        最后数据在多个系统上，对数据安全策略、监控等都提出了挑战。
    时效性低。数据从 HBase 导出成静态文件是周期性的，一般这个周期是一天（或一小时），在时效性上不是很高。
    难以应对后续的更新。真实场景中，总会有数据是「延迟」到达的。如果这些数据之前已经从 HBase 导出到HDFS，新到的变更数据就难以处理了，一个方案是把原有数据应用上新的变更后重写一遍，但这代价又很高。

OLAP分析领域有两个典型的方向：

一是 ROLAP，通过列存、索引等各类技术手段，提升查询时性能。
另一是 MOLAP，通过预计算提前生成聚合后的结果数据，降低查询读取的数据量，属于计算换性能方式。
前者更为灵活，但需要的技术栈相对复杂；后者实现相对简单，但要达到的极致性能，需要生成所有常见查询对应的物化视图，消耗大量计算、存储资源。
ClickHouse 一定程度上做了两者的结合，在尽可能采用 ROLAP 方式提高性能的同时，支持一定的 MOLAP 能力，具体实现方式为 MergeTree 系列表引擎和 MATERIALIZED VIEW。

概念：

ClickHouse集群（Cluster）
    在物理构成上，ClickHouse集群是由多个ClickHouse server实例组成的分布式数据库。
    这些ClickHouse server根据购买规格的不同而可能包含1个或多个副本（Replica）、1个或多个分片（Shard）。
    在逻辑构成上，一个ClickHouse集群可以包含多个数据库（Database）对象。
分片（Shard）
    在超大规模海量数据处理场景下，单台服务器的存储、计算资源会成为瓶颈。
    为了进一步提高效率，云数据库ClickHouse将海量数据分散存储到多台服务器上，每台服务器只存储和处理海量数据的一部分，在这种架构下，每台服务器被称为一个分片（Shard）。
副本（Replica）
    为了在异常情况下保证数据的安全性和服务的高可用性，ClickHouse提供了副本机制，将单台服务器的数据冗余存储在2台或多台服务器上。 
表（Table）
    表是数据的组织形式，由多行、多列构成。ClickHouse的表从数据分布上，可以分为本地表、分布式表两种类型。从存储引擎上，可以分为单机表、复制表两种类型。  
本地表（Local Table）
    本地表的数据，只会存储在当前写入的节点上，不会被分散到多台机器上。
分布式表（Distributed Table）
    分布式表是本地表的集合，它将多个本地表抽象为一张统一的表，对外提供写入、查询功能。当写入分布式表时，数据会被自动分发到集合中的各个本地表中；
    当查询分布式表时，集合中的各个本地表都会被分别查询，并且把最终结果汇总后返回。

架构：

Shard：集群内划分为多个分片或分组(Shard 0 … Shard N)，通过 Shard 的线性扩展能力，支持海量数据的分布式存储计算。
Node：每个 Shard 内包含一定数量的节点(Node，即进程)，同一 Shard 内的节点互为副本，保障数据可靠。ClickHouse 中副本数可按需建设，且逻辑上不同 Shard 内的副本数可不同。
ZooKeeper Service：集群所有节点对等，节点间通过 ZooKeeper 服务进行分布式协调。

优点：

1.真正的面向列的DBMS

2.数据压缩

列存储，相同列的数据连续存储，而且排序，压缩比更高。
由于 ClickHouse 采用列存储，相同列的数据连续存储，且底层数据在存储时是经过排序的，这样数据的局部规律性非常强，有利于获得更高的数据压缩比。

3.磁盘存储的数据

4.多核并行处理

5.在多个服务器上分布式处理

6.SQL支持

支持的查询包括GROUP BY, ORDER BY
子查询在FROM，IN，JOIN子句中被支持;
标量子查询支持。
关联子查询不支持。
真是因为ClickHouse提供了标准协议的SQL查询接口，使得现有可视化分析系统能够轻松与他集成对接

7.向量化引擎

相比于传统火山模型中的逐行处理模式，向量化执行引擎采用批量处理模式，可以大幅减少函数调用开销，降低指令、数据的 Cache Miss，提升 CPU 利用效率。

8.实时数据更新

为了快速执行对主键范围的查询，数据使用合并树(MergeTree)进行递增排序。由于这个原因，数据可以不断地添加到表中。添加数据时无锁处理。
缺点：不能update或者delete

9.索引

ClickHouse 的底层数据按建表时指定的 ORDER BY 列进行排序，并按 index_granularity 参数切分成数据块，然后抽取每个数据块的第一行形成一份稀疏的排序索引。
用户在查询时，如果查询条件包含主键列，则可以基于稀疏索引进行快速的裁剪。

10.支持在线查询

作为Web界面的后端。低延迟意味着可以无延迟实时地处理查询。

11.支持近似计算

支持近似查询算法、数据抽样等近似查询方案，加速查询性能。
12.数据复制和对数据完整性的支持。

13.Colocated join

数据打散规则一致的多表进行 Join 时，支持本地化的 Colocated Join，提升查询性能。

14.物化视图(Cube/Rollup)

OLAP 分析领域有两个典型的方向：

- ROLAP，通过列存、索引等各类技术手段，提升查询时性能。
- MOLAP，通过预计算提前生成聚合后的结果数据，降低查询读取的数据量，属于计算换性能方式。
前者更为灵活，但需要的技术栈相对复杂;后者实现相对简单，但要达到的极致性能，需要生成所有常见查询对应的物化视图，消耗大量计算、存储资源。
ClickHouse 一定程度上做了两者的结合，在尽可能采用 ROLAP 方式提高性能的同时，支持一定的 MOLAP 能力，具体实现方式为 MergeTree 系列表引擎和 MATERIALIZED VIEW。

缺点：

1.不支持事务。
2.不支持Update/Delete操作。(现在支持了，这类操作被称之为Mutation查询，是ALTER语句的变种。)
3.分布式管控层弱
    ①分布式表
        ClickHouse 对分布式表的抽象并不完整，在多数分布式系统中，用户仅感知集群和表，对分片和副本的管理透明，而在 ClickHouse 中，用户需要自己去管理分片、副本。
        例如前面介绍的建表过程：用户需要先创建本地表（分片的副本），然后再创建分布式表，并完成分布式表到本地表的映射。
    ②弹性伸缩
        ClickHouse 集群自身虽然可以方便的水平增加节点，但并不支持自动的数据均衡。
        例如，当包含 6 个节点的线上生产集群因存储或计算压力大，需要进行扩容时，我们可以方便的扩容到 10 个节点。
        但是数据并不会自动均衡，需要用户给已有表增加分片或者重新建表，再把写入压力重新在整个集群内打散，而存储压力的均衡则依赖于历史数据过期。
        ClickHouse在弹性伸缩方面的不足，大幅增加了业务在进行水平伸缩时运营压力。
    ③故障恢复
        与弹性伸缩类似，在节点故障的情况下，ClickHouse 并不会利用其它机器补齐缺失的副本数据。需要用户先补齐节点后，然后系统再自动在副本间进行数据同步。
4.计算引擎
    在单表性能方面表现非常出色，但是在复杂场景仍有不足，缺乏成熟的MPP大规模并行处理计算引擎和执行优化器。
    例如：多表关联查询、复杂嵌套子查询等场景下查询性能一般，需要人工优化;缺乏 UDF 等能力，在复杂需求下扩展能力较弱等。
5.实时写入
    采用类 LSM Tree 架构，并且建议用户通过批量方式进行写入，每个批次不少于 1000 行 或 每秒钟不超过一个批次，从而提高集群写入性能。
    但 ClickHouse 并不适合实时写入，原因在于 ClickHouse 并非典型的 LSM Tree 架构，它没有实现 Memory Table 结构，每批次写入直接落盘作为一棵 Tree（如果单批次过大，会拆分为多棵 Tree），每条记录实时写入会导致底层大量的小文件，影响查询性能。
    这使得 ClickHouse 不适合有实时写入需求的业务，通常需要在业务和 ClickHouse 之间引入一层数据缓存层，实现批量写入。
    最新特性：
        比如物化视图也是实时写入，但是背后有预聚合，新版clickhouse有内存缓存区+log文件合并。
6.聚合结果必须小于一台机器的内存，否则失败。
    而hive可以分开聚合，然后写入

clickhouse为啥比hive/spark快：

（物化视图和MergeTree引擎）7*24小时都在后台预聚合.hive/spark计算的时候才申请资源,平时只占一点点;
可以用星型模型缩减数据类型、压缩友好;
计算过程没有hive/spark中的shuffle概念,全是mapAgg;
列存储，仅需读取必要的数据

对比Druid和Apache Kylin：

ClickHouse可以支持从原始数据的直接查询，ClickHouse支持类SQL语言，提供了传统关系型数据的便利。

与MapReduce系统的区别：

在运行简单的 map-reduce 任务时，分布式排序是性能降低的主要原因之一。map结果存到内存后溢出落盘，reduce拉取。
解决方法：
    使用RAM中的hash表进行预聚合，根据索引，对数据根据key进行存储。而这正是clickhouse的做法。（join算法可选）

检索过程：

1.每个索引都有对应的min/max的partition值，存储在内存中。
    当contition带上partition时就可以从这些block列表中找到需要检索的索引，找到对应的数据存储文件夹，命中对应的索引（primary.idx）
2.根据ID字段，把条件转化为[10,100)的条件区间,再把条件区间与这个partition对应的稀疏索引做交集判断。
    如果没有交集则不进行具体数据的检索；
    如果有交集，则把稀疏索引等分8份，再把条件区间与稀疏索引分片做交集判断，直到不能再拆分或者没有交集，则最后剩下的所有条件区间就是我们要检索的block值。
3.通过步骤2我们得到了我们要检索的block值。
    通过上面我们知道存在多个block压缩在同一个压缩数据块的情况并且一个bin文件里面又存在N个压缩数据的情况，所以不能直接通过block的值直接到bin文件中搜寻数据。
    我们通过映射block值到mrk中，通过mrk知道这个block对应到的压缩数据以及在压缩数据块里面的字节偏移量，就得到了我们最后需要读取的数据地址。
4.把bin文件中的数据读取到内存中，找到对应的压缩数据，直接从对应的起始偏移量开始读取数据。

数据存储：

数据以压缩数据为单位，存储在bin文件中。
压缩数据对应的压缩数据块，严格限定按照64K~1M byte的大小来进行存储。
    （1）如果一个block对应的大小小于64K，则需要找下一个block来拼凑，直到拼凑出来的大小大于等于64K。
    （2）如果一个block的大小在64K到1M的范围内，则直接生成1个压缩数据块。
    （3）如果一个block的大小大于了1M，则切割生成多个压缩数据块。
一个part下不同的列分别存储，不同的列存储的行数是一样的。
写入ClickHouse的一个分片，会先写入一个副本的内存中，在内存中按照指定条件排好序，再写入磁盘的一个临时目录。等写入磁盘临时目录完成，将临时目录重命名为最终目录的名字。

安装运维：

安装：

安装4个rpm文件
https://clickhouse.tech/#quick-start通过apt安装

启动：

nohup sudo -u clickhouse clickhouse-server --config-file=/etc/clickhouseserver/config.xml >null 2>&1 &
或者 
service clickhouse-server start

开启shell：

clickhouse-client --password -m          --multiline表示多行，需要手动加分号结尾
密码保存在/etc/clickhouse-server/users.xml，忘记了可以修改该配置即可
    <password>123456</password>
    <password_sha256_hex>967f3bf355dddfabfca1c9f5cab39352b2ec1cd0b05f9e1e6b8f629705fe7d6e</password_sha256_hex>  # 可以通过echo -n 123456 | openssl dgst -sha256
chown clickhouse:clickhouse /etc/clickhouse-server/users.xml
如果密码尝试失败，修改
    /etc/clickhouse-server/users.d/default-password.xml
如果要想置为空：
    <password></password>
    再不行e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855替换掉default-password.xml

集群部署：

配置文件/etc/clickhouse-server/config.xml，增加<perftest_3shards_1replicas>配置；配置zk；配置macros用于分片标识部分（每个节点不同）
    <macros>
        <layer>01</layer>
        <shard>01</shard>           //第二个节点响应更改，select * from system.macros查询具体节点的配置
        <replica>worker1</replica>
    </macros>
    如果配置了密码，那么<replica>也需要配置密码
分发配置文件,注意config.xml文件所有者chown clickhouse:clickhouse /etc/clickhouse-server/config.xml
各个节点启动服务service clickhouse-server restart
select * from system.clusters
连接各节点：
    clickhouse-client -h worker1 -u default --password
然后各节点创建local表，再创建distributed表

数据类型：

整型：

固定长度的整型，包括有符号整型或无符号整型。
int8\Int16\Int32\Int64
UInt8\UInt16\UInt32\UInt64

浮点型：

Float32\Float64
特殊：
    Inf-正无穷 1/0
    -Inf-负无穷 -1/0
    NaN-非数字  0/0
Decimal(P,S)
    P:代表精度，决定总位数（正数部分+小数部分），取值范围0-38
    S:代表规模，决定小数位数，取值范围是0-P
    提供三种简写：Decimal32(S)，Decimal64，Decimal128
    注意：
        superset某个版本不支持decimal，需要修改代码才能支持。
    运算：
        相加、减精度取大
        相乘精度取和
        相除精度取被除数

字符串：

String，字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
FixedString(N)，不够补空字节
UUID，可以通过generateUUIDv4()方法生成，默认不写的话填充为00000000-0000-0000-0000-000000000000

枚举类型：

包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。
Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。
默认select返回string，要想看到数字通过CAST(x, 'Int8')来得到

数组：

格式：

Array(T)：由 T 类型元素组成的数组。
T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在MergeTree 表中存储多维数组。

创建：

1.可以使用array函数来创建数组：array(T)

2.也可以使用方括号：[]，ClickHouse能够自动推断数据类型

元组：

格式：

Tuple(T1, T2, ...)：元组，其中每个元素都有单独的类型。

Date、DateTime：

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

布尔型：

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

表引擎：

概述：

表引擎（即表的类型）决定了：

1）数据的存储方式和位置，写到哪里以及从哪里读取数据

2）支持哪些查询以及如何支持。
3）并发数据访问。
4）索引的使用（如果存在）。
5）是否可以执行多线程请求。
6）数据复制参数。

日志：

1.TinyLog

概述：

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。
该引擎没有并发控制
    如果同时从表中读取和写入数据，则读取操作将抛出异常；
    如果同时写入多个查询中的表，则数据将被破坏。

适合场景：

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。
此引擎适用于相对较小的表（建议最多1,000,000行）。
如果有许多小表，则使用此表引擎是适合的，因为它需要打开的文件更少。
当拥有大量小表时，可能会导致性能低下。
不支持索引。

2.Log

概述：

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

适合场景：

Log 引擎适用于临时数据，write-once 表以及测试或演示目的。

3.StripeLog

概述：

写：

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。
ClickHouse 为每张表写入以下文件：
    data.bin — 数据文件。
    index.mrk — 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。
StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。

读：

带标记的文件使得 ClickHouse 可以并行的读取数据。这意味着 SELECT 请求返回行的顺序是不可预测的。使用ORDER BY 子句对行进行排序。

适合场景：

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

Set集合：

始终存在于 RAM 中的数据集。它适用于IN运算符的右侧
可以使用 INSERT 向表中插入数据。新元素将添加到数据集中，而重复项将被忽略。但是不能对此类型表执行 SELECT 语句。检索数据的唯一方法是在 IN 运算符的右半部分使用它。
数据始终存在于 RAM 中。对于 INSERT，插入数据块也会写入磁盘上的表目录。启动服务器时，此数据将加载到 RAM。也就是说，重新启动后，数据仍然存在。
对于强制服务器重启，磁盘上的数据块可能会丢失或损坏。在数据块损坏的情况下，可能需要手动删除包含损坏数据的文件。

字典：

概述：

Dictionary 引擎将字典数据展示为一个ClickHouse的表。

使用：

1.使用 dictGet* 函数来获取这种格式的字典数据

dictGetString('default.t_dict_region', 'region_name', toUInt64(2))
dictGetHierarchy('default.t_dict_region', toUInt64(3))

2.select语句

select * from products limit 1;

更新频率：

定期更新字典。 完全下载字典的更新间隔和缓存字典的无效间隔在 <lifetime> 在几秒钟内标记。
字典更新（除首次使用的加载之外）不会阻止查询。 在更新期间，将使用旧版本的字典。 如果在更新过程中发生错误，则将错误写入服务器日志，并使用旧版本的字典继续查询。
LIFETIME(30)
LIFETIME(MIN 3 MAX 5)    在此范围内选择一个统一的随机时间

存在内存的方式layout：

1.flat

字典以平面数组的形式完全存储在内存中。 字典使用多少内存？ 量与最大键的大小（在使用的空间中）成正比。
字典键具有 UInt64 类型和值限制为500,000。 如果在创建字典时发现较大的键，ClickHouse将引发异常，不会创建字典。

2.hashed

该字典以哈希表的形式完全存储在内存中。 字典中可以包含任意数量的带有任意标识符的元素，在实践中，键的数量可以达到数千万项。
支持所有类型的来源。 更新时，数据（来自文件或表）将完整读取。

3.complex_key_hashed

这种类型的存储是用于复合 键. 类似于 hashed.

4.其他

数据源：

1.clickhouse

SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9001
    user 'default'
    db 'default'
    password ''
    table 't_region'
))

2.mysql表

SOURCE(MYSQL(
    port 3306
    user 'root'
    password '123456'
    replica(host '127.0.0.1' priority 1)
    replica(host '127.0.0.1' priority 1)
    db 'test'
    table 'test_dc'
    invalidate_query 'select age from test.test_dc where id=3'
))

3.文件

SOURCE(FILE(path '/var/lib/clickhouse/user_files/person.tsv' format 'TabSeparated'))

4.可执行命令

SOURCE(EXECUTABLE(command 'cat /opt/dictionaries/os.tsv' format 'TabSeparated'))

5.Http(s)

SOURCE(HTTP(
    url 'http://[::1]/os.tsv'
    format 'TabSeparated'
    credentials(user 'user' password 'password')
    headers(header(name 'API-KEY' value 'key'))
))

6.redis

create DICTIONARY dict.imei_to_first_active (
    `hash_key`  String,
    `key` String,
    `value` String
)
primary key `hash_key`,`key`
SOURCE(REDIS(
    host '10.0.48.9'
    port 6379
    storage_type 'hash_map'            // simple 适用于简单源和散列单键源, hash_map 用于具有两个键的散列源
    db_index 0
)) 
LAYOUT(COMPLEX_KEY_HASHED())        // 存储在内存的方式
LIFETIME(3600)
查询过程：
    通过keys * 去匹配所有的类型，还不支持pattern.
缺点：
    耗时太久。

Memory内存：

概述：

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

适合场景：

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

Merge：

概述：

Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。 读是自动并行的，不支持写入。
读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。

格式：

Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。

示例：

create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');

MergeTree：

概述：

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

基本理念：

当你有巨量数据要插入到表中，你要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。相比在插入时不断修改（重写）数据进存储，这种策略会高效很多。

格式：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()    引擎没有参数
ORDER BY expr            表的排序键。必选！可以是一组列的元组或任意的表达式。
[PARTITION BY expr]        分区键 。增加了查询性能。
[PRIMARY KEY expr]        默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。创建一个小型的稀疏索引来加快数据检索。
[SAMPLE BY expr]        用于抽样的表达式。如果要用抽样表达式，主键中必须包含这个表达式
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]影响 MergeTree 性能的额外参数：
                            index_granularity — 索引粒度。即索引中相邻『标记』间的数据行数。默认值，8192 。
                            index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。
                            enable_mixed_granularity_parts — 启用或禁用通过 index_granularity_bytes 控制索引粒度的大小。如果你的表内数据量很大，可以开启这项配置用以提升SELECT 查询的性能。
                            use_minimalistic_part_header_in_zookeeper — 数据片段头在 ZooKeeper 中的存储方式。
                            min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。
                            merge_with_ttl_timeout — TTL合并频率的最小间隔时间。默认值: 86400 (1 天)。
                            write_final_mark — 启用或禁用在数据片段尾部写入最终索引标记。默认值: 1（不建议更改）。
                            storage_policy — 存储策略。 参见 使用多个区块装置进行数据存储.                           

案例：

CREATE TABLE mt_table
(
`date` Date,
`id` UInt8,
`name` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY id

存储结构：

-- mt_table
    |-- 20180301_20180330_1_100_20
    | |-- checksums.txt二进制的校验文件，保存了余下文件的大小size和size的Hash值，用于快速校验文件的完整和正确性
    | |-- columns.txt明文的列信息文件
    | |-- date.bin压缩格式（默认LZ4）的数据文件，保存了原始数据。以列名.bin命名。
    | |-- date.mrk2使用了自适应大小的索引间隔,名字为 .mrk2，二进制的列字段标记文件，作用是把稀疏索引.idx文件和存数据的文件.bin联系起来。
    | |-- id.bin
    | |-- id.mrk2
    | |-- name.bin
    | |-- name.mrk2
    | `-- primary.idx二进制的一级索引文件，在建表的时候通过OrderBy或者PrimaryKey声明的稀疏索引。

数据分区：

数据是以分区目录的形式组织的，每个分区独立分开存储。这种形式，查询数据时，可以有效的跳过无用的数据文件。
规则
    分区键的取值，生成分区ID，分区根据ID决定。根据分区键的数据类型不同，分区ID的生成目前有四种规则：
    （1）不指定分区键
    （2）使用整形
    （3）使用日期类型 toYYYYMM(date)
    （4）使用其他类型
    数据在写入时，会对照分区ID落入对应的分区。
分区目录的生成规则
    partitionID_MinBlockNum_MaxBlockNum_Level

合并过程：

MergeTree的分区目录在数据写入过程中被创建。
不同的批次写入数据属于同一分区，也会生成不同的目录，在之后的某个时刻再合并（写入后的10-15分钟），合并后的旧分区目录默认8分钟后删除。
同一个分区的多个目录合并以后的命名规则：
    MinBlockNum:取同一分区中MinBlockNum值最小的
    MaxBlockNum：取同一分区中MaxBlockNum值最大的
    Level:取同一分区最大的Level值加1

索引：

一级索引：

MergeTree的主键使用Primary Key定义，主键定义之后，MergeTree会根据index_granularity间隔（默认8192)为数据生成一级索引并保存至primary.idx文件中。这种方式是稀疏索引。
稠密索引：每一行索引标记对应一行具体的数据记录
稀疏索引：每一行索引标记对应一段数据记录（类似跳表）（默认索引粒度为8192）。稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度快！

索引粒度：

index_granularity参数，表示索引粒度。新版本中clickhouse提供了自适应索引粒度。

索引的查询过程：

类似跳表

索引规则：

基数太小（即区分度太低）的列不适合做索引列，因为很可能横跨多个mark的值仍然相同，没有索引的意义了。

跳数索引：

index_granularity定义了数据的粒度 granularity定义了聚合信息汇总的粒度 换言之，granularity定义了一行跳数索引能够跳过多少个index_granularity区间的数据

数据存储：

ClickHouse 会为每个数据片段创建一个索引文件，索引文件包含每个索引行（『标记』）的主键值。索引行号定义为 n * index_granularity 。
最大的 n 等于总行数除以 index_granularity 的值的整数部分。对于每列，跟主键相同的索引行处也会写入『标记』。这些『标记』让你可以直接找到数据所在的列。
按列存储

TTL：

概述：

TTL既可以设置在表上，也可以设置在列上。TTL指定的时间到期后则删除相应的表或列，如果同时设置了TTL，则根据先过期时间删除相应数据。

用法：

TTL time_col + INTERVAL 3 DAY
SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

存储策略：

默认策略
    19.15之前,只能单路径存储，存储位置为在config.xml配置文件中指定
    19.15之后，支持多路径存储策略的自定义存储策略，目前有三类策略：
JBOD策略
HOT/COLD策略    

MergeTree家族表引擎：

ReplacingMergeTree
    概述： 
        这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。
    特点：
        1. 使用ORDER BY排序键作为判断重复的唯一键
        2. 数据的去重只会在合并的过程中触发
        3. 以数据分区为单位删除重复数据，不同分区的的重复数据不会被删除，所以分区键要与主键有相同前缀。
        4. 找到重复数据的方式依赖数据已经ORDER BY排好序了
        5. 如果没有ver版本号，则保留重复数据的最后一行
        6. 如果设置了ver版本号，则保留重复数据中ver版本号最大的数据
    格式： 
        ENGINE [=] ReplacingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, [ver])
        可以看出他比MergeTree只多了一个ver，这个ver指代版本列。
    注意：
        只有在合并分区时才会触发删除重复数据的逻辑
        去重的时候只会去重相同分区的数据，跨分区不会去重。所以一般主键和分区键不同。
    示例：
        ENGINE = ReplacingMergeTree()
        PARTITION BY date
        ORDER BY(udid_md5,age)
    FINAL关键字：
        ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。
        注意：
            1.select语句+final的话，会对表的order字段进行排序去重。注意主键要加上分区字段（虽然跨分区不会去重，但select会合并结果）（试验过）。
            2.在大多数情况下，避免使用 FINAL. 常见的方法是使用假设后台进程的不同查询 MergeTree 引擎还没有发生，并通过应用聚合（例如，丢弃重复项）来处理它。
            3. join 去重表 final 这样不会去重，join (select * from 去重表 final)这样才能去重。
    ReplicatedReplacingMergeTree:
        分布式表可以加final，可以实现去重。
        要确定好分布式表的sharding_key(一定是主键的第一个)，确保相同的数据去到同一个节点。

SummingMergeTree：
    概述：
        该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同聚合数据的条件Key的行合并为一行，
        该行包含了被合并的行中具有数值数据类型的列的汇总值。
        如果聚合数据的条件Key的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度，对于不可加的列，会取一个最先出现的值。
    特征：
        1. 用ORDER BY排序键作为聚合数据的条件Key。会对主键相同的数据进行合并，合并的字段分情况，如果是数字字段，进行累加，非数字字段取第一个。
        2. 合并分区的时候触发汇总逻辑
        3. 以数据分区为单位聚合数据，不同分区的数据不会被汇总
        4. 如果在定义引擎时指定了Columns汇总列（非主键）则SUM汇总这些字段
        5. 如果没有指定，则汇总所有非主键的数值类型字段
        6. SUM汇总相同的聚合Key的数据，依赖ORDER BY排序
        7. 同一分区的SUM汇总过程中，非汇总字段的数据保留第一行取值
        8. 支持嵌套结构，但列字段名称必须以Map后缀结束。
    语法： 
        ENGINE = SummingMergeTree([columns])columns — 包含将要被汇总的列的列名的元组
    ReplicatedSummingMergeTree:
        示例：
            CREATE TABLE test.abcd
            (
                key1 String,
                key2 UInt32,
                key3 UInt32,
                value UInt32
            )
            ENGINE = ReplicatedSummingMergeTree('/clickhouse_test_table_cluster/tables/{shard}/testabcd','{replica}',value)
            ORDER BY (key1,key2)
        场景：
            1. key1和key2相同的数据会进行合并。
                如果是其他字段也相同，那么数据会被去重（副本表本身可以去重）
                如果是其他字段不相同，那么数据会被合并求和。同时其他字段取第一个值。（而不是SummingMergeTree其他数字字段求和，非数字取第一个）
                与SummingMergeTree的区别：
                    1.合并是否求和
                    2.合并的时候应该会存源数据，但是select看不出来，只能看合并后的数据，后续再添加相同的数据会被去重。
                        而SummingMergeTree不会对相同的数据进行去重。

        其他解决方案：
            ReplicatedAggregatingMergeTree进行sum聚合，缺点是保存了中间数据。

AggregatingMergeTree：
    概述： 
        说明： 该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 
        !!! ClickHouse 会将相同主键的所有行（在一个数据片段内）替换为单个存储一系列聚合函数状态的行。 
        可以使用 AggregatingMergeTree 表来做增量数据统计聚合，包括物化视图的数据聚合。 
        引擎需使用 AggregateFunction 类型来处理所有列。 (不一定)
        如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。 
    select和insert：
        要插入数据，需使用带有 -State- 聚合函数的 INSERT SELECT 语句。
        从 AggregatingMergeTree 表中查询数据时，需使用 GROUP BY 子句并且要使用与插入时相同的聚合函数，但后缀要改为 -Merge 。
    比较：
        SummingMergeTree只支持算pv,AggregatingMergeTree能支持算uv,分位数,抽样
    示例： 
        create table dwd.data_api_collect_all_aggregate (
          `date` Date,
          `udid` String,
          model String,
          event String,
          type String,
          ext String,
          pkgname String,
          st String,
          `event_uv` AggregateFunction(uniqExact, String,String,String,String,String,String)
        ) engine=ReplicatedAggregatingMergeTree(
          '/clickhouse/tables/{data_shard}/{database}/data_api_collect_all_aggregate',
          '{replica}')
        PARTITION BY date 
        ORDER BY (date,udid, event, type, ext, pkgname, st)

        CREATE MATERIALIZED VIEW agg_view
        ENGINE = AggregatingMergeTree()
        PARTITION BY city
        ORDER BY (id, city) AS
        SELECT
            id,
            city,
            uniqState(code) AS code,
            sumState(value) AS value
        FROM arr_table_base
        GROUP BY
            id,
            city
        往表arr_table_base正常写数据
        查询： 
            select id,sumMerge(value),uniqMerge(code) from agg_view group by id,city;
    场景：
        可以使用AggregatingMergeTree表来做增量数据统计聚合，包括物化视图的数据聚合。
    生产经验：
        1.使用AggregatingMergeTree+AggregateFunction(min,String)选择重复数据中最小的
            如果是为了去重，还不如用ReplacingMergeTree，select的时候去重即可。
            这个表就是强制别人select的时候必须group by + merge
        2.使用AggregateFunction(uniqexact,String...)类型，insert时使用uniqExactState(udid, event, type, ext, pkgname, st)来做总和统计。
            优点：
                避免每次查询都全量查询明细表。
            缺点：
                相当于复制了一份数据，并没有减小数据量.不知道uniqExactState有没有去重，待实验。
            其他方案：
                用ReplacingMergeTree也可以。

CollapsingMergeTree：
    概述： 
        以增代删
        CollapsingMergeTree 会异步的删除（折叠）这些除了Sign列有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。
        没有成对的行会被保留。该引擎可以显著的降低存储量并提高 SELECT查询效率。 
        CollapsingMergeTree引擎有个状态列sign，这个值1为”状态”行，-1为”取消”行，对于数据只关心状态列为状态的数据，不关心状态列为取消的数据
    格式： 
        ENGINE = CollapsingMergeTree(某个字段如sign)
    使用：
        正常的数据sign字段值为1
        要想取消数据，再发送一条相同的记录，但sign字段值为-1即可。
    适合场景：
        数据库的binglog，读取update操作会有old和new之分，删除操作可以折叠。
    触发： 
        optimize table amt_tab_view
        可以触发相同主键，不同sign/重复sign的数据合并。
        注意：
            只会触发不同parts的合并，如果数据都相同，在同一个part，不会合并。
            需要optimize table amt_tab_view final
    使用场景：
        大数据中对于数据更新很难做到，比如统计一个网站或TV的在用户数，更多场景都是选择用记录每个点的数据，再对数据进行一定聚合查询。
        而clickhouse通过CollapsingMergeTree就可以实现，所以使得CollapsingMergeTree大部分用于OLAP场景。

VersionedCollapsingMergeTree：
    概述： 
        这个引擎和CollapsingMergeTree差不多，只是对CollapsingMergeTree引擎加了一个版本，比如可以适用于非实时用户在线统计，统计每个节点用户在在线业务

HDFS：

概述：

支持select读取和insert写入

格式：

ENGINE = HDFS(URI, format)
支持多种常见输入和输出format，比如ocr、parquet等

示例：

CREATE TABLE hdfs_engine_table (name String, value UInt32)
    ENGINE=HDFS('hdfs://hadoop@1hdfs1:9000/other_storage', 'TSV')

删除表后：

hdfs数据不会删除。

支持格式：

常见JSON、CSV、ORC、TabSeparated（文本）

虚拟列：

_path文件路径，可以split用于获取分区字段。
_file文件名

通配符：

路径部分 URI 可能包含 glob 通配符。 在这种情况下，表将是只读的。
HDFS('hdfs://hdfs1:9000/{some,another}_dir/some_file_{1..3}', 'TSV')
HDFS('hdfs://hdfs1:9000/{some,another}_dir/some_file_?', 'TSV')        
HDFS('hdfs://hdfs1:9000/{some,another}_dir/*', 'TSV')
HDFS('hdfs://hadoop@10.0.0.16:4007/user/hadoop/bigdata/zgstat/imei_md5_first_active_attribute/*/part-*', 'ORC')
注意： 
    如果文件列表包含带有前导零的数字范围，请单独使用带有大括号的构造或使用 ?.
    HDFS('hdfs://hdfs1:9000/big_dir/file{0..9}{0..9}{0..9}', 'CSV')

缺点：

不支持分区

生产上优化：

通过JDBC读取数据比较慢，可以改为hdfs引擎写入hdfs后，通过spark读取parquet文件。速度在500M parquet大小的情况下，时间由3分钟缩减为秒级。

MySQL：

概述：

MySQL 引擎可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询。
生产环境慎用，可能会导致连接数太多，clickhouse服务因此瘫痪。

格式：

MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query,'on_duplicate_clause']);

物化视图：

不会自动更新。

Kafka：

格式：

旧版：

Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format
    [, kafka_row_delimiter, kafka_schema, kafka_num_consumers])

新版：

Kafka SETTINGS
    kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'topic1,topic2',
    kafka_group_name = 'group1',
    kafka_format = 'JSONEachRow',
    kafka_row_delimiter = '\n',
    kafka_schema = '',      如果解析格式需要一个 schema 时，此参数必填。
    kafka_num_consumers = 2 单个表的消费者数量。默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。

消费者使用方式：

SELECT 查询对于读取消息并不是很有用（调试除外），因为每条消息只能被读取一次。
使用物化视图创建实时线程更实用。(可以多个物化视图，已经验证)
    1. 使用引擎创建一个 Kafka 消费者并作为一条数据流。
    2. 创建一个结构表。
    3. 创建物化视图，该视图会在后台转换引擎中的数据并将其放入之前创建的表中。
当 MATERIALIZED VIEW 添加至引擎，它将会在后台收集数据。可以持续不断地从 Kafka 收集数据并通过 SELECT 将数据转换为所需要的格式。

生产者使用方式：

物化视图 TO kafka表，全量数据通过insert into kafka表即可
    CREATE TABLE DEST.kafka_partner_partition (
      `token_province_name` String,
      `active_time` String COMMENT '激活时间'
    ) ENGINE = Kafka SETTINGS kafka_broker_list = '10.0.32.15:9092',
    kafka_topic_list = 'partner-partition',
    kafka_group_name = 'partner-partition-consumer',
    kafka_format = 'CSV'
注意：
    topic名字避免下划线
生产大数据调优：
    kafka_max_block_size = 1048576;
    kafka_num_consumers = 4          // 待验证

示例：

create table SRC.kafka_zg_stat_api (`jsonRaw` String) ENGINE = Kafka() SETTINGS kafka_broker_list = '10.0.32.15:9092',
    kafka_topic_list = 'zg_server_request_stat',
    kafka_group_name = 'clickhouse_kafka_zg_stat_api_ods',
    kafka_format = 'JSONAsString'
解析：
    SELECT
              JSONExtract(jsonRaw, 'requestInfo', 'activeDate','String') AS activeDate,
              JSONExtract(jsonRaw, 'requestInfo', 'attributeDate','String') AS attributeDate,
              arrayJoin(JSONExtractArrayRaw(jsonRaw, 'response') AS responseList) as onwRecord,        //解析列表json
              JSONExtractInt(jsonRaw, 'logTime') as logTime
            FROM
              SRC.kafka_zg_stat_api

或者直接在源头解析：

CREATE TABLE queue2 (
    timestamp UInt64,
    level String,
    message String
) ENGINE = Kafka SETTINGS kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'topic',
    kafka_group_name = 'group1',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4
解析为array：
解析：
JSONExtractArrayRaw(onwRecord,'udidImeiList') as udIdImeiList,
存储类型为
udIdImeiList Array(String)
再次解析：
arrayJoin进行explode
JSONExtract(udidImei,'udIdMd5','String')然后提取
解析JSON array：
arrayJoin(JSONExtractArrayRaw(data))

如何清除：
DETACH TABLE consumer;
ATTACH TABLE consumer;
如果使用 ALTER 更改目标表，为了避免目标表与视图中的数据之间存在差异，推荐停止物化视图。

JDBC：

概述：

允许CH通过 JDBC 连接到外部数据库。
要实现JDBC连接，CH需要使用以后台进程运行的程序 clickhouse-jdbc-bridge。
该引擎支持 Nullable 数据类型。

格式：

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
columns list...
)
ENGINE = JDBC(dbms_uri, external_database, external_table)
引擎参数：    
    dbms_uri — 外部DBMS的uri.格式: jdbc:://:/?user=&password=. MySQL示例: jdbc:mysql://localhost:3306/?user=root&password=root.
    external_database — 外部DBMS的数据库名.
    external_table — external_database中的外部表名.

副本和分片：

集群：

ClickHouse的集群配置非常灵活，用户既可以将所有节点组成一个单一集群，也可以按照业务的诉求，把节点划分为多个小的集群。
在每个小的集群区域之间，它们的节点、分区和副本数量可以各不相同
集群配置用shard代表分片、用replica代表副本。

副本：

概述：

ReplicatedMergeTree,只有 MergeTree 系列里的表可支持副本
防止数据丢失，增加数据存储的冗余

特点:

依赖ZooKeeper：在执行INSERT和ALTER查询的时候，ReplicatedMergeTree需要借助ZooKeeper的分布式协同能力，以实现多个副本之间的同步。
    但是在查询副本的时候，并不需要使用ZooKeeper。信息，会在稍后详细介绍。
表级别的副本：
    副本是在表级别定义的，所以每张表的副本配置都可以按照它的实际需求进行个性化定义，包括副本的数量，以及副本在集群内的分布位置等。
    副本不依赖分片。每个分片有它自己的独立副本。
多主架构（Multi Master）：
    可以在任意一个副本上执行INSERT和ALTER查询，它们的效果是相同的。这些操作会借助ZooKeeper的协同能力被分发至每个副本以本地形式执行。
Block数据块：
    在执行INSERT命令写入数据时，会依据max_insert_block_size的大小（默认1048576行）将数据切分成若干个Block数据块。
    所以Block数据块是数据写入的基本单元，并且具有写入的原子性和唯一性。
原子性：在数据写入时，一个Block块内的数据要么全部写入成功，要么全部失败。
唯一性：在写入一个Block数据块的时候，会按照当前Block数据块的数据顺序、数据行和数据大小等指标，计算Hash信息摘要并记录在案。
    在此之后，如果某个待写入的Block数据块与先前已被写入的Block数据块拥有相同的Hash摘要（Block数据块内数据顺序、数据大小和数据行均相同），则该Block数据块会被忽略。
    这项设计可以预防由异常原因引起的Block数据块重复写入的问题。如果只是单纯地看这些特点的说明，可能不够直观。
数据块会去重。
    对于被多次写的相同数据块（大小相同且具有相同顺序的相同行的数据块），该块仅会写入一次。这样设计的原因是万一在网络故障时客户端应用程序不知道数据是否成功写入DB，此时可以简单地重复 INSERT 。把相同的数据发送给多个副本 INSERT 并不会有问题。因为这些 INSERT 是完全相同的（会被去重）。
    配置：
        insert_deduplicate控制开关
        https://github.com/ClickHouse/ClickHouse/issues/2578
        查询：
            SELECT getSetting('insert_deduplicate')
        优先级：
            users.xml(该配置只有这个级别下才能生效)
            Session配置
            Query配置
                （sql只能select）
                (URL?setting_1=value&setting_2=value...).
    非Replicated如何去重：
        non_replicated_deduplication_window保存最后多少个 block 的 hash，默认是 0 

数据块：

一个column.bin文件是由一个到多个压缩数据块组成的。
INSERT 的数据按每块最多 max_insert_block_size = 1048576 行进行分块，换句话说，如果 INSERT 插入的行少于 1048576，则该 INSERT 是原子的。
会按照当前 Block 的数据顺序、数据行和数据大小等指标，计算 Hash 并生成 block_id。

定义：

CREATE TABLE table_name_local ON cluster 'cluster_name'        这个指令使得操作能在集群范围内的节点上都生效
(
    EventDate DateTime,
    CounterID UInt32,
    UserID UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}', ver)    # ver用来表示版本。
replica约定俗成使用服务器的名称，其中replica、layer和shard都在macros中定义，每个服务器可以定义不一样，注意副本用到的变量要一样，分片不一样。
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

副本与分片的区别：

ENGINE = ReplicatedReplacingMergeTree(
  '/clickhouse/tables/{data_shard}/{database}/partition_dim_local',        //zoo_path，副本要保持一致(固定某个数字)，分片要不一致
  '{replica}'                                                        //replica_name，副本要不一致，clickhouse根据zookeeper上的replica列表来复制数据
)
metrika.xml用来on cluster时读取配置
既分片又副本：部分节点的data_shard相同，所有节点的replica不同即可。

与物化视图的搭配使用注意：

写入节点上的物化视图才会触发。其他节点写入而同步过来的副本数据插入不会触发。

数据原子写入与去重：

1.INSERT查询按照数据块插入数据，每个数据块最多max_insert_block_size(默认 max_insert_block_size = 1048576)条记录。换言之， 如果INSERT插入少于1048576条记 录，则插入操作是原子的。单个数据块的写入是原子的。
2.数据块是去重的。 对于同一数据块的多次写入（相同大小的的数据块，包含相同的行以及相 同的顺序），该块仅写入一次。在出现网口故障等异常情况下， 客户端应用程序不知道数据 是否已将数据成功写入数据库，因此可以简单地重复执行INSERT查询。相同的数据发送到哪 个副本进行插入并不重要，INSERT是幂等的。数据去重可通过参数 insert_deduplicate控 制，默认为0(开启去重)。

分布：

概述：

实现数据的水平切分

Distributed：

概述：

分布式引擎，本身不存储数据, 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时，远程服务器表的索引(如果有的话)会被使用。

底层表：

可以是普通的MergeTree表，也可以是Replicated*系列表。
分步表只是负责分发数据，写入每个节点上的底层。

格式：

Distributed(cluster_name, database, table [, sharding_key])
服务器配置文件中的集群名,在/etc/metrika.xml中配置的

关于sharding key的注意事项：

1.ReplicatedCollapsingMergeTree引擎要注意order by主键要与partition key、sharding key有公共前缀，否则CollapsingMergeTree合并不了主键相同的数据。
    其他用到主键的引擎也是类似。

示例：

CREATE TABLE default.replicaTest3_all ON cluster 'cluster_name'             # 方便各个节点都能查询Distributed表  
( `id` Int32, `name` String)
ENGINE = Distributed('cluster_name', 'database_name', '表名', id)             # replicaTest3为ReplicatedMergeTree表，建立在集群my_shard_3上，副本通过配置文件来设置。
如果表名不存在，那么插入数据的时候会报错。

写入数据：

可选方式：

1.自已指定要将哪些数据写入哪些服务器，并直接在每个分片上执行写入。换句话说，在分布式表上«查询»，在数据表上 INSERT。
    这是最灵活的解决方案 – 你可以使用任何分片方案，对于复杂业务特性的需求，这可能是非常重要的。
    这也是最佳解决方案，因为数据可以完全独立地写入不同的分片。
2.在分布式表上执行 INSERT。在这种情况下，分布式表会跨服务器分发插入数据（轮询、随机等多种算法）。
    为了写入分布式表，必须要配置分片键（最后一个参数）。当然，如果只有一个分片，则写操作在没有分片键的情况下也能工作，因为这种情况下分片键没有意义。
    select注意要从分布式表，而不是local表。
    internal_replication参数：
        此参数设置为«true»时，写操作只选一个正常的副本写入数据。如果分布式表的子表是复制表(*ReplicaMergeTree)，请使用此方案。换句话说，这其实是把数据的复制工作交给实际需要写入数据的表本身而不是分布式表。
        若此参数设置为«false»（默认值），写操作会将数据写入所有副本。实质上，这意味着要分布式表本身来复制数据。这种方式不如使用复制表的好，因为不会检查副本的一致性，并且随着时间的推移，副本数据可能会有些不一样。(该情况下，可能与insert_quorum参数重复)

原子性写入：

单个数据块写入是原子的。 INSERT 的数据按每块最多 max_insert_block_size = 1048576 行进行分块，换句话说，如果 INSERT 插入的行少于 1048576，则该 INSERT 是原子的。

异步写入：

概述：

数据是异步写入的。对于分布式表的 INSERT，数据块只写本地文件系统。之后会尽快地在后台发送到远程服务器。
你可以通过查看表目录中的文件列表（等待发送的数据）来检查数据是否成功发送：/var/lib/clickhouse/data/database/table/ 。
如果在 INSERT 到分布式表时服务器节点丢失或重启（如，设备故障），则插入的数据可能会丢失。如果在表目录中检测到损坏的数据分片，则会将其转移到«broken»子目录，并不再使用。

配置同步：

insert_distributed_sync=1
仅当所有分片写入成功后才返回。

其他相关配置：

fsync_after_insert    将文件数据写入发起节点的文件中
fsync_directories    刷新目录metadata

ACK机制：

默认情况下，INSERT 语句仅等待一个副本写入成功后返回。如果数据只成功写入一个副本后该副本所在的服务器不再存在，则存储的数据会丢失。
要启用数据写入多个副本才确认返回，使用 insert_quorum 选项。
settings insert_quorum=4
相关配置：
    insert_quorum_parallel   默认1，启用 
    insert_quorum_timeout    默认10分钟

查询过程：

1.Client 发送查询语句到 ClickHouse 代理，一般为 Http 请求，同时查询语句一般会设定分区范围进行查询。
2.ClickHouse 代理根据负载均衡策略，选取一个请求节点（集群中的一个分片），将查询语句转到请求节点中。
3.请求节点将语句转换，比如如果用到了 Distributed 表，将其转成文为本地表，之后将查询语句请求到 ClickHouse 集群所有分片进行数据查询。
    在分片上执行查询语句时，会根据查询语句中的分区范围，先进行分区级别的数据过滤。之后在满足分区条件的目录中，通过 primary.idx 文件，结合索引键的取值范围，查询出索引编号的范围，然后通过查询列的 [Column].mrk 文件，找到其 [Column].bin 文件中的偏移量对应关系，最终将数据加载到内存进行分析和计算。
4.每个分片执行完查询语句后，返回结果给请求节点。
5.请求节点根据用户的查询逻辑，合并最终的结果，并返回给 ClickHouse 代理。
6.最终，ClickHouse 代理将结果返回给客户端，业务层进行数据的使用。
MergeTree 转换为 ReplicatedMergeTree
1.如果各个副本上的数据不一致，则首先对其进行同步，或者除保留的一个副本外，删除其他所有副本上的数据。
2.重命名现有的 MergeTree 表，然后使用旧名称创建 ReplicatedMergeTree 表。
3.将数据从旧表移动到新表（/var/lib/clickhouse/data/db_name/table_name/）目录内的 ‘detached’ 目录中。
4.然后在其中一个副本上运行ALTER TABLE ATTACH PARTITION，将这些数据片段添加到工作集中。
ReplicatedMergeTree 转换为 MergeTree
1.使用其他名称创建 MergeTree 表。
2.将具有ReplicatedMergeTree表数据的目录中的所有数据移动到新表的数据目录中。
3.然后删除ReplicatedMergeTree表并重新启动服务器。

如何删除ReplicatedMergeTree表（不重启）：

删除元数据目录中的相应 .sql 文件（/var/lib/clickhouse/metadata/）。
删除 ZooKeeper 中的相应路径（/path_to_table/replica_name）。

清空数据：

truncate table  ods.zg_stat_attribute_api_local on cluster 'default_cluster'
这样all表的数据也就清空了。

物化视图：

通过分布式表写入可以触发。
通过副本表写入不能触发。

单机JOIN：

单机JOIN操作默认采用HASH JOIN算法
    从right_table 读取该表全量数据，在内存中构建HASH MAP；
    从left_table 分批读取数据，根据JOIN KEY到HASH MAP中进行查找，如果命中，则该数据作为JOIN的输出；

分布式表进行JOIN：

概述：

两个分布式表join，集群中每个节点会去执行分布式查询。这里就会存在一个非常严重的读放大现象。假设集群有N个节点，右表查询会在集群中执行N*N次。
(左表不是分布式表的话，则只会查询一次)

解决：

一、尽量减少JOIN右表数据量
    ClickHouse根据JOIN的右表数据，构建HASH MAP，并将SQL中所需的列全部读入内存中。在某个内存消耗阈值之后，ClickHouse回退到合并联接算法。
二、利用GLOBAL JOIN 避免查询放大带来性能损失
    如果右表或者子查询的数据量可控，可以使用GLOBAL JOIN来避免读放大。需要注意的是，GLOBAL JOIN 会触发中间表数据在节点之间传播，占用部分网络流量。如果数据量较大，同样会带来性能损失。
三、数据预分布实现Colocate JOIN（推荐）
    当JOIN涉及的表数据量都非常大时，读放大，或网络广播都带来巨大性能损失时，我们就需要采取另外一种方式来完成JOIN计算了。
    根据“相同JOIN KEY必定相同分片”原理，我们将涉及JOIN计算的表，按JOIN KEY在集群维度作分片。将分布式JOIN转为为节点的本地JOIN，极大减少了查询放大问题。
    SELECT a_.i, a_.s, b_.t FROM a_all as a_ JOIN b_local AS b_ ON a_.i = b_.i
    如果预分区了，那么各个节点将会执行一次SELECT a_.i, a_.s, b_.t FROM a_local AS a_ JOIN b_local as b_ ON a_.i = b_.i即可。

IN分布式查询：

LOCAL IN：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
将会在所有远程服务器执行：
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
IN子句中的数据集将在每台服务器上独立收集本地数据。（只适合所有必要的数据将在每台服务器上本地提供。）

N*N扩散（随机传播时查询）：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
子查询将开始在每个远程服务器上运行。 由于子查询使用分布式表，因此每个远程服务器上的子查询将重新发送到每个远程服务器
SELECT UserID FROM local_table WHERE CounterID = 34
如果您有100台服务器的集群，则执行整个查询将需要10,000个基本请求，这通常被认为是不可接受的。

GLOBAL IN：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
请求者所在服务器执行：SELECT UserID FROM distributed_table WHERE CounterID = 34
收集结果放在RAM中的临时表中
然后在所有的远程服务器执行：SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1
否定：
    GLOBAL NOT IN
生产注意：
    global join的表如果没有加where，每次会拿全部的数据。需要考虑内存的问题。
    无论是建view，还是查询sql，都要注意是否需要加上where条件。

SQL语法：

DDL操作：

创建数据库：

create databases mydatabase;执行完成以后，会在clickhouse的安装路径后生成mydatabase的文件目录
show databases

建表：

create table my_table ( Title String, URL String ,EventTime DateTime) ENGINE=Memory
create table if not exists new_db.hits_v1 as default.hits_v1 engine=TinyLog
create table if not exists hits_v1_1 engine=Memory as select * from hits_v1;

查询表结构：

desc xxx
show create table xxx

检查表：

CHECK TABLE
检查表中的数据是否损坏，他会返回两种结果：
    0 – 数据已损坏
    1 – 数据完整
该命令只支持Log，TinyLog和StripeLog引擎。

删除表：

drop table    

修改表（暂无成功）：

detach table xxx.
SELECT metadata_path FROM system.tables WHERE name = 'request_income';
根据metadata_path修改sql
ATTACH TABLE request_income;    可能会报错，但show create table xxx可以看到成功。

表的移除和添加：

移除：

在线：detach table xxx.
离线：服务停止时移除整个库或表data目录和metadata目录，启动后不会加载（system.tables虽然还有记录）

添加：

attach table 表名

触发表合并：

optimize table xxx final 
注意： 
    这种DDL会阻塞整个集群。后续的create table操作都会阻塞。

清除数据DELETE：

概述：

在2018年发布了UPDATE和DELETE。这些异步的，非原子的更新被实现为ALTER TABLE UPDATE语句，并且有可能shuffle 很多数据。
当不需要立即知道结果时，这对于批量操作和不频繁更新很有用。

特点：

1.Mutation操作适用于批量数据的修改和删除

2.不支持事务 一旦语句被提交执行就会立刻对现有的数据产生影响，无法回滚。
3.Mutation操作执行是一个异步的过程，语句提交会立即返回，但是不代表具体逻辑已经执行完毕，具体的执行记录需要在system.mutations系统表查询。

示例：

alter table dws.data_api_collect_view delete where 1=1
truncate table ads.token_profit_view_test
select * from system.mutations where `table`='daily_udid_event_cnt_local'
取消：
    kill kutation where database='app' and table='test'

临时表：

create temporary table tmp_v1 (createtime Datetime);
如果临时表和正常表名字相同，临时表优先
临时表的表引擎只能是Memory，数据是临时的，断电即无的数据。
更多的是应用在clickhouse内部，是数据在集群间传播的载体

分区表：

只有合并数（MergeTree）家族的表引擎支持分区表。
语法： 
    partition by (field,...)
注意：
    分区字段越多，写入速度越慢。一个分区字段速度非常快。二个就比较慢了。

视图：

普通视图：不保存数据，只是一层单纯的select查询映射，起着简化查询、明晰语义的作用。
物化视图：
    概述：
        保存数据，如果源表被写入新数据，物化视图也会同步更新。
    注意事项：
        1.一定要使用 TO 关键字为 MV 表指定存储位置，否则不支持 嵌套视图(多个物化视图继续聚合一个新的视图)（已验证，嵌套外层的物化视图需要使用存储表作为源表）
            或者select语句的表写为`.inner.viewname`也行，dws.`.inner.system_daily_active_udid`。
            或者使用普通视图，但是这样不能加快查询速度。
        2.如果用到了多表联查，只有当第一个查询的表有数据插入时，这个 MV 才会被触发（设计left join时需要注意）
            更新的时候可能会产生重复数据。后台没来得及合并。
        3.普通视图 -> 物化视图，那么物化视图不能更新。
        4.无to的物化视图 -> 普通视图 -> 物化视图，最后的物化视图也不能同步更新。
        5.在物化视图上建立物化视图时，需要用背后的存储表才行。（TO指定的表，没有To时默认为.inner开头的）

    同一个表多个物化视图的更新顺序：
        按照字母序

    POPULATE修饰符：决定在创建物化视图的过程中是否将源表的数据同步到物化视图里。
    示例： 
        CREATE MATERIALIZED VIEW consumer TO daily 
            AS SELECT toDate(toDateTime(timestamp)) AS day, level, count() as total
            FROM queue GROUP BY day, level;
    示例2:
        CREATE MATERIALIZED VIEW dws.data_api_collect_view (
          `date` Date,
          `tokenId` String,
          `udid_md5` String,
          `search_cnt` UInt64,
          `app_download_cnt` UInt64,
          `dsp_cnt` UInt64,
          `yingyongbao_download_cnt` UInt64
        ) ENGINE = ReplacingMergeTree()
            PARTITION BY date
            ORDER BY (tokenId,udid_md5) AS
            ....
    TO关键字：
        TO 关键字为 MV 表指定存储位置，否则不支持 嵌套视图(多个物化视图继续聚合一个新的视图)
        可以不指定to，直接查询物化视图。
    手动更新数据：
        INSERT INTO xxx             //视图表或者是to背后的默认表也可以
        SELECT语句
    更新触发时机：
        最左边的表（如果嵌套则是最里边的最左边）更新后，执行当前sql。
    分布式上的物化视图：
        做法一：
            1.先建立副本表
            2.建立物化视图CREATE MATERIALIZED VIEW test.a on cluster 'default_cluster' TO test.b select local表
            3.创建分布式物化视图，insert来同步数据源
                create table mv_all on cluster xxx engine=Distributed(cluster,test,mv,rand())
              或者创建分布式表，insert这个表也可以，注意select xxx from要选取分布式表（不然只有本节点数据）。
        做法二：
            基于分布式表做物化视图，物化视图会拿到本节点所有insert到分布式表的数据，包括重复的数据。
                会忽略其他节点insert到分布式表的数据
                会忽略insert到local的数据。

    坑：
        1.如果不是left join，而是outer join，那么左表更新时，会取右表全量数据。这样会导致其他left join为空的情况。（暂时没想通为什么left join某个字段会为空，join字段都有的）
            需要避免这种情况。可以用union all然后外层distinct（物化视图不支持）
            需要根据业务场景来决定是否使用left join还是right join（比如订单表，select日期出来inner join其他uid表，再正常处理）
        2.去重的物化视图，做报表可视化展示时，加上final，预防没合并的情况。
        3.物化视图注意如果前后都用到了同一个表，select出来的数据只会是新增的。
            (SELECT date FROM ods.spark_all_billing_all group by date ) AS a left join b on xxx left join (select 多个账单 from ods.spark_all_billing_all) c 
            这个时候left join拿不到同一天date的其他账单收入(与触发的数据在不同的行，除非在同一行才可以拿到)。
            解决方法：
                SELECT date FROM ods.spark_all_billing_all group by date ) AS a单独拎出来做个物化视图，换为背后的存储表即可。
            新出现的坑：
                select 多个账单 from ods.spark_all_billing_all 可能会有多条重复的记录。加上final。
                注意物化视图的最左边的表(无论是视图还是实体表)不能加final。

        4.left join物化视图背后的存储表，dws.`inner.data_api_collect`，会出现某个字段如token_id为空的情况，但其他字段不为空。
            与是否使用TO无关
            select语句的tc.token_id没有重命名as为token_id，insert的时候没有报错，正常insert有值，但触发器insert就没值了。
        5.物化视图左表不能使用FINAL
            两种场景：
                a.建表时最外层的最左边
                b.JOIN或者view视图中的表，也不能使用FINAL。

修改表结构：

只有MergeTree系列、Merge、Distribution这三类表引擎支持alter操作。
追加字段，两种方式：
    1、alter table partition_v1 add column os String default 'mac';
    2、alter table partition_v1 add column IP String after ID;
修改数据类型：
    alter...modify column...注意：类型需要相互兼容
修改备注：
    alter...comment column ...
删除已有字段：
    alter table partition_v1 drop column URL会把该字段下的数据一起删除
移动数据表
    rename... to...
    注意：表的移动只能在单节点内完成

数据分区的基本操作：

查询分区信息：

ClickHouse内置了很多system系统表，用于查询自身状态信息。
select partition_id,name,table,database from system.parts where table = 'partition_v1';

删除指定分区：

alter table partition_v1 drop partition 202005
可以利用删除完成更新操作---先删除，再insert插入
复制分区数据
条件：
    1、两张表需要有相同的分区键
    2、两张表需要有相同的表结构
alter table partition_v2 replace partition 202005 from partition_v1;
重置分区数据
alter table partition_v1 clear clumn URL in partition 202005
卸载和装载分区
alter table ...detach..
alter table ...attach...
分布式DDL执行
在集群上的任意一个节点上执行DDL语句，那么集群上的任意一个节点都会以相同的顺序执行相同的语义。

选择查询：

with子句：

概述：

本节提供对公共表表达式的支持 (CTE），所以结果 WITH 子句可以在其余部分中使用 SELECT 查询。
限制
1. 不支持递归查询。只适合当前select。
2. 当在section中使用子查询时，它的结果应该是只有一行的标量。
3. Expression的结果在子查询中不可用。

FROM子句：

FINAL 修饰符
    当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。
    在大多数情况下，避免使用 FINAL. 常见的方法是使用假设后台进程的不同查询 MergeTree 引擎还没有发生，并通过应用聚合（例如，丢弃重复项）来处理它。

SAMPLE子句：

概述：

启用数据采样时，不会对所有数据执行查询，而只对特定部分数据（样本）执行查询。
例如，如果您需要计算所有访问的统计信息，只需对所有访问的1/10分数执行查询，然后将结果乘以10即可。

近似查询处理在以下情况下可能很有用:

当你有严格的时间需求（如<100ms），但你不能通过额外的硬件资源来满足他们的成本。
当您的原始数据不准确时，所以近似不会明显降低质量。
业务需求的目标是近似结果（为了成本效益，或者向高级用户推销确切结果）。

条件：

您只能使用采样中的表 MergeTree 族，并且只有在表创建过程中指定了采样表达式（请参阅 MergeTree引擎).

格式：

SAMPLE k        这里 k 是从0到1的数字。
SAMPLE N        这里 n 是足够大的整数。 例如, SAMPLE 10000000.  
SAMPLE K OFFSET M    从数据的后半部分1/2取出10％的样本。如果10%超出自动截断

示例：

select CounterID,_sample_factor from hits_v1 sample 0.1 limit 2;

采样百分比：

select CounterID,_sample_factor from hits_v1 sample 10000 limit 1;
该 _sample_factor 列包含动态计算的相对系数。 当您执行以下操作时，将自动创建此列

JOIN子句：

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
    如何补全非匹配行的连接字段？
        将join改为using，要求两表字段相同。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。

array join
    示例： 
        select title,value from query_v1 array join value;将多个value拍扁
    LEFT ARRAY JOIN：
        保留左边null值
    用法：
        搭配groupArray，arrayEnumerate实现row_number         // argMax\argMin也可以实现取第一个的做法。

array相关函数：

arrayMap(func, arr1, …)
    示例： 
        SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;        返回[3,4,5]
        SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res  返回[(1,4),(2,5),(3,6)] 
arrayJoin
    函数获取每一行并将他们展开到多行（unfold）。行转列函数。

arraySort：
    排序，语法为arraySort([func], arr)

groupArray：
    列转行，输出数组
    用法：
        select groupArray(src) from (select arrayJoin([1,2,3]) as src)
    指定个数：
        select groupArray(limit)(src)
    实现row_number:
        select partner_id,partition_id,val,row from (
        select partner_id,partition_id,groupArray(create_time) as arr_val,arrayEnumerate(arr_val) as row from default.sync_token_record_from_mysql group by partner_id,partition_id) array join arr_val as val,row
        hive效果：
            按照partner_id,partition_id分组，按照create_time排序
    实现lag/lead：
        groupArray开窗后，用arrayPushFront/arrayPopBack实现上一个。用arrayPushBack/arrayPopFront实现下一个。

neighbor(column, offset[, default_value]):
    实现lag上一行/lead下一行，不支持分组开窗

all inner join
    保留重复的id
any inner join
    保留第一个
asof inner join
    当您需要连接没有完全匹配的记录时非常有用。可以使用任意数量的相等条件和恰好一个最接近的匹配条件。
空值处理：
    join_use_nulls
    0：填充字段的类型默认值，默认选项
    1：填充null
    示例：
        SELECT * FROM id_val ANY LEFT JOIN id_val_join USING (id) SETTINGS join_use_nulls = 1

tuple相关：

表示：

(field1,field2...)

相关函数：

tupleElement(tuple_field, n) 从1开始

PREWHERE子句：

概述：

Prewhere是更有效地进行过滤的优化。 默认情况下，即使在 PREWHERE 子句未显式指定。 它也会自动移动WHERE 条件到prewhere阶段。 
PREWHERE 子句只是控制这个优化，如果你认为你知道如何做得比默认情况下更好才去控制它。
使用prewhere优化，首先只读取执行prewhere表达式所需的列。 然后读取运行其余查询所需的其他列，但只读取prewhere表达式所在的那些块 “true” 至少对于一些行。 
如果有很多块，其中prewhere表达式是 “false” 对于所有行和prewhere需要比查询的其他部分更少的列，这通常允许从磁盘读取更少的数据以执行查询。

手动控制Prewhere：

该子句具有与 WHERE 相同的含义，区别在于从表中读取数据。 当手动控制 PREWHERE 对于查询中的少数列使用的过滤条件，但这些过滤条件提供了强大的数据过滤。 
这减少了要读取的数据量。
查询可以同时指定 PREWHERE 和 WHERE. 在这种情况下, PREWHERE 先于 WHERE.
如果 optimize_move_to_prewhere 设置为0，启发式自动移动部分表达式 WHERE 到 PREWHERE 被禁用。

限制：

PREWHERE 只有支持 *MergeTree 族系列引擎的表。

WHERE：

null的情况：

is not null 或者isNotNull()适合null的字段

GROUP BY子句：

空处理
    对于分组，ClickHouse解释 NULL 作为一个值，并且 NULL==NULL. 它不同于 NULL 在大多数其他上下文中的处理方式。

LIMIT BY子句：

格式：

LIMIT [offset_value, ]n BY expressions
LIMIT n OFFSET offset_value BY expressions

HAVING 子句：

SELECT 子句：

正则：

SELECT COLUMNS('a') FROM col_names
以下查询所有列名包含 a 。
可以使用多个 COLUMNS 表达式并将函数应用于它们。

DISTINCT子句：

如果 SELECT DISTINCT 被声明，则查询结果中只保留唯一行。 因此，在结果中所有完全匹配的行集合中，只有一行被保留。
限制
    DISTINCT 不支持当 SELECT 包含有数组的列。

LIMIT：

LIMIT m 允许选择结果中起始的 m 行。
LIMIT n, m 允许选择个 m 从跳过第一个结果后的行 n 行。 与 LIMIT m OFFSET n 语法是等效的。
LIMIT … WITH TIES 修饰符可以不计重复值

UNION ALL子句：

限制
    只有 UNION ALL 支持。 UNION (UNION DISTINCT）不支持。 如果你需要 UNION DISTINCT，你可以写 SELECT DISTINCT 子查询中包含 UNION ALL.
    不支持物化视图
        - 可以用full outer join + using来替代
        - 可以用2个mv，然后指向同一个存储表

INTO OUTFILE 子句：

添加 INTO OUTFILE filename 子句（其中filename是字符串） SELECT query 将其输出重定向到客户端上的指定文件。
实现细节
    此功能是在可用 命令行客户端 和 clickhouse-local. 因此通过 HTTP接口 发送查询将会失败。
    如果具有相同文件名的文件已经存在，则查询将失败。
    默认值 输出格式 是 TabSeparated （就像在命令行客户端批处理模式中一样）。

Format子句：

概述：

指定 FORMAT format 在查询结束时以任何特定格式获取结果集。

默认格式：

如果 FORMAT 被省略则使用默认格式，这取决于用于访问ClickHouse服务器的设置和接口。 
如果为 HTTP接口 和 命令行客户端 在批处理模式下，默认格式为 TabSeparated. 对于交互模式下的命令行客户端，默认格式为 PrettyCompact（它生成紧凑的人类可读表）。

实现细节：

使用命令行客户端时，数据始终以内部高效格式通过网络传递 (Native). 客户端独立解释 FORMAT 查询子句并格式化数据本身（以减轻网络和服务器的额外负担）。

聚合类型AggregateFunction：

用法：

AggregateFunction(name, types_of_arguments…)

常见聚合函数name：

uniq计算参数的不同值的近似数量。是近似去重，千万级用户，精确度能达到99%以上
uniqExact精确去重，和mysql的count distinct功能相同，比如统计uv。
min
max

如何查询：

AggregateFunction类型总是以特定的二进制形式展现在所有的输出格式中
使用聚合函数组合器Merge+其他字段group by
     select minMerge(first_active_date) from dwd.system_daily_update_udid_age_view2 group by date,udid_md5

如何写入：

将数据包含在INSERT SELECT语句中，同时对于AggregateFunction类型的数据，您需要使用对应的以-State为后缀的函数进行处理。
示例：INSERT INTO TABLE agg_table SELECT 'A000','test', uniqState('code1'), sumState(toUInt32(100)), '2019-08-10 17:00:00';

聚合函数组合器：

-If 
    -If可以加到任何聚合函数之后。加了-If之后聚合函数需要接受一个额外的参数，一个条件（Uint8类型），如果条件满足，那聚合函数处理当前的行数据，如果不满足，那返回默认值（通常是0或者空字符串）。
-Array 
    -Array后缀可以附加到任何聚合函数。 在这种情况下，聚合函数采用的参数 ‘Array(T)’ 类型（数组）而不是 ‘T’ 类型参数。 如果聚合函数接受多个参数，则它必须是长度相等的数组。 在处理数组时，聚合函数的工作方式与所有数组元素的原始聚合函数类似。
-State 
    如果应用此combinator，则聚合函数不会返回结果值（例如唯一值的数量 uniq 函数），但是返回聚合的中间状态（对于 uniq，返回的是计算唯一值的数量的哈希表）。 这是一个 AggregateFunction(...) 可用于进一步处理或存储在表中以完成稍后的聚合。
-Merge 
    如果应用此组合器，则聚合函数将中间聚合状态作为参数，组合状态以完成聚合，并返回结果值。

时间函数:

cast('2021-02-22' as Date)
toMonth()
dateDiff('单位',startDay,endDay)

字符串分割函数：

splitByChar(separator, s)
splitByString(separator, s)
splitByRegexp(regexp, s)

窗口函数：

概述：

与hive差不多。

语法：

over([partition by id] [order by time desc] [{ ROWS | RANGE } BETWEEN frame_start AND frame_end ])

支持：

rows
ranges
rank()\dense_rank()\row_number()
first_value\last_value

不支持：

lag、lead，但可以通过any(value) over (.... rows between <offset> preceding and <offset> preceding)实现lag，following实现lead

请求相关：

SELECT query_id, query FROM system.processes;
KILL QUERY WHERE query_id = '<id>';

执行计划：

explain查询：

explain sql 
比较粗糙
示例：
    Expression (Projection)                                │
    │   Expression (Before ORDER BY and SELECT)            │
    │     Aggregating                                      │
    │       Expression (Before GROUP BY)                   │
    │         ReadFromStorage (Read from MaterializedView)

log查询：

通过将ClickHouse的服务日志，设置到DEBUG或者TRACE级别，可以变相实现EXPLAIN查询的作用
    clickhouse-client  -h ch7.nauu.com --send_logs_level=trace <<< 'SELECT * FROM hits_v1' > /dev/null
示例：
    Access granted: SELECT(date, udid_md5, age) ON dwd.system_daily_update_udid_age
    Key condition: unknown
    MinMax index condition: (column 0 in [18771, +inf))
    Not using primary index on part 20210525_36_36_0
    Selected 36 parts by date, 36 parts by key, 36 marks by primary key, 36 marks to read from 36 ranges
    Reading approx. 294912 rows with 8 streams
    Read 27543 rows, 1.34 MiB in 0.010402338 sec., 2647770 rows/sec., 128.68 MiB/sec.
    Peak memory usage (for query): 8.29 MiB.

优化：

基础优化-表优化：

1.数据类型

- 建表时能用数值型或日期时间型表示的字段就不要用字符串，全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。
- 虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。
- 官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）。
- 维度表如果数据量少，可以不建分布式表，每个节点存储重复一份，可以避免网络开销。

2.分区和索引

- 分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。
- 必须指定索引列，ClickHouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的userid字段；通常筛选后的数据满足在百万以内为最佳。

3.表参数

Index_granularity是用来控制索引粒度的，默认是8192，如非必须不建议调整。
如果表中不是必须保留全量历史数据，建议指定TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过alter table语句随时修改。

查询优化：

1 单表查询

- 使用Prewhere替代where，当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。
- 数据采样，通过采用运算可极大提升数据分析的性能 
    采样修饰符只有在MergeTree engine表中才有效，且在创建表时需要指定采样策略。
- 数据量太大时应避免使用select *操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的io资源越少，性能就会越高。
- 千万以上数据集进行order by 查询时需要搭配where 条件和limit语句一起使用。
- 如非必须不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者子啊表中构造实际字段进行额外存储。
- 使用uniqCombined替代distinct性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使用这种去重方式提升查询性能。
- 对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。
- 不建议在高基列上执行distinct去重查询，改为近似去重uniqCombined

2.多表关联

- 当多表联查是，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是JOIN
- 多表join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse中无论是Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
    具体业务场景：
        left join的左表是分区表，查询字段，这个时候特别慢。
        解决1:
            如果将分区去掉，改为普通的表，那么查询速度很快。可能与分区需要分开检索再聚合有关。
        解决2: 
            将所有查询的字段作为group by的字段，这样也可以很快返回。可能与clickhouse的优化器有关。查询log发现rows速度更慢，带宽更低，只有Peak memory usage为0
            select 
                date,udid_md5,age
            from 
                dwd.system_daily_update_udid_age
            prewhere date>='2021-05-24' and date<=today()-7
            加上group by date,udid_md5,age即可。
        解决3:
            换掉tabix为superset
- ClickHouse在join查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）
- 将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不易太大，因为字典表会常驻内存
- 通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。

写入和删除优化：

尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge任务带来巨大压力
不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge速度跟不上而报错，一般建议每秒钟发起2-3次写入操作，每次操作写入2w~5w条数据（依服务器性能而定）

运维优化：

1.配置优化

backgroup_pool_size    后台线程池的大小，merge线程就是在该线程池中执行，该线程池不仅仅是给merge线程用的，默认值16，允许的前提下建议改成cpu个数的2倍。
log_queries    默认值为0，修改为1，系统会自动创建system_query_log表，并记录每次查询的query信息 。
max_execution_time    设置单次查询的最大耗时时，单位是秒；默认无限制；需要注意的是客户端的超时设置会覆盖该参数
max_concurrent_queries    最大并发处理的请求数(包含select,insert等)，默认值100，推荐150(不够再加)。
max_threads    设置单个查询所能使用的最大cpu个数，默认是cpu核数
max_memory_usage    此参数在config.xml  中,表示单次Query占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。
max_bytes_before_external_group_by    一般按照max_memory_usage的一半设置内存，当group使用内存超过阈值后会刷新到磁盘进行。
max_bytes_before_external_sort    当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
max_table_size_to_drop    此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除50GB以上的分区表会失败。建议修改为0，这样不管多大的分区表都可以删除。

2.存储

            ClickHouse不支持设置多数据目录，为了提升数据io性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景SSD会比普通机械硬盘快2-3倍。

3.数据同步

            ClickHouse伪装成一个MySQL的备库去实时拉取MySQL中的数据，节省掉canal + kafka + clickhouse

4.查询熔断

        为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

其他优化：

1.关闭虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢。
2.为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值。
3.批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能。
4.尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先group by再join比先join再group by查询时间更短。
5.ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多，防止数据导入过程磁盘可能会被打满。
6.cpu一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，cpu是最关键的指标，要非常关注。

监控：

腾讯云指标：

网络连接数
select查询数
已打开的文件总数
insert行数
正在后台执行的merge总量
查询处理的线程总量

prometheus+grafana监控：

1.修改clickhouse集群配置config.xml：

去掉prometheus的注释，相当于exporter的作用

2.启动prometheus和grafana：

location /prometheus/ {
            proxy_pass http://10.0.0.2:9090;
        }
        location /grafana/ {
            proxy_pass http://10.0.0.2:3000/;
        }
        docker run -d -p 3000:3000 -v /data/grafana/:/var/lib/grafana --user 'root' --name grafana -e GF_SERVER_ROOT_URL=/grafana/ grafana/grafana

创建prometheus.yml文件：

global:

  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.

alerting:
  alertmanagers:
  - static_configs:
    - targets:

rule_files:
   - "first_rules.yml"
   - "second_rules.yml"

scrape_configs:
  - job_name: 'clickhouse_9363'

    static_configs:
    - targets: ['ip1:9363','ip2:9363','ip3:9363','ip4:9363']
启动服务
        docker run -d -p 9090:9090 -v /data/prometheus/prometheus.yml:/etc/prometheus/prometheus.yml -v /data/prometheus/data/:/prometheus --user "root" --name prometheus prom/prometheus:v2.28.1 --web.external-url http://localhost:9090/prometheus/ --config.file=/etc/prometheus/prometheus.yml --storage.tsdb.path=/prometheus --web.console.libraries=/usr/share/prometheus/console_libraries --web.console.templates=/usr/share/prometheus/consoles

3.grafana操作：

        添加datasource
            http://172.17.0.2:9090/prometheus
        添加dashboard
grafana + clickhouse plugins监控
    1.启动grafana
        docker run -d -p 3000:3000 -v /data/grafana/:/var/lib/grafana --user 'root' --name grafana -e GF_SERVER_ROOT_URL=/grafana/ -e GF_PLUGINS_ALLOW_LOADING_UNSIGNED_PLUGINS=vertamedia-clickhouse-datasource grafana/grafana
        下载插件
            grafana-cli plugins install vertamedia-clickhouse-datasource
        重启容器
    2.配置
        添加datasource
            http://10.0.0.2:8123
        导入dashboard
            ClickHouse Performance Monitor
        根据需求修改sql语句
    多分片集群监控：
        创建分布式表：
            CREATE TABLE system.query_log_all AS system.query_log ENGINE = Distributed(集群名称, system, query_log)
        修改dashboard配置文件：
            将下载好的ClickHouse Queries打开,将其中的query_log全部替换为query_log_all

常用配置：

耗时查询：

max_rows_to_read 运行查询时可从表中读取的最大行数。
    set max_rows_to_read=5000000000000;
max_bytes_to_read 运行查询时可以从表中读取的最大字节数（未压缩数据）。
    set max_bytes_to_read=5000000000000;
max_execution_time
    set max_execution_time=5;
max_memory_usage
    SET max_memory_usage = 20000000000000;

拉链表：

分区表：

增加字段insert date，物化视图触发insert
    CREATE TABLE ads.token_sharing_backup (
      `date` Date,
      `token_id` String,
      `insert_date` Date,
        `is_delete` Int8
    ) ENGINE = ReplacingMergeTree() PARTITION BY insert_date
    ORDER BY
      (insert_date, is_delete,date, token_id) SETTINGS index_granularity = 8192

    CREATE MATERIALIZED VIEW ads.token_sharing_backup_mv TO ads.token_sharing_backup (
      `date` Date,
      `token_id` String,
      `insert_date` Date,
      is_delete Int8
    ) AS
    SELECT
      date,
      token_id,
      today() AS insert_date,
      IF(sign=-1,1,0) as is_delete                  第一次insert的时候is_delete需要改为0，因为final会折叠所有记录，所有数据都为新的
    FROM 
        xxxxx

拉链表：

增加start_date和end_date字段，增加两个物化视图，分别负责从分区表，insert增量数据，封存历史数据
    CREATE TABLE ads.token_sharing_history (
      `date` Date,
      `token_id` String,
      `start_date` Date,
      `end_date` String         // Date类型存储9999-12-31不行
    ) ENGINE = ReplacingMergeTree() PARTITION BY start_date
    ORDER BY
      (start_date, date, token_id) SETTINGS index_granularity = 8192
insert增量数据
    SELECT
      date,
      token_id,
      insert_date AS start_date,
      '9999-12-31' AS end_date
    FROM
      ads.token_sharing_backup
    where is_delete=0                   //第一次和以后每次的触发，都只选取新增的数据插入（同时如果是修改操作，会多发一条is_delete=1的数据，用来闭链）
封存历史数据
    insert into xxx         // clickhouse自动对主键去重，所以不用hive那种insert override
    SELECT
      date,
      token_id,
      tb.start_date AS start_date,
      date_add(DAY,-1,ta.insert_date) AS end_date
    FROM
      ads.token_sharing_backup ta 
    inner join 
      (select * from ads.token_sharing_history final where end_date='9999-12-31') tb     // 这样才能去重
    on 
        ta.date=tb.date and ta.token_id=tb.token_id and ta.is_delete=1   // 针对删除或旧数据，进行闭链
    where 
        ta.insert_date>tb.start_date        // 避免刚插入的数据
优化：
    合并成left join也可以
其他方案：
    前提：业务库binlog发的记录，针对旧记录会发sign=-1的记录。
    直接将sign=1的记录end_date标记为9999-12-31，-1的记录标end_date记为today()（这条记录会覆盖之前相同主键的记录）

回溯历史：

select count(*) from ads.token_sharing_history where start_date <= '2020-08-25' and end_date >= '2020-08-25'

生产环境问题：

1.server一直启动不了，系统log发现有oom kill clickhouse-serv

data和metadata目录可以尝试备份重启看看
原因：是否有大文件wal加载，导致serv启动load发生oom了。
大文件产生的原因：
    每个表会有预写文件，当超过阈值大小后（1G）滚动。同时内存保存写入数据（小量数据写入堆内内存，大批量数据写入堆外内存）。
    clickhouse后台有空闲的线程时（对比java的full gc），当堆外内存的排序后数据超过阈值后，将数据刷入磁盘，并扫描wal文件，当wal文件对应的所有的数据都刷入后，删除该wal文件。
    clickhouse启动时，会读取wal文件加载到内存中。
    极端情况：
        堆内内存可能存有前面滚动的wal文件的数据，导致很早之前滚动的wal文件不能删除。wal文件越来越多。可能发生load到内存oom的情况。
优化：
    1.由wal文件刷入持久化存储，而不是内存。这样可以从后往前刷，删除之前的滚动wal文件，避免过多。
    2.内存刷入的机制，要避免小数据写入（物化视图可能避免不了），加缓存层。

2.toDate(now())要比toDate(start_time)快速很多

初步估计now可能有缓存，而每行数据的start_time会不同。

3. 配置生效范围问题

SETTINGS join_use_nulls = 1会作用于使用到的view普通视图，导致Cannot convert NULL value to non-Nullable type情况出现。
解决：
    在view里面单独加setting。

4.回溯历史的时候数据量太大，导致内存超过单个节点可用最大内存

聚合group by数据过多的解决：

配置：

max_bytes_before_external_sort：如果启用，则当要排序的数据量达到指定的字节数时，将对收集的数据进行排序并转储到临时文件中。要比max_memory_usage小。
max_bytes_before_external_group_by：将group by的临时文件放到文件中，推荐为max_memory_usage的一半或以下。
    因为聚合分为两个阶段：读取数据形成中间数据，合并中间数据，如果没写临时文件，那么阶段二需要和阶段一一样大的内存。（否则max_memory_usage又不需要dump，会OOM）
    当触发时，
        占用的内存仅稍大于max_bytes_before_external_group_by。
        运行时间大概是3倍。

适合场景：

一张表比较大，中间聚合数据多

Join过程：

相关配置：

max_bytes_before_external_join还在开发中，仅支持join_overflow_mode中断操作并抛出异常或者是中断操作

解决：

方式一：

手动加上where条件分批写入。

方式二：

使用sort-merge shuffle
配置：
    join_algorithm='partial_merge'对数据排序和dump入文件。此外还是hash，主要基于内存。
    partial_merge_join_optimizations，默认开启，可能会导致错误的结果
    partial_merge_join_rows_in_right_blocks，限制右边block的大小，默认65536
        1.将右边数据按照该配置分割为blocks
        2.定位每个block的最大最小值。
        3.尽可能上传blocks。
    join_on_disk_max_files_to_merge，允许MergeJoin阶段disk文件并行合并的数量，越大RAM使用越多。默认64。