一、ClickHourse简介

OLAP场景的关键特征
1.大多数是读请求 
2.数据总是以相当大的批(> 1000 rows)进行写入 
3.不修改已添加的数据 
4.每次查询都从数据库中读取大量的行，但是同时又仅需要少量的列 
5.宽表，即每个表包含着大量的列 
6.较少的查询(通常每台服务器每秒数百个查询或更少) 
7.对于简单查询，允许延迟大约50毫秒 
8.列中的数据相对较小： 数字和短字符串(例如，每个URL 60个字节) 
9.处理单个查询时需要高吞吐量（每个服务器每秒高达数十亿行） 
10.事务不是必须的 
11.对数据一致性要求低 
12.每一个查询除了一个大表外都很小 
13.查询结果明显小于源数据，换句话说，数据被过滤或聚合后能够被盛放在单台服务器的内存中]()]()

mysql的引擎是跟数据库绑在一起的

二、特点

1.列式存储
2.DBMC功能
3.多样化引擎
4.高吞吐能力LSM 预写日志，排序，合并，默认七天一次大合并
5.数据分区和线程并行类似hive的分区表弊端：对于单条查询使用多cpu，就不利于同时并发多条查询对于高qps（每秒查询的sql语句）的查询业务，并不是强项
6.一些限制没有完整的事务支持缺少高频率，低延迟的修改或删除已存在数据的能力，仅能用于批量删除或修改数据，单符合GDPR

三、操作**

--启动服务端
sudo systemctl start clickhouse-server
--启动客户端
clickhouse-client -mclickhouse-client --host=hadoop162 -m
--存放表订单路径
[root@hadoop162 default]# pwd /var/lib/clickhouse/data/default"错误解决办法："
-- 去zookeeper删除clickhouse相关节点deleteall /clickhouse/tables/gmall
-- 删除 
/var/lib/clickhouse/data/gmallxcall sudo rm -rf /var/lib/clickhouse/data/gmall
-- 重启clickhourse集群xcall sudo systemctl restart clickhouse-server

四、表引擎 (engine)

1.TinyLog2.Memory
-----------------------------------------------------------------------
3.MergeTree (合并树)  ——-用的比较多3.1 建表语句
create table t_order_mt(id UInt32,sku_id String,total_amount Decimal(16,2),create_time  Datetime) engine=MergeTreepartition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id)3.2 partition by 分区--作用：防止全表扫描，降低扫描的范围，优化查询速度。--分区目录：MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件		就会保存到不同的分区目录中。--数据写入与分区合并：任何一个批次的数据写入都会"产生一个临时分区"，不会纳入任何一个已有的分区。写入后的		某个时刻（大概10-15分钟后），clickhouse会自动执行合并操作（等不及也可以手动通过		optimize执行），把临时分区的数据，合并到已有分区中。"手动合并："optimize table xxxx [final]optimize table xxxx [final] final3.3 primary key主键--clickhouse的主键不是唯一标识，有可能重复clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是		唯一约束。这就意味着是可以"存在相同primary key的数据的"。--依据查询中的where条件--稀疏索引：好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。由于稀疏索引比较少, 所以理论上可以完全加载到内存中, 从提高查询速度3.4 order by  "唯一一个必填项"--order by 设定了分区内的数据按照哪些字段顺序进行有序保存。--唯一一个必填项"要求：主键必须是order by字段的前缀字段。"分区内按id排序3.5 TTL(time to life)--列级别的TTL针对某列数据过期 "TTL create_time+interval 10 SECOND"--表级别的TTL针对整张表数据过期 "MODIFY TTL create_time + INTERVAL 10 SECOND;"--判断时间-----------------------------------------------------------------------
4.ReplacingMergeTree分区内去重--什么样的数据是重复sort by字段相同认为重复--去重时机数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先			作出计划。有一些数据可能仍未被处理。即使使用optimize 也不能保证一定会去重--去重范围："分区内去重"如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。"该引擎使用场景:ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。"------
create table t_order_rmt(id UInt32,sku_id String,total_amount Decimal(16,2) ,create_time  Datetime 
) engine =ReplacingMergeTree(create_time)   --引擎
partition by toYYYYMMDD(create_time)  --分区
primary key (id)   --主键
order by (id, sku_id);  -- 排序  [按这个去重]
-----------------------------------------------------------------------------
5. SummingMergeTree对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。Clickhouse 为了这种场景，提供了一种能够"预聚合"的引擎，SummingMergeTree."结论:"1.以SummingMergeTree(字段)中指定的列作为汇总数据列。可以填写多列必须数字列，如果不		填，以所有非维度列且为数字列的字段为汇总数据列\2.以order by 的列为准，作为维度列(group by …)3.其他的列保留第一行。4.不在一个分区的数据不会被预聚合。5.聚合发生的时机不确定6.查询的时候仍然需要sql聚合语句

