ClickHouse介绍和使用_clickhouse使用

概述

什么是clickhouse

ClickHouse是开元的列式存储数据库（DBMS），主要用于在线处理查询（OLAP），能够使用SQL查询实时生成数据分析报告。

安装前的准备

Centos取消打开文件数限制

在/etc/security/limits.conf、/etc/security/limits.d/20-nproc.conf这两个文件的末尾加入一下内容：

* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
1
2
3
4

重启服务器之后生效，可以用ulimit -n查看是否生效，默认为1024，看是否修改成了添加的内容的数字

取消SELINUX

修改/etc/selinux/config中的SELINUX=disabled后重启

取消防火墙

安装依赖

yum install -y libtool
yum install -y *unixODBC*
1
2

在线安装命令

rpm包下载地址https://packagecloud.io/Altinity/clickhouse

点击去想要下载的rpm包

下面的是在线下载rpm包，上面的是安装命令，
一共需要下载

• clickhouse-client-18.16.1-1.el7.x86_64.rpm，
• clickhouse-common-static-18.16.1-1.el7.x86_64.rpm
• clickhouse-server-18.16.1-1.el7.x86_64.rpm
• clickhouse-server-common-18.16.1-1.el7.x86_64.rpm
• clickhouse-test-18.16.1-1.el7.x86_64.rpm
  安装完成后，需要重启，直接使用clickhouse-client命令直接登录即可

重启命令

/etc/init.d/clickhouse-server restart
# 或者使用systemctl start clickhouse-server
1
2

客户端修改链接配置

客户端连接工具：
dbeaver
连接时候回报错，因为dbeaver 连接clickhouse端口是8123
而clickhouse 命令：clickhouse-client 连接的地址是：localhost:9000 端口
此时需要对config.xml
配置： <listen_host>::</listen_host>

卸载命令

# 卸载及删除安装文件（需root权限）
yum list installed | grep clickhouse
 
yum remove -y clickhouse-common-static
 
yum remove -y clickhouse-server-common
 
rm -rf /var/lib/clickhouse
 
rm -rf /etc/clickhouse-*
 
rm -rf /var/log/clickhouse-server
1
2
3
4
5
6
7
8
9
10
11
12

使用

配置文件

config.xml配置文件在clickhouse-server下，一般在/etc/clickhouse-server目录下，配置文件中有log日志的路径，可通过搜索logger节点

客户端命令

• 登录命令

clickhouse-client -u username --password='password'
1

–host ,-h :服务端的host名称
–port ：连接的端口号，默认是9000
–user -u :用户名，默认值为default
–password ：密码，默认值空字符串

数据类型

和MySQL的数据类型对比

表引擎

创建库

CREATE DATABASE 数据库名;
重要引擎

Kafka

可以实时读取kafka主题中数据，并保存到表中

创建表

CREATE TABLE 数据库名.队列表名(列名 数据类型) ENGINE = Kafka SETTINGS kafka_broker_list = ‘ip:port’, kafka_topic_list = ‘主题名’, kafka_group_name = ‘消费组名’, kafka_format = ‘JSONEachRow’, kafka_num_consumers = 1;

必要参数

• kafka_broker_list：以逗号分隔的 brokers 列表 (localhost:9092)
• kafka_topic_list：topic 列表 (my_topic)。
• kafka_group_name：Kafka 消费组名称 (group1)。如果不希望消息在集群中重复，请在每个分片中使用相同的组名。
• kafka_format：消息体格式。 使用与 SQL 部分的 FORMAT 函数相同表示方法，例如 JSONEachRow。了解详细信息 请

非必要参数

• kafka_num_consumers – 单个表的消费者数量。
  默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。消费者的总数不应该超过 topic 中分区的数量，因为每个分区只能分配一个消费者。

创建物化视图，用于从同步表向数据表同步内容
CREATE MATERIALIZED VIEW 数据库名.视图名 TO 数据库名.创建过得表名 AS SELECT 列字段 from 数据库 名.创建过的队列名;

