Tablestore:Create a search index

Optimal method to create search indexes

We recommend that you determine the number of search indexes that you want to create for a data table based on your query requirements.

If you have a data table that contains the id, name, age, city, and sex fields, you can create search indexes to query data by name, age, or city by using one of the following methods:

• Method 1: Create a search index for each field

  If you use this method, you must create the following search indexes: name_index, age_index, and city_index.

  • To query students by city, use the city_index search index. To query students by age, use the age_index search index.

  • However, if you want to query students who are younger than 12 years old and live in City A, this method does not work.

  The implementation of this method is similar to that of the global secondary index feature. However, this method is not cost-effective. We recommend that you use Method 2 in this case.

• Method 2 (recommended): Create one search index for multiple fields

  In this method, a search index named student_index is created. This search index consists of the following fields: name, age, and city.

  • To query students by city, query the city field in the student_index search index. To query students by age, query the age field in the student_index search index.

  • To query students who are younger than 12 years old and live in City A, query the age and city fields in the student_index search index.

  This method makes full use of the advantages of search indexes and is more cost-effective. We recommend that you use this method if you want to create a search index.

Limits

• Timeliness of search index creation

  After you create a search index, you must wait for a few seconds before you can use the search index. During this period, you can write data to the data table, but you cannot query the metadata of the index or query data by using the index.

• When you create a search index, make sure that the types of fields in the search index match the types of fields in the data table for which the search index is created. For more information, see Mappings of basic data types.

• Number of search indexes

  For more information, see Search index limits.

Parameters

When you create a search index, you must configure the tableName, indexName, and indexSchema parameters. You must also configure the fieldSchemas, indexSetting, and indexSort parameters in the indexSchema parameter. The following table describes the parameters.

Usage notes

• To perform full-text search, you can set the type of the field in a search index to Text and specify the type of the analyzer for the Text field. Then, you can use match query or match phrase query to query data. For information about tokenization, see Tokenization.

• You can map fields in data tables to virtual columns in search indexes to query new fields and the data of new field types without the need to modify the storage schema and the data in the tables. For more information, see Virtual columns.

• You can map String or Integer fields in data tables to Date fields in search indexes. This way, you can query data of the Date type. For more information, see Date data type.

• If you want to perform an approximate nearest neighbor search based on vectors, you can set the type of the field to Vector in a search index and use the k-nearest neighbor (KNN) vector query feature to query data. For more information, see Overview.

• If you want to store data as arrays in a search index, you can store the data in String fields in the data table and set the type of the mapped fields in the search index to Array.

  When you write data to the data table, the values of the fields to which the Array fields in the search index are mapped must be JSON arrays. Example: ["a","b","c"].

  Note

  Search indexes support Array fields. However, data tables do not support Array fields. For more information, see Array and Nested data types.

• If you want to use a field to store data that has a hierarchical relationship or one-to-many relationship, you can set the type of the field in the search index to Nested, and use nested query to query data.

  When you write data to the data table, the values of the fields to which the Nested fields in the search index are mapped must be JSON arrays. Example: [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}].

• If the data that you want to store in the data table is geographical location information, you can set the type of the field in the search index to Geo-point, and use geo-distance query, geo-bounding box query, or geo-polygon query to query data.

• By default, the system uses the partition key as the routing key. You can specify custom routing fields to use other primary key columns as the routing key. This reduces the query latency because the partition range scanned by Tablestore is narrowed. For more information, see How do I use routing fields?

• By default, if you use a search index to query data, the query results are returned and sorted in ascending order of the primary key. You can specify a presorting method to sort the query results based on the values of specific fields or in descending order of the primary key. For more information, see Index presorting.

• You can use the TTL feature to automatically delete historical data in a search index. This reduces storage usage and storage costs. For more information, see Specify the TTL of a search index.

• If you want to highlight the matched query strings in the query results, you can enable the highlight feature. For more information, see Highlight.

Methods

You can create a search index in the Tablestore console or by using the Tablestore CLI or Tablestore SDKs.

Before you create a search index, make sure that the following preparations are complete:

• An Alibaba Cloud account or a RAM user that has Tablestore operation permissions is created. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.

  If you want to use Tablestore SDKs or the Tablestore CLI to create a search index, an AccessKey pair is created for your Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.

• A data table is created. For more information, see Operations on a data table.

• If you want to use Tablestore SDKs to create a search index, an OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

• If you want to use the Tablestore CLI to create a search index, the Tablestore CLI is downloaded and started as expected. In addition, the instance access information is configured. For more information, see Download the Tablestore CLI and Start the Tablestore CLI and configure access information.

private static void createSearchIndex(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    request.setTableName("<TABLE_NAME>"); 
    // Specify the name of the search index. 
    request.setIndexName("<SEARCH_INDEX_NAME>"); 
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
            // Specify the names and types of the fields. 
            new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
            new FieldSchema("Col_Long", FieldType.LONG)));
            // Specify the vector type. 
            new FieldSchema("Col_Vector", FieldType.VECTOR).setIndex(true)
                    // Set the vector dimension to 4 and the distance measurement algorithm for vectors to the dot product algorithm. 
                    .setVectorOptions(new VectorOptions(VectorDataType.FLOAT_32, 4, VectorMetricType.DOT_PRODUCT))
    ));
    request.setIndexSchema(indexSchema);
    // Call the client to create the search index. 
    client.createSearchIndex(request); 
}

