All Products
Search
Document Center

Tablestore:How do I use routing fields?

Last Updated:Nov 09, 2023

You can specify some primary key columns as routing fields when you create a search index. Tablestore distributes data written to a search index to different partitions based on the specified routing fields. The data with the same routing field values is distributed to the same partition.

Use a routing field

  1. Specify one or more routing fields when you create a search index.

    When you read and write data from or to the search index, the specified routing fields are used to control which partition to query. You must create a search index if you want to use the system default routes (primary key column-based routing) or use other fields as routing fields.

    Note

    Only primary key columns in Tablestore can be specified as the routing fields.

  2. When you query data by using a search index, specify the routing field values in the query request.

    Use routing fields to query only specified partitions, which minimizes latencies caused by large amounts of data to query. You must provide routing fields for a query request that is based on custom routing fields. If you do not provide routing fields for a query request that is based on custom routing fields, unrelated partitions are accessed, which wastes system resources and increases latencies.

Examples



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();
    searchIndexRequest.setTableName("order"); // Set the table name to order.
    searchIndexRequest.setIndexName("order_index");  // Set the name of the search index to order_index.
    IndexSchema indexSchema = new IndexSchema();
    IndexSetting indexSetting = new IndexSetting();
    indexSetting.setRoutingFields(Arrays.asList("user_id"));// Specify user_id as the routing field.
    indexSchema.setIndexSetting(indexSetting);

    // Add fields to index.
    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);

    Thread.sleep(6*1000); // Wait until the table is loaded.

    // 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));

    }

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

    // Use the routing field to query.
    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());

  }