Skip to main content

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​

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

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