All Products
Search
Document Center

Tablestore:Create search indexes

Last Updated:Jan 16, 2024

You can call the CreateSearchIndex operation to create one or more search indexes for a data table. When you create a search index, you must add the fields whose data you want to query to the search index. You can configure advanced options for the search index, such as the time to live (TTL) and presorting.

Prerequisites

  • An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

  • A data table for which the maxVersions parameter is set to 1 is created. The timeToLive parameter of the data table meets one of the following conditions. For more information, see Create a data table.

    • The timeToLive parameter of the data table is set to -1, which specifies that the data in the data table never expires.

    • The timeToLive parameter of the data table is set to a value other than -1, and the Allow Updates parameter is set to No, which specifies that update operations on the data table are disabled.

Usage notes

  • The data types of the fields in a search index must match the data types of the fields in the data table for which the search index is created. For more information, see Data type mappings.

  • If you want to set the timeToLive parameter of a search index to a value other than -1, you must disable the UpdateRow operation on the data table for which the search index is created. This way, no data is updated in or written to the data table. The TTL of the search index must be smaller than or equal to the TTL of the data table. For more information, see TTL of search indexes.

Syntax

public class CreateSearchIndexRequest implements Request {
    /** The name of the data table. */
    private String tableName;
    /** The name of the search index. */
    private String indexName;
    /** The schema of the search index. */
    private IndexSchema indexSchema;
    /**
     * In most cases, you do not need to specify this parameter. 
     * If the schema of the search index is dynamically modified, use the setter method to specify the value of this parameter. The value is the name of the source search index used for reindexing. 
     */
    private String sourceIndexName;
    /** The TTL of the search index. Unit: seconds. After the search index is created, you can call the UpdateSearchIndex operation to dynamically modify this parameter. */
    private Integer timeToLive;
}

public class IndexSchema implements Jsonizable {
    /** The settings of the search index. */
    private IndexSetting indexSetting;
    /** The schemas of the fields in the search index. */
    private List<FieldSchema> fieldSchemas;
    /** The presorting settings of the search index. */
    private Sort indexSort;
}

Parameters

When you create a search index, you must specify the tableName, indexName, indexSchema, and timeToLive parameters. In the indexSchema parameter, specify the fieldSchemas, indexSetting, and indexSort parameters. The following table describes the parameters.

Parameter

Description

tableName

The name of the data table.

indexName

The name of the search index.

fieldSchemas

The list of field schemas. In each field schema, specify the following parameters:

  • fieldName: required. The name of the field in the search index. The value is also a column name. Type: STRING.

    A column in a search index can be a primary key column or an attribute column.

  • fieldType: required. The type of the field. Type: FieldType.XXX. For more information, see Data type mappings.

    Note
    • If you want to store and query data in multi-level logical relationships, you can store the data as nested fields. For more information, see ARRAY and Nested field types.

    • If you want to store and query data in the JSON format, you can store the JSON-formatted data as strings in a data table. Then, you can query data in ARRAY fields and nested fields in the search index that is created for the data table in a flexible manner. For more information, see ARRAY and Nested field types.

    • If you need to query data that is related to geographical locations, you can store the data as GEOPOINT fields.

  • enableSortAndAgg: optional. Specifies whether to enable sorting and aggregation. Type: BOOLEAN.

    Sorting can be enabled only for fields for which the enableSortAndAgg parameter is set to true. For more information, see Sorting and paging.

  • array: optional. Specifies whether the value is an array. Type: BOOLEAN.

    If you set this parameter to true, the column stores data as an array. Data written to the column must be a JSON array. Example: ["a","b","c"].

    The values of nested fields are arrays. If you set the fieldType parameter to Nested, this parameter is not required.

  • analyzer: optional. The type of the analyzer that you want to use. If the fieldType parameter is set to Text, you can specify this parameter. Otherwise, single-word tokenization is used as the analyzer by default. For more information, see Tokenization.

  • isVirtualField: optional. Specifies whether the field is a virtual column. Type: BOOLEAN. Default value: false. You must set this parameter to true for virtual columns. For more information, see Virtual columns.

  • sourceFieldName: optional. The name of the source field to which the virtual column is mapped in the data table. Type: STRING. This parameter is required if the isVirtualField parameter is set to true.

  • dateFormats: optional. The format of dates. Type: STRING. This parameter is required if the fieldType parameter is set to Date. For more information, see Types of date data.

indexSetting

The settings of the search index, including the setting of the routingFields parameter.

