How to use ProxySQL to work on ClickHouse like MySQL ?

Use ClickHouse like MySQL with ProxySQL


Introduction

We have several customers on ClickHouse now for both columnar database analytics and archiving MySQL data, You can access data from ClickHouse with clickhouse-client but this involves some learning  and also limitations technically. Our customers are very comfortable using MySQL so they always preferred a MySQL client for ClickHouse query analysis and reporting, Thankfully ProxySQL works as a optimal bridge between ClickHouse and MySQL client, This indeed was a great news for us and our customers worldwide. This blog post is about how we can use MySQL client with ClickHouse.

Installation

Start ProxySQL once completed installation successfully.

  # The default configuration file is this: 
  /etc/proxysql.cnf 
  # There is no such data directory by default: 
  mkdir / var / lib / proxysql 
  # start up 
  proxysql --clickhouse-server 
  # ProxySQL will default to daemon mode in the background

Creating ClickHouse user

Create a user for ClickHouse in the ProxySQL with password, The password is not configured for ClickHouse but for accessing ProxySQL:  

# ProxySQL port is 6032, the default username and password are written in the configuration file 
 root@10.xxxx: / 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.6.81 (ProxySQL Admin Module) 
  Copyright (c) 2000, 2018, 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 ('chuser', 'chpasswd', 1,100); 
  Query OK, 1 row affected (0.00 sec) 
  MySQL [(none)] > select * from clickhouse_users; 
  + ---------- + ---------- + -------- + ----------------- + 
  | username | password | active | max_connections | 
  + ---------- + ---------- + -------- + ----------------- + 
  | chuser | chpasswd | 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)

Connecting to ClickHouse from MySQL Client 

By default ProxySQL opens the port 6090 to receive user access to ClickHouse:

  # Use username and password above 
  # If it is a different machine, remember to change the IP 
  root@10.xxxx: / root # mysql -h 127.0.0.1 -P 6090 -uclicku -pclickp --prompt "ProxySQL-To-ClickHouse>" 
  Welcome to the MariaDB monitor. Commands end with; or \ g. 
  Your MySQL connection id is 64 
  Server version: 5.6.81 (ProxySQL ClickHouse Module) 
  Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
  Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement. 
  ProxySQL-To-ClickHouse >

Querying ClickHouse like MySQL

 MySQL [(none)] > select version (); 
+ ------------------- + 
| version | 
+ ------------------- + 
| 5.6.81-clickhouse | 
+ ------------------- + 
1 row in set (0.00 sec) 
MySQL [(none)] > select now (); 
+ --------------------- + 
| now () | 
+ --------------------- + 
| 2019-12-25 20:17:14 | 
+ --------------------- + 
1 row in set (0.00 sec) 
MySQL [(none)] > select today (); 
+ ------------ + 
| today () | 
+ ------------ + 
| 2019-12-25 | 
+ ------------ + 
1 row in set (0.00 sec) 
# Our table is over 55 billion 
ProxySQL-To-ClickHouse > select count (*) from mysql_audit_log_data; 
+ ------------- + 
| count () | 
+ ------------- + 
| 539124837571 | 
+ ------------- + 
1 row in set (8.31 sec) 

Limitations

  • This ProxySQL solution works only when it is on the local ClickHouse (Note.- ClickHouse instance cannot have password in this ecosystem / recommended solution)
  • ProxySQL query rewrite limitations – The simple queries work seamless, The complex query rewrite are quite expense and there might some levels of SQL semantics limitations 

Conclusion – ProxySQL Version 2.0.8 new features and enhancements

  • Changed default max_allowed_packet from 4M to 64M
  • Added support for mysqldump 8.0 and Admin #2340
  • Added new variable mysql-aurora_max_lag_ms_only_read_from_replicas : if max_lag_ms is used and the writer is in the reader hostgroup, the writer will be excluded if at least N replicas are good candidates.
  • Added support for unknown character set , and for collation id greater than 255 #1273
  • Added new variable mysql-log_unhealthy_connections to suppress messages related to unhealty clients connections being closed
  • Reimplemented rules_fast_routing using khash
  • Added support for SET CHARACTERSET #1692
  • Added support for same node into multiple Galera clusters #2290
  • Added more verbose output for error 2019 (Can’t initialize character set) #2273
  • Added more possible values for mysql_replication_hostgroups.check_type #2186
    • read_only | innodb_read_only
    • read_only & innodb_read_only
  • Added support and packages for RHEL / CentOS 8

References: 

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide

1 Trackbacks & Pingbacks

  1. /home1/minerho3/public_html/wp-includes/comment-template.php on line 677
    " class=""> MySQL query rewrite for ClickHouse using ProxySQL 

Comments are closed.

UA-155183614-1