大数据学习教程SD版第十三篇【Clickhouse】

大数据学习教程SD版第十三篇【Clickhouse】

Clickhouse 列式数据库

快，简单，yandex开源，C++编写，分析型数据库，SQL查询，列 *** 作快，多样化引擎，高吞吐写入（LSM Tree），多版本数据（Hbase），顺序Append写，数据分区，线程级并行（单条查询能利用整机所有CPU），不适合初始存储，尽量避免join *** 作（不快，右表加载到内存）

1. Clickhouse 安装

• 准备工作

1. 关闭防火墙

# status
systemctl status firewalld

2. 取消打开文件数限制，并分发配置

/etc/security/limits.conf

/etc/security/limits.d/20-nproc.conf

* soft nofile 65535
* hard nofile 65535
* soft nproc 131072
* hard nproc 131072

ulimit -a ：查看修改后的效果

3. 安装依赖(三台)

yum install -y libtool
yum install -y *unixODBC

4. 取消selinux，同步配置,重启生效

/etc/selinux/config

SELINUX=disabled

临时关闭

setenforce 0

• 安装Clickhouse

1. 下载并安装rpm包(三台)

# rpm包安装之后的路径关系
bin/  -> /usr/bin/xxx
conf/ -> /etc/xxx
lib/  -> /var/lib/xxx
log/  -> /var/log/xxx

2. 修改配置，同步配置

/etc/clickhouse-server/config.xml

::

3.启动

# 会开机自启，关闭开启命令
systemctl disable clickhouse-server

# start
systemctl start clickhouse-server
clickhouse-client -m

2. Clickhouse 数据类型 数据类型Clickhouse整型Int、Nullable(Int) Int 默认是Int32浮点型float、double布尔型无Decimal型Decimal(20,4)、Nullable(Decimal(20,4))字符串String枚举类型Enum8、Enum16时间类型Date、Datetime、数组Array[T]

# enum example
CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

select * from t_enum;
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
# 查看值
SELECt CAST(x, 'Int8') FROM t_enum;

# array example
SELECt array(1, 2) AS x, toTypeName(x) ;
SELECT [1, 2] AS x, toTypeName(x);

3. Clickhouse 表引擎

引擎名称大小写敏感

引擎名称引擎特点TinyLog测试使用，列文件磁盘存储，不支持索引，无并发Memory测试使用，内存存储MergeTree最强大，支持索引和分区(分区字段必须为表中字段)，TTL 生命周期ReplacingMergeTreeMergeTree 子类，去重SummingMergeTreeMergeTree 子类，针对指定列聚合 *** 作，提供“预聚合”

关于数据分区：任何一个批次写入时会写入零食分区，10-15min后，会合并到已有分区，可以手动合并

optimize table xxxx final;

关于主键：只提供一级索引，不同于MySQL的唯一约束

关于稀疏索引：两个相邻索引之间存在数据的间隔

关于建表order by： 必须参数，并且主键必须是 order by 字段的前缀字段

关于TTL ：列的数据失效时间或者表修改失效

关于去重：只会在合并过程中出现，并且只在分区内部进行去重，所以并不能保证全局去重，唯一建：order by的字段

4. Clickhouse SQL 4.1 DML

# 1.insert
insert into table_name1 values(),()……
insert into table_name1 select xxx from table_name2
# 2.delete
alter table table_name1 delete where xxx=xxx;
truncate table table_name1;
# 3.update
alter table table_name1 update xxx=xxx where xxx=xxx;

# 4.query

## 4.1 with rollup : 从右至左去掉维度进行小计
## 4.2 with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计
## 4.3 with totals: 只计算合计

4.2 DDL

# table
alter table table_name1 add column New_col Type after col1;
alter table table_name1 modify column new_col_name Type;
alter table table_name1 drop column col_name;

# export data
clickhouse-client --query "select ……"> /xxx/xxx.csv
## header
clickhouse-client --query "select ……" --format CSVWithNames > /xxx/xxx.csv

# import data
???

5. Clickhouse 副本

保证数据高可用，一台ck宕机，不影响整体使用

副本只能同步数据，不能同步表结构

1. 启动ZK
2. 增加配置,同步配置

