ProxySQL Support for ClickHouse

How to enable support for ClickHouse

To enable support for ClickHouse is it necessary to start proxysql with the --clickhouse-server option.

When support for ClickHouse is enabled, ProxySQL will:

listen on port 6090 , accepting connection using MySQL protocol
establish connections to ClickHouse server on localhost , using Default username and empty password.

This behavior is currently hardcoded. In the future it will be possible to change the listening port, the clickhouse server(s), and credentials.

Supported Datatypes

Datatypes currently supported:

Int8 , UInt8 , Int16 , UInt16 , Int32 , UInt32 , Int64 and UInt64
Float32 and Float64
String and FixedString
Date
DateTime

Configure ProxySQL

Currently it is only possible to configure the credentials that clients will use to connect to ProxySQL. These credentials are used only to authenticate the clients, and are not used to connect to ClickHouse. To connect to ClickHouse, Default username and empty password are used (for now).

Configuration table

In ProxySQL’s Admin, a new table defines the credentials that clients will use to connect to ProxySQL, clickhouse_users :

Admin> 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)

To conform with ProxySQL’s Admin 3 layers configuration system, 3 tables exist:

clickhouse_users : in memory config
runtime_clickhouse_users : runtime config
disk.clickhouse_users : persistent config

Configuration example:

Verify what users are configured:

Admin> SELECT * FROM clickhouse_users;
Empty set (0.00 sec)

Verify what users are loaded at runtime:

Admin> SELECT * FROM runtime_clickhouse_users;
Empty set (0.00 sec)

Verify what users are saved on disk:

Admin> SELECT * FROM disk.clickhouse_users;
Empty set (0.00 sec)

Configure new users:

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

Admin> SELECT * FROM clickhouse_users;
+----------+----------+--------+-----------------+
| username | password | active | max_connections |
+----------+----------+--------+-----------------+
| clicku   | clickp   | 1      | 100             |
+----------+----------+--------+-----------------+
1 row in set (0.00 sec)

Load configuration to runtime:

Admin> LOAD CLICKHOUSE USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Save users to disk:

Admin> SAVE CLICKHOUSE USERS TO DISK;
Query OK, 0 rows affected (0.34 sec)

Verify runtime and disk:

Admin> SELECT * FROM runtime_clickhouse_users;
+----------+----------+--------+-----------------+
| username | password | active | max_connections |
+----------+----------+--------+-----------------+
| clicku   | clickp   | 1      | 100             |
+----------+----------+--------+-----------------+
1 row in set (0.00 sec)

Admin> SELECT * FROM disk.clickhouse_users;
+----------+----------+--------+-----------------+
| username | password | active | max_connections |
+----------+----------+--------+-----------------+
| clicku   | clickp   | 1      | 100             |
+----------+----------+--------+-----------------+
1 row in set (0.00 sec)

Now we can connect to port 6090 using username clicku and password clickp.
Again, note that these credentials are only used to connect to ProxySQL: ProxySQL will connect to ClickHouse using Default username and empty password.

New Admin commands to support ClickHouse users

The following new commands (and their aliases) were introduced to manage ClickHouse users:

LOAD CLICKHOUSE USERS TO MEMORY ( aliases: LOAD CLICKHOUSE USERS TO MEM and LOAD CLICKHOUSE USERS FROM DISK)
SAVE CLICKHOUSE USERS FROM MEMORY ( aliases: SAVE CLICKHOUSE USERS FROM MEM and SAVE CLICKHOUSE USERS TO DISK)
LOAD CLICKHOUSE USERS TO RUNTIME (aliases: LOAD CLICKHOUSE USERS TO RUN, LOAD CLICKHOUSE USERS FROM MEMORY and LOAD CLICKHOUSE USERS FROM MEM)
SAVE CLICKHOUSE USERS FROM RUNTIME (aliases: SAVE CLICKHOUSE USERS FROM RUN, SAVE CLICKHOUSE USERS TO MEMORY and SAVE CLICKHOUSE USERS TO MEM)

Use ProxySQL to send commands to ClickHouse

Commands supported

Currently ProxySQL allows only commands starting with the following words. Everything else is rejected with an error stating that the command is not supported.
Supported commands:

