ClickHouse安装与使用

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

clickhouse 安装

环境: CentOS 7 x64
安装方式: 在线yum安装

sudo yum install yum-utils
sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
sudo yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/clickhouse.repo
sudo yum install clickhouse-server clickhouse-client

将clickhouse注册为系统服务

systemctl enable clockhouse-server.service

clickhouse卸载

找出yum安装过的clickhouse程序及依赖：

yum list installed | grep clickhouse

clickhouse-client.noarch           22.2.2.1-2                          @clickhouse-stable
clickhouse-common-static.x86_64    22.2.2.1-2                          @clickhouse-stable
clickhouse-server.noarch           22.2.2.1-2                          @clickhouse-stable

依次yum remove

yum remove -y clickhouse-client

yum remove -y clickhouse-common-static

删除clickhouse的配置文件和数据文件，日志文件等

rm -rf /var/lib/clickhouse*

rm -rf /etc/clickhouse*

rm -rf /var/log/clickhouse-server/*

clickhouse 配置

默认配置文件位置

服务端配置文件: /etc/clickhouse-server

config.xml 全局配置文件

user.xml 用户配置文件

数据文件: /var/lib/clickhouse

日志存放目录: /var/log/clickhouse-server

开启远程连接

# 1. 编辑配置文件
vim /etc/clickhouse-server/config.xml

# 2. 找到
<listen_host>::</listen_host>
# 3.去掉注释

# 4.重启服务
systemctl restart clickhouse-server.service

设置密码

vim /etc/clickhouse-server/user.xml

找到<password>标签页，添加密码，保存退出，重启服务即可

连接到ClickHouse-Server

命令行工具(clickhouse-client)

查找配置文件中的tcp端口
cat /etc/clickhouse-server/config.xml | grep "<tcp_port>"

输出：
<tcp_port>9001</tcp_port>

看到tcp端口是9001，用客户端连接：

clickhouse-client --password 123456 --port 9001

不加 --port 选项，默认是9000端口。

图形化界面软件(DBeaver, DataGrip)

查看配置文件，查找http端口，用图形化界面工具连接的时候，端口就填这个

cat /etc/clickhouse-server/config.xml | grep "<http_port>"

clickhouse踩坑

数据格式

建表语句格式如下

CREATE TABLE default.toll_transaction
(
    `id` Int64,
    `transaction_id` String,
    `user_uuid` String,
    `user_id` Int64,
    `money_destination` Int32,
    `trade_type` Enum8('RECHARGE' = 1, 'PAY' = 2, 'CROSS' = 3, 'COUPON' = 4, 'WITHDRAW' = 5, 'REFUND' = 6, 'PENALTY' = 7, 'WASH' = 8, 'CHARGE' = 9, 'VIP' = 10, 'SLOTSOLD' = 11) DEFAULT 'PAY',
    `pay_type` Enum8('BALANCE' = 1, 'WECHAT' = 2, 'ALIPAY' = 3, 'BANK' = 4, 'PLATFORM' = 5, 'BOC' = 6, 'ALIPAY_NOSENSE' = 7, 'BOC_QUICKPAY' = 8, 'WECHAT_NOSENSE' = 9, 'UNION_SCAN_ALI' = 10, 'UNION_SCAN_WECHAT' = 11, 'UNION_ALI' = 12, 'UNION_WECHAT' = 13, 'BOC_ALI' = 14, 'BOC_WECHAT' = 15, 'BOC_BANK' = 16, 'ICBC_ALI' = 17, 'ICBC_WECHAT' = 18, 'ICBC_QUICKPAY' = 19) DEFAULT 'BALANCE',
    `amount` Decimal(15, 2),
    `lucky_money_amount` Decimal(15, 2),
    `third_party_transaction_id` String,
    `licence_plate` String,
    `transaction_time` Int64,
    `transaction_result` Enum8('SUCCESS' = 1, 'PENDING' = 2, 'FAILED' = 3, 'PROCESSING' = 4),
    `order_id` String,
    `park_name` String,
    `parking_time` Int32,
    `updated_at` DateTime DEFAULT now(),
    `created_at` DateTime,
    `prepay_id` String,
    `operator_id` Int64,
    `park_id` Int64,
    `used_lucky_money_id` Int64 DEFAULT 0,
    `status` Int32 DEFAULT 0,
    `slot_renewal_record_id` Int64,
    `redemption_id` Int64
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY id
SETTINGS index_granularity = 8192;

Nullable问题

建表时，指定某个字段可为空，语法为 字段名 Nullable(类型) ，这样插入数据时，字段可为Null值。这样会带来性能问题：

表的列中存储 Nullable 类型值，ClickHouse 除了使用带有值的普通文件外，还使用带有 NULL 掩码的单独文件。掩码文件中的条目允许 ClickHouse 区分每个表行的 NULL 和相应数据类型的默认值。由于附加了新文件，Nullable 列与类似的普通文件相比消耗额外的存储空间。

枚举类型问题

clickhouse中的枚举类型和MySQL中的不太一样，声明的时候，格式是这样的：

licence_plate_type Nullable(Enum8('yellow' = 1, 'blue' = 2, 'green' = 3)),

order by问题

千万以上数据集进行order by 查询时需要搭配where 条件和limit语句一起使用。

去重查询

用uniqCombined替代distinct性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使用这种去重方式提升查询性能。

数据导入编码问题

~~latin1字符集导入ck的时候会报错，或许是MySQL数据库版本问题?~~

MySQL 旧版本的时间日期格式为 0000-00-00 00:00:00 的记录，导入新版MySQL会报错。

解决方式：

SELECT @@SESSION.sql_mode;

如果结果有NO_ZERO_DATE和NO_ZERO_IN_DATE，就是我们日期不能设为0000-00-00的原因，把sql_mode中的这两个去掉就行了

set @@global.sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));

使用dataX导入数据的问题

dataX没有clickhouse插件解决方案

默认下载安装的dataX不包含clickhousereader和clickhousewriter的插件，因此如果要从MySQL导入数据，需要自己手动编译dataX的源码。

编译失败，先准备依赖

先下载源码

git clone [email protected]:nnego/eigenbase-properties.git
#编译为jar
mvn clean install
#手动安装jar，改为你自己的目录
mvn install:install-file -DgroupId=eigenbase -DartifactId=eigenbase-properties -Dversion=1.1.4 -Dpackaging=jar -Dfile=/Users/htc/code/git/eigenbase-properties/target/eigenbase-properties-1.1.7-SNAPSHOT.jar

下载DataX并编译

#下载DataX源码
git clone https://github.com/alibaba/DataX.git
#打开根pom文件，把不需要的子模块注释掉
#编译dataX
mvn -U clean package assembly:assembly -Dmaven.test.skip=true

编译成功之后进到 target/datax/datax/plugin 目录中找到clickhouse的读写插件，复制到安装目录对应位置即可。

其他注意事项

clickhouse必须指定密码，否则dataX同步会失败。clickhouse更改密码在配置文件 users.xml 里。
显示成功但是实际上并没有导入记录

dataX日志输出显示成功导入数据，但是实际clickhouse数据库里面并没有数据。

~~观察日志输出？猜想可能出现问题的原因？？~~

原因：

采用ReplacingMergeTree，order by id，id是主键，相同主键只保留最新的记录。源数据的多条记录id都是0，所以会触发merge，最终只剩下一条数据，其他数据都被删除了。

怎么办：

重新建表，表引擎改为MergeTree即可。

kafka消息格式问题

json的key，value 大小驼峰和ORM框架的问题。

大表join的性能优化问题

参考链接：

从携程性能测试case中重新认识clickhouse - 简书 (jianshu.com)

苏小霂的博客