13. Clickhouse support for proxysql configuration
Note: during the writing time of the article from April 201904 to may 201905, subsequent official updates in github were not written
~
~
ClickHouse Support
1, Preface
ProxySQL support for ClickHouse is still in the experimental phase and may change.
1. How to enable support for ClickHouse
To enable support for ClickHouse, you must start proxysql with the -- ClickHouse server option.
When ClickHouse support is enabled, ProxySQL will:
1) Listen to port 6090, accept the connection using MySQL protocol;
2) Use the Default user name and empty password to establish a connection to the ClickHouse server on localhost.
This behavior is currently hard coded. In the future, you can change the listening port, clickhouse server, and credentials.
2. Supported Datatypes
Currently supported data types:
1)Int8 , UInt8 , Int16 , UInt16 , Int32 , UInt32 , Int64 and UInt64
2)Float32 and Float64
3)String and FixedString
4)Date
5)DateTime
3. Configure proxysql
Currently, only the credentials that clients use to connect to ProxySQL can be configured. These credentials are only used to authenticate clients and will not be used to connect to ClickHouse. To connect to ClickHouse, temporarily use the Default user name and an empty password.
1) Configuration table
In the Admin of ProxySQL, a new table is defined, which contains the credentials used by the client to connect to ProxySQL. It is 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)
In order to conform to the three-tier configuration system structure of ProxySQL's Admin, there are three tables:
Clickhouse? Users: memory layer configuration
Runtime? Clickhouse? Users: runtime layer configuration
DISK.clickhouse'users: persistent DISK layer configuration
2) Configuration example:
The MEMORY layer views the user configuration:
Admin> SELECT * FROM clickhouse_users;
See which users are loaded into the RUNTIME layer:
Admin> SELECT * FROM runtime_clickhouse_users;
See which users are persistent to the DISK layer:
Admin> SELECT * FROM disk.clickhouse_users;
To configure a new user:
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 user configuration to RUNTIME layer:
Admin> LOAD CLICKHOUSE USERS TO RUNTIME;
Persistent user configuration to DISK layer:
Admin> SAVE CLICKHOUSE USERS TO DISK;
View the new user configuration of RUNTIME layer and DISK layer:
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 the username clicku and the password clickp.
Again, these credentials are only used for clients that connect to ProxySQL: ProxySQL will connect to ClickHouse with a Default user name and an empty password.
4. 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 (alias: LOAD CLICKHOUSE USERS TO MEM and LOAD CLICKHOUSE USERS FROM DISK) SAVE CLICKHOUSE USERS FROM MEMORY (alias: SAVE CLICKHOUSE USERS FROM MEM and SAVE CLICKHOUSE USERS TO DISK) LOAD CLICKHOUSE USERS TO RUNTIME (alias: LOAD CLICKHOUSE USERS TO RUN, LOAD CLICKHOUSE USERS FROM MEMORY and LOAD CLICKHOUSE USERS FROM MEM) SAVE CLICKHOUSE USERS FROM RUNTIME (alias: SAVE CLICKHOUSE USERS FROM RUN, SAVE CLICKHOUSE USERS TO MEMORY and SAVE CLICKHOUSE USERS TO MEM)
2, Use ProxySQL to send commands to ClickHouse
1. Supported commands [Commands supported]
Currently, ProxySQL only allows commands that start with the following words. All other commands are rejected with an error indicating 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 statement) is not supported.
example:
$ 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>
1)SHOW DATABASES
ProxySQL-ClickHouse> SHOW DATABASES; +---------+ | name | +---------+ | default | | system | +---------+ 2 rows in set (0.04 sec)
2)SHOW PROCESSLIST
ProxySQL-ClickHouse> SHOW PROCESSLIST; Empty set (0.16 sec)
3)SHOW TABLES
ProxySQL-ClickHouse> SHOW TABLES; +----------+ | name | +----------+ | numbers | | numbers2 | | numbers3 | +----------+ 3 rows in set (0.00 sec)
4)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)
5)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)
6)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)
7)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)
8)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)
9)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)
10)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)
2,CREATE TEMPORARY TABLE
1)CREATE TEMPORARY TABLE and INSERT
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)
2)INSERT
INSERT 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)
3,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)
4. SET parameter [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
~
~
complete!