ProxySQL configuration for ClickHouse

ProxySQL is the high-performance SQL proxy, which has support for MySQL at its initial releases. From ProxySQL 1.4.3, it has support for ClickHouse as well. This article will explain “How to configure the ProxySQL to connect ClickHouse and its limitations.”

Install ProxySQL:

ProxySQL can be directly download and installed from GitHub. Use the link to download the ProxySQL based on  your OS.

wget https://github.com/sysown/proxysql/releases/download/v2.4.3/proxysql_2.4.3-ubuntu22_arm64.deb
dpkg -i proxysql_2.4.3-ubuntu22_arm64.deb
dpkg -l | grep -i proxysql

After the installation, We need to use the option “–clickhouse-server” while starting the ProxySQL service. Otherwise, ProxySQL will be started with the default settings, it don’t have the support to ClickHouse.

proxysql --clickhouse-server

By default, ProxySQL will listen the port 6032 for admin login and 6033 port for client connections. When using the option “–clickhouse-server”, the port 6090 will be assigned to connect ClickHouse. This connection will be establish using MySQL protocol.

root@vm1:~# netstat -tulnp | grep -i proxysql
tcp        0      0 0.0.0.0:6090            0.0.0.0:*               LISTEN      16464/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      16464/proxysql      
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      16464/proxysql      

Configuration:

By default, we are not allowed to login using default credentials.

root@vm1:~# mysql -uadmin -padmin -P6090 -h127.0.0.1
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)

we need to configure the dedicated ClickHouse user in ProxySQL to make the connections through 6090.  So, login the ProxySQL admin console and create the ClickHouse user.

Note: We need to connect ProxySQL using the MySQL protocol, so if the MySQL client is not installed already, install it using the following command.

apt-get install mysql-client

Lets login the ProxySQL admin console using default credentials ( admin:admin:127.0.0.1:6032 – user:password:host:port )

mysql -uadmin -padmin -P6032 -h127.0.0.1

After login, we can see the following ClickHouse related tables. We can use those tables to whitelist the ClickHouse users.

mysql> show tables like '%clickhouse%';
+--------------------------+
| tables                   |
+--------------------------+
| clickhouse_users         |
| runtime_clickhouse_users |
+--------------------------+
2 rows in set (0.00 sec)

• clickhouse_users: Main table to configure the users
• runtime_clickhouse_users: Display the runtime settings

Table structure:

mysql> show create table clickhouse_users\G
*************************** 1. row ***************************
       table: clickhouse_users
Create Table: CREATE TABLE clickhouse_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    PRIMARY KEY (username))
1 row in set (0.00 sec)

• username: User to connect
• password: Password to authenticate the user
• active: enable/disable the user
• max_connections: Maximum allowed connections for the users

Let’s add the ClickHouse user “chista” and password will be like “Chista@321”.

mysql> insert into clickhouse_users (username,password,active,max_connections) 
    -> values
    -> ('chista','Chista@321',1,100);
Query OK, 1 row affected (0.00 sec)

Once we add the config, we need to load the configuration as well as persist them in the disk. otherwise, when the ProxySQL gets restarted, the configurations will be erased. So, the following commands are necessary to load configuration in memory and save them on disk.

mysql> load clickhouse users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save clickhouse users to disk;
Query OK, 0 rows affected (0.02 sec)

Lets query both the tables

mysql> select * from clickhouse_users;
+----------+------------+--------+-----------------+
| username | password   | active | max_connections |
+----------+------------+--------+-----------------+
| chista   | Chista@321 | 1      | 100             |
+----------+------------+--------+-----------------+
1 row in set (0.00 sec)

mysql> select * from runtime_clickhouse_users;
+----------+------------+--------+-----------------+
| username | password   | active | max_connections |
+----------+------------+--------+-----------------+
| chista   | Chista@321 | 1      | 100             |
+----------+------------+--------+-----------------+
1 row in set (0.00 sec)

Connect to ClickHouse through ProxySQL:

Now we are all set to connect the ClickHouse through ProxySQL using the MySQL protocol. The connection string will be like ( user:password:host:port – chista:Chista@321:127.0.0.1:6090 )

root@vm1:~# mysql -uchista -pChista@321 -P6090 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL ClickHouse Module)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| name               |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| information_schema |
| sri                |
| system             |
+--------------------+
5 rows in set (0.00 sec)

Yes, it works!

Here, you may have the question “How the user “chista” authenticated the backend ClickHouse server” 🙂

The credentials we added into ProxySQL (chista:Chista@321) are only used to connect to ProxySQL. By default, ProxySQL will connect to ClickHouse using “default” username and “empty” password.

So, make sure you have the user “default” with NO password. If you have the password for “default” user, you will get the following exception.

mysql> show databases;
ERROR 1148 (42000): Backend not connected

Lets do some Queries,

Currently the following statements are supported.

• SELECT
• SET
• USE
• SHOW
• DESC and DESCRIBE
• CREATE , ALTER , DROP and RENAME
• INSERT

root@vm1:~# mysql -uchista -pChista@321 -P6090 -h127.0.0.1

Server version: 5.5.30 (ProxySQL ClickHouse Module)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

mysql> use sri
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> select count(*) from cell_towers;
+----------+
| count()  |
+----------+
| 43276158 |
+----------+
1 row in set (0.01 sec)

mysql> select radio,mcc from cell_towers limit 1;
+-------+------+
| radio | mcc  |
+-------+------+
| CDMA  |  302 |
+-------+------+
1 row in set (0.01 sec)

mysql> rename table cell_towers to backup_cell_towers;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------------+
| name               |
+--------------------+
| backup_cell_towers |
+--------------------+
1 row in set (0.01 sec)

Queries are working as expected.

Limitations:

• You can only establish the connections to the ClickHouse server on localhost. This means ProxySQL and ClickHouse needed to be installed on the same server
• ClickHouse listening port 6090 is hardcoded now. So, you can’t change the port.
• ClickHouse “default” user name and “empty” password are hardcoded now. So, you can’t change your username or password.