Sharding

Syntax

Sharding Table Rule

CREATE SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
ALTER SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
DROP SHARDING TABLE RULE tableName [, tableName] ...
CREATE DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)
ALTER DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)
DROP DEFAULT SHARDING shardingScope STRATEGY;
CREATE SHARDING ALGORITHM shardingAlgorithmDefinition [, shardingAlgorithmDefinition] ...
ALTER SHARDING ALGORITHM shardingAlgorithmDefinition [, shardingAlgorithmDefinition] ...
DROP SHARDING ALGORITHM algorithmName [, algorithmName] ...
CREATE SHARDING KEY GENERATOR keyGeneratorDefinition [, keyGeneratorDefinition] ...
ALTER SHARDING KEY GENERATOR keyGeneratorDefinition [, keyGeneratorDefinition] ...
DROP SHARDING KEY GENERATOR [IF EXISTS] keyGeneratorName [, keyGeneratorName] ...
CREATE SHARDING AUDITOR auditorDefinition [, auditorDefinition] ...
ALTER SHARDING AUDITOR auditorDefinition [, auditorDefinition] ...
DROP SHARDING AUDITOR [IF EXISTS] auditorName [, auditorName] ...
shardingTableRuleDefinition:
    shardingAutoTableRule | shardingTableRule
shardingAutoTableRule:
    tableName(resources, shardingColumn, algorithmDefinition [, keyGenerateDeclaration] [, auditDeclaration])
shardingTableRule:
    tableName(dataNodes [, databaseStrategy] [, tableStrategy] [, keyGenerateDeclaration] [, auditDeclaration])
resources:
    RESOURCES(resource [, resource] ...)
dataNodes:
    DATANODES(dataNode [, dataNode] ...)
resource:
    resourceName | inlineExpression
dataNode:
    dataNodeName | inlineExpression
shardingColumn:
    SHARDING_COLUMN=columnName
algorithmDefinition:
    TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])
keyGenerateDeclaration:
    keyGenerateDefinition | keyGenerateConstruction
keyGenerateDefinition:
    KEY_GENERATE_STRATEGY(COLUMN=columnName, strategyDefinition)
auditDeclaration:
    auditDefinition | auditStrategy
auditDefinition:
    AUDIT_STRATEGY([(singleAuditDefinition),(singleAuditDefinition)], ALLOW_HINT_DISABLE=true)
singleAuditDefinition:
    NAME=auditor1, algorithmDefinition
auditStrategy:
    AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2], ALLOW_HINT_DISABLE=true)
shardingScope:
    DATABASE | TABLE
databaseStrategy:
    DATABASE_STRATEGY(shardingStrategy)
tableStrategy:
    TABLE_STRATEGY(shardingStrategy)
keyGenerateConstruction
    KEY_GENERATE_STRATEGY(COLUMN=columnName, KEY_GENERATOR=keyGenerateAlgorithmName)
shardingStrategy:
    TYPE=strategyType, shardingColumn, shardingAlgorithm
shardingAlgorithm:
    existingAlgorithm | autoCreativeAlgorithm
existingAlgorithm:
    SHARDING_ALGORITHM=shardingAlgorithmName
autoCreativeAlgorithm:
    SHARDING_ALGORITHM(algorithmDefinition)
strategyDefinition:
    TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])
shardingAlgorithmDefinition:
    shardingAlgorithmName(algorithmDefinition)
algorithmProperties:
    algorithmProperty [, algorithmProperty] ...
algorithmProperty:
    key=value   
keyGeneratorDefinition: 
    keyGeneratorName (algorithmDefinition)
auditorDefinition:
    auditorName (auditorAlgorithmDefinition)
auditorAlgorithmDefinition:
    TYPE(NAME=auditorAlgorithmType [, PROPERTIES([algorithmProperties])])

