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 to create search indexes.

• Method 2: Create one search index for multiple fields

  In this method, a search index named student_index is created. The search index contains 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 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 Data type mappings.

• 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 columns 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 fields of the String or Integer type in data tables to fields of the Date type in search indexes. This way, you can query data of the Date type. For more information, see Types of date data.

• To perform approximate nearest neighbor searches by using vectors, you can set the type of the fields to Vector in search indexes and use the k-nearest neighbor (KNN) vector query feature to query data.

• If you want to store data as arrays in a search index, you can store the data in fields of the String type 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 fields of the Array type must be JSON arrays. Example: ["a","b","c"].

  Note

  The fields of search indexes support the Array type. However, the fields of data tables do not support the Array type. For more information, see ARRAY and Nested field 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 fields of the Nested type in the search index is 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 fields in the search index to Geo-point, and use geo 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 partitions scanned by Tablestore are narrowed down. 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 columns 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.

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:

• You have an Alibaba Cloud account or a RAM user that has the permissions to perform operations on Tablestore. 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 perform operations on Tablestore, 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 tables.

• If you want to use Tablestore SDKs to perform operations on Tablestore, an OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

• If you want to use the Tablestore CLI to perform operations on Tablestore, the Tablestore CLI is downloaded and started, and information about the instance that you want to access 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)));
            // Set the type of the field to Vector. 
            new FieldSchema("Col_Vector", FieldType.VECTOR).setIndex(true)
                    // Set the number of dimensions for the vector 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 column. 
    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 for the search index. 
    createRequest.setTimeToLiveInDays(days);
    // Call the client to 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 name and type of the field. 
        new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
        // Specify the name and type of the field. 
        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 in the data table. 
            .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); 
}

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 TTL of search indexes.

• 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 List 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 search indexes.

• 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 for vectors. A greater value that is obtained by using the 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;
  }