RAL 语法 #
RAL (Resource & Rule Administration Language) 为 SphereEx-DBPlusEngine 的管理语言, 负责强制路由、事务类型切换、弹性伸缩、分片执行计划查询等增量功能的操作。
强制路由 #
语句 | 说明 | 示例 |
---|---|---|
set readwrite_splitting hint source = [auto / write] | 针对当前连接,设置读写分离的路由策略(自动路由或强制到写库) | set readwrite_splitting hint source = write |
set sharding hint database_value = yy | 针对当前连接,设置 hint 仅对数据库分片有效,并添加分片值,yy:数据库分片值 | set sharding hint database_value = 100 |
add sharding hint database_value xx = yy | 针对当前连接,为表 xx 添加分片值 yy,xx:逻辑表名称,yy:数据库分片值 | add sharding hint database_value t_order= 100 |
add sharding hint table_value xx = yy | 针对当前连接,为表 xx 添加分片值 yy,xx:逻辑表名称,yy:表分片值 | add sharding hint table_value t_order = 100 |
clear hint | 针对当前连接,清除 hint 所有设置 | clear hint |
clear [sharding hint / readwrite_splitting hint] | 针对当前连接,清除 sharding 或 readwrite_splitting 的 hint 设置 | clear readwrite_splitting hint |
show [sharding / readwrite_splitting] hint status | 针对当前连接,查询 sharding 或 readwrite_splitting 的 hint 设置 | show readwrite_splitting hint status |
数据迁移 #
语句 | 说明 | 示例 |
---|---|---|
SHOW MIGRATION RULE | 查询数据迁移规则 | SHOW MIGRATION RULE |
ALTER MIGRATION RULE | 修改数据迁移规则 | ALTER MIGRATION RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’))))) |
MIGRATE TABLE ds.schema.table INTO table | 从源端迁移到目标端 | MIGRATE TABLE ds_0.public.t_order INTO t_order |
SHOW MIGRATION LIST | 查询运行列表 | SHOW MIGRATION LIST |
SHOW MIGRATION STATUS jobId | 查询作业状态 | SHOW MIGRATION STATUS 1234 |
STOP MIGRATION jobId | 停止作业 | STOP MIGRATION 12345 |
START MIGRATION jobId | 开启停止的作业 | START MIGRATION 1234 |
CHECK MIGRATION jobId | 数据一致性校验 | CHECK MIGRATION 1234 |
SHOW MIGRATION CHECK ALGORITHMS | 查询可用的一致性校验算法 | SHOW MIGRATION CHECK ALGORITHMS |
CHECK MIGRATION jobId BY TYPE(NAME=algorithmTypeName) | 数据一致性校验,使用指定的校验算法 | CHECK MIGRATION 1234 BY TYPE(NAME="DATA_MATCH”) |
SHOW MIGRATION CHECK STATUS jobId | 查询数据一致性校验进度 | SHOW MIGRATION CHECK STATUS 1234 |
STOP MIGRATION CHECK jobId | 停止数据一致性校验 | STOP MIGRATION CHECK 1234 |
DROP MIGRATION CHECK jobId | 删除数据一致性校验 | DROP MIGRATION CHECK 1234 |
START MIGRATION CHECK jobId | 开启数据一致性校验 | START MIGRATION CHECK 1234 |
ROLLBACK MIGRATION jobId | 撤销作业。注意:该语句会清理目标端表,请谨慎操作 | ROLLBACK MIGRATION 1234 |
COMMIT MIGRATION jobId | 提交作业 | COMMIT MIGRATION 1234 |
弹性伸缩 #
语句 | 说明 | 示例 |
---|---|---|
SHOW RESHARDING RULE | 查询重分片规则 | SHOW RESHARDING RULE |
ALTER RESHARDING RULE | 修改重分片规则 | ALTER RESHARDING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’))))) |
SHOW RESHARDING LIST | 查询运行列表 | SHOW RESHARDING LIST |
SHOW RESHARDING STATUS jobId | 查询任务状态 | SHOW RESHARDING STATUS 1234 |
STOP RESHARDING jobId | 停止运行任务 | STOP RESHARDING 12345 |
START RESHARDING jobId | 开始运行任务 | START RESHARDING 1234 |
CHECK RESHARDING jobId | 数据一致性校验 | CHECK RESHARDING 1234 |
SHOW RESHARDING CHECK ALGORITHMS | 查询可用的一致性校验算法 | SHOW RESHARDING CHECK ALGORITHMS |
CHECK RESHARDING jobId BY TYPE(NAME=algorithmTypeName) | 数据一致性校验,使用指定的校验算法 | CHECK RESHARDING 1234 BY TYPE(NAME=DEFAULT) |
SHOW RESHARDING CHECK STATUS jobId | 查询数据一致性校验进度 | SHOW RESHARDING CHECK STATUS 1234 |
STOP RESHARDING CHECK jobId | 停止数据一致性校验 | STOP RESHARDING CHECK 1234 |
START RESHARDING CHECK jobId | 开启数据一致性校验 | START RESHARDING CHECK 1234 |
DROP RESHARDING CHECK jobId | 删除数据一致性校验 | DROP RESHARDING CHECK 1234 |
STOP RESHARDING SOURCE WRITING jobId | 旧的 DBPlusEngine 数据源停写 | STOP RESHARDING SOURCE WRITING 1234 |
FORCE? APPLY RESHARDING jobId | 切换至新的 DBPlusEngine 元数据 | APPLY RESHARDING 1234 |
RESTORE RESHARDING SOURCE WRITING jobId | 旧的 DBPlusEngine 数据源恢复写 | RESTORE RESHARDING SOURCE WRITING 1234 |
FORCE? COMMIT RESHARDING jobId | 提交作业 | COMMIT RESHARDING 1234 |
ROLLBACK RESHARDING jobId | 撤销作业 | ROLLBACK RESHARDING 1234 |
AUTO RESHARD TABLE tableName BY rule | 扩缩容自动模式 | AUTO RESHARD TABLE t_order BY(STORAGE_UNITS(ds_0, ds_1, ds_2), SHARDING_COLUMN=order_id, TYPE(NAME="hash_mod”,PROPERTIES(“sharding-count"="6”)), KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake”)) ); |
加密洗数 #
语句 | 说明 | 示例 |
---|---|---|
SHOW ENCRYPTING RULE | 查询加密洗数规则 | SHOW ENCRYPTING RULE |
ALTER ENCRYPTING RULE | 修改加密洗数规则 | ALTER ENCRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’))))) |
SHOW ENCRYPTING LIST; | 查询运行列表 | SHOW ENCRYPTING LIST; |
SHOW ENCRYPTING STATUS {jobId}; | 查询任务状态,jobId:任务 id | SHOW ENCRYPTING STATUS 1234; |
ENCRYPT TABLE {tableName}; | 开始运行任务,tableName:表名 | ENCRYPT TABLE t_user; |
COMMIT ENCRYPTING {jobId}; | 提交洗数任务,jobId:任务 id。当前版本 commit 只做清理 job 的操作,后续版本中将支持 commit 和 roolback | COMMIT ENCRYPTING 1234; |
解密洗数 #
语句 | 说明 | 示例 |
---|---|---|
SHOW DECRYPTING RULE | 查询解密洗数规则 | SHOW DECRYPTING RULE |
ALTER DECRYPTING RULE | 修改解密洗数规则 | ALTER DECRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’))))) |
SHOW DECRYPTING LIST; | 查询运行列表 | SHOW DECRYPTING LIST; |
SHOW DECRYPTING STATUS {jobId}; | 查询任务状态,jobId:任务 id | SHOW DECRYPTING STATUS 1234; |
DECRYPT TABLE {tableName}; | 开始运行任务,tableName:表名 | DECRYPT TABLE t_user; |
COMMIT DECRYPTING {jobId}; | 提交解密洗数任务,jobId:任务 id。当前版本 commit 只做清理 job 的操作,后续版本中将支持 commit 和 roolback | COMMIT ENCRYPTING 1234; |
换密钥 #
语句 | 说明 | 示例 |
---|---|---|
REENCRYPT TABLE WITH RULE tableName | 换密钥第一阶段 | REENCRYPT TABLE WITH RULE t_encrypt ( COLUMNS((NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_QUERY_COLUMN=user_assisted,LIKE_QUERY_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES’,PROPERTIES(‘aes-key-value'='665544aaa’))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='AES’,PROPERTIES(‘aes-key-value'='523234abc’))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE’))),(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES’,PROPERTIES(‘aes-key-value'='445533ccc’))))), QUERY_WITH_CIPHER_COLUMN=true); |
START REENCRYPTING {jobId}; | 换密钥第二阶段洗数,jobId:任务 id | START REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6; |
换密钥第一阶段语句 #
REENCRYPT TABLE WITH RULE tableName
参数说明
参数名 | 说明 |
---|---|
tableName | 换密钥的表 |
示例
REENCRYPT TABLE WITH RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_QUERY_COLUMN=user_assisted,LIKE_QUERY_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='665544aaa'))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='523234abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))),
(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc'))))
), QUERY_WITH_CIPHER_COLUMN=true);
换密钥第二阶段洗数语句 #
START REENCRYPTING jobId;
参数说明
参数名 | 说明 |
---|---|
jobid | 换密钥作业的id |
示例
START REENCRYPTING jobId;
熔断 #
语句 | 说明 | 示例 |
---|---|---|
ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName] | 启用 / 禁用读库 | ALTER READWRITE_SPLITTING RULE group_1 ENABLE read_ds_1 |
[ENABLE / DISABLE] COMPUTE NODE instanceId | 启用 / 禁用 proxy 实例 | DISABLE COMPUTE NODE instance_1 |
SHOW COMPUTE NODES | 查询 proxy 实例信息 | SHOW COMPUTE NODES |
SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE groupName) [FROM databaseName] | 查询读写分离数据源状态 | SHOW STATUS FROM READWRITE_SPLITTING RULES |
全局规则 #
语句 | 说明 | 示例 |
---|---|---|
SHOW AUTHORITY RULE | 查询权限规则配置 | SHOW AUTHORITY RULE |
SHOW TRANSACTION RULE | 查询事务规则配置 | SHOW TRANSACTION RULE |
SHOW SQL_PARSER RULE | 查询解析引擎规则配置 | SHOW SQL_PARSER RULE |
SHOW TRAFFIC[ RULES / RULE ruleName] | 查询指定的双路由规则或者所有双路由规则 | SHOW TRAFFIC RULES; |
CREATE TRAFFIC RULE sql_match_traffic (LABLES(xxx),TRAFFIC_ALGORITHM(TYPE(NAME=xxx,PROPERTIES(“key” = “value”))), LOAD_BALANCER(TYPE(NAME=xxx,PROPERTIES(“key"="value”)))) | 创建双路由规则,TRAFFIC_ALGORITHM 支持 SQL_MATCH 与 SQL_HINT 两种类型 LOAD_BALANCER 支持 RANDOM 与 ROUND_ROBIN 两种类型 | CREATE TRAFFIC RULE sql_match_traffic (LABLES(OLTP),TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES(“sql” = “SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))),LOAD_BALANCER(TYPE(NAME=RANDOM))) |
ALTER TRANSACTION RULE(DEFAULT=xx,TYPE(NAME=xxx, PROPERTIES(“key1"="value1”,“key2"="value2”…))) | 更新事务规则配置,DEFAULT :默认事务类型,支持 LOCAL、XA、BASE;NAME :事务管理器名称,支持 Atomikos、Narayana 和 Bitronix | ALTER TRANSACTION RULE(DEFAULT=XA,TYPE(NAME=Narayana, PROPERTIES(“databaseName"="jbossts”,“host"="127.0.0.1”))) |
ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=xx, PARSE_TREE_CACHE(INITIAL_CAPACITY=xx, MAXIMUM_SIZE=xx, CONCURRENCY_LEVEL=xx), SQL_STATEMENT_CACHE(INITIAL_CAPACITY=xxx, MAXIMUM_SIZE=xxx, CONCURRENCY_LEVEL=xxx) | 更新解析引擎规则配置,SQL_COMMENT_PARSE_ENABLE :是否解析 SQL 注释,PARSE_TREE_CACHE :语法树本地缓存配置,SQL_STATEMENT_CACHE :SQL 语句本地缓存配置项 | ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=false, PARSE_TREE_CACHE(INITIAL_CAPACITY=10, MAXIMUM_SIZE=11, CONCURRENCY_LEVEL=1), SQL_STATEMENT_CACHE(INITIAL_CAPACITY=11, MAXIMUM_SIZE=11, CONCURRENCY_LEVEL=100) |
ALTER TRAFFIC RULE sql_match_traffic (LABLES(xxx),TRAFFIC_ALGORITHM(TYPE(NAME=xxx,PROPERTIES(“key” = “value”))),LOAD_BALANCER(TYPE(NAME=xxx,PROPERTIES(“key"="value”)))) | 修改双路由规则,TRAFFIC_ALGORITHM 支持 SQL_MATCH 与 SQL_HINT 两种类型 LOAD_BALANCER 支持 RANDOM 与 ROUND_ROBIN 两种类型 | ALTER TRAFFIC RULE sql_match_traffic (LABLES(OLTP),TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES(“sql” = “SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))), LOAD_BALANCER(TYPE(NAME=RANDOM))) |
DROP TRAFFIC RULE ruleName [, ruleName] | 删除双路由规则 | DROP TRAFFIC RULE sql_match_traffic |
变量管理 #
在 DistSQL 中,属性配置名称均使用下划线分隔。
语句 | 说明 | 示例 |
---|---|---|
SET DIST VARIABLE variable_name = xx | variable_name 为 proxy 的属性配置 | SET DIST VARIABLE sql_show = true |
SHOW DIST VARIABLES | 查询 proxy 所有的属性配置 | SHOW DIST VARIABLES |
SHOW DIST VARIABLE WHERE name = variable_name | 查询指定的 proxy 属性 | SHOW DIST VARIABLE WHERE name = sql_show |
- 特殊变量
特殊变量不在 Proxy 的 属性配置 中,但可以通过 DistSQL 动态管理:
语句 | 说明 | 示例 |
---|---|---|
SHOW DIST VARIABLE WHERE name = transaction_type | 查询事务类型 | SHOW DIST VARIABLE WHERE name = transaction_type |
SET DIST VARIABLE transaction_type = xx | 修改当前连接的事务类型, 支持 LOCAL,XA,BASE | SET DIST VARIABLE transaction_type = XA |
SHOW DIST VARIABLE WHERE name = agent_plugins_enabled | 查询 agent 插件启用状态 | SHOW DIST VARIABLE WHERE name = agent_plugins_enabled |
SET DIST VARIABLE agent_plugins_enabled = xx | 设置 agent 插件的启用状态,值为布尔型 | SET DIST VARIABLE agent_plugins_enabled = true |
SHOW DIST VARIABLE WHERE name = cached_connections | 查询当前缓存的连接数量 | SHOW DIST VARIABLE WHERE name = cached_connections |
SHOW DIST VARIABLE WHERE name = general_query_log | 查询全量日志开启状态 | SHOW DIST VARIABLE WHERE name = general_query_log |
SET DIST VARIABLE general_query_log = xx | 设置全量日志开启状态 | SET DIST VARIABLE general_query_log = true |
SHOW DIST VARIABLE WHERE name = slow_query_log | 查询慢日志开启状态 | SHOW DIST VARIABLE WHERE name = slow_query_log |
SET DIST VARIABLE slow_query_log = xx | 设置慢日志开启状态 | SET DIST VARIABLE slow_query_log = true |
SHOW DIST VARIABLE WHERE name = long_query_time | 查询慢查询阈值 | SHOW DIST VARIABLE WHERE name = long_query_time |
SET DIST VARIABLE long_query_time = xx | 设置慢查询阈值,单位为毫秒 | SET DIST VARIABLE long_query_time = 5000 |
其他 #
语句 | 说明 | 示例 |
---|---|---|
SHOW COMPUTE NODE INFO | 查询当前 proxy 的实例信息 | SHOW COMPUTE NODE INFO |
SHOW COMPUTE NODE MODE | 查询当前 proxy 的 mode 配置 | SHOW COMPUTE NODE MODE |
LABEL COMPUTE NODE instanceId WITH labelName [ , labelName ] | 为实例添加标签 | LABEL COMPUTE NODE 10.7.7.136@3309 with olap,group_by |
UNLABEL COMPUTE NODE instanceId | 移除实例的所有标签 | UNLABEL COMPUTE NODE 10.7.7.136@3309 |
UNLABEL COMPUTE NODE instanceId WITH labelName | 为实例移除指定的标签 | UNLABEL COMPUTE NODE 10.7.7.136@3309 WITH group_by |
RELABEL COMPUTE NODE instanceId with labelName [ , labelName ] | 为实例重新添加标签(覆盖原有的标签) | RELABEL COMPUTE NODE 10.7.7.136@3309 with label_9, label_0 |
REFRESH TABLE METADATA | 刷新所有表的元数据 | REFRESH TABLE METADATA |
REFRESH TABLE METADATA tableName | 刷新指定表的元数据 | REFRESH TABLE METADATA t_order |
REFRESH TABLE METADATA tableName FROM STORAGE UNIT storageUnitName | 刷新指定数据源中表的元数据 | REFRESH TABLE METADATA t_order FROM STORAGE UNIT ds_1 |
REFRESH TABLE METADATA FROM STORAGE UNIT storageUnitName SCHEMA schemaName | 刷新指定 schema 中表的元数据,如果 schema 中不存在表,则会删除该 schema | REFRESH TABLE METADATA FROM STORAGE UNIT ds_1 SCHEMA db_schema |
SHOW TABLE METADATA tableName [, tableName] … | 查询表的元数据 | SHOW TABLE METADATA t_order |
EXPORT DATABASE CONFIGURATION [FROM databaseName] [TO FILE “filePath”] | 将 database 中的数据源和规则配置导出为 YAML 格式 | EXPORT DATABASE CONFIGURATION TO FILE “/xxx/config-sharding.yaml” |
REFRESH DATABASE METADATA schemaName FROM GOVERNANCE CENTER | 刷新指定逻辑库的元数据 | REFRESH DATABASE METADATA sharding_db FROM GOVERNANCE CENTER |
IMPORT DATABASE CONFIGURATION FROM FILE “filePath” | 将 YAML 中的配置导入到 database 中,仅支持对空库进行导入操作 | IMPORT DATABASE CONFIGURATION FROM FILE “/xxx/config-sharding.yaml” |
SHOW RULES USED STORAGE UNIT storageUnitName [FROM databaseName] | 查询 database 中使用指定数据源的规则 | SHOW RULES USED STORAGE UNIT ds_0 FROM databaseName |
select [,string] … [,] decrypt({tableName}, {encryptColumn}, {encryptValue}), [,string] … | 查询解密结果,跟 select 用法一致,提供 decrypt() 函数,tableName 为需要解密的表,encryptColumn 为需要解密的列, encryptValue 为需要解密的真实值。使用时需要配置对应的加密规则。 | SELECT ‘Lm04PRW+sp+mG/QuimAUew==’, decrypt(t_user, pwd_cipher, ‘Lm04PRW+sp+mG/QuimAUew==’) UNION SELECT ‘XXX’, decrypt(t_user, pwd_cipher, ‘XXX’); |
注意事项 #
DBPlusEngine-Proxy 默认不支持 hint,如需支持,请在 conf/server.yaml
中,将 properties
的属性 proxy-hint-enabled
设置为 true。