Tablestore .NET SDK create search index - Tablestore - Alibaba Cloud Documentation Center

Use the CreateSearchIndex method to create a search index on a data table. A data table supports multiple search indexes. When creating a search index, you need to add the fields you want to query to the search index. You can also configure advanced options such as routing keys and presorting for the search index.

Prerequisites

A client is initialized. For more information, see Initialize a Tablestore client.
A data table that meets the following conditions is created. For more information, see Create a data table.
- The max versions parameter is set to 1.
- The time to live (TTL) is set to -1 or updates on the data table are prohibited.

Considerations

When creating a search index, the data type of fields in the search index must match the data type of fields in the data table.

Parameters

When creating a search index, you need to specify the table name (TableName), search index name (IndexName), and index structure information (IndexSchema). IndexSchema includes FieldSchemas (settings for all fields in the index), IndexSetting (index settings), and IndexSort (index presorting settings). The following table describes the parameters.

Parameter	Description
TableName	The name of the table.
IndexName	The name of the search index.
FieldSchemas	The list of field schemas. In each field schema, configure the following parameters: FieldName (required): the name of the field for which you want to create a search index, which is the column name. Type: String. A field in a search index can be a primary key column or an attribute column. FieldType (required): the data type of the field. Type: FieldType.XXX. Array (optional): specifies whether the value is an array. Type: Boolean. If you set this parameter to true, the field stores data as an array. Data written to the field must be a JSON array. Example: ["a","b","c"]. Nested values are arrays. If you set FieldType to Nested, skip this parameter. Index (optional): specifies whether to enable indexing for the column. Type: Boolean. Default value: true. A value of true specifies that Tablestore indexes the field with an inverted indexing schema or a spatio-temporal indexing schema. A value of false specifies that Tablestore does not enable indexing for the field. Analyzer (optional): the tokenizer type. If you set the FieldType parameter to Text, you can configure this parameter. If you do not configure this parameter, the default analyzer type single-word tokenization is used. EnableSortAndAgg (optional): specifies whether to enable sorting and aggregation. Type: Boolean. Only fields for which EnableSortAndAgg is set to true can be sorted. Important Fields of the Nested type do not support the sorting and aggregation feature. The subcolumns of fields of the Nested type support the sorting and aggregation feature. Store (optional): specifies whether to store the value of the field in the search index. Type: Boolean. If you set Store to true, you can read the value of the field from the search index without querying the data table. This improves query performance. IsVirtualField (optional): specifies whether the field is a virtual column. Type: Boolean. Default value: false. This parameter is required only when you use virtual columns. SourceFieldNames (optional): specifies the name of the source field to which the virtual column is mapped in the data table. Type: String. Important This parameter is required when IsVirtualField is set to true. DateFormats (optional): the format of dates. Type: String. For more information, see Date and time types. Important This parameter is required when the field type is Date.
IndexSetting	The settings of the search index, including the settings of the RoutingFields parameter. RoutingFields (optional): the custom routing fields. You can select some primary key columns as routing fields. In most cases, you need to specify only one routing field. If you specify multiple routing fields, the system concatenates the values of the routing fields into one value as the routing key. Tablestore distributes data that is written to a search index across different partitions based on the specified routing fields. Data with the same routing field values is distributed to the same partition.
IndexSort	The presorting settings of the search index, including the settings of the Sorters parameter. If the IndexSort parameter is left empty, field values are sorted by primary key. Note You can skip the presorting settings for the search indexes that contain fields of the Nested type. Sorters (required): the presorting method for the search index. PrimaryKeySort and FieldSort are supported. For more information about sorting, see Sorting and pagination. PrimaryKeySort: sorts data by primary key. You can specify the following parameter for PrimaryKeySort: Order: the sorting order, which can be ascending or descending. Default value: ascending (DataModel.Search.Sort.SortOrder.ASC). FieldSort: sorts data by field value. You can specify the following parameters for FieldSort: Only fields for which an index is created and the EnableSortAndAgg parameter is set to true can be presorted. FieldName: the name of the column whose values are used to sort data. Order: the sorting order, which can be ascending or descending. Default value: ascending (DataModel.Search.Sort.SortOrder.ASC). Mode: the sorting method that you want to use when the field contains multiple values.
TimeToLive	The retention period of data in the search index. Unit: seconds. This parameter is optional. Default value: -1. The TTL must be at least 86,400 seconds (one day) or -1. A value of -1 specifies that the data never expires. If the retention period of data in the search index exceeds the TTL value of the search index, Tablestore automatically deletes the data.

Examples

Create a search index by using the default configurations

The following sample code provides an example on how to create a search index. The search index contains three columns: Keyword_type_col (Keyword type), Long_type_col (Long type), and Text_type_col (TEXT type). The sorting and aggregation feature is enabled.

/// <summary>
/// Create a search index that contains the Keyword_type_col, Long_type_col, and Text_type_col attribute columns. Set the type of data in Keyword_type_col to Keyword, in Long_type_col to Long, and in Text_type_col to Text.
/// </summary>
/// <param name="otsClient"></param>
public static void CreateSearchIndex(OTSClient otsClient)
{
    //Specify the names of the table and search index.
    CreateSearchIndexRequest request = new CreateSearchIndexRequest(TableName, IndexName);
    List<FieldSchema> FieldSchemas = new List<FieldSchema>() {
        new FieldSchema(Keyword_type_col,FieldType.KEYWORD){ //Set the field name and field type.
            index =true, //Enable indexing.
            EnableSortAndAgg =true //Enable sorting and aggregation.
        },
        new FieldSchema(Long_type_col,FieldType.LONG){ index=true,EnableSortAndAgg=true},
        new FieldSchema(Text_type_col,FieldType.TEXT){ index=true}
    };
    request.IndexSchame = new IndexSchema()
    {
        FieldSchemas = FieldSchemas
    };
    //Call a client to create the search index.
    CreateSearchIndexResponse response = otsClient.CreateSearchIndex(request);
    Console.WriteLine("Searchindex is created: " + IndexName);
}

