大数据

• 大数据Clickhouse(CK)
• • 1. Ubuntu下安装
  • • • 1）下载
      • 2）安装
      • 3）启动
      • 4）登录
      • 5）测试
  • 2. python连接clickhouse （CK）
  • 3. 数据库管理工具连接clickhouse
  • • 1）DataGrip连接clickhouse
    • 2）DBeaver连接clickhouse
  • 4. 实例

大数据Clickhouse(CK)

Clickhouse本为俄罗斯yandex搜索引擎服务。近几年大火，各大大厂大都在用。Clickhouse在国外简称CH，在国内简称CK，因叫起来有AK的感觉。

1. Ubuntu下安装

环境：Ubuntu16.04，其他版本应该也可以（未测）

1）下载

官方下载地址：
https://repo.clickhouse.tech/deb/stable/main/。或 https://repo.yandex.ru/clickhouse/deb/stable/main/。因为clickhouse本属于俄罗斯yandex搜索引擎。
下载如下三个文件

clickhouse-common-static_20.9.2.20_amd64.deb
clickhouse-server_20.9.2.20_all.deb
clickhouse-client_20.9.2.20_all.deb

2）安装

执行

sudo dpkg -i clickhouse-common-static_20.9.2.20_amd64.deb
sudo dpkg -i clickhouse-server_20.9.2.20_all.deb
sudo dpkg -i clickhouse-client_20.9.2.20_all.deb

安装过程中会提示输入密码。假设密码设置为root

3）启动

sudo service clickhouse-server start 

或

systemctl restart clickhouse-server

4）登录

clickhouse-client --password

输入安装时设置的密码root即可登录

5）测试

输入 show databases;

2. python连接clickhouse （CK）

需要下载clickhouse-driver驱动。
（1）在线安装
pip install clickhouse-driver即可。
（2）离线安装
先到
https://pypi.org/project/tzlocal/#files 下载 tzlocal-2.1-py2.py3-none-any.whl (16.2 kB)
https://pypi.org/project/clickhouse-driver/#files 下载 clickhouse_driver-0.2.1-cp37-cp37m-manylinux1_x86_64.whl
先安装插件
pip install tzlocal-2.1-py2.py3-none-any.whl
再安装clickhouse_driver：
pip install clickhouse_driver-0.2.1-cp37-cp37m-manylinux1_x86_64.whl

实例：

# 注意代码中是clickhouse_driver，而pip安装包是clickhouse-driver。因为python代码习惯用下划线
from clickhouse_driver import Clientclient = Client(host = '127.0.0.1',port = '9000', # 或 9000user = 'default',password = 'root',database = 'test_db'
)def test():global clientsql = 'show tables' # show databases;res = client.execute(sql)print(res)if __name__ == '__main__':test()

注：ClickHouse提供两个网络端口： TCP 默认9000，HTTP 默认8123， 这里clickhouse_driver.Client连接法，我们要用TCP端口9000

python 驱动：clickhouse-driver 使用的是 tcp 端口： 9000
java 驱动：clickhouse-jdbc 使用的是http 端口： 8123

附：除了clickhouse-driver驱动，还有asynch 等驱动也可实现python连接clickhouse，参考https://github.com/long2ice/asynch,
来自官方文档https://clickhouse.tech/docs/zh/interfaces/third-party/client-libraries/#sidebar-sidebar-10

3. 数据库管理工具连接clickhouse

navicat等不支持连接clickhouse，如下两个软件支持

1）DataGrip连接clickhouse

DataGrip为大名鼎鼎地Jetbrains公司，用过pycharm、idea的对他不陌生，
官方下载链接：https://www.jetbrains.com/zh-cn/datagrip/promo/

安装启动，如下图，点击左上角“+”，再点“Data Source”，再点“ClickHouse”，

弹出框如下图，输入如下信息：
输入IP：如果是本地，则127.0.0.1
端口：默认HTTP端口 8123。注意不再是TCP端口9000
Clickhouse数据库用户：默认default
Clickhouse数据库密码：默认空，这里我设置了密码，如root
然后点击“编辑驱动设置”
点击Apply，会提示下载驱动，下载后点击“Test Connection”。成功后点OK。

