Using ClickHouse like MySQL by P

2017-12-19  本文已影响1119人  JackpGao

用户访问ClickHouse，需要默认的客户端clickhouse-client，但是这个工具不够友好：

  1. 需要额外安装，并且使用上，不是那么nice
  2. clickhouse-client其实是clickhouse的软连接，即要么全装，要么不用

啥最普及啊？
答：MySQL

基本所有的服务器都装了mysql，注意，这里指的是mysql-client，就是那个让你用命令行连接到MySQL服务的那个命令

ProxySQL是众多MySQL中间件中的佼佼者，一直被Percona推崇，最近他们也放出了支持ClickHouse的版本

因此，今天的话题就是：
ClickHouse+ProxySQL = Using ClickHouse like MySQL

Install

yum -y install   perl-DBD-MySQL

启动ProxySQL

# 默认配置文件是这个：
/etc/proxysql.cnf 

# 默认是没有这个数据目录的:
mkdir /var/lib/proxysql

# 启动
proxysql --clickhouse-server

# ProxySQL默认会以daemon的方式在后台

创建ClickHouse用户

# 登陆本地的ProxySQL
# ProxySQL端口是6032，默认用户名密码在配置文件里写有

root@10.x.x.x.x:/root  # mysql -h 127.0.0.1 -P 6032  -uadmin -padmin
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> INSERT INTO clickhouse_users VALUES ('clicku','clickp',1,100);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select * from clickhouse_users ; 
+----------+----------+--------+-----------------+
| username | password | active | max_connections |
+----------+----------+--------+-----------------+
| clicku   | clickp   | 1      | 100             |
+----------+----------+--------+-----------------+
1 row in set (0.00 sec)

MySQL [(none)]> LOAD CLICKHOUSE USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SAVE CLICKHOUSE USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

通过MySQL客户端连接ClickHouse

# 使用上面的用户名和密码
# 如果是不同机器，记得改IP

root@10.x.x.x.x:/root  # mysql -h 127.0.0.1 -P 6090  -uclicku -pclickp --prompt "ProxySQL-ClickHouse> " 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.30 (ProxySQL ClickHouse Module)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQL-ClickHouse>

执行ClickHouse操作

MySQL [(none)]> select version(); 
+-------------------+
| version           |
+-------------------+
| 5.5.30-clickhouse |
+-------------------+
1 row in set (0.00 sec)


MySQL [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-12-19 15:45:26 |
+---------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select today(); 
+------------+
| today()    |
+------------+
| 2017-12-19 |
+------------+
1 row in set (0.00 sec)

# 我们的某个表 600多亿
ProxySQL-ClickHouse> select count(*) from XXXXX ; 
+-------------+
| count()     |
+-------------+
| 62699641362 |
+-------------+
1 row in set (11.20 sec)

# 另一个表 1300多亿
ProxySQL-ClickHouse> select count(*) from edge_msg_all;
+--------------+
| count()      |
+--------------+
| 131929726359 |
+--------------+
1 row in set (5.30 sec)

缺点

问题

总结

Reference

Update

ProxySQL在ClickHouse里能干吗？

上一篇 下一篇

猜你喜欢

热点阅读