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 :

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:

Verify what users are loaded at runtime:

Verify what users are saved on disk:

Configure new users:

Load configuration to runtime:

Save users to disk:

Verify runtime and disk:

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

SHOW DATABASES

SHOW PROCESSLIST

SHOW TABLES

USE

DESC and DESCRIBE

SELECT

DROP

SHOW CREATE TABLE

CREATE TABLE example 1

CREATE TABLE example 2

CREATE TEMPORARY TABLE

CREATE TEMPORARY TABLE and

INSERT

INSERT is limited to only INSERT ... SELECT

ALTER TABLE

SET