1.基础知识
clickhouse的特点：
- DBMS 的功能：使用SQL
- 列式存储: 在列的统计计算上有优势，便于压缩，节省磁盘空间
- 高吞吐写入能力：顺序写，充分利用了磁盘的吞吐能力
- 数据分区与线程级并行：多核并行处理，单条查询就可以利用所有CPU,对应多并行查询优势不大
- 多样化引擎： TinyLog（不支持索引，无并发控制），Memory （不支持索引），MergeTree（支持索引和分区） ，ReplacingMergeTree（有去重机制，对于order by后的字段分组去重，分区内有效，合并分区时触发，不定时触发），SummingMergeTree
- 分区：提高查询效率
- 分片：横向扩容，解决数据水平切分问题，
- 副本：高可用，提高容错性
2. 语法知识
1. 数据类型
- 整型：Int8,Int16,Int32,Int64；UInt8,UInt16,UInt32,UInt64
- 浮点型：Float32，Float64
- Decimal 型 ：Decimal32(s)，Decimal64(s)，Decimal128(s)
- 字符串：String(最常用)，FixedString(N)
- 枚举： Enum8 ， Enum16
- 时间类型：Date （‘2019-12-16’ ）， Datetime （‘2019-12-16 20:50:10’），Datetime64（‘2019-12-16 20:50:10.66’）
2. 类型转换
- toInt64OrNull('123123'), toInt8OrNull('123qwe123')
- CAST(x, T)，CAST（x AS t）
- toString
- toDate，toDateTime，toDate(toDateTime(unix_timestamp))
3. 建表语句
- 单节点建表：
必要：引擎，order by（order by中为索引）
可选：分区，主键（必须为order by的最左边的字段）
建表语句：
create table t_order_mt (id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id);
- 副本建表：副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表
engine =ReplicatedMergeTree
指明副本：rep_102
第一个参数：/clickhouse/table/{shard}/{table_name}
副本建表
create table t_order_rep2 (id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime ) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102') partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id);
- 分片建表：分布式表和本地表。分布式表不存储数据
本地表：
create table st_order_mt on cluster gmall_cluster ( id UInt32,sku_id String, total_amount Decimal(16,2),create_time Datetime ) engine =ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt','{replica}') partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id);
分布式表：
create table st_order_mt_all2 on cluster gmall_cluster ( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime )engine = Distributed(gmall_cluster,default, st_order_mt,hiveHash(sku_id));
3. select as建表
create table ods_peakreport.zss_tmp_date ENGINE = MergeTree() order by num_date as select * from ...
4. clickhouse优化
- 数据类型：避免频繁转换，避免空值
- 写入优化：尽量不要执行单条或小批量删除和插入操作，不要一次写入太多分区，会给merge带来压力
- 语法规则优化：
- 避免select
- 大表关联小表：小表放右边
- prewhere替代where
- orderby 结合 where、limit
- 物化视图
- 分布式表使用 GLOBAL：两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字
5. 物化视图
- 概念：ClickHouse 的物化视图是一种查询结果的持久化，物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，它也像是一张时刻在预计算的表。
- 优点：预计算特性，查询速度快
缺点：流式数据的使用场景，是累加式的技术，很消耗机器资源 - 语法：
CREATE MATERIALIZED VIEW hits_mv ENGINE=SummingMergeTree PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) AS SELECT UserID, EventDate, count(URL) as ClickCount, sum(Income) AS IncomeSum FROM hits_test WHERE EventDate >= '2014-03-20'
6. 数据库引擎MaterializeMySQL
- 概念：数据库映射到 MySQL 中 的某 个 database ，并 自动 在 ClickHouse 中 创建 对 应的 ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。
- 特点：
（1）MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。
（2）MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。 其中， _version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1。 - 语法
（1）开启ck的物化引擎
set allow_experimental_database_materialize_mysql=1;
（2）ClickHouse 中创建 MaterializeMySQL 数据库
create database test_binlog ENGINE = MaterializeMySQL('hadoop1:3306','testck','root','000000');
其中 4 个参数分别是 MySQL 地址、databse、username 和 password。
（3）测试
use test_binlog; show tables; select * from t_organization; select * from t_user;