/etc/clickhouse-server/config.d/metrika.xml 文件名可以随便，后面指定读取

hadoop102
2181


hadoop103
2181


hadoop104
2181

/etc/clickhouse-server/config.xml

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

3. 重启ck

4. 测试

ReplicatedMergeTree(param1,param2)

param1: zk_path,一般格式：/clickhouse/table/{shard}/{table_name} #shard 分片名称
param2: replication_name,不重复即可

# 1.hadoop102、hadoop103 create
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_103')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

# 2. hadoop102 insert
insert into t_order_rep2 values
(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');

# 3.hadoop103 query

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sku_003 │       600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 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 │
└─────┴─────────┴──────────────┴─────────────────────┘

6. Clickhouse 分片

横向扩容，通过Distributed 表引擎进行数据拼接使用，Distributed 表引擎本身不存储数据

1. 配置规划

hadoop102hadoop103hadoop104shard_01shard_01shard_02rep_1_1rep_1_2rep_2_1

2. 修改配置，同步配置并修改标注的

/etc/clickhouse-server/config.d/metrika-shard.xml

	
		
			
				true
        		 
					hadoop102
					9000
				
                
                    hadoop103
                    9000
                
            
             
                true
                
                    hadoop104
                    9000
                
            
            
    
    
        
            hadoop102
            2181
        
        
            hadoop103
            2181
        
        
            hadoop104
            2181
        
    
    
        01 
        rep_1_1 
    

/etc/clickhouse-server/config.xml

/etc/clickhouse-server/config.d/metrika-shard.xml

3. 重启ck

clickhouse restart

4. 测试

on cluster cluster_name : 配置的集群名称

ReplicatedMergeTree(’/clickhouse/tables/{shard}/st_order_mt’,’{replica}’) ：这两个参数从配置文件宏中获取

Distributed(cluster,default, st_order_mt,hiveHash(sku_id)):四大参数含义

(集群名称，库名，本地表名，分片键) 分片键：必须是整型

# 1.hadoop102 create local table
create table st_order_mt on cluster 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);


┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ hadoop102 │ 9000 │      0 │       │                   2 │                0 │
│ hadoop104 │ 9000 │      0 │       │                   1 │                0 │
│ hadoop103 │ 9000 │      0 │       │                   0 │                0 │
└───────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

# 2.hadoop102 creat distribute table
create table st_order_mt_all2 on cluster cluster
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)engine = Distributed(cluster,default, st_order_mt,hiveHash(sku_id));

# 3.hadoop102 insert data
insert into st_order_mt_all2 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');

# 4.query
select * from st_order_mt_all2;  # 共5条
select * from st_order_mt; # hadoop102|hadoop103 3条 hadoop104 2条

7. Clickhouse Explain

20.6.3 版本 成为 正式版本功能

explain [AST 抽象语法树|SYNTAX 优化语法| PLAN 默认值,执行计划 |PIPELINE PIPELINE计划][setting=value,……] select xxx  [FORMAT……]

# 1.PLAN
explain  select arrayJoin([1,2,3,null,null]);
# 2.AST
EXPLAIN AST SELECT number from system.numbers limit 10;
# 3.SYNTAX 【最常用】
explain syntax SELECt number = 1 ? 'hello' : (number = 2 ? 'world' : 'other') FROM numbers(10); 
┌─explain──────────────────────────────────────────────────────────┐
│ SELECt if(number = 1, 'hello', if(number = 2, 'world', 'other')) │
│ FROM numbers(10)                                                 │
└──────────────────────────────────────────────────────────────────┘
SET optimize_if_chain_to_multiif = 1;   # 开启三元运算符优化
┌─explain───────────────────────────────────────────────────────────┐
│ SELECt multiIf(number = 1, 'hello', number = 2, 'world', 'other') │
│ FROM numbers(10)                                                  │
└───────────────────────────────────────────────────────────────────┘

# 4.PIPELINE
EXPLAIN PIPELINE SELECt sum(number) FROM numbers_mt(100000) GROUP BY number % 20; 

8. Clickhouse 建表

• 数据类型

  1. 时间字段类型：DateTime 不用全用String，执行效率更高
  2. 空值存储类型：Nullable类型可以避免空值，但是会影响性能

