Data Management:Configure a routing algorithm

Background information

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.

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 that appears, click the icon to go to the table list page.

4. Find the logical table that you have 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#)

  Note

  • You must use line breaks between functions and expressions.

  • DMS supports CRC32(java.util.zip.CRC32) function routing.

General built-in functions

• cobarOldHash

  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

  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 that consists 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 minimum length, a specific number of 0s are padded to the left of the value so that the value contains characters of the specified minimum length.

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