clickhous原理解析与使用

Myth丶恋晨 2022-05-13 14:36 554阅读 0赞

### 为什么选择ck ###

编码风格 jdbc，与mysql一致  
可靠性高，支持主从和集群  
效率高  
索引是稀疏索引，类似跳表

### 为什么ck效率这么高？ ###

1 分布式，这样可以支持集群，但是实际就用了一主一从已经满足。  
2 按列存储，列式存储可以仅读取需要的列，相对mysql可以有效减少IO  
3 数据分区,减少IO  
4 数据压缩，相对mysql10倍压缩，同样减少IO  
5 ck多线程并发处理执行计算；mysql针对单个sql单线程  
即：分布式+减少IO的3策略+多线程计算

### 缺点： ###

不支持事物  
单条插入特曼  
不支持高并发

# clickhouse简要介绍 #

ClickHouse是一个超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面（如GROUP BY），ClickHouse的查询更快，因此通常情况下在MySQL上进行聚合。

刚好现在有如下场景：每日千万条记录,,需要按条件筛选和聚合，对记录进行数据分析，只有写入和查询，没有数据更新很明显的MySQL无法支持，所以选用了clickhouse，并且比Vertica的快。

## clickhouse快的原因： ##

1）式列存储优势  
列式数据库的英文非常有效值的，从两个方面可以理解  
1。磁盘I / O的优化  
\- 作为列式存储，查询只需要访问所关心的列数据  
\- 列数据放在一起，数据格式类似，非常容易压缩，因此减少I / O数据量  
\- 输入输出的减少，内存可以腾出更多地方作为缓存  
2）索引数据分块，向量化和并发充分应用CPU  
由于数量行数特别大，数据的解压缩和计算将耗费非常多的CPU资源，为了提高CPU的效率，行业中通常是将数据转换成Vector的计算。例如行业比较流行的VectorWise方法。  
其基本想法就是将压缩的列数据整理成现代CPU容易处理的矢量模式，利用现代CPU的多线程，SIMD（单指令，多个数据，每次处理都是一批矢量数据，极大的提高了处理效率  
.CK不能简单看成一个数据库，它用了大量的并行计算方式，把单机性能压榨到极限  
Hadoop生态非常依赖集群的数量，通过缩小的方式，让计算发生在本地，分而治之，通过M后再R的方式，提 执行效率。  
在实际的使用过程中，很明显的感觉到，10台规模的Hadoop的和100台规模的Hadoop的无法同日而语，原因就在于数据打的不够散  
CK的方式，可以理解为，通过列式存储的方式，本身查询的时候就做了地图化，再对每一列做操作的时候，又使用向量化操作，等于是又增加了并发，因此，单机效率极高

## 索引效用实例-以[MergeTree][]  为例 ##

*  [MergeTree][]  系列的引擎，数据是由多组部分文件组成的，一般来说，每个月（译者注：CK目前最小分区单元是月）会有几个部分文件（这里的部分就是块）。
 *  每一个部分的数据，是按照主键进行字典序排列。例如，如果你有一个主键是（CounterID，Date），数据行会首先按照CounterID排序，如果CounterID相同，按照日期排序。
 *  主键的数据结构，看起来像是标记文件组成的矩阵，这个标记文件就是每间隔`index_granularity`（索引粒度）行的主键值。
 *  MergeTree引擎中，的默认`index_granularity`设置的英文8192。

**主键是（CounterID，Date）的存储示意图如下：**

