ClickHouse 对比 mysql clickhouse对比mongodb

clickhouse作为分析型数据库，相对于mysql，es，mongodb等，其有着优秀的查询性能以及数据压缩存储能力，在处理大数据的查询场景时有独特优势，所以这里搭建一个基本环境进行一定的研究。

服务器选择：192.168.17.81

1. 安装命令

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

作为测试，这里安装单机版本。yum 安装会自动创建 clickhouse 用户。

2. 修改配置文件

yum 安装完成之后，配置文件，数据文件日志目录设置如下：

配置文件目录:/etc/clickhouse-server/
数据文件目录:/var/lib/clickhouse/
日志文件目录:/var/log/clickhouse-server/

clickhouse 相关的各个目录可以在配置文件 /etc/clickhouse-server/config.xml 中进行修改。

3. 启动服务

sudo /etc/init.d/clickhouse-server start

4. 开启远程连接

vi /etc/clickhouse-server/config.xml

把<listen_host>::</listen_host>的注释打开

重启ClickHouse服务

clickhouse stop
clickhouse start

5. 设置密码

在配置文件/etc/clickhouse-server/users.xml中，设置<users>下的<password>设置，如果没有设置用户名，则使用default进行连接。

可以设置明文密码，也可以设置sha256密码。

# echo -n 123456 | openssl dgst -sha256
(stdin)= 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92

设置密码：

<password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>

明文密码：

<password>123456</password>

设置完成后，重启服务：

systemctl restart clickhouse-server

6. 通过命令行客户端进行访问

启动命令行如下：

clickhouse-client -h [ip] --port [port] -u [ussername] --password [password]

这里的命令行参数和mysql有一定的差异，比如--port后面指定端口号，--password 指定密码。

 --host, -h        – 服务端的 host 名称, 默认是 'localhost'。 您可以选择使⽤ host 名称或者 IPv4 或 IPv6 地址。 
--port            – 连接的端⼝，默认值： 9000。注意 HTTP 接⼝以及 TCP 原⽣接⼝是使⽤不同端⼝的。 
--user, -u        – ⽤户名。 默认值： default。 
--password        – 密码。 默认值： 空字符串。 
--query, -q       – ⾮交互模式下的查询语句. 
--database, -d    – 默认当前操作的数据库. 默认值： 服务端默认的配置 （默认是 default ）。 
--multiline, -m   – 如果指定，允许多⾏语句查询（Enter 仅代表换⾏，不代表查询语句完结）。 
--multiquery, -n  – 如果指定, 允许处理⽤逗号分隔的多个查询，只在⾮交互模式下⽣效。 
--format, -f      – 使⽤指定的默认格式输出结果。 
--vertical, -E    – 如果指定，默认情况下使⽤垂直格式输出结果。这与 '--format=Vertical' 相同。在这种格式中，每个值都在单独的⾏上打印，这种⽅式对显示宽表很有帮助。 
--time, -t        – 如果指定，⾮交互模式下会打印查询执⾏的时间到 'stderr' 中。 
--stacktrace      – 如果指定，如果出现异常，会打印堆栈跟踪信息。 
-config-file      – 配置⽂件的名称。...

6.1 创建和删除数据库

-- 创建数据库
create database test;

-- 删除数据库
drop database test;

-- 查看数据库列表
show databases;

-- 查看当前数据库
select currentDatabase();

-- 查看表
show tables;

6.2 创建表

CREATE TABLE `plat_access_perm` (
  `access_id` int(11),
  `developer_id` String ,
  `developer_name` String ,
  `access_key` String ,
  `access_secret` String ,
  `merchant_no` String ,
  `encrypt_alg` String ,
  `private_key` String ,
  `public_key` String ,
  `session_time` datetime ,
  `access_token` String ,
  `token_time` datetime ,
  `token_valid` int(10) ,
  `replay_check` int(2) ,
  `replay_times` int(10) ,
  `ip_whitelist` String ,
  `ip_blacklist` String ,
  `perm_apilist` String,
  `perm_query` tinyint(1) ,
  `perm_add` tinyint(1) ,
  `perm_update` tinyint(1) ,
  `perm_delete` tinyint(1),
  `max_tps` int(11) ,
  `max_qps` int(11) ,
  `create_by` String ,
  `create_time` datetime ,
  `update_by` String ,
  `update_time` datetime   
) ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
SETTINGS index_granularity = 8192;

可以看出，创建表和mysql创建表还是有一定差别的，主要是数据类型和引擎参数。

6.3 查看表的分区信息

select partition_id,partition, name, active,min_block_number,max_block_number,level from system.parts WHERE table = 'box_bill';

┌─partition_id─┬─partition─┬─name────────┬─active─┬─min_block_number─┬─max_block_number─┬─level─┐
│ all          │ tuple()   │ all_1_31_2  │      1 │                1 │               31 │     2 │
│ all          │ tuple()   │ all_32_37_1 │      1 │               32 │               37 │     1 │
│ all          │ tuple()   │ all_38_43_1 │      1 │               38 │               43 │     1 │
│ all          │ tuple()   │ all_44_49_1 │      1 │               44 │               49 │     1 │
│ all          │ tuple()   │ all_50_50_0 │      1 │               50 │               50 │     0 │
│ all          │ tuple()   │ all_51_51_0 │      1 │               51 │               51 │     0 │

-- 删除分区
alter table xxx drop partition '2018-08-08';

可以看出，没有指定分区，所以放到了默认分区all。

partition_id：根据分区规则生成

min_block_number：1，最小块编号，MergeTree引|擎从1开始计数，每次+1；

max block_number：1，最大块编号，新插入的数据，最小与最大编号一致；

level：0，这个可以理解为合并的次数，新插入的数据都是0，每合并1次+1。

6.4 查看后台进程

-- 这个命令和mysql是一样的
show processlist
-- 如果进程太多，也可用通过查询系统表 processes，
select * from system.processes
-- 指定主要关心字段
select user,query_id,query,elapsed,memory_usage from system.processes;

 一般是在性能大幅下降的时候，通过查看正在执行的进程，以便查找什么操作导致的，这点和mysql的思路是一致的。

--  通过上面指令获取到进程相关信息后，可以用query_id条件kill进程
KILL QUERY WHERE query_id='2e33f3ae-f7e1-4c10-af8c-af96aff20837'

6.5 从mysql迁移数据

create table box_bill ENGINE=MergeTree order by bill_id AS SELECT * from mysql('ip:port', 'dbname', 'tablename', 'username', 'password');

Query id: 983249f9-459f-4e80-97c4-344702368127

Ok.

0 rows in set. Elapsed: 2017.381 sec. Processed 53.46 million rows, 21.81 GB (26.50 thousand rows/s., 10.81 MB/s.)

6.6 执行sql脚本文件

clickhouse-client --user xxx --password xxx --multiquery <  /root/temp.sql

类似与mysql的source命令。