create table t_memory(id Int16,name String) engine = Memory

找分区：

五、sql操作

having要写在groupby后面，他可以接窗口函数

5.1 增

5.1.1 插入数据

insert into  t_order_mtvalues(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,(102,'sku_002',2000.00,'2020-06-01 12:00:00'),(103,'sku_004',2500.00,'2020-06-01 12:00:00'),(104,'sku_002',2000.00,'2020-06-01 12:00:00')(105,'sku_003',600.00,'2020-06-02 12:00:00'),(106,'sku_001',1000.00,'2020-06-04 12:00:00'),(107,'sku_002',2000.00,'2020-06-04 12:00:00'),(108,'sku_004',2500.00,'2020-06-04 12:00:00'),(109,'sku_002',2000.00,'2020-06-04 12:00:00'),(110,'sku_003',600.00,'2020-06-01 12:00:00')

5.1.2 with rollup:从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with rollup;

5.1.3 with cube:各种维度组合进行聚合

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with cube;

5.1.4 with taotals：仅仅多了一个总计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with totals;

5.2 删

5.3 查

"支持如下操作:"
WITH clause
FROM clause
SAMPLE clause
JOIN clause
PREWHERE clause
WHERE clause
GROUP BY clause
LIMIT BY clause
HAVING clause
SELECT clause
DISTINCT clause
LIMIT clause
UNION ALL clause
INTO OUTFILE clause
FORMAT clause
"不支持"
窗口函数，不支持自定义函数
"group by 的字句特殊说明"
GROUP BY 操作增加了with rollup\with cube\with total 用来计算小计和总计。

5.4 改

'新增字段'
alter table tableName  add column  newcolname  String after col1;'修改字段类型'
alter table tableName  modify column  newcolname  String    ；'删除字段'
alter table tableName  drop column  newcolname   ;

六、高可用

副本的目的主要是保障数据的高可用性，即使一台clickhouse节点宕机，那么也可以从其他服务器获得相同的数据。

clickhouse的副本严重依赖zookeeper, 用于通知副本server状态变更

副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复本表和非复本表。

6.1 复本写入流程

6.2 配置规划

hadoop102	hadoop103	hadoop104
zookeeper	zookeeper	zookeeper
clickhouse	clickhouse

6.3 在hadoop103安装clickhouse

参考第3章

6.4 创建配置文件:metrika.xml

6.4.1 分别在hadoop102和hadoop103创建配置文件metrika.xml,配置zookeeper地址

sudo vim /etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?><yandex><zookeeper-servers><node index="1"><host>hadoop162</host><port>2181</port></node><node index="2"><host>hadoop163</host><port>2181</port></node><node index="3"><host>hadoop164</host><port>2181</port></node></zookeeper-servers></yandex>

6.4.2 告诉clickhouse刚刚创建的文件的目录

在hadoop102和hadoop103添加如下配置

sudo vim /etc/clickhouse-server/config.xml

找到节点, 在下面添加如下内容

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

6.5 分别在hadoop162和hadoop163上建表

clickhouse的复本是****表级别****的. 有些语句不会自动产生复本, 有些语句会自动产生复本

Ø 对于 INSERT 和 ALTER 语句操作数据会在压缩的情况下被复制

Ø 而 CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制

所以建表的时候, 需要在2个节点上分别手动建表

在hadoop162上建表

create table rep_t_order_mt (id UInt32,sku_id String,total_amount Decimal(16,2),create_time  Datetime) engine =ReplicatedMergeTree('/clickhouse/tables/01/rep_t_order_mt2020','rep_hadoop162')partition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id);

在hadoop163上建表

create table rep_t_order_mt (id UInt32,sku_id String,total_amount Decimal(16,2),create_time  Datetime) engine =ReplicatedMergeTree('/clickhouse/tables/01/rep_t_order_mt2020','rep_hadoop163')partition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id);

• 说明

ReplicatedMergeTree('/clickhouse/tables/01/rep_t_order_mt2020','rep_hadoop163')

​ 参数1: 该表在zookeeper中的路径.

​ /clickhouse/table/{shard}/{table_name} 通常写法,