步骤

• 首先创建一张Kafka表引擎的表，用于从Kafka中读取数据（数据库名.队列表名）
• 然后再创建一张普通表引擎的表，比如MergeTree，面向终端用户使用（数据库名.表名）
• 最后创建物化视图，用于将Kafka引擎表实时同步到终端用户所使用的表中（数据库名.视图名）

最终终端查询都是查的MergeTree引擎创建的表

新增或者删除列

修改表中的列是可以通过先删除再重建即可，因为此种引擎的表不会保存数据，不会导致数据丢失

MergeTree

ClickHouse中最强大的表引擎当属MergeTree（合并树）及该系列（*MergeTree）中的其他引擎。
MergeTree引擎系列的基本理念如下。当你有巨量数据要插入表中，你要高效的一批一批写入数据片段，并希望这些数据片段在后台按照一定规则合并，相比在插入时不断修改数据进存储，这种策略会高效很多。
特点如下：

1. 数据按主键排序
2. 可以使用分区（如果指定了主键）
3. 支持数据副本
4. 支持数据采样

创建表
CREATE TABLE 数据库名.表名 (列名 数据类型) ENGINE =MergeTree() PARTITION BY toYYYYMM(log_time) ORDER BY (log_time) SETTINGS index_granularity=8192;

• index_granularity索引粒度，用默认值8192即可

新增列

和MySQL新增列一样的指令，不同点事默认为空字符串的话用DEFAULT ‘’ ，使用单引号，也可以不加
ALTER TABLE 库名.表名 ADD COLUMN 列名 数据类型 DEFAULT ‘’;

删除列

ALTER TABLE 库名.表名 DROP COLUMN 列名;

修改数据

ALTER TABLE table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
1

处理异常

首先查看异常日志，一般在clickhouse目录下的clickhouse-server.err.log中，

1. 先查看错误日志，如果不知道是哪个表报的错，就查看正常日志
2. 如果没有读取到kafka的数据，先看下物化图中对应的kafka地址是否正确，查看kafka的索引，如果消费到了解析不了的数据，需要修改此消费者组的的消费索引，如果还消费不了要把索引扩大，直到可以消费，如果服务器挂了重启服务器导致消费不到kafka数据则需要访问一次clickhouse的数据
3. 如果启动提示UNKNOWN，一般是配置文件出错了，只需把改正确即可
4. 
   此种错误是因为版本问题，19版本的有这个问题，18和20版本的都没有
5. 如果服务器启动了但是没有生成日志文件，可能是clickhouse用户没有日志文件路径的权限，可以用id clickhouse查看clickhouse用户，然后用chown -R clickhouse:clickhouse logPath(日志所在文件夹)
6. 异常信息： Received from localhost:9000, ::1. DB::Exception: Column '1' already exists: (while reading from part /clickhouse/data/202_239_8163_43_8049/)，是因为where条件中有两列都筛选了条件为1的，只有修改一列的筛选跳转才会正常，版本 18.16.1，如where age = 1 and genden = 1就会报这个错，修改为 where age = 1 and genden != 0就可以了

常用函数

• 日期函数
  formatDateTime(log_time,'%Y-%m-%d %H:%M:%S') log_tikme 为日期类型的列

取整函数

• 向下取整：floor(x[,N])，floor(toFloat32(12.08098), 2)
• 四舍五入：round(expression [, decimal_places])，round(toFloat32(12.1234), 3)
• .向上取整：ceil(x[, N]) 或者 ceiling(x[, N])
• 返回绝对值小于或等于x的最大绝对值的整数：trunc(x[, N]), truncate(x[, N])
• 银行家舍入方法：roundBankers

查询各个表的数据大小

SELECT table, formatReadableSize(total_bytes) AS size FROM system.tables ORDER BY size DESC;
1

参考 ：https://blog.csdn.net/weixin_43829047/article/details/117219725