routingFields: optional. The custom routing fields. You can specify multiple primary key columns as routing fields. Tablestore distributes data that is written to a search index across different partitions based on the specified routing fields. The data that has the same routing field values is distributed to the same partition.

indexSort

The presorting settings of the search index, including the setting of the sorters parameter. By default, the field values are sorted by primary key if no value is specified for the indexSort parameter.

Note

If you set the fieldType parameter to Nested, you cannot specify the indexSort parameter.

sorters: optional. The presorting method for the search index. PrimaryKeySort and FieldSort are supported. For more information, see Sorting and paging.

  • PrimaryKeySort: sorts data by primary key. You can specify the following parameter for PrimaryKeySort:

    order: the sort order. Data can be sorted in ascending or descending order. Default value: SortOrder.ASC. This specifies that data is sorted in ascending order.

  • FieldSort: sorts data by field value. You can specify the following parameters for FieldSort:

    Only columns for which an index is created and the enableSortAndAgg parameter is set to true can be presorted.

    • fieldName: the name of the field that is used to sort data.

    • order: the sort order. Data can be sorted in ascending or descending order. Default value: SortOrder.ASC. This specifies that data is sorted in ascending order.

    • mode: the sorting method that is used if the field contains multiple values.

timeToLive

Optional. The retention period of data in the search index. Unit: seconds. Default value: -1.

If the retention period of data exceeds the TTL, the data expires. Tablestore automatically deletes expired data.

The value of this parameter must be greater than or equal to 86400. A value of 86400 specifies one day. You can also set this parameter to -1, which specifies that data never expires.

For more information about how to use the TTL feature for search indexes, see TTL of search indexes.

Sample code

Create a search index by using the default configurations

The following sample code provides an example on how to create a search index that consists of the Col_Keyword and Col_Long columns. The value of the Col_Keyword column is of the STRING type and the value of the Col_Long column is of the LONG type. The data in the search index is presorted by primary key and never expires.

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), 
            new FieldSchema("Col_Long", FieldType.LONG)));
    request.setIndexSchema(indexSchema);
    // Call the client to create the search index. 
    client.createSearchIndex(request); 
}

Create a search index with the indexSort parameter specified

The following sample code provides an example on how to create a search index with the indexSort parameter specified. The search index consists of the following columns: Col_Keyword, Col_Long, Col_Text, and Timestamp. The value of the Col_Keyword column is of the STRING type, the value of the Col_Long column is of the LONG type, the value of the Col_Text column is of the TEXT type, and the value of the Timestamp column is of the LONG type. The data in the search index is presorted based on the Timestamp column.

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

Create a search index with the TTL specified

Important

Make sure that update operations on the data table are disabled.

The following sample code provides an example on how to create a search index with the TTL specified. The search index consists of the Col_Keyword and Col_Long columns. The value of the Col_Keyword column is of the STRING type and the value of the Col_Long column is of the LONG type. The TTL of the search index is seven days.

// 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 name and type of the field. 
            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);
}

Create a search index with virtual columns specified

The following sample code provides an example on how to create a search index that consists of the Col_Keyword and Col_Long columns. Each of the columns has a virtual column. The Col_Keyword_Virtual_Long column is mapped to the Col_Keyword column in the data table, and the Col_Long_Virtual_Keyword column is mapped to the Col_Long column in the data table.

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

FAQ

References

  • After you create a search index, you can query the data in the search index from multiple dimensions by using query types based on your business requirements. The following query types are supported: term query, terms query, match all query, match query, phrase query, prefix query, range query, wildcard query, geo query, Boolean query, nested query, and exists query.

    If you call the Search operation to query data, you can sort or paginate the rows that meet the query conditions by using the sorting and paging features. For more information, see Sorting and paging.

  • If you call the Search operation to query data, you can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).

  • If you want to clear historical data or extend the data retention period in a search index, you can modify the TTL of the search index. For more information, see TTL of search indexes.

  • If you want to analyze data in a data table, such as obtaining the extreme values, sum, and total number of rows, you can perform aggregation operations or execute SQL statements. For more information, see Aggregation and SQL query.

  • If you want to quickly obtain all rows that meet the query conditions and you do not need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.

  • If you want to add indexed columns to, update indexed columns in, or remove indexed columns from a search index, you can use the feature that allows you to dynamically modify the schema of the search index. For more information, see Dynamically modify schemas.

  • If you want to obtain all search indexes that are associated with a data table, you can call the ListSearchIndex operation. For more information, see List search indexes.

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

  • If you no longer need to use a search index, you can delete the search index. For more information, see Delete a search index.