Configure a Routing Algorithm for Logical Tables - Data Management - Alibaba Cloud - Data Management

A routing algorithm lets a query that carries the routing field of a logical table locate the target table shard directly, without manual route calculation. This topic covers the expression format, the configuration procedure, routing expression examples, and the built-in functions that you can call in an expression.

Why use a routing algorithm

A routing algorithm reduces routing overhead. After you configure a routing algorithm for a logical table, a query that carries the routing field quickly locates the specified table shard, so you no longer need to calculate routes manually or switch between physical databases and tables.

For more information about routing algorithms, see routing algorithm.

Use cases

Query data from specific table shards.
Change data in specific table shards.
Export data from specific table shards.

Prerequisites

Configure a logical table in a logical database. For instructions, see logical table.

Configure a simple modulo routing algorithm

The following procedure configures a routing algorithm that uses a simple modulo operation. Before you select a modulo operation, note the field types that each operation supports:

Simple Modulo Operation — Supports only fields of a numeric type.
Modular hashing — Supports fields of a numeric or string type.

Go to the Global Search page and click Query to the right of the target logical database to open the SQL Console page.
Note
You can also choose SQL Console > SQL Console from the top menu bar.
In the upper-right corner of the page, click the icon to go to the table list page.
In the row that contains the target logical table, click Configure Algorithm.
On the algorithm list page, click Create.
Configure Algorithm Type, Modulo Operation, Table Partitioning Field, and Modulus.
In this example, set Algorithm Type to Modulo Operation on One Column, Modulo Operation to Simple Modulo Operation, Table Partitioning Field to id, and Modulus to 4. The expression is #id#%4.
Click Save.
Query table data or run INSERT statements on the logical table to verify the routing result. The system calculates the routing result and inserts the data into the table shard that has the corresponding index.
For example, if you insert a data row with an ID of 9, the calculated routing result is 1, and the data is inserted into the logic_table_01 table shard.

Expression format

The table sharding routing algorithm in DMS is defined by a Groovy expression, similar to the routing algorithms that are configured in application code. An expression references a routing field in the format # + routing field + #, for example, #shardKey#. The routing field is the field that you select as the Table Partitioning Field.

Routing expression examples

In the following expressions, user_id is an example table field. The examples are grouped by what the expression determines: the table shard only, both the database shard and the table shard, or a target derived from a date, from digits in a string, or from a custom function.

Routing by table rule
- Simple modulo operation
  - Modulo on numbers: #user_id#%100
  - Chained modulo: #user_id#%10000%100
  - Modulo on the Java hash of a number: Math.abs(#user_id#.hashCode())%100
- Modular hashing on strings
  - Hash of a numeric string: Math.abs(#user_id#.toString().hashCode())%100
  - Hash of a string: Math.abs(#user_id#.hashCode())%100
  - CobarHash: Math.abs(cobarHash(#column#,start, end)).intdiv(8)
  - CobarOldHash: Math.abs(cobarOldHash(#column#, len)).intdiv(8)
Routing by database rule
- Same database and table name rule: 'schema_prefix_'+(#user_id#%10)+'.table_name'
- Database name and table name
  - 'schema_prefix_'+(#user_id#%100)+'.table_name_prefix_'+(#user_id#%1000)
  - Same set of table shards in each database shard: 'schema_prefix_'+lastSwapZero(String.valueOf((#user_id#%1024).intdiv(128)),4)+'.table_name_prefix_'+lastSwapZero(String.valueOf((#user_id#%128)),4)
  - Route to a database shard by dividing digits 16 and 17 of the string by 2, and route to a table shard by using digits 16 and 17 of the string: 'schema_prefix_'+substring(#EXTEND_ID#,16,18).toLong().intdiv(2)+'.table_name_prefix_'+substring(#EXTEND_ID#,16,18)
Routing by date
Route to the same table on the same day of each month: dayOfMonth(#time#)
Routing by the third-to-last digit of a string
The table name increments in steps of 10, so the extracted digit is multiplied by 10. If the increment is 1, no multiplication is required: Integer.valueOf(substring(#ip_id#,-3,-2))*10
Other complex routing
Use a custom function:
```
String func(String arg){ return arg.hashCode()%10;}
'table_name_'+func(#user_id#)+'_other_'+func(#user_id#)
```
- Add a line break between the function and the expression.
- DMS supports routing by using the CRC32(java.util.zip.CRC32) function.

Built-in functions for routing expressions

You can call the following built-in functions directly in a routing expression.

cobarOldHash — The legacy CobarHash algorithm.

public static long cobarOldHash(String s, int len) {
  long h = 0;
  int sLen = s.length();
  for (int i = 0; (i < len && i < sLen); i++) {
      h = (h << 5) - h + s.charAt(i);
  }
  return h;
}

cobarHash — The new cobarHash algorithm.

public static long cobarHash(String s, int start, int end) {
  if (start < 0) {
      start = 0;
  }
  if (end > s.length()) {
      end = s.length();
  }
  long h = 0;
  for (int i = start; i < end; ++i) {
      h = (h << 5) - h + s.charAt(i);
  }
  return h;
}

weekOfYear — Returns the week of the year.

public static int weekOfYear(String dateValue) {
  Date date = DateTimeUtils.getSomeDate(dateValue);
  if(date != null) {
      return DateTimeUtils.getWeekOfYear(date);
  }
  return 0;
}

dayOfYear — Returns the day of the year.

public static int dayOfYear(String dateValue) {
  Date date = DateTimeUtils.getSomeDate(dateValue);
  if(date != null) {
      return DateTimeUtils.getDayOfYear(date);
  }
  return 0;
}

dayOfMonth — Returns the day of the month.

public static int dayOfMonth(String dateValue) {
  Date date = DateTimeUtils.getSomeDate(dateValue);
  if (date != null) {
      return DateTimeUtils.getDayOfMonth(date);
  }
  return 0;
}

dayOfWeek — Returns the day of the week.

public static int dayOfWeek(String dateValue) {
  Date date = DateTimeUtils.getSomeDate(dateValue);
  if (date != null) {
      int dayOfWeek = DateTimeUtils.getDayOfWeek(date);
      if (dayOfWeek==1){
          dayOfWeek=7;
      }else {
          dayOfWeek=dayOfWeek-1;
      }
      return dayOfWeek;
  }
  return 0;
}

substring — Extracts a substring. The start and end positions support negative values, which count backward from the end of the string.

public static String substring(String value, int start, int end) {
  return StringUtils.substring(value, start, end);
}

public static String substring(String value, int start) {
  return StringUtils.substring(value, start);
}

last4swap — Returns the last four characters of a string. If the string is shorter than four characters, zeros are padded on the left. The last four characters are then swapped in pairs.

public static String last4swap(String value) {
  if(value.length() < 4) {
      value = StringUtils.leftPad(value, 4, '0');
  }
  return StringUtils.substring(value, -2)+StringUtils.substring(value, -4, -2);
}

lastSwapZero — Returns a string of the specified minimum length. If the string is shorter than the specified length, zeros are padded before the digits.

public static String lastSwapZero(String value, int length) {
  if (value.length() < length) {
      return StringUtils.leftPad(value, length, '0');
  }
  return value;
}