ProxySQL官档翻译__13_ProxySQL配置之ClickHouse_Support

翻译

追梦沙哈拉 2020-04-28 11:27:26 博主文章分类：ProxySQL

文章标签 ProxySQL 读写分离负载均衡中间件 MySQL 文章分类 MySQL 数据库

13_ProxySQL配置之ClickHouse_Support

备注：文章编写时间201904-201905期间，后续官方在github的更新没有被写入

~ ~ ClickHouse Support

一、前言[Preface]

对ClickHouse的ProxySQL支持还处于实验阶段，可能会有所变化。

1、如何启用对ClickHouse的支持[How to enable support for ClickHouse]

要启用对ClickHouse的支持，必须使用--clickhouse-server选项启动proxysql。

当启用对ClickHouse的支持时，ProxySQL将： 1）监听端口6090，接受使用MySQL协议的连接； 2）使用 Default 用户名和空密码在localhost上建立与ClickHouse服务器的连接。此行为目前是硬编码的。将来可以更改侦听端口，clickhouse服务器和凭据。

2、支持的数据类型[Supported Datatypes]

目前支持的数据类型： 1）Int8 , UInt8 , Int16 , UInt16 , Int32 , UInt32 , Int64 and UInt64 2）Float32 and Float64 3）String and FixedString 4）Date 5）DateTime

3、配置ProxySQL[Configure ProxySQL]

目前，只能配置客户端用于连接ProxySQL的凭据。这些凭据仅用于验证客户端，不会用于连接到ClickHouse。要连接到ClickHouse，暂时使用 Default 用户名和空密码。

1）配置表[Configuration table]

在ProxySQL的Admin中，定义了一个新表，包含了客户端用于连接到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)

为了符合ProxySQL的Admin的3层配置系统结构，以此，它也存在3个表： clickhouse_users : MEMORY层配置 runtime_clickhouse_users : RUNTIME层配置 disk.clickhouse_users : 持久化的DISK层配置

2）配置示例：

MEMORY层查看用户配置的内容：

Admin> SELECT * FROM clickhouse_users;

查看哪些用户加载到了RUNTIME层：

Admin> SELECT * FROM runtime_clickhouse_users;

查看哪些用户持久化到了DISK层：

Admin> SELECT * FROM disk.clickhouse_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)

加载用户配置到RUNTIME层：

Admin> LOAD CLICKHOUSE USERS TO RUNTIME;

持久化用户配置到DISK层：

Admin> SAVE CLICKHOUSE USERS TO DISK;

查看RUNTIME层和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)

现在我们可以使用用户名clicku和密码clickp连接到端口6090。再次注意，这些凭据仅用于连接到ProxySQL的客户端：ProxySQL将使用 Default 用户名和空密码连接到ClickHouse。

4、支持ClickHouse用户设置的新增Admin命令[New Admin commands to support ClickHouse users]

引入了以下新命令（及其别名）来管理ClickHouse用户：

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

二、使用ProxySQL向ClickHouse发送命令[Use ProxySQL to send commands to ClickHouse]

1、支持的命令[Commands supported]

目前，ProxySQL仅允许以下单词开头的命令。其他所有命令都被拒绝，并显示错误，指出该命令不受支持。支持的命令： SELECT SET USE SHOW DESC and DESCRIBE CREATE , ALTER , DROP and RENAME INSERT(支持非常有限!!) 注意：只支持文本协议。不支持二进制协议（Prepared语句）。

例子：

$ 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仅限于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]

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

~ ~ 完毕！

上一篇：ProxySQL官档翻译__12_ProxySQL配置之ProxySQL_Cluster

下一篇：ProxySQL官档翻译__14_ProxySQL_Configuration

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