When you query, change, or export data from a logical table, DMS uses a routing algorithm to identify which physical table shard to target. Once configured, DMS evaluates the algorithm automatically—no manual calculation or table switching needed.
For background on routing algorithms, see Routing algorithm.
How routing expressions work
DMS routing algorithms use Groovy expressions. Wrap the routing field name in # characters to reference it in the expression:
#<routing-field>#Example: #shardKey#
The expression syntax is similar to what you would write in application code.
Prerequisites
Before you begin, make sure you have:
A logical database in DMS
A logical table created in that database (see Logical table)
Configure an algorithm
The following steps configure a simple modulo algorithm as an example.
On the Global Search page, find the logical database that contains the logical table.
Click Query in the Actions column to open the SQL Console.
Alternatively, choose SQL Console > SQL Console in the top navigation bar, then select the logical database from the drop-down list.
In the upper-right corner, click the
icon to open the table list.Find the logical table, then click Configure Algorithm in the Routing Algorithm column.
Click Create. In the Create Algorithm dialog box, configure the following parameters: With these settings, the routing expression is
#id#%4.Simple modulo operations apply to numeric fields only. Modular hashing operations apply to both numeric and string fields.
Parameter Description Example value Algorithm Type The type of algorithm to apply Modulo Operation on One ColumnModulo Operation The modulo variant to use Simple Modulo OperationTable Partitioning Field The field used as the routing key idModulus The divisor in the modulo calculation 4Click Save.
After saving, run queries or INSERT statements against the logical table. DMS evaluates the routing expression and directs each operation to the correct shard automatically.
Example: Inserting a row where id = 9 produces a routing result of 1, so DMS inserts the row into the shard named logic_table_01.
Sample routing expressions
All examples use user_id as the routing field. The Field type column shows which field types each expression supports.
Table sharding
| Expression | Field type | Description |
|---|---|---|
#user_id#%100 | Numeric | Modulo on a numeric field |
#user_id#%10000%100 | Numeric | Secondary (chained) modulo |
Math.abs(#user_id#.hashCode())%100 | Numeric | Modulo on the Java hash code of a numeric field |
Math.abs(#user_id#.toString().hashCode())%100 | String | Hash a numeric value converted to string |
Math.abs(#user_id#.hashCode())%100 | String | Hash a string field directly |
Math.abs(cobarHash(#column#, start, end)).intdiv(8) | String | CobarHash (new version) |
Math.abs(cobarOldHash(#column#, len)).intdiv(8) | String | CobarOldHash (previous version) |
Database sharding
| Expression | Description |
|---|---|
'schema_prefix_'+(#user_id#%10)+'.table_name' | Route to same-name tables across different databases |
'schema_prefix_'+(#user_id#%100)+'.table_name_prefix_'+(#user_id#%1000) | Route to a specific database and table |
'schema_prefix_'+lastSwapZero(String.valueOf((#user_id#%1024).intdiv(128)),4)+'.table_name_prefix_'+lastSwapZero(String.valueOf((#user_id#%128)),4) | Route to the same table shard in each database shard |
'schema_prefix_'+substring(#EXTEND_ID#,16,18).toLong().intdiv(2)+'.table_name_prefix_'+substring(#EXTEND_ID#,16,18) | Route by the 16th–17th characters of EXTEND_ID |
Date-based routing
| Expression | Description |
|---|---|
dayOfMonth(#time#) | Route to the same table on the same day of each month |
String character–based routing
| Expression | Description |
|---|---|
Integer.valueOf(substring(#ip_id#,-3,-2))*10 | Multiply the third-last character of ip_id by 10 when the table index increments by 10; omit the multiplication when the index increments by 1 |
User-defined functions (UDFs)
Define functions on separate lines, then reference them in the routing expression:
String func(String arg){ return arg.hashCode()%10;}
'table_name_'+func(#user_id#)+'_other_'+func(#user_id#)DMS also supportsCRC32(java.util.zip.CRC32) function routing.
Built-in functions
DMS provides the following built-in functions for use in routing expressions.
Hash functions
| Function | Parameters | Description |
|---|---|---|
cobarHash(s, start, end) | s (String): the input string<br>start (int): start index, inclusive; clamped to 0 if negative<br>end (int): end index, exclusive; clamped to string length if out of range | Hashes the substring from start to end. This is the current CobarHash version. |
cobarOldHash(s, len) | s (String): the input string<br>len (int): number of characters to hash from the start | Hashes the first len characters. This is the previous CobarHash version. |
Date functions
All date functions accept a date string and return an integer.
| Function | Returns | Description |
|---|---|---|
weekOfYear(dateValue) | int | Week number within the year |
dayOfYear(dateValue) | int | Day number within the year |
dayOfMonth(dateValue) | int | Day number within the month |
dayOfWeek(dateValue) | int | Day of the week (Monday = 1, Sunday = 7) |
String functions
| Function | Parameters | Returns | Description |
|---|---|---|---|
substring(value, start, end) | value (String)<br>start (int): start position; negative values count from the end<br>end (int): end position; negative values count from the end | String | Returns the substring between start and end. |
substring(value, start) | value (String)<br>start (int): start position | String | Returns the substring from start to the end of the string. |
last4swap(value) | value (String) | String | Returns the last four characters, left-padded with 0 if the string is shorter than four characters, with the third-last and fourth-last characters swapped with the last two. |
lastSwapZero(value, length) | value (String)<br>length (int): minimum length | String | Left-pads the string with 0 if it is shorter than length; returns the string as-is if it is already length or more characters long. |