Create a search index with IndexSort specified

The following sample code provides an example on how to create a search index. The search index contains three columns: Keyword_type_col, Long_type_col, and Text_type_col. The data types of the columns are set to string (Keyword), integer (Long), and tokenized string (TEXT). The search index is configured to presort data by the Long_type_col column.

/// <summary>
/// Create a search index that contains the Keyword_type_col, Long_type_col, and Text_type_col attribute columns. Set the type of data in Keyword_type_col to Keyword, in Long_type_col to Long, and in Text_type_col to Text.
/// </summary>
/// <param name="otsClient"></param>
public static void CreateSearchIndexWithIndexSort(OTSClient otsClient)
{
    //Specify the names of the table and search index.
    CreateSearchIndexRequest request = new CreateSearchIndexRequest(TableName, IndexName);
    List<FieldSchema> FieldSchemas = new List<FieldSchema>() {
        new FieldSchema(Keyword_type_col,FieldType.KEYWORD){ //Set the field name and field type.
            index =true, //Enable indexing.
            EnableSortAndAgg =true //Enable sorting and aggregation.
        },
        new FieldSchema(Long_type_col,FieldType.LONG){ index=true,EnableSortAndAgg=true},
        new FieldSchema(Text_type_col,FieldType.TEXT){ index=true}
    };
    request.IndexSchame = new IndexSchema()
    {
        FieldSchemas = FieldSchemas,
        //Presort data by the Long_type_col column. You must index the Long_type_col column and enable sorting and aggregation for the column.
        IndexSort = new DataModel.Search.Sort.Sort()
        {
            Sorters = new List<DataModel.Search.Sort.ISorter>
            {
                new DataModel.Search.Sort.FieldSort(Long_type_col, DataModel.Search.Sort.SortOrder.ASC)
            }
        }
    };

    CreateSearchIndexResponse response = otsClient.CreateSearchIndex(request);
    Console.WriteLine("Searchindex is created: " + IndexName);
}

Create a search index that contains date columns and virtual columns

The following sample code provides an example on how to create a search index. The search index contains the pk0 (Keyword type), pk1 (Long type), date_col (Date type), geo_col (Geo-Point type), and col0_v1 (Text type) fields. The col0_v1 virtual column is mapped to the col0 source column. The results are sorted in ascending order by the pk1 column.

/// <summary>
/// Create a search index that contains date columns and virtual columns.
/// </summary>
/// <param name="otsClient"></param>
public static void CreateSearchIndex(OTSClient otsClient)
{
    List<FieldSchema> fieldSchemas = new List<FieldSchema> {
        new FieldSchema("pk0", FieldType.KEYWORD)
        {
            index = true,
            EnableSortAndAgg = true
        },

        new FieldSchema("pk1", FieldType.LONG)
        {
            index = true,
            EnableSortAndAgg = true
        },

        new FieldSchema("date_col", FieldType.DATE)
        {
            index = true,
            DateFormats = new List<string>(){
                "yyyy-MM-dd'T'HH:mm:ss.SSSSSS",
                "yyyy-MM-dd'T'HH:mm:ss.SSS"
            }
        },

        new FieldSchema("geo_col", FieldType.GEO_POINT)
        {
            index = true,
            EnableSortAndAgg = true
        },

        new FieldSchema("col0_v1", FieldType.TEXT)
        {
            index = true,
            Analyzer = Analyzer.Split,
            AnalyzerParameter = new SingleWordAnalyzerParameter(true, true),
            IsVirtualField = true,
            SourceFieldNames = new List<string> { "col0" }
        },
    };

    CreateSearchIndexRequest request = new CreateSearchIndexRequest(TableName, IndexName);
    request.IndexSchame = new IndexSchema()
    {
        FieldSchemas = fieldSchemas,
        IndexSort = new Sort(new List<ISorter> { new FieldSort("pk1", SortOrder.ASC) })
    };
    request.TimeToLive = -1;

    otsClient.CreateSearchIndex(request);
}

FAQ

References

After you create a search index, you can use the query methods provided by the search index to query data from multiple dimensions based on your business requirements. The query methods include term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, geo query, Boolean query, nested query, and exists query.
When you query data, you can perform sorting and paging on the result set or use the collapse (distinct) feature to collapse the result set based on a specific field. For more information, see Sorting and paging and Collapse (distinct).
After you create a search index, you can manage the search index based on your business requirements. For example, you can dynamically modify the schema of the search index, query the names of search indexes in an instance, query information of the search index, and delete the search index. For more information, see Dynamically modify the schema of a search index, List search indexes, Query the description of a search index, and Delete a search index.
You can use the aggregation feature or the SQL query feature to analyze data in a table. For example, you can query the maximum and minimum values, the sum of the values, and the number of rows. For more information, see Aggregation and SQL query.
If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can use the parallel scan feature. For more information, see Perform a parallel scan.