Tablestore:How do I use routing fields?

When you create a search index in the Tablestore console, turn on Advanced Settings and specify routing fields, as shown in the following figure. For more information, see Step 1: Create a search index.

You can use the Tablestore CLI to execute the create_search_index command to create a search index. For more information, see Search index.

1. Specify routing fields when you create a search index.

   The following sample code provides an example on how to create a search index named mysearchindex. The search index contains the gid field of the LONG type, the uid field of the LONG type, the col2 field of the LONG type, the col3 field of the TEXT type, the col1 field of the KEYWORD type, and the col3V field of the LONG type. The col3V field is a virtual column that corresponds to the col3 column in the data table. In this example, the routing field of the search index is the uid field.

   create_search_index -n mysearchindex

   Follow the on-screen instructions to specify the schema of the search index. You must modify the field settings in the sample code based on your business requirements before you use the search index to query data. Sample code:

   {
   
       "IndexSetting": {
           "RoutingFields": ["uid"]
       },
       "FieldSchemas": [
       {
           "FieldName": "gid",
           "FieldType": "LONG",
           "Index": true,
           "EnableSortAndAgg": true,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": false
       },
       {
           "FieldName": "uid",
           "FieldType": "LONG",
           "Index": true,
           "EnableSortAndAgg": true,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": false
       },
       {
           "FieldName": "col2",
           "FieldType": "LONG",
           "Index": true,
           "EnableSortAndAgg": true,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": false
       },
       {
           "FieldName": "col3",
           "FieldType": "TEXT",
           "Index": true,
           "Analyzer": "single_word",
           "AnalyzerParameter": {
           "CaseSensitive": true,
               "DelimitWord": null
           },
           "EnableSortAndAgg": false,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": false
       },
       {
           "FieldName": "col1",
           "FieldType": "KEYWORD",
           "Index": true,
           "EnableSortAndAgg": true,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": false
       },
       {
           "FieldName": "col3V",
           "FieldType": "LONG",
           "Index": true,
           "EnableSortAndAgg": true,
           "Store": true,
           "IsArray": false,
           "IsVirtualField": true,
           "SourceFieldNames": [
           "col3"
           ]
       }]
   }

2. Provide the routing fields when you use the search index to query data.

   The following sample code provides an example on how to use the mysearchindex search index to query the rows in which the value of the col2 column is less than 200. When you query data, you must provide the routing fields of the search index. In this example, the routing field is uid.

   search -n search_index --return_all_indexed

   Follow the on-screen instructions to specify the query conditions. You must modify the query conditions in the sample code based on your business requirements before you use the search index to query data. Sample code:

   Note

   In this example, the range query method is used. For information about the query methods supported by the search index feature, see Basic features.

   {
       "Offset": -1,
       "Limit": 10,
       "Collapse": null,
       "Sort": null,
       "GetTotalCount": true,
       "Token": null,
   	  "IndexSetting": {
           "RoutingFields": ["uid"]
       },
       "Query": {
           "Name": "RangeQuery",
           "Query": {
               "FieldName": "col2",
               "From": null,
               "To": 200,
               "IncludeLower": false,
               "IncludeUpper": false
            }
        }
   }

You can use the following Tablestore SDKs to specify routing fields when you create a search index: Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. In this example, Tablestore SDK for Java is used to describe how to specify and use routing fields.

The following sample code provides an example on how to create a search index named order_index and set the routing field to the user_id field when you create a data table named order, write data to the data table, and provide the routing field in a query request. In this example, the data table contains the order_id column of the STRING type and the user_id column of the STRING type. The search index contains the product_name field of the KEYWORD type, the order_time field of the LONG type, and the user_id field of the KEYWORD type.

