RAL Syntax #

RAL (Resource & Rule Administration Language) is responsible for the added-on features of hint, transaction type switch, resharding, sharding execute planning and so on.

Hint #

Statement	Function	Example
set readwrite_splitting hint source = [auto / write]	For current connection, set read/write splitting routing strategy (automatic or forced to write data source)	set readwrite_splitting hint source = write
set sharding hint database_value = yy	For current connection, set sharding value for database sharding only, yy: sharding value	set sharding hint database_value = 100
add sharding hint database_value xx= yy	For current connection, add sharding value for table, xx: logic table, yy: database sharding value	add sharding hint database_value t_order = 100
add sharding hint table_value xx = yy	For current connection, add sharding value for table, xx: logic table, yy: table sharding value	add sharding hint table_value t_order = 100
clear hint	For current connection, clear all hint settings	clear hint
clear [sharding hint / readwrite_splitting hint]	For current connection, clear hint settings of sharding or readwrite splitting	clear readwrite_splitting hint
show [sharding / readwrite_splitting] hint status	For current connection, query hint settings of sharding or readwrite splitting	show readwrite_splitting hint status

Migration #

Statement	Function	Example
SHOW MIGRATION RULE	Show migration rule	SHOW MIGRATION RULE
ALTER 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 from source to target	MIGRATE TABLE ds_0.public.t_order INTO t_order
SHOW MIGRATION LIST	Query running list	SHOW MIGRATION LIST
SHOW MIGRATION STATUS jobId	Query migration status	SHOW MIGRATION STATUS 1234
STOP MIGRATION jobId	Stop migration	STOP MIGRATION 1234
START MIGRATION jobId	Start the stopped migration	START MIGRATION 1234
CHECK MIGRATION jobId	Data consistency check	CHECK MIGRATION 1234
SHOW MIGRATION CHECK ALGORITHMS	Show available consistency check algorithms	SHOW MIGRATION CHECK ALGORITHMS
CHECK MIGRATION jobId BY TYPE(NAME=algorithmTypeName)	Data consistency check with defined algorithm	CHECK MIGRATION 1234 BY TYPE(NAME="DATA_MATCH”)
SHOW MIGRATION CHECK STATUS jobId	Query data consistency check status	SHOW MIGRATION CHECK STATUS 1234
STOP MIGRATION CHECK jobId	Stop data consistency check	STOP MIGRATION CHECK 1234
DROP MIGRATION CHECK jobId	Drop data consistency check	DROP MIGRATION CHECK 1234
START MIGRATION CHECK jobId	Start data consistency check	START MIGRATION CHECK 1234
ROLLBACK MIGRATION jobId	Rollback migration	ROLLBACK MIGRATION 1234
COMMIT MIGRATION jobId	Commit migration	COMMIT MIGRATION 1234

Reshard #

Statement	Function	Example
SHOW RESHARDING RULE	Show resharding rule	SHOW RESHARDING RULE
ALTER RESHARDING RULE	Alter resharding rule	ALTER RESHARDING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW RESHARDING LIST	Query running list	SHOW RESHARDING LIST
SHOW RESHARDING STATUS jobId	Query resharding status	SHOW RESHARDING STATUS 1234
STOP RESHARDING jobId	Stop resharding	STOP RESHARDING 1234
START RESHARDING jobId	Start resharding	START RESHARDING 1234
CHECK RESHARDING jobId	Data consistency check with algorithm	CHECK RESHARDING 1234
SHOW RESHARDING CHECK ALGORITHMS	Show available consistency check algorithms	SHOW RESHARDING CHECK ALGORITHMS
CHECK RESHARDING jobId BY TYPE(NAME=algorithmTypeName)	Data consistency check with defined algorithm	CHECK RESHARDING 1234 BY TYPE(NAME=DEFAULT)
SHOW RESHARDING CHECK STATUS jobId	Query data consistency check status	SHOW RESHARDING CHECK STATUS 1234
STOP RESHARDING CHECK jobId	Stop data consistency check	STOP RESHARDING CHECK 1234
START RESHARDING CHECK jobId	Start data consistency check	START RESHARDING CHECK 1234
DROP RESHARDING CHECK jobId	Drop data consistency check	DROP RESHARDING CHECK 1234
STOP RESHARDING SOURCE WRITING jobId	The source DBPlusEngine data source is discontinued	STOP RESHARDING SOURCE WRITING 1234
FORCE? APPLY RESHARDING jobId	Switch to target DBPlusEngine metadata	APPLY RESHARDING 1234
RESTORE RESHARDING SOURCE WRITING jobId	The source DBPlusEngine data source resumes writing	RESTORE RESHARDING SOURCE WRITING 1234
FORCE? COMMIT RESHARDING jobId	Commit resharding	COMMIT RESHARDING 1234
ROLLBACK RESHARDING jobId	Rollback resharding	ROLLBACK RESHARDING 1234
AUTO RESHARD TABLE tableName BY rule	Automatic mode for expansion and contraction	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”)) );