​ shard表示表的分片编号, 一般用01,02,03…表示

​ table_name 一般和表明保持一致就行

​ 参数2: 在zookeeper中的复本名. 相同的表, 复本名不能相同

• 在hadoop162上插入数据

insert into  rep_t_order_mtvalues(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,(102,'sku_002',2000.00,'2020-06-01 12:00:00'),(103,'sku_004',2500.00,'2020-06-01 12:00:00'),(104,'sku_002',2000.00,'2020-06-01 12:00:00'),(105,'sku_003',600.00,'2020-06-02 12:00:00')

• 分别在hadoop102和hadoop103查询

七、分片集群（高并发）

​ 复本虽然能够提高数据的可用性，降低丢失风险，但是对数据的横向扩容没有解决。每台机子实际上必须容纳全量数据。

​ 要解决数据水平切分的问题，需要引入分片的概念。

​ 通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上。在通过Distributed表引擎把数据拼接起来一同使用。

​ Distributed表引擎本身不存储数据，有点类似于MyCat之于MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

7.1 读写原理

7.2 分片集群规划

hadoop102	hadoop102	hadoop102
distribute
shard1 replica1	shard1 replica2	shard2 replica1

说明:

1. shard1 一共两个复本(hadoop102, hadoop103)

2. shard2 只有一个复本(hadoop104)

7.3 配置分片集群

在hadoop102, 打开配置文件:

sudo vim /etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?><yandex><clickhouse_remote_servers><gmall_cluster> <!-- 集群名称--> <shard>     <!--集群的第一个分片--><internal_replication>true</internal_replication><replica>   <!-- 该分片的第一个副本 --><host>hadoop162</host><port>9000</port></replica><replica>   <!-- 该分片的第二个副本--><host>hadoop163</host><port>9000</port></replica></shard><shard>  <!--集群的第二个分片--><internal_replication>true</internal_replication><replica>   <!-- 该分片的第一个副本--><host>hadoop164</host><port>9000</port></replica></shard></gmall_cluster></clickhouse_remote_servers> <zookeeper-servers><node index="1"><host>hadoop162</host><port>2181</port></node><node index="2"><host>hadoop163</host><port>2181</port></node><node index="3"><host>hadoop164</host><port>2181</port></node></zookeeper-servers><!-- 宏: 将来建表的时候, 可以从这里自动读取, 每个机器上的建表语句就可以一样了 相当于变量 --><macros><shard>01</shard>  <!-- 不同机器放的分片索引不一样,  hadoop103,hadoop104需要更改 --><replica>hadoop163</replica>  <!-- 不同机器放的副本数不一样, hadoop103,hadoop104需要更改, 以主机命名比较方便--></macros>
</yandex>

分发metrika.xml到hadoop103,hadoop104

1. 不要忘记先在hadoop104安装clickhouse

2. 分发/etc/clickhouse-server/config.xml

3. 分发/etc/clickhouse-server/config.d/metrika.xml

hadoop103的宏

​

​ 01

​ hadoop163

hadoop104 的宏

​

​ 02

​ hadoop164

7.4 3个节点创建数据库

分别在hadoop102,hadoop103,hadoop104上创建数据库gmall

create database gmall;

7.5 任意节点创建本地表

选择任意一节点创建本地表, 会自动同步到其他节点

create table st_order_mt_gmall on cluster gmall_cluster (id UInt32,sku_id String,total_amount Decimal(16,2),create_time  Datetime) engine =ReplicatedMergeTree('/clickhouse/tables/gmall/{shard}/st_order_mt_gmall','{replica}')partition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id);

7.6 创建分布式表 st_order_mt_gmall_all

在hadoop102创建分布式表

create table st_order_mt_gmall_all on cluster gmall_cluster(id UInt32,sku_id String,total_amount Decimal(16,2),create_time  Datetime)engine = Distributed(gmall_cluster,gmall, st_order_mt_gmall,hiveHash(sku_id));

7.7 通过分布式表添加数据

insert into  st_order_mt_gmall_all values(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,(202,'sku_002',2000.00,'2020-06-01 12:00:00'),(203,'sku_004',2500.00,'2020-06-01 12:00:00'),(204,'sku_002',2000.00,'2020-06-01 12:00:00')(205,'sku_003',600.00,'2020-06-02 12:00:00')

7.8 查询数据

通过分布式表查询

select * from st_order_mt_gmall_all;

可以查询到所有数据!

通过本地表查询

select * from st_order_mt_gmall;

​ 只能查到当前节点的分片数据