All Products
Search
Document Center

Data Management:Configure a routing algorithm

Last Updated:Oct 31, 2023

This topic describes the format of a routing expression and how to configure a routing algorithm for a logical table.

Overview

You can configure a routing algorithm for a logical table to efficiently query data of a specific physical table that matches the logical table based on a routing field. This way, you do not need to perform manual calculations or switch between physical tables. This reduces the routing overhead.

For more information, see Routing algorithm.

Scenarios

  • Query the data of table shards.
  • Change the data of table shards.
  • Export the data of table shards.

Expression format

Data Management (DMS) allows you to define a routing algorithm to query table shards by using a Groovy expression. The format of a routing expression is similar to that used in application code.

You can specify a routing field in the following format: #Routing field#. Example: #shardKey#.

Procedure

In the following example, a routing algorithm is configured to perform a simple modulo operation.

  1. Create a logical table in a logical database. For more information, see Logical table.

  2. After the logical table is configured, find the logical database on the Global Search page. Then, click Query in the Actions column to go to the SQL Console page.

    Note

    You can also choose SQL Console > SQL Console in the top navigation bar. Then, select the logical database from the drop-down list to go to the SQL Console page.

  3. In the upper-right corner of the page, click the image.png icon to go to the table list page.

  4. Find the logical table that you created. Then, click Configure Algorithm in the Routing Algorithm column.

  5. On the page that appears, click Create. In the Create Algorithm dialog box, configure the Algorithm Type, Modulo Operation, Table Partitioning Field, and Modulus parameters.

    In this example, Modulo Operation on One Column is selected as Algorithm Type, Simple Modulo Operation is selected as Modulo Operation, id is selected as Table Partitioning Field, and the Modulus parameter is set to 4. The expression of the routing algorithm is #id#%4.

    Note
    • You can perform simple modulo operations only on numeric fields.

    • You can perform modular hashing operations on both numeric and string fields.

  6. Click Save.

  7. After the routing algorithm is configured, query data in the logical table and execute INSERT statements. The system inserts the data into the corresponding table shard based on the result calculated by using the routing algorithm.

    For example, if you insert a data row whose value of the ID column is 9, the routing result is 1 and the data row is inserted into the table shard whose name is logic_table_01.

Sample routing expressions

In the following sample routing expressions, user_id is a field in a logical table.

  • Routing based on table sharding

    • Simple modulo operations

      • Modulo operation on a numeric field: #user_id#%100

      • Secondary modulo operation: #user_id#%10000%100

      • Modulo operation on the Java hash code of a numeric field: Math.abs(#user_id#.hashCode())%100

    • Modulo operations on a string field

      • Numeric string hashing: Math.abs(#user_id#.toString().hashCode())%100

      • String hashing: Math.abs(#user_id#.hashCode())%100

      • CobarHash: Math.abs(cobarHash(#column#,start, end)).intdiv(8)

      • CobarOldHash: Math.abs(cobarOldHash(#column#, len)).intdiv(8)

  • Routing based on database sharding

    • Route data to the tables that have the same name in different databases: 'schema_prefix_'+(#user_id#%10)+'.table_name'

    • Route data to the specified database and table:

      • 'schema_prefix_'+(#user_id#%100)+'.table_name_prefix_'+(#user_id#%1000)

      • Route data to the same table shard 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 data to the database whose name is determined by dividing the integer formed by the sixteenth and seventh characters of the EXTEND_ID field by 2 and to the table whose name is determined by the integer formed by the sixteenth and seventh characters of the EXTEND_ID field: 'schema_prefix_'+substring(#EXTEND_ID#,16,18).toLong().intdiv(2)+'.table_name_prefix_'+substring(#EXTEND_ID#,16,18)

  • Routing based on the date

    Route data to the same table on the same day of each month: dayOfMonth(#time#)

  • Routing based on the third last character of a string field

    Integer.valueOf(substring(#ip_id#,-3,-2))*10: If the index value of a table name increases by 10, the integer converted from the third last character of the ip_id field is multiplied by 10. If the index value of a table name increases by 1, the integer converted from the third last character of the ip_id field is not multiplied by 10.

  • Other complex routing methods

    Route data by using user-defined functions (UDFs): String func(String arg){ return arg.hashCode()%10;} 'table_name_'+func(#user_id#)+'_other_'func(#user_id#)

    For more information about Groovy syntax, see Groovy.

General built-in functions

  • cobarOldHash

    The corbarHash algorithm of the previous version.

    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 cobarHash algorithm of the new version.

    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 year for a date.

    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 year for a date.

    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 month for a date.

    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 week for a date.

    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

    Truncates a string field and returns a substring. The values of the start and end parameters can be negative. In this case, the positions are counted backward.

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

    Truncates a string field and returns a substring. The start parameter specifies the start position of the truncation.

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

    Returns a substring that consists of the last four characters of a string field. If a value of the string field does not have four characters in length, a specific number of 0s are padded to the left of the value so that the value contains four characters. The positions of the fourth and third last characters are swapped with the last two characters to form the substring.

    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 substring of the last characters of a string field. The length parameter specifies the number of characters to return. If a value of the string field is shorter than the specified length, a specific number of 0s are padded to the left of the value so that the value contains characters of the specified length.

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