CREATE SHARDING TABLE RULE

Description

The CREATE SHARDING TABLE RULE syntax is used to add sharding table rule for the currently selected database

Syntax

  1. CreateShardingTableRule ::=
  2.   'CREATE' 'SHARDING' 'TABLE' 'RULE' ( tableDefinition | autoTableDefinition ) ( ',' ( tableDefinition | autoTableDefinition ) )*
  4. tableDefinition ::= 
  5.    tableName '(' 'DATANODES' '(' dataNode ( ',' dataNode )* ')'  ( ',' 'DATABASE_STRATEGY' '(' strategyDefinition ')' )?  ( ',' 'TABLE_STRATEGY' '(' strategyDefinition ')' )?  ( ',' 'KEY_GENERATE_STRATEGY' '(' keyGenerateStrategyDefinition ')' )? ( ',' 'AUDIT_STRATEGY' '(' auditStrategyDefinition ')' )? ')'
  7. autoTableDefinition ::=
  8.     tableName '(' 'STORAGE_UNITS' '(' storageUnitName ( ',' storageUnitName )*  ')' ',' 'SHARDING_COLUMN' '=' columnName ',' algorithmDefinition ( ',' 'KEY_GENERATE_STRATEGY' '(' keyGenerateStrategyDefinition ')' )? ( ',' 'AUDIT_STRATEGY' '(' auditStrategyDefinition ')' )? ')'
  10. strategyDefinition ::=
  11.   'TYPE' '=' strategyType ',' ( 'SHARDING_COLUMN' | 'SHARDING_COLUMNS' ) '=' columnName ',' algorithmDefinition
  13. keyGenerateStrategyDefinition ::= 
  14.   'KEY_GENERATE_STRATEGY' '(' 'COLUMN' '=' columnName ','  algorithmDefinition  ')' 
  16. auditStrategyDefinition ::= 
  17.   'AUDIT_STRATEGY' '(' 'AUDITORS' '=' '[' auditorName ',' auditorName ']' ',' 'ALLOW_HINT_DISABLE' '=' 'TRUE | FALSE' ')'
  18.   |
  19.   'AUDIT_STRATEGY' '(' '[' 'NAME' '=' auditorName ',' algorithmDefinition ']' ',' '[' 'NAME' '=' auditorName ',' algorithmDefinition ']' ')'
  21. algorithmDefinition ::=
  22.   'SHARDING_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' algorithmType ( ',' 'PROPERTIES'  '(' propertyDefinition  ')' )?')' ')'
  24. propertyDefinition ::=
  25.   ( key  '=' value ) ( ',' key  '=' value )* 
  27. tableName ::=
  28.   identifier
  30. storageUnitName ::=
  31.   identifier
  33. columnName ::=
  34.   identifier
  36. auditorName ::=
  37.   identifier
  39. algorithmName ::=
  40.   identifier
  42. algorithmType ::=
  43.   string

Supplement

  • tableDefinition is defined for standard sharding table rule; autoTableDefinition is defined for auto sharding table rule. For standard sharding rules and auto sharding rule, refer to Data Sharding;
  • use standard sharding table rule:
    • DATANODES can only use resources that have been added to the current database, and can only use INLINE expressions to specify required resources;
    • DATABASE_STRATEGY, TABLE_STRATEGY are the database sharding strategy and the table sharding strategy, which are optional, and the default strategy is used when not configured;
    • The attribute TYPE in strategyDefinition is used to specify the type of Sharding Algorithm, currently only supports STANDARD, COMPLEX. Using COMPLEX requires specifying multiple sharding columns with SHARDING_COLUMNS.
  • use auto sharding table rule:
    • STORAGE_UNITS can only use storage units that have been registered to the current database, and the required storage units can be specified by enumeration or INLINE expression;
    • Only auto sharding algorithm can be used, please refer to Auto Sharding Algorithm.
  • algorithmType is the sharding algorithm type, please refer to Sharding Algorithm;
  • The auto-generated algorithm naming rule is tableName _ strategyType _ shardingAlgorithmType;
  • The auto-generated primary key strategy naming rule is tableName _ strategyType;
  • KEY_GENERATE_STRATEGY is used to specify the primary key generation strategy, which is optional. For the primary key generation strategy, please refer to Distributed Primary Key.
  • AUDIT_STRATEGY is used to specify the sharding audit strategy, which is optional. For the sharding audit generation strategy, please refer to Sharding Audit.

Example

1.Standard sharding table rule

  1. CREATE SHARDING TABLE RULE t_order_item (
  2. DATANODES("ds_${0..1}.t_order_item_${0..1}"),
  3. DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
  4. TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}")))),
  5. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  6. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  7. );

2.Auto sharding table rule

  1. CREATE SHARDING TABLE RULE t_order (
  2. STORAGE_UNITS(ds_0,ds_1),
  3. SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
  4. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  5. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  6. );

Reserved word

CREATE, SHARDING, TABLE, RULE, DATANODES, DATABASE_STRATEGY, TABLE_STRATEGY, KEY_GENERATE_STRATEGY, STORAGE_UNITS, SHARDING_COLUMN, TYPE, SHARDING_COLUMN, KEY_GENERATOR, SHARDING_ALGORITHM, COLUMN, NAME, PROPERTIES, AUDIT_STRATEGY, AUDITORS, ALLOW_HINT_DISABLE