clickhouse之安装与基本使用
近期要做一个数据统计功能，公司选择了clickhouse作为数据库；下面记录一下该数据库的特性和使用教程。
clickhouse是一个列式数据库，主要用于数据分析；
从目前使用看来，特点如下：
- 列式存储查询效率高
- 不支持事务；
- 适用于一写多读
- 支持特殊的修改和删除语句，sql标准的删改语句不支持
安装：
离线安装
wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-client-20.11.3.3-2.noarch.rpm wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-20.11.3.3-2.x86_64.rpm wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-dbg-20.11.3.3-2.x86_64.rpm wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-server-20.11.3.3-2.noarch.rpm yum install -y clickhouse-*
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/stable/x86_64 sudo yum install clickhouse-server clickhouse-client
配置：
ClickHouse中的配置项很多，默认会在/etc下生成clickhouse-server和clickhouse-client两个目录，由于我们安装服务我们去修改clickhouse-server下的配置文件
- 修改
/etc/clickhouse-server/config.xml
我们一般修改以下配置项：
logger修改日志的存放路径http_port修改浏览器访问的端口，默认为8123tcp_port修改tcp协议的传输端口openSSL一些ssl的认证配置文件，我们暂时不做ssl认证，此处不做处理listen_host如果我们要对外使用服务的话，remote_servers此处是集群相关的配置信息后续会详解
其他自定义的配置可自行修改，修改配置后我们便可以启动一个本地的ClickHouse服务
- 创建相关目录及权限
mkdir -p /data2/clickhouse
chown -R clickhouse:clickhouse /data2/clickhouse
启动服务：
sudo -u clickhouse clickhouse-server --daemon --pid-file=/var/run/clickhouse-server/clickhouse-server.pid --config-file=/etc/clickhouse-server/config.xml --daemon 标志我们要后台启动服务 --pid-file 指定服务启动后的进行文件路径 --config-file 指定服务启动的配置文件
调试命令：
sudo -u clickhouse clickhouse-server start
测试clickhouse服务：
ClickHouse安装完成后会生成clickhouse-server和clickhouse-client两个目录，这个时候我们使用clickhouse-client去测试服务 clickhouse-client -h 127.0.0.1 --port 9000 --multiquery --query="show databases"
注意：--port指定的是tcp的端口
配置外网访问：
修改配置文件 <listen_host>::</listen_host> 取消注释 注意一定要配成 :: 而且要将其他本地监视连接注释，0.0.0.0将导致本地的命令行客户端无法连接，本地命令行客户端使用的是ipv6地址 vim users.xml <networks> <ip>::/0</ip> </networks>
常用基本操作：
1.创建数据库
语法：CREATE DATABASE [IF NOT EXISTS] db_name
如果数据库db_name已经存在，则不会创建新的db_name数据库。
2.创建表
1) 直接创建 语法：CREATE TABLE t1(id UInt16,name String) ENGINE=TinyLog 例如：create table t1(id UInt8,name String,address String)engine=MergeTree order by id 2）创建一个与其他表具有相同结构的表 语法：CREATE TABLE [IF NOT EXISTS] [db.]table_name AS[db2.]name2 [ENGINE = engine] 例如：create table t2 as t1 engine=MergeTree order by id 可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与db2.name2使用相同的表引擎。 3）使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它 语法：CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ... 例如：create table t3engine=TinyLog as select * from t1
3.插入数据
语法：INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), … 例如：insert into t1 (id,name,address) values(1,'aa','addr1'),(2,'bb','addr2')
也可以使用SELECT结果插入数据，语法：INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
例如：insert into t2 select *from t1
4.添加表的列
语法：ALTER TABLE [db].name [ONCLUSTER cluster] ADD COLUMN ...
例如：alter table t1 add column age Int8增加age列类型为Int8
5.修改表的列
语法：ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN ...
例如：alter table t1 modify
column age String修改age列类型为String
6.删除表的列
语法：ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN ...
例如：alter table t1 drop column age删除age列
7.打印数据库和数据表
打印数据库列表语法：SHOUW DATABASES
打印表列表语法：SHOW TABLES
8.查看表结构
语法：DESC|DESCRIBE TABLE [db.]table [INTO OUTFILE filename] [FORMAT format]
例如：desc t1
9.删除表
语法：DROP [TEMPORARY] TABLE [IF
EXISTS] [db.]name [ON CLUSTER cluster]
例如：drop table t2
示例：
create table if not exists test.tb_test ( id Int64, datetime DateTime, content Nullable(String), value Nullable(Float64), date Date ) engine = MergeTree --使用mergeTree引擎，ch主要引擎 partition by toYYYYMM(datetime) --按照datetime这个字段的月进行分区 order by id --按照id进行排序 TTL datetime + INTERVAL 3 DAY ; --三天过期 --修改表中数据过期时间，到期后数据会在merge时被删除 ALTER TABLE test.tb_test MODIFY TTL datetime + INTERVAL 1 DAY; --查询 select * from tb_test order by id; --删除分区，可用于定时任务删除旧数据 alter table tb_test drop partition '202005'; --插入数据 insert into tb_test values (5, '2020-02-29 12:38:37', 'abcde', 12.553, '2020-04-25'); --修改数据，不推荐使用 alter table tb_test update content = 'hello click' where id=52; --删除数据，不推荐使用 alter table tb_test delete WHERE id=56;
求和引擎SummingMergeTree
这种引擎可以自动聚合非主键数字列，可以用于事件统计
--自动求和聚合表 CREATE TABLE IF NOT EXISTS tb_stat ( regionId String, --门店id groupId String, --统计组id in int, --进客流 out int, --出客流 statDate DateTime --统计时间 ) ENGINE = SummingMergeTree partition by (toYYYYMM(statDate), regionId) ORDER BY (toStartOfHour(statDate), regionId, groupId); insert into tb_stat values ('1232364', '111', 32, 2, '2020-03-25 12:56:00'); insert into tb_stat values ('1232364', '111', 34, 44, '2020-03-25 12:21:00'); insert into tb_stat values ('1232364', '111', 54, 12, '2020-03-25 12:20:00'); insert into tb_stat values ('1232364', '222', 45, 11, '2020-03-25 12:13:00'); insert into tb_stat values ('1232364', '222', 32, 33, '2020-03-25 12:44:00'); insert into tb_stat values ('1232364', '222', 12, 23, '2020-03-25 12:22:00'); insert into tb_stat values ('1232364', '333', 54, 54, '2020-03-25 12:11:00'); insert into tb_stat values ('1232364', '333', 22, 74, '2020-03-25 12:55:00'); insert into tb_stat values ('1232364', '333', 12, 15, '2020-03-25 12:34:00'); select toStartOfHour(statDate), regionId, groupId, sum(in), sum(out) from tb_stat group by toStartOfHour(statDate), regionId, groupId;
数据插入后，大概过1分钟，在此查询该表可以发现，只剩下3调数据：
select * from tb_stat; 1232364 111 480 232 2020-03-25 04:56:00 1232364 222 356 268 2020-03-25 04:13:00 1232364 333 352 572 2020-03-25 04:11:00
常见问题：
1. 启动clickhouse-server报错：
ClickHouse 启动报错Init script is already running，clickhouse-client报错Code: 210. DB::NetException: Connection refused (localhost:9000)
原因：我是使用的service 的方式进行的启动，/etc/init.d/clickhouse-server start 结果就是报这个错误， 因为Redhat7之后需要使用systemctl进行启动
解决：
systemctl start clickhouse-server