private static void testRoute(SyncClient client) throws InterruptedException {
    // Create a table. 
    TableMeta meta = new TableMeta("order");
    meta.addPrimaryKeyColumn("order_id",PrimaryKeyType.STRING);
    meta.addPrimaryKeyColumn("user_id",PrimaryKeyType.STRING);
    TableOptions options = new TableOptions();
    options.setMaxVersions(1);
    options.setTimeToLive(-1);
    CreateTableRequest request = new CreateTableRequest(meta,options);
    request.setReservedThroughput(new ReservedThroughput(new CapacityUnit(0, 0)));
    CreateTableResponse response = client.createTable(request);

    // Create a search index and specify routing fields. 
    CreateSearchIndexRequest searchIndexRequest = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    searchIndexRequest.setTableName("order"); 
    // Specify the name of the search index that is created for the data table. 
    searchIndexRequest.setIndexName("order_index"); 
    IndexSchema indexSchema = new IndexSchema();
    IndexSetting indexSetting = new IndexSetting();
    // Set the routing field to the user_id field. 
    indexSetting.setRoutingFields(Arrays.asList("user_id"));
    indexSchema.setIndexSetting(indexSetting);

    // Add index fields. The following index fields are added only for reference. You can add index fields based on your business requirements. 
    indexSchema.setFieldSchemas(Arrays.asList(
        new FieldSchema("product_name",FieldType.KEYWORD).setStore(true).setIndex(true),
        new FieldSchema("order_time",FieldType.LONG).setStore(true).setEnableSortAndAgg(true).setIndex(true),
        new FieldSchema("user_id",FieldType.KEYWORD).setStore(true).setIndex(true)
    ));

    searchIndexRequest.setIndexSchema(indexSchema);
    client.createSearchIndex(searchIndexRequest);
    // Wait until the data table is loaded. 
    Thread.sleep(6*1000); 

    // Insert data for testing. 

    String[] productName = new String[]{"product a", "product b", "product c"};
    String[] userId = new String[]{"00001", "00002", "00003", "00004", "00005"};
    for (int i = 0; i < 100; i++){

      PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
      primaryKeyBuilder.addPrimaryKeyColumn("order_id",PrimaryKeyValue.fromString(i+""));
      primaryKeyBuilder.addPrimaryKeyColumn("user_id",PrimaryKeyValue.fromString(userId[i%(userId.length)]));
      PrimaryKey primaryKey = primaryKeyBuilder.build();

      RowPutChange rowPutChange = new RowPutChange("order",primaryKey);

      // Write data to attribute columns. 
      rowPutChange.addColumn("product_name",ColumnValue.fromString(productName[i%(productName.length)]));
      rowPutChange.addColumn("order_time",ColumnValue.fromLong(System.currentTimeMillis()));
      rowPutChange.setCondition(new Condition(RowExistenceExpectation.IGNORE));

      client.putRow(new PutRowRequest(rowPutChange));

    }
    // Wait until data is synchronized to the search index. 
    Thread.sleep(20*1000);

    // Provide the routing field in a query request. 
    SearchRequest searchRequest = new SearchRequest();
    searchRequest.setTableName("order");
    searchRequest.setIndexName("order_index");
    MatchQuery matchQuery = new MatchQuery();
    matchQuery.setFieldName("user_id");
    matchQuery.setText("00002");
    SearchQuery searchQuery = new SearchQuery();
    searchQuery.setQuery(matchQuery);
    searchQuery.setGetTotalCount(true);

    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    columnsToGet.setReturnAll(true);
    searchRequest.setColumnsToGet(columnsToGet);
    searchRequest.setSearchQuery(searchQuery);

    PrimaryKeyBuilder pkbuild = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    pkbuild.addPrimaryKeyColumn("user_id",PrimaryKeyValue.fromString("00002"));
    PrimaryKey routingValue = pkbuild.build();
    searchRequest.setRoutingValues(Arrays.asList(routingValue));
    SearchResponse searchResponse = client.search(searchRequest);

    System.out.println(searchResponse.isAllSuccess());
    System.out.println("totalCount:"+ searchResponse.getTotalCount());
    System.out.println("RowCount:"+searchResponse.getRows().size());

  }