Encryption #

Statement	Function	Example
SHOW ENCRYPTING RULE	Show encryption rule	SHOW ENCRYPTING RULE
ALTER ENCRYPTING RULE	Alter encryption rule	ALTER ENCRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW ENCRYPTING LIST;	Query running list	SHOW ENCRYPTING LIST;
SHOW ENCRYPTING STATUS {jobId};	Query task status, jobId: task ID	SHOW ENCRYPTING STATUS 1234;
ENCRYPT TABLE {tableName};	Start running task, tableName: table name	ENCRYPT TABLE t_user;
COMMIT ENCRYPTING {jobId};	Submit the encryption task, jobId: task id. The current version of commit only cleans up jobs, and subsequent versions will support commit and rollback	COMMIT ENCRYPTING 1234;

Decryption #

Statement	Function	Example
SHOW DECRYPTING RULE	Show decryption rule	SHOW DECRYPTING RULE
ALTER DECRYPTING RULE	Alter decryption rule	ALTER DECRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW DECRYPTING LIST;	Query running list	SHOW DECRYPTING LIST;
SHOW DECRYPTING STATUS {jobId};	Query task status, jobId: task ID	SHOW DECRYPTING STATUS 1234;
DECRYPT TABLE {tableName};	Start running task, tableName: table name	DECRYPT TABLE t_user;
COMMIT DECRYPTING {jobId};	Submit the decryption task, jobId: task id. The current version of commit only cleans up jobs, and subsequent versions will support commit and rollback	COMMIT ENCRYPTING 1234;

Key Exchange #

Statement	Function	Example

| REENCRYPT TABLE WITH RULE tableName| Key exchange phase 1 | 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}; | Key exchange phase 2 encryption, jobId: task id | START REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6;|

Key exchange phase 1 statement #

REENCRYPT TABLE WITH RULE tableName

Parameter description

Parameter name	Function
tableName	Key exchange table

Example

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

Key exchange phase 2 statement #

START REENCRYPTING jobId;

Parameter description

Parameter name	Function
jobid	Key exchange task id

Example

START REENCRYPTING jobId;

Circuit Breaker #

Statement	Function	Example
ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName]	Enable or disable read data source	ALTER READWRITE_SPLITTING RULE group_1 ENABLE read_ds_1
[ENABLE / DISABLE] COMPUTE NODE instanceId	Enable or disable proxy instance	DISABLE COMPUTE NODE instance_1
SHOW COMPUTE NODES	Query proxy instance information	SHOW COMPUTE NODES
SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE groupName) [FROM databaseName]	Query data sources status of readwrite splitting groups	SHOW STATUS FROM READWRITE_SPLITTING RULES

Global Rule #

Statement	Function	Example
SHOW AUTHORITY RULE	Query authority rule configuration	SHOW AUTHORITY RULE
SHOW TRANSACTION RULE	Query transaction rule configuration	SHOW TRANSACTION RULE
SHOW SQL_PARSER RULE	Query SQL parser rule configuration	SHOW SQL_PARSER RULE
SHOW TRAFFIC[ RULES / RULE ruleName]	Query the specified double routing rules or all double routing rules	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”))))	Create a dual routing rule,`TRAFFIC_ALGORITHM` support `SQL_MATCH` and `SQL_HINT` two types;`LOAD_BALANCER` support `RANDOM` and `ROUND_ROBIN` two types.	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”…)))	Alter transaction rule configuration，`DEFAULT`: default transaction type，support LOCAL、XA、BASE; `NAME`: name of transaction manager, support Atomikos, Narayana and 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)	Alter SQL parser rule configuration, `SQL_COMMENT_PARSE_ENABLE`: whether to parse the SQL comment, `PARSE_TREE_CACHE`: local cache configuration of syntax tree, `SQL_STATEMENT_CACHE`: local cache of SQL statement	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”))))	Modify dual routing rules,`TRAFFIC_ALGORITHM` support `SQL_MATCH` and `SQL_HINT` two types;`LOAD_BALANCER` support `RANDOM` and `ROUND_ROBIN` two types.	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]	Delete double routing rule	DROP TRAFFIC RULE sql_match_traffic

Variable Management #

In DistSQL, attribute configuration names are separated by underscores.

Statement	Function	Example
SET DIST VARIABLE variable_name = xx	variable_name as Attribute Configuration of proxy	SET VARIABLE sql_show = true
SHOW DIST VARIABLES	Query all attribute configurations of the proxy	SHOW ALL VARIABLES
SHOW DIST VARIABLE WHERE name = variable_name	Query the specified proxy attribute	SHOW VARIABLE WHERE name = sql_show