SELECT
SET
USE
SHOW
DESC and DESCRIBE
CREATE , ALTER , DROP and RENAME
INSERT (very limited support!!)

NOTE: Only TEXT protocol is supported. BINARY protocol (prepared statements) is not supported.

Examples

Connect

$ mysql -u clicku -pclickp -h 127.0.0.1 -P6090 --prompt "ProxySQL-ClickHouse> "
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 3
Server version: 5.5.30 (ProxySQL ClickHouse Module)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

ProxySQL-ClickHouse>

SHOW DATABASES

ProxySQL-ClickHouse> SHOW DATABASES;
+---------+
| name    |
+---------+
| default |
| system  |
+---------+
2 rows in set (0.04 sec)

SHOW PROCESSLIST

ProxySQL-ClickHouse> SHOW PROCESSLIST;
Empty set (0.16 sec)

SHOW TABLES

ProxySQL-ClickHouse> SHOW TABLES;
+----------+
| name     |
+----------+
| numbers  |
| numbers2 |
| numbers3 |
+----------+
3 rows in set (0.00 sec)

USE

ProxySQL-ClickHouse> USE system
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
ProxySQL-ClickHouse> SHOW TABLES;
+----------------------+
| name                 |
+----------------------+
| asynchronous_metrics |
| build_options        |
| clusters             |
| columns              |
| databases            |
| dictionaries         |
| events               |
| functions            |
| graphite_retentions  |
| merges               |
| metrics              |
| numbers              |
| numbers_mt           |
| one                  |
| parts                |
| processes            |
| query_log            |
| replicas             |
| replication_queue    |
| settings             |
| tables               |
+----------------------+
21 rows in set (0.00 sec)

DESC and DESCRIBE

ProxySQL-ClickHouse> DESC numbers;
+--------+--------+--------------+--------------------+
| name   | type   | default_type | default_expression |
+--------+--------+--------------+--------------------+
| number | UInt64 |              |                    |
+--------+--------+--------------+--------------------+
1 row in set (0.24 sec)

ProxySQL-ClickHouse> DESCRIBE settings;
+---------+--------+--------------+--------------------+
| name    | type   | default_type | default_expression |
+---------+--------+--------------+--------------------+
| name    | String |              |                    |
| value   | String |              |                    |
| changed | UInt8  |              |                    |
+---------+--------+--------------+--------------------+
3 rows in set (0.00 sec)

SELECT

ProxySQL-ClickHouse> SELECT * FROM numbers;
+----+-------+
| id | name  |
+----+-------+
| 2  | two   |
| 1  | one   |
| 1  | one   |
| 2  | two   |
| 1  | one   |
| 2  | two   |
| 3  | three |
+----+-------+
7 rows in set (0.09 sec)

DROP

ProxySQL-ClickHouse> USE default
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
ProxySQL-ClickHouse> SHOW TABLES;
+----------+
| name     |
+----------+
| numbers  |
| numbers2 |
| numbers3 |
+----------+
3 rows in set (0.00 sec)

ProxySQL-ClickHouse> DROP TABLE numbers3;
Query OK, 0 rows affected (0.11 sec)

ProxySQL-ClickHouse> SHOW TABLES;
+----------+
| name     |
+----------+
| numbers  |
| numbers2 |
+----------+
2 rows in set (0.00 sec)

SHOW CREATE TABLE

ProxySQL-ClickHouse> SHOW CREATE TABLE numbers\G
*************************** 1. row ***************************
statement: CREATE TABLE default.numbers ( id UInt64,  name String) ENGINE = Memory
1 row in set (0.14 sec)

CREATE TABLE example 1

ProxySQL-ClickHouse> CREATE TABLE newTable (id UInt64,  name String, EventDate Date) ENGINE = MergeTree(EventDate, (id, EventDate), 1024);
Query OK, 0 rows affected (0.38 sec)

ProxySQL-ClickHouse> SHOW TABLES;
+----------+
| name     |
+----------+
| newTable |
| numbers  |
| numbers2 |
+----------+
3 rows in set (0.00 sec)

ProxySQL-ClickHouse> SHOW CREATE TABLE newTable\G
*************************** 1. row ***************************
statement: CREATE TABLE default.newTable ( id UInt64,  name String,  EventDate Date) ENGINE = MergeTree(EventDate, (id, EventDate), 1024)
1 row in set (0.00 sec)