private static void createSearchIndexWithIndexSort(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    request.setTableName("<TABLE_NAME>"); 
    // Specify the name of the search index. 
    request.setIndexName("<SEARCH_INDEX_NAME>"); 
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
            new FieldSchema("Col_Keyword", FieldType.KEYWORD),
            new FieldSchema("Col_Long", FieldType.LONG),
            new FieldSchema("Col_Text", FieldType.TEXT),
            new FieldSchema("Timestamp", FieldType.LONG)
                    .setEnableSortAndAgg(true)));
    // Presort data based on the Timestamp field. 
    indexSchema.setIndexSort(new Sort(
            Arrays.<Sort.Sorter>asList(new FieldSort("Timestamp", SortOrder.ASC))));
    request.setIndexSchema(indexSchema);
    // Call the client to create the search index. 
    client.createSearchIndex(request);
}

// Use Tablestore SDK for Java V5.12.0 or later to create a search index. 
public static void createIndexWithTTL(SyncClient client) {
    int days = 7;
    CreateSearchIndexRequest createRequest = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    createRequest.setTableName("<TABLE_NAME>");
    // Specify the name of the search index. 
    createRequest.setIndexName("<SEARCH_INDEX_NAME>");
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
            // Specify the names and types of the fields. 
            new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
            new FieldSchema("Col_Long", FieldType.LONG)));
    createRequest.setIndexSchema(indexSchema);
    // Specify the TTL of the search index. 
    createRequest.setTimeToLiveInDays(days);
    // Create the search index. 
    client.createSearchIndex(createRequest);
}

private static void createSearchIndex(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    request.setTableName("<TABLE_NAME>"); 
    // Specify the name of the search index. 
    request.setIndexName("<SEARCH_INDEX_NAME>"); 
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
        // Specify the names and types of the fields. 
        new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
        // Specify the names and types of the fields. 
        new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) 
             // Specify whether the field is a virtual column. 
            .setVirtualField(true) 
             // Specify the name of the source field to which the virtual column is mapped. 
            .setSourceFieldName("Col_Keyword"), 
        new FieldSchema("Col_Long", FieldType.LONG),
        new FieldSchema("Col_Long_Virtual_Keyword", FieldType.KEYWORD)
            .setVirtualField(true)
            .setSourceFieldName("Col_Long")));
    request.setIndexSchema(indexSchema);
    // Call the client to create the search index. 
    client.createSearchIndex(request); 
}

private static void createSearchIndexwithHighlighting(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    // Specify the name of the data table. 
    request.setTableName("<TABLE_NAME>"); 
    // Specify the name of the search index. 
    request.setIndexName("<SEARCH_INDEX_NAME>"); 
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
            // Specify the names and types of the fields. 
            new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
            new FieldSchema("Col_Long", FieldType.LONG),
            // Enable the highlight feature for the Col_Text field. 
            new FieldSchema("Col_Text", FieldType.TEXT).setIndex(true).setEnableHighlighting(true)
    ));
    request.setIndexSchema(indexSchema);
    // Call the client to create the search index. 
    client.createSearchIndex(request); 
}

What to do next

After you create a search index, you can use the search index to query, analyze, and export data.

References

After you create a search index, you can perform operations on the search index based on your business requirements.

• You can specify the TTL for a search index to delete historical data in the search index or extend the retention period of data in the search index. For more information, see Configure the TTL of a search index.

• You can dynamically modify the schema of a search index to add, update, or remove index columns in the search index. For more information, see Dynamically modify the schema of a search index.

• You can call the ListSearchIndex operation to query all search indexes that are created for a data table. For more information, see Query search indexes.

• You can call the DescribeSearchIndex operation to query the description of a search index, such as the field information and search index configurations. For more information, see Query the description of a search index.

• You can delete a search index that you no longer require. For more information, see Delete a search index.

• You can also use the SQL query feature or compute engines, such as MaxCompute, Spark, Hive, HadoopMR, Function Compute, Flink, and PrestoDB, to compute and analyze data in tables. For more information, see SQL query and Overview.

Appendix: distance measurement algorithms for vectors

The following table describes the algorithms that you can use to measure the distance between vectors. A greater value that is obtained by using an algorithm indicates a higher similarity.

The following sample code provides an example on how to normalize vectors:

  public static float[] l2normalize(float[] v, boolean throwOnZero) {
    double squareSum = 0.0f;
    int dim = v.length;
    for (float x : v) {
      squareSum += x * x;
    }
    if (squareSum == 0) {
      if (throwOnZero) {
        throw new IllegalArgumentException("can't normalize a zero-length vector");
      } else {
        return v;
      }
    }
    double length = Math.sqrt(squareSum);
    for (int i = 0; i < dim; i++) {
      v[i] /= length;
    }
    return v;
  }