Special variables

The special variables are not in Attribute Configuration of proxy, However, it can be dynamically managed through DistSQL:

Statement	Function	Example
SHOW DIST VARIABLE WHERE name = transaction_type	Query Transaction Type	SHOW DIST VARIABLE WHERE name = transaction_type
SET DIST VARIABLE transaction_type = xx	Modify the transaction type of the current connection, support LOCAL, XA, BASE	SET DIST VARIABLE transaction_type = XA
SHOW DIST VARIABLE WHERE name = agent_plugins_enabled	Querying agent plugin enabling status	SHOW DIST VARIABLE WHERE name = agent_plugins_enabled
SET DIST VARIABLE agent_plugins_enabled = xx	Set the enabling status of the agent plug-in. The value is Boolean	SET DIST VARIABLE agent_plugins_enabled = true
SHOW DIST VARIABLE WHERE name = cached_connections	Query the number of connections currently cached	SHOW DIST VARIABLE WHERE name = cached_connections
SHOW DIST VARIABLE WHERE name = general_query_log	Querying the Full Log Enable Status	SHOW DIST VARIABLE WHERE name = general_query_log
SET DIST VARIABLE general_query_log = xx	Set the Full Audit Log Enable Status	SET DIST VARIABLE general_query_log = true
SHOW DIST VARIABLE WHERE name = slow_query_log	Querying the Slow Log Enable Status	SHOW DIST VARIABLE WHERE name = slow_query_log
SET DIST VARIABLE slow_query_log = xx	Set the Slow Log Enable Status	SET DIST VARIABLE slow_query_log = true
SHOW DIST VARIABLE WHERE name = long_query_time	Query Slow Query Threshold	SHOW DIST VARIABLE WHERE name = long_query_time
SET DIST VARIABLE long_query_time = xx	Set slow query threshold, unit is milliseconds	SET DIST VARIABLE long_query_time = 5000

Other #

Statement	Function	Example
SHOW COMPUTE NODE INFO	Query the instance information of the proxy	SHOW COMPUTE NODE INFO
SHOW COMPUTE NODE MODE	Query the mode configuration of the proxy	SHOW COMPUTE NODE MODE
LABEL COMPUTE NODE instanceId WITH labelName [ , labelName ]	Add tags to instances.	LABEL COMPUTE NODE 10.7.7.136@3309 with olap,group_by
UNLABEL COMPUTE NODE instanceId	Remove all tags from instances.	UNLABEL COMPUTE NODE 10.7.7.136@3309
UNLABEL COMPUTE NODE instanceId WITH labelName	Removes the specified tags from the instance	UNLABEL COMPUTE NODE 10.7.7.136@3309 WITH group_by
RELABEL COMPUTE NODE instanceId with labelName [ , labelName ]	Readd tags to the instances.(overwrite the original tags)	RELABEL COMPUTE NODE 10.7.7.136@3309 with label_9, label_0
REFRESH TABLE METADATA	Refresh the metadata of all tables	REFRESH TABLE METADATA
REFRESH TABLE METADATA tableName	Refresh the metadata of the specified table	REFRESH TABLE METADATA t_order
REFRESH TABLE METADATA tableName FROM STORAGE UNIT storageUnitName	Refresh the tables’ metadata in the specified data source	REFRESH TABLE METADATA t_order FROM STORAGE UNIT ds_1
REFRESH TABLE METADATA FROM STORAGE UNIT storageUnitName SCHEMA schemaName	Refresh the tables’ metadata in a schema of a specified data source. If there are no tables in the schema, the schema will be deleted.	REFRESH TABLE METADATA FROM STORAGE UNIT ds_1 SCHEMA db_schema
SHOW TABLE METADATA tableName [, tableName] …	Query table metadata	SHOW TABLE METADATA t_order
EXPORT DATABASE CONFIGURATION [FROM databaseName] [TO FILE “filePath”]	Export data sources and rule configurations to YAML format	EXPORT DATABASE CONFIGURATION FROM readwrite_splitting_db
IMPORT DATABASE CONFIGURATION FILE="file_path”	Import data sources and rule configurations from YAML, only supports import into an empty database	IMPORT DATABASE CONFIGURATION FILE = “/xxx/config-sharding.yaml”
SHOW RULES USED STORAGE UNIT storageUnitName [FROM databaseName]	Query the rules for using the specified data source in database	SHOW RULES USED STORAGE UNIT ds_0 FROM databaseName

Notice #

DBPlusEngine-Proxy does not support hint by default, to support it, set proxy-hint-enabled to true in conf/server.yaml.