CREATE TABLE example 2

ProxySQL-ClickHouse> SHOW TABLES;
+----------+
| name     |
+----------+
| newTable |
| numbers  |
| numbers2 |
+----------+
3 rows in set (0.01 sec)

ProxySQL-ClickHouse> CREATE TABLE newTable2 ENGINE = MergeTree(EventDate, (id, EventDate), 256) AS SELECT * FROM newTable;
Query OK, 0 rows affected (0.20 sec)

ProxySQL-ClickHouse> SHOW TABLES;
+-----------+
| name      |
+-----------+
| newTable  |
| newTable2 |
| numbers   |
| numbers2  |
+-----------+
4 rows in set (0.00 sec)

ProxySQL-ClickHouse> SHOW CREATE TABLE newTable2\G
*************************** 1. row ***************************
statement: CREATE TABLE default.newTable2 ( id UInt64,  name String,  EventDate Date) ENGINE = MergeTree(EventDate, (id, EventDate), 256)
1 row in set (0.00 sec)

CREATE TEMPORARY TABLE

CREATE TEMPORARY TABLE and

ProxySQL-ClickHouse> SELECT COUNT(*) FROM numbers;
+---------+
| COUNT() |
+---------+
| 7       |
+---------+
1 row in set (0.00 sec)

ProxySQL-ClickHouse> CREATE TEMPORARY TABLE numbers4 ENGINE = Memory  AS SELECT * FROM numbers;
Query OK, 0 rows affected (0.08 sec)

ProxySQL-ClickHouse> SELECT COUNT(*) FROM numbers4;
+---------+
| COUNT() |
+---------+
| 7       |
+---------+
1 row in set (0.00 sec)

INSERT

INSERT is limited to only INSERT ... SELECT

ProxySQL-ClickHouse> SELECT COUNT(*) FROM numbers4;
+---------+
| COUNT() |
+---------+
| 7       |
+---------+
1 row in set (0.00 sec)

ProxySQL-ClickHouse> INSERT INTO numbers4 VALUES (6,'six');
ERROR 1148 (42000): Command not supported
ProxySQL-ClickHouse> INSERT INTO numbers4 SELECT * FROM numbers ORDER BY id DESC LIMIT 1;
Query OK, 0 rows affected (0.15 sec)

ProxySQL-ClickHouse> SELECT COUNT(*) FROM numbers4;
+---------+
| COUNT() |
+---------+
| 8       |
+---------+
1 row in set (0.00 sec)

ALTER TABLE

ProxySQL-ClickHouse> SHOW CREATE TABLE newTable2\G
*************************** 1. row ***************************
statement: CREATE TABLE default.newTable2 ( id UInt64,  name String,  EventDate Date) ENGINE = MergeTree(EventDate, (id, EventDate), 256)
1 row in set (0.00 sec)

ProxySQL-ClickHouse> ALTER TABLE newTable2 ADD COLUMN col2 String;
Query OK, 0 rows affected (0.16 sec)

ProxySQL-ClickHouse> SHOW CREATE TABLE newTable2\G
*************************** 1. row ***************************
statement: CREATE TABLE default.newTable2 ( id UInt64,  name String,  EventDate Date,  col2 String) ENGINE = MergeTree(EventDate, (id, EventDate), 256)
1 row in set (0.01 sec)

ProxySQL-ClickHouse> ALTER TABLE newTable2 DROP COLUMN col2;
Query OK, 0 rows affected (0.08 sec)

ProxySQL-ClickHouse> SHOW CREATE TABLE newTable2\G
*************************** 1. row ***************************
statement: CREATE TABLE default.newTable2 ( id UInt64,  name String,  EventDate Date) ENGINE = MergeTree(EventDate, (id, EventDate), 256)
1 row in set (0.00 sec)

SET

ProxySQL-ClickHouse> SET max_rows_to_sort = 100000;
Query OK, 0 rows affected (0.13 sec)

ProxySQL-ClickHouse> SET non_existing_variable = 100000;
ERROR 1148 (42000): DB::Exception: Unknown setting non_existing_variable

ClickHouse Configuration