*  ![20181219171346716][]
    
    首先按照CounterID排序，如果CounterID相同，按照日期排序
    
    --------------------
    
     *    
        主键是有序数据的稀疏索引。我们用图的方式看一部分的数据（原则上，图中应该保持标记的平均长度，但是用ASCI码的方式不太方便）。
     *  标记文件，就像一把尺子一样。主键对于范围查询的过滤效率非常高。对于查询操作，CK会读取一组可能包含目标数据的标记文件。
     *  例如，如果你的查询条件是CounterID IN（'a'，'h'），服务器将会读取标记文件为\[0,3\]和\[6,8\]之间对应的数据文件。
     *  如果你的查询条件是CounterID IN（'a'，'h'）并且指定了Date = 3，服务器将会读取标记文件为\[1,3\]和\[7,8\}之间对应的数据文件。
     *  有时，主键的过滤效果并不是很好，比如，只有第二列出现在查询条件中：
     *  如果查询条件只是Date = 3，服务器讲读取\[1,10）之间对应的数据文件。
     *  在上述例子中，标记文件除了0，其他90％的数据都需要扫描，虽然索引过滤效果不好，但是，仍然是可以跳过一些数据的。
     *  另一方面，如果每个CounterID对应多条数据，索引将会跳过更多的日期数据。（???）
     *  综合来讲，使用索引，总是会比全表扫描要高效一些的。

关于主键还有以下几点需要说明

稀疏索引会读取很多不必要的数据：读取主键的每一个部分，会多读取`index_granularity * 2`的数据。这对于稀疏索引来说很正常，也没有必要减少`index_granularity`的值.ClickHouse的设计，致力于高效的处理海量数据，这就是为什么一些多余的读取并不会有损性能。`index_granularity=8192`对于大多数场景都是比较好的选择。

--------------------

*  主键并不是唯一的，可以插入主键相同的数据行。
 *  主键的构成，同样可以存在函数表达式。
    
     *  如，（CounterID，EventDate，intHash32（UserID））
 *  上述例子中，通过使用哈希函数，把特定的用户名对应的CounterID和EVENTDATE做了聚合，顺便，这种聚合方式，可以在[样本][Link 1]这个功能中利用到。稀疏索引适用于海量数据表，并且，稀疏索引文件本身，放到内存是没有问题的

## clickhouse MergeTree原理分析 ##

`MergeTree` 允许您依据主键和日期创建索引，并进行实时的数据更新操作。`MergeTree` 是ClickHouse里最为先进的表引擎。请注意不要将  `MergeTree` 跟  [Merge][]  引擎混淆。

`MergeTree` 引擎在创建时接收以下4个参数，

*  日期字段的名称（索引字段）
 *  采样表达式（可选的）
 *  含有主键相关字段的元组
 *  稀疏索引的粒度（见下文）示例：

不使用采样表达式的例子：

MergeTree(EventDate, (CounterID, EventDate), 8192)

使用采样表达式的例子：

MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

以MergeTree作为引擎的数据表必须含有一个独立的  `Date` 字段。比如说，  `EventDate` 字段。这个日期字段必须是  `Date` 类型的（非  `DateTime` 类型）。

主键可以是任意表达式构成的元组（通常是列名称的元组），或者是单独一个字段。

抽样表达式（可选的）可以是任意表达式。这个表达式必须在主键中。的上面例子使用了  `CounterID` 的哈希  `intHash32` 作为采样表达式，近乎旨在地随机在  `CounterID` 状语从句：  `EventDate` 换速内打乱数据条目。而言之，我们当查询在使用中  `SAMPLE` \[主语\]时，我们就可以得到一个近乎随机分布的用户列表。

数据表将数据分割为小的索引块作为单位进行处理。每个索引块记录了指定的开始日期和结束日期。在你插入数据时，MergeTree就会对数据进行排序处理，以保证存储在索引块内的数据有序.MergeTree引擎会选择几个相邻的索引块进行合并（通常是较小的索引块），然后对二者合并，排序。

具体而言，向MergeTree表中插入数据时，引擎会首先对新数据执行递增排序而保存索引块;其后，数据索引块之间又会进一步合并，以减少总体索引块数量。因此，合并过程本身并无过多排序工作。

向MergeTree插入数据时，不同月份的数据会被自动分散在不同索引块中。不同月份的索引块不会被合并。这是为了便于本地化数据修改（以及备份）。

索引块合并时设有体积上限，以避免索引块合并产生庞大的新索引块。

除了保存索引块中的数据外，引擎会额外保存一个索引文件，以储存每  `index_granularity` 行的主键值和对应位置，这就构成了对有序数据的稀疏的索引。

对列而言，MergeTree在每`index_granularity`行的位置也写入了标记，从而确定数据所在的范围，以便查找。

当使用  `SELECT` 读取表内数据时，MergeTree会判断是否能够使用索引。以下两种情况里，索引将被使用：

1.  当  `WHERE` 语句或  `PREWHERE` 语句用于判断相等或不等判关系时（作为子句）;
2.  或当  `IN` 语句的对象为主键或者  `Date` 或者它们之间的逻辑关系。

因此，MergeTree能够快速查询一个或多个主键范围的值。在下面的示例中，MergeTree能够快速的查询一个明确的  `CounterID` ，指定范围的日期区间里的一个 明确的  `CounterID` ，各种  `CounterID`的集合等。

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
    SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
    SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

上面例子中的查询会使用基于日期和主键的索引索引也可以被用在更加复杂的查询之中;读取表的过程是按部就班进行的，所以使用索引绝不会比全表搜索耗时。

示例贴：

可以看到，下面的例子中，MergeTree无法使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

若要知晓MergeTree能否在查询中使用索引，请配置系统参数  [force\_index\_by\_date][force_index_by_date]  ，  [force\_primary\_key][force_primary_key]。

全局的索引之中仅仅保存了单个数据索引块的日期范围。然而，一个数据索引块可能包含很多日期的数据（甚至整月），MergeTree在数据索引块内部依照主键排序，然而用于分组的日期并不一定在数据表的首列。因此，在查询语句中，如果只有日期范围而没有限定主键范围，这将可能导致不必要的数据读取。

对于并发查询，MergeTree使用了多版本管理：当我们试图同时读取，写入数据时，查询操作将会在已经插入完毕的索引快中进行，而排除没有写入完毕的索引块，正在被写入的块因而不会受到干扰，这个过程没有使用任何锁机制，同时插入操作不会阻塞读取操作。

对MergeTree进行读取的操作会在引擎内部自动的并行执行。

MergeTree支持  `OPTIMIZE` 语句，它会调用额外的合并步骤。

它可以管理一张很大的数据表，我们也可以小批量，连续地向其添加数据，这正是MergeTree设计之初的衷。

它支持数据备份功能，见具体  [数据][Link 2][副本 ][Link 3]一章。

## 下面是clickhouse的安装步骤 ##

# #

安装与部署  
安装说明：参照  
 https://blog.csdn.net/m0\_37739193/article/details/79611560中的centos 7系统安装办法，在内网192.168.\*\*.\*\*上通过https：// packagecloud. io / altinity / clickhouse /下载el / 7对应的rpm包（测试环境使用版本为14年12月18日）进行安装。

配置系统-  
    数据目录配置以及集群配置

1，数据目录配置

修改/etc/clickhouse-server/config.xml

<！ - 数据目录的路径，带有斜杠.->   
<path> / data / clickhouse / data / clickhouse / </ path>

<！ - 用于处理硬查询的临时数据的路径.->   
<tmp\_path> / \*\*\* / clickhouse / tmp / </ tmp\_path>

<！ - 具有用户提供的文件的目录，可通过'file'表函数访问.->   
<user\_files\_path> / data / clickhouse / user\_files / </ user\_files\_path>

<！ - 配置文件的路径，包含用户，访问权限，设置配置文件，配额.->  
< users\_config  > users.xml </ users\_config>

2，服务的启动与停止

启动/etc/init.d/clickhouse-server start

停止/etc/init.d/clickhouse-server停止

3，集群配置

参考https://www.jianshu.com/p/ae45e0aa2b52

需要修改/etc/metrika.xml文件，以一主一备为例

<clickhouse\_remote\_servers>   
<zp\_ck\_cluster>

<！ - 集群分片配置 - >   
<shard> true </ internal\_replication>   
<replica>   
<host> 192.168.\*.\*\* </ host>   
<port> 9000 </ port>   
</ replica>   
<replica>   
<host> 192.168. \*.\* </ host>   
<port> 9000 </ port>   
</ replica>   
</ shard>   
</ zp\_ck\_cluster>   
</ clickhouse\_remote\_servers>

<！ - 本机分片和备份配置 - >   
<宏>   
<shard> 01 </ shard>   
<replica> 01 </ replica>   
</ macros>

<！ - 网络配置 - >   
<networks>   
<ip> :: / 0 </ ip>   
</ networks>

<！ - ZK - >   
<zookeeper-servers>   
<node index =“1”>   
<host> 192.\*\*.\*.\* </ host>   
<port> \*\*\*</ port>   
</ node>   
</ zookeeper-servers>

复制创建³³表  
          需要在多台机器中同时创建³³复制表：

CREATE TABLE dau\_replica（user\_id Int32，time Date）ENGINE = ReplicatedMergeTree（'/ clickhouse / tables / dau\_replica'，'\{replica\}'，time，（user\_id），8192）;

CREATE TABLE bi.geek\_info ( user\_id Int32,  name String,  gender String,  age Int32,  degree String,  work\_years String,  fresh\_graduate String,  completion String,  extra\_resume String,  extra\_status String,  user\_status String,  apply\_status String,  resume\_status String,  resume\_time String,  resume\_num String,  his\_resume\_num String,  active\_time String,  unactive\_days Int32,  platform String,  add\_time String,  com\_time String,  is\_985 String,  is\_211 String,  overseas\_school String,  edu\_description String,  work\_description String,  industry\_1 String,  industry\_2 String,  industry\_3 String,  industry\_4 String,  industry\_5 String,  industry\_6 String,  industry\_7 String,  industry\_8 String,  industry\_9 String,  l1\_name\_a String,  l2\_name\_a String,  l3\_name\_a String,  l1\_name\_b String,  l2\_name\_b String,  l3\_name\_b String,  l1\_name\_c String,  l2\_name\_c String,  l3\_name\_c String,  city\_a String,  city\_b String,  city\_c String,  level\_a String,  level\_b String,  level\_c String,  salary\_a String,  salary\_b String,  salary\_c String,  is\_boss String,  data\_dt Date) ENGINE =   
  MergeTree() PARTITION BY toDate(add\_time) ORDER BY (user\_id, unactive\_days) SAMPLE BY user\_id SETTINGS

\- 执行插入数据

插入dau\_replica（USER\_ID，时间）值（1， '2018年9月30日'）;

插入数据后，在不同的客户端上查询，看是否都能在本机上查到对应数据，如果有说明复制功能生效

## 下面是clickhouse建表及数据迁移部分 ##

创建表语句  
下面的示例带有分区

CREATE TABLE bi.dau_baseinfo
    （user_id Int32，sex Int8，age Int32，user_type Int8，refresh_graduate Int8，work_years Int16，degree Int8，boss_type Int8，status Int8，platform Int8，platform_detail Int8，reg_time Date ，complete_time Date，not_active_day Int32，time_type Int8，date8 String，ds Date）
    ENGINE = MergeTree PARTITION BY ds ORDER BY ds SETTINGS index_granularity = 8192

下面的不带有分区

create table bi.dau_city(
    
    code Int16
    
    ,name String 
    
    ,level Int8
    
    ,ds Date
    
    )ENGINE=MergeTree(ds,intHash32(level),8192)

使用导数步骤（从hive到clickhouse）  
1将hive中数据导入到txt中（注意还可能需要进行字符的转换）

#!/bin/bash
    source /etc/profile
    
    #date=$1
    date=`date +%Y%m%d`
    yesteday=`date -d "$date -1days" +%Y-%m-%d`
    filePath='/data1/bi_analysis/mazhen/data/dau_pay/'
    echo $date $yesteday
    
    hive -e"
    select
         case when user_id = '' then -99 else user_id end 
        ,case when boss_type = '' then -99 else boss_type end  
        ,case when city_code = '' then -99 else city_code end
        ,case when scale_code = '' then -99 else scale_code end
        ,case when is_pay = '' then -99 else is_pay end
        ,case when online_pay = '' then -99 else online_pay end
        ,case when offline_pay = '' then -99 else offline_pay end
        ,case when offline_add_time = '' then '1900-01-01 00:00:00'  else offline_add_time end
        ,case when offline_update_time = '' then '1900-01-01 00:00:00'  else offline_update_time end
        ,case when online_first_time = '' then '1900-01-01 00:00:00'  else online_first_time end
        ,case when online_last_time = '' then '1900-01-01 00:00:00'  else online_last_time end
        ,date8
        ,ds
         from
    (
    select 
         nvl(user_id,-99)                                     as user_id
        ,nvl(boss_type,-99)                                   as boss_type
        ,nvl(city_code,-99)                                   as city_code   
        ,nvl(scale_code,-99)                                  as scale_code    
        ,nvl(is_pay,-99)                                      as is_pay
        ,nvl(online_pay,-99)                                  as online_pay
        ,nvl(offline_pay,-99)                                 as offline_pay
        ,nvl(offline_add_time,'1900-01-01 00:00:00')          as offline_add_time 
        ,nvl(offline_update_time,'1900-01-01 00:00:00')       as offline_update_time
        ,nvl(online_first_time,'1900-01-01 00:00:00')         as online_first_time
        ,nvl(online_last_time,'1900-01-01 00:00:00')          as online_last_time
        ,nvl(date8,'1900-01-01')                              as date8
        ,nvl(concat(substr(date8,1,4),'-',substr(date8,5,2),'-',substr(date8,7,2)),'1900-01-01')      as ds
        from bi_analysis.pay_boss_dau where ds = '$yesteday'    ) t
    ">$filePath'dau_pay_'$yesteday'.txt'
    cd  $filePath
    tar cvf 'dau_pay_'$yesteday'.tar.gz' 'dau_pay_'$yesteday'.txt' 
    #删除压缩前的txt文件
    rm 'dau_pay_'$yesteday'.txt'
    #解压缩命令
    #tar -xf $filePath'dau_pay_'$yesteday'.tar.gz' 
    #拷贝文件
    scp -P 13122 'dau_pay_'$yesteday'.tar.gz'  bi_analysis@172.**.***.***:/home/bi_analysis/hive_data/dau/pay/
    #删除压缩后的文件
    #rm 'dau_pay_'$yesteday'.tar.gz'

2将txt导入clickhouse

#!/bin/bash
    function ergodic(){
        for file in ` ls $1 `
    #    do  
    #        if [ -d $1"/"$file ]  
    #        then  
    #             ergodic $1"/"$file  
    #        else  
    #             wc -L $1"/"$file | cut -d' ' -f1 >> /home/huanghongbo/out  
    #        fi  
    #    done
        do
          echo $file
          gunzip "dau_baseinfo/"$file
        done
        for file2 in ` ls $1 `
        do
           echo $file2
           tar -xvf "work_baseinfo/"$file2 -C "dau_baseinfo"
        done
        
        for file3 in ` ls $1 `
            do
               if [ "${file3##*.}"x = "txt"x ]
               then
                      cat "work_baseinfo/"$file3 | clickhouse-client -d bi --host 172.18.37.250 --password ****** --query="INSERT INTO work_baseinfo FORMAT TabSeparated"  
               fi
            done
        
    
    }
    INIT_PATH="/data/hive-data/dau_baseinfo/"
    ergodic $INIT_PATH

索引部分参考于：HTTPS：//blog.csdn.net/bluetjs/article/details/80322497

MergeTree引擎详解：  
https ：//blog.csdn.net/lu\_gt/article/details/79278283  
更多的引擎介绍：可以根据不同的需求选择不同的引擎  
https://www.jianshu.com/p/dbca0f5ededb

[MergeTree]: https://clickhouse.yandex/reference_en.html#MergeTree
[20181219171346716]: /images/20220504/e58d001865784849904ab6ff6d67122d.png
[Link 1]: https://clickhouse.yandex/reference_en.html#SAMPLE
[Merge]: https://github.com/lugt/ch_in_chinese/blob/master/zh/table_engines/merge.md
[force_index_by_date]: https://github.com/lugt/ch_in_chinese/blob/master/zh/operations/settings/settings.md#settings-settings-force_index_by_date
[force_primary_key]: https://github.com/lugt/ch_in_chinese/blob/master/zh/operations/settings/settings.md#settings-settings-force_primary_key
[Link 2]: https://github.com/lugt/ch_in_chinese/blob/master/zh/table_engines/replication.md
[Link 3]: https://clickhouse.yandex/docs/en/table_engines/replication.html