• 分区和索引

  1. 一般按天分区
  2. 必须指定索引列，索引列即排序，order by 指定

• 写入和删除

  1. 尽量不要小批量删除和插入
  2. 不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge速度跟不上而报错

ClickHouse不支持设置多数据目录

9. Clickhouse 查询

1. uniqCombined替代distinct ：千万级数据可以使用近似去重
2. 避免构建虚拟列
3. 当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN *** 作而不是JOIN
4. 多表join时要满足小表在右的原则
5. 两张分布式表上的IN和JOIN之前必须加上GLOBAL关键字

Mergetree 对于一致性支持最好

10. Clickhouse 物化视图

1. 物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中,快照（snapshot）,查询速度快

2. 物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新

3. 物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍保留

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ... 

11. Clickhouse MaterializeMySQL

1. 会自动解析Binlog日志，实时同步更新

2. 同时支持全量和增量同步

3. 目前 MaterializeMySQL 支持如下几种 binlog 事件

   ➢ MYSQL_WRITE_ROWS_EVENT: _sign = 1，_version ++

   ➢ MYSQL_DELETE_ROWS_EVENT: _sign = -1，_version ++

   ➢ MYSQL_UPDATE_ROWS_EVENT: 新数据 _sign = 1

   ➢ MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE等

• 使用步骤

1. 开启MySQL binlog,格式为Row

server-id=1 
log-bin=mysql-bin
binlog_format=ROW

2. 开启GTID模式，保证mysql主从模式下数据一致

gtid-mode=on 
enforce-gtid-consistency=1 # 设置为主从强一致性 
log-slave-updates=1 # 记录日志 

3. 重启MySQL

systemctl restart mysqld

4. 在MySQL中创建库表，并插入数据

+------------------+
| Tables_in_testck |
+------------------+
| t_organization   |
| t_user           |
+------------------+

5. 开启CK物化引擎

set allow_experimental_database_materialize_mysql=1;

6. CK创建同步数据库

CREATE DATAbase test_binlog ENGINE = MaterializeMySQL('hadoop102:3306','testck','root','000000');


┌─name───────────┐
│ t_organization │
│ t_user         │
└────────────────┘

12. Clickhouse 监控

借助 Prometheus + Grafana 实现CK 可视化监控与展示

12.1 Prometheus 安装

1. 下载并解压安装包
2. 修改配置文件

vim prometheus.yml

scrape_configs:
  - job_name: "prometheus"
    static_configs:
      - targets: ["hadoop102:9090"]
  - job_name: clickhouse-1
    static_configs:
      - targets: ['hadoop102:9363']

3. 启动Prometheus Server

nohup ./prometheus --config.file=prometheus.yml > ./prometheus.log 2>&1 &

# web UI
http://hadoop102:9090/

12.2 Grafana 安装

tar包下载并解压即可

1. 启动

nohup ./bin/grafana-server web > ./grafana.log 2>&1 &

2. 访问地址

http://hadoop102:3000
默认： admin admin

12.3 Clickhouse 配置

1. /etc/clickhouse-server/config.xml 配置并分发

 
     /metrics
     9363   
     true  
     true  
     true  
     true  
 

2. 重启CK

3. Web 查看

http://hadoop102:9363/metrics 有信息即说明成功

12.4 Grafana Dashboard

可以从社区导入一个Dashboard的json配置模板

13. Clickhouse 备份 13.1 手动备份

#  1.备份
sudo mkdir -p /var/lib/clickhouse/shadow/
echo -n 'alter table table_name1 freeze' | clickhouse-client
cp dir

# 2.恢复
mv /xxx/xxx /var/lib/clickhosue/data/default/{table_name}/detached/

13.2 工具备份

下载并安装官方自带的backup自动化备份工具，注意兼容性问题

备份的默认路径为：/var/lib/clickhouse/backup/{datetime}

# 1.backup
clickhouse-backup tables
clickhouse-backup create
clickhouse-backup create -t {db}.{table}

# 2.recover
clickhouse-backup restore {datetime}
clickhouse-backup restore {datetime} --schema
clickhouse-backup restore {datetime} --data
clickhouse-backup restore {datetime} --table {table}