clickhouse笔记

官网文档：https://clickhouse.tech/docs/zh/

spark读写clickhouse

spark读取clickhouse数据

//一：这种jdbc的了连接加载的是全量表数据

val prop = new java.util.Properties
prop.setProperty("user", "default")
prop.setProperty("password", "123456")
prop.setProperty("driver", "ru.yandex.clickhouse.ClickHouseDriver")

val readDataDf = sparkSession
  .read
  .jdbc("jdbc:clickhouse://hadoop05:8123",
    "table_op",
    prop)
  .where("AND Year=2020 AND Month=5 AND LocationTime>='2020-05-05 09:00:00' AND LocationTime<='2020-05-05 18:00:00'")


//二：这种是添加过滤条件加载部分数据（推荐这种，因为如果你的表很大的话spark任务driver启不来）
//将过滤查询提前存到临时表
val tablename = s"(select * from table_op where LocationTime between '$start_time' and '$end_time') temp"

val readDataDf = session.read
  .format("jdbc")
  .option("url", "jdbc:clickhouse://hadoop05:8123")
  .option("fetchsize", "500000")
  .option("driver", "ru.yandex.clickhouse.ClickHouseDriver")
  .option("user", "default")
  .option("password", "123456")
  .option("dbtable", tablename)
  .load()

spark写入clickhouse

val prop = new java.util.Properties
prop.setProperty("user", CommonConfig.CLICK_USER)
prop.setProperty("password", CommonConfig.CLICK_PASSWORD)
prop.setProperty("driver", CommonConfig.CLICK_DRIVER)

resultDataDf
  .write
  .mode(SaveMode.Append)
  .option("batchsize", "500000")
  .option("isolationLevel", "NONE")
  .option("numPartitions", "1")
  .jdbc(CommonConfig.CLICK_URL,
    "table_op_2020", prop)

clickhouse建表及数据操作

启动clickhouse客户端

clickhouse-client --port 9020 -h hadoop05 -d default -m -u default --password 123456

建表（本地表，分布式表）

一次性在集群每个机器上建立本地表(因为加了on cluster,所以只需在任意一台机器上执行一次建表，集群中每台都会生成本地表；如果不加on cluster,则需要去每台机器都执行一遍建表语句生成本地表)
CREATE TABLE `table_2020` ON CLUSTER fangteCluster (
        `Id` String,
        `Udid` String,
        `LocationTime` String,
        `Gcode` String,
        `IP` String,
        `IMEI` String,
        `IDFA` String,
        `UserId` String,
        `Year` UInt16,
        `Month` UInt8,
        `Day` UInt8
)ENGINE = MergeTree()
PARTITION BY (Year,Month,Day)
ORDER BY (Gcode,Udid) 
SETTINGS index_granularity=8192, enable_mixed_granularity_parts=1;

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句	
enable_mixed_granularity_parts=1 启用以字节为单位的索引粒度，有助于提升大表的查询性能

一次性在集群所有机器上建立分布式表
CREATE TABLE table_op_2020 ON CLUSTER mycluster AS table_2020 ENGINE = Distributed(mycluster, default, table_2020, rand());
只在一台机器上建分布式表
CREATE TABLE table_op_2020 AS table_2020 ENGINE = Distributed(mycluster, default, table_2020, rand());

即使只在一个机器上建分布式表也是可以的。只是说，在每个机器上建分布式表的话，那么可以在每个服务器上都做分布式查询了。
分布式表本身并不存储数据，只是提供了一个可以分布式访问数据的框架，
查询分布式表的时候clickhouse会自动路由到对应的每个本地表中去查询数据，
可以直接往分布式表中写数据，clickhouse会自动按照建表时指定的rand()方式来分配数据和自平衡

一个分区是一个文件夹
查看表分区信息

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

这里的name值就是分区的目录名称，然后你就可以去这个目录里面查看数据存储的结构了
如果找不到目录地址用：find / -name 2019-1-11_1523_1523_0

每个分区里一个字段存储为一个被压缩的小文件(.bin)，以及对应的索引/顺序标识文件(.mrk2)，还有记录表信息的txt文件，以及分区字段和索引字段的索引文件(.idx)，空值会有特殊的文件记录

修改表名（本地表适用，分布式表删掉重新建就好了）

rename table table_new to table_2019 on cluster mycluster;

清空表数据

truncate table db.table on cluster fangteCluster;不支持Distributed引擎

按条件删除数据（只支持mergetree引擎的本地表，要去集群中每个节点都执行，因为数据是分散在各个节点的）
不能是分布式表，会报：Mutations are not supported by storage Distributed

alter table local_table delete where LocationTime <= '2018-12-31 23:59:59';

插入数据

insert into table_op_2020 select *,toDayOfMonth(toDateTime(LocationTime)) as Day from table_op where Year=2020 and Month=1 and LocationTime >= '2020-01-06 00:00:00' and LocationTime < '2020-05-07 23:59:59';

查询数据可以通过查看日志文件clickhouse-server.log 来分析查询索引和分区是否命中，进而调整查询语句或优化表索引

clickhouse数据迁移方案

1.自己写spark代码，从一个集群读出，再写入新集群
2.remote table （适合小表,简单）

insert into sink_customer_op select * from remote('0.0.0.0','default.sink_customer_op','default','123123');

将近6000万条数据耗时2分钟

说明一下我这两个集群都在公司内部，属于内网千兆网传输，如果你是有集群在外部云上的话比如腾讯云，和内网集群做数据迁移就会有带宽限制，速度就会慢很多。

3.clickhouse-copier (https://clickhouse.tech/docs/zh/operations/utilities/clickhouse-copier/)
适合大表，依赖zookeeper,配置较复杂

clickhouse为什么这么快

1.支持分区的过滤和列级别的稀疏索引

• 索引粒度默认是8192条数据进行一次记录 二分查找+遍历可以快速索引到指定的数据
• 数据sharding分片可以充分利用集群的大规模并行计算能力

2.存储和执行耦合，避免网络数据传输的开销
3.列式存储对OLAP分析天然友好
4.向量化的执行引擎：思想是均摊开销，一个批次（多行数据8192）调用一次计算引擎，每次调用的开销是相对恒定的，所以计算框架的总开销就减小了
5.动态代码生成，消除开销
6.高吞吐的写入：基于批次batch写入的，直接落盘，采用类似LSM tree的机构（对数据排序及拆分，能有效提升写吞吐），数据写入时是顺序append写，写入后会定期compaction压缩合并，小文件合并成大文件，压缩时也是多个段merge sort后顺序写回磁盘。顺序写充分利用了磁盘的吞吐能力。
7.通过主备复制提供了高可用能力，多个副本都是处于active模式，查询的时候根据query下发策略进行查询

所遇问题：复杂查询时会超出sql查询内存上线，查询会被kill（但是这个时候其实我们的max_memory_usage即单个SQL在单台机器最大内存使用量已经调的很大了）

配置最大外部的排序参数和聚合参数(如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成)
当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)；
在进行group by的时候，内存使用量已经达到了max_bytes_before_external_group_by的时候就进行写磁盘(基于磁盘的group by）
clickhouse的join是右表广播的，所以小表应该放到右表位置