ClickHouse Configuration

Overview

ProxySQL provides support for ClickHouse, enabling high-performance analytical querying through a MySQL protocol interface.

Enabling ClickHouse Support

To activate ClickHouse functionality, start ProxySQL with the --clickhouse-server option. When enabled, ProxySQL will:

Listen on port 6090 using MySQL protocol
Connect to ClickHouse on localhost using “Default” username and empty password (currently hardcoded)

Supported Data Types

ProxySQL handles these ClickHouse data types:

Integer types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64
Floating point: Float32, Float64
String types: String, FixedString
Date/time: Date, DateTime

Configuration

Global Variables

The ClickHouse module is compile-time gated by PROXYSQLCLICKHOUSE and must also be selected at startup with --clickhouse-server. Its four released global variables are dynamic once the module is running:

Variable	Default	Accepted values	Runtime effect
`clickhouse-mysql_ifaces`	`0.0.0.0:6090`	Any non-empty interface string; no general syntax or bind validation during LOAD	Reconciles the module’s MySQL-protocol listeners after `LOAD CLICKHOUSE VARIABLES TO RUNTIME`.
`clickhouse-hostname`	`127.0.0.1`	Any non-empty string; empty is an accepted no-op	ClickHouse backend host used for new connections. An empty assignment returns success but leaves the current host unchanged.
`clickhouse-port`	`9000`	Integer 1–65535	ClickHouse native-protocol backend port used for new connections.
`clickhouse-read_only`	`false`	Case-insensitive `true`/`1` or `false`/`0`	Enables or disables the module’s write-command rejection.

An out-of-range port, an invalid Boolean, or an empty interface string leaves the corresponding runtime value unchanged. For interfaces, empty is the only up-front rejection: any non-empty interface string is accepted without general parse or bind validation during LOAD. Reconciliation closes the old listener sockets before parsing and binding the replacements, so a malformed address or bind failure can leave the intended listener unavailable. Verify replacement listener access before SAVE CLICKHOUSE VARIABLES TO DISK. Backend target changes apply to new connections; they do not rewrite an already-established connection.

User Credentials Table

The clickhouse_users table manages client authentication credentials (separate from ClickHouse backend credentials):

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

Three configuration layers exist:

clickhouse_users (in-memory)
runtime_clickhouse_users (active configuration)
disk.clickhouse_users (persistent storage)

Configuration Example

INSERT INTO clickhouse_users VALUES ('clicku','clickp',1,100);
LOAD CLICKHOUSE USERS TO RUNTIME;
SAVE CLICKHOUSE USERS TO DISK;

Admin Commands

LOAD CLICKHOUSE USERS TO RUNTIME
SAVE CLICKHOUSE USERS TO DISK
LOAD CLICKHOUSE USERS TO MEMORY
SAVE CLICKHOUSE USERS FROM RUNTIME

The complete variables command family is:

Memory → runtime: LOAD CLICKHOUSE VARIABLES TO RUNTIME, FROM MEMORY, FROM MEM, or TO RUN
Runtime → memory: SAVE CLICKHOUSE VARIABLES TO MEMORY, TO MEM, FROM RUNTIME, or FROM RUN
Disk → memory: LOAD CLICKHOUSE VARIABLES FROM DISK, TO MEMORY, or TO MEM
Memory → disk: SAVE CLICKHOUSE VARIABLES TO DISK, FROM MEMORY, or FROM MEM

Supported Commands

ProxySQL accepts these command types:

SELECT, SET, USE, SHOW
DESC/DESCRIBE
CREATE, ALTER, DROP, RENAME
INSERT (limited to INSERT...SELECT only)

Note: Only TEXT protocol is supported; prepared statements are not available.

Key Limitations

“INSERT VALUES” syntax is rejected with error “Command not supported”
Only INSERT...SELECT syntax is permitted
BINARY protocol/prepared statements unavailable