• RESOURCES needs to use data source resources managed by RDL
• shardingAlgorithmType specifies the type of automatic sharding algorithm, please refer to Auto Sharding Algorithm
• keyGenerateStrategyType specifies the distributed primary key generation strategy, please refer to Key Generate Algorithm
• auditorAlgorithmType specifies the sharding audit strategy, please refer to Sharding Audit Algorithm；
• Duplicate tableName will not be created
• shardingAlgorithm can be reused by different Sharding Table Rule, so when executing DROP SHARDING TABLE RULE, the corresponding shardingAlgorithm will not be removed
• To remove shardingAlgorithm, please execute DROP SHARDING ALGORITHM
• strategyType specifies the sharding strategy, please refer toSharding Strategy
• Sharding Table Rule supports both Auto Table and Table at the same time. The two types are different in syntax. For the corresponding configuration file, please refer to Sharding
• When using the autoCreativeAlgorithm way to specify shardingStrategy, a new sharding algorithm will be created automatically. The algorithm naming rule is tableName_strategyType_shardingAlgorithmType, such as t_order_database_inline

Sharding Binding Table Rule

CREATE SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
ALTER SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
DROP SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
bindTableRulesDefinition:
    (tableName [, tableName] ... )

• ALTER will overwrite the binding table configuration in the database with the new configuration

Sharding Broadcast Table Rule

CREATE SHARDING BROADCAST TABLE RULES (tableName [, tableName] ... )
ALTER SHARDING BROADCAST TABLE RULES (tableName [, tableName] ... )
DROP SHARDING BROADCAST TABLE RULES

• ALTER will overwrite the broadcast table configuration in the database with the new configuration

Example

Sharding Table Rule

Key Generator

CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME="SNOWFLAKE")
);
ALTER SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME="SNOWFLAKE"))
);
DROP SHARDING KEY GENERATOR snowflake_key_generator;

Auditor

CREATE SHARDING AUDITOR sharding_key_required_auditor (
TYPE(NAME="DML_SHARDING_CONDITIONS")
);
ALTER SHARDING AUDITOR sharding_key_required_auditor (
TYPE(NAME="DML_SHARDING_CONDITIONS")
);
DROP SHARDING AUDITOR IF EXISTS sharding_key_required_auditor;

Auto Table

CREATE SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1),
SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2],ALLOW_HINT_DISABLE=true)
);
ALTER SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1,resource_2,resource_3),
SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="16")),
KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2],ALLOW_HINT_DISABLE=true)
);
DROP SHARDING TABLE RULE t_order;
DROP SHARDING ALGORITHM t_order_hash_mod;

Table

CREATE SHARDING ALGORITHM table_inline (
TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}"))
);
CREATE SHARDING TABLE RULE t_order_item (
DATANODES("resource_${0..1}.t_order_item_${0..1}"),
DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="resource_${user_id % 2}")))),
TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
KEY_GENERATE_STRATEGY(COLUMN=another_id,KEY_GENERATOR=snowflake_key_generator),
AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2],ALLOW_HINT_DISABLE=true)
);
ALTER SHARDING ALGORITHM database_inline (
TYPE(NAME="inline",PROPERTIES("algorithm-expression"="resource_${user_id % 4}"))
),table_inline (
TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}"))
);
ALTER SHARDING TABLE RULE t_order_item (
DATANODES("resource_${0..3}.t_order_item${0..3}"),
DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
KEY_GENERATE_STRATEGY(COLUMN=another_id,KEY_GENERATOR=snowflake_key_generator),
AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2],ALLOW_HINT_DISABLE=true)
);
DROP SHARDING TABLE RULE t_order_item;
DROP SHARDING ALGORITHM database_inline;
CREATE DEFAULT SHARDING DATABASE STRATEGY (
TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=database_inline
);
ALTER DEFAULT SHARDING DATABASE STRATEGY (
TYPE="standard",SHARDING_COLUMN=another_id,SHARDING_ALGORITHM=database_inline
);
DROP DEFAULT SHARDING DATABASE STRATEGY;

Sharding Binding Table Rule

CREATE SHARDING BINDING TABLE RULES (t_order,t_order_item),(t_1,t_2);
ALTER SHARDING BINDING TABLE RULES (t_order,t_order_item);
DROP SHARDING BINDING TABLE RULES;
DROP SHARDING BINDING TABLE RULES (t_order,t_order_item);

Sharding Broadcast Table Rule

CREATE SHARDING BROADCAST TABLE RULES (t_b,t_a);
ALTER SHARDING BROADCAST TABLE RULES (t_b,t_a,t_3);
DROP SHARDING BROADCAST TABLE RULES;