点如下两个红圈的地方，选中All schemas可以显示所有的库，这里test_db为我新建的库，其他为系统自带。

输入sql 语句：
点击如下，选中一个库，如test_db库，右键 -> new -> Query Console，即可在console框中输入sql语句。

2）DBeaver连接clickhouse

DataGrip试用期30天，如果没破解，也可以替换为DBeaver，留作备用，也不错，但体验感没有DataGrip好。
如下：
启动软件，点击左上角加号图标，依次如下图点击操作


输入IP：如果是本地，则127.0.0.1
端口：默认HTTP端口 8123。注意不再是TCP端口9000
Clickhouse数据库用户：默认default
Clickhouse数据库密码：默认空，这里我设置了密码，如root
然后点击“编辑驱动设置”

点击库，点击下载/更新

下载驱动，这里一定注意要下载0.2.4版本的驱动，因为这个驱动稳定。双击图中红线位置。

如下，选择0.2.4版本进行下载。

注：其他版本0.2.6试过，不能用，连接失败。

4. 实例

用实例（1000w条数据）对比一下CK和mysql的性能
1）创建表
（1）CK
先建立一个MergeTree()引擎的表test_y

CREATE TABLE test_y
(id UInt64,a String,b String,c String
)ENGINE = MergeTree() 
ORDER BY id

（2）MySQL
先创建一个空表test_y，

create table `test_y` (`id` int(11) unsigned NOT NULL auto_increment,`a` varchar(255) collate utf8_bin NOT NULL default '',`b` varchar(255) collate utf8_bin NOT NULL default '',`c` varchar(255) collate utf8_bin NOT NULL default '',
) ENGINE = InnoDB auto_increment = 1 default charset = utf8 ROW_FORMAT=COMPACT;

2）插入insert
（1）CK
（i）插入1000w 条数据， —— 执行时间：10s

INSERT INTO TABLE test_y SELECT number,concat('A',toString(number)),concat('B',toString(number)),concat('C',toString(number)) FROM numbers(1, 10000000) -- numbers(10000000)表从0开始的1000w条，即0~9999999，numbers(1, 10000000) 表从1开始的1000w条，即1~10000000

（ii）插入10亿 条数据， —— 执行时间：0.5h

INSERT INTO TABLE test_y SELECT number,concat('A',toString(number)),concat('B',toString(number)),concat('C',toString(number)) FROM numbers(1, 1000000000)

（2）MySQL
（i）插入1000w条数据， —— 执行时间：2.5h
写入1000w条数据数据，思路是创建存储过程：如proc3()，可将存储过程看成函数proc3()，设置变量 i 自增，最后调用call proc3；

mysql> delimiter $$ -- 将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> create procedure proc3() -- 存储过程名，类似函数, proc = procedure-> begin-> declare i int; -- 声明变量 i-> set i = 1;-> while i < 10000001 do-> insert into test_y (id, a, b, c) values(i, concat('A', i), concat('B', i), concat('C', i));-> set i = i + 1;-> end while;-> end;-> $$
mysql> delimiter ; -- 将语句的结束符号恢复为分号
Query OK, 0 rows affected (0.12 sec)mysql> call proc3; -- 调用存储过程

解析：

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

（ii）插入10亿条数据， —— 执行时间：未测试

3) 查询SELECT
（1）CK
（i）查询1000w 条数据， —— 执行时间：1s

SELECT * FROM test_y WHERE b = 'B555555'

（i）查询10亿 条数据， —— 执行时间：2.5min

SELECT * FROM test_billion WHERE b = 'B555555'

（2）MySQL
（i）查询1000w 条数据， —— 执行时间：44s

SELECT * FROM test_y WHERE b = 'B555555'

（i）查询10亿 条数据， —— 执行时间：未测试
注：mysql把要查询的那字段，加上索引也会飞快！