Create a search index using the Tablestore .NET SDK - Tablestore

You can use the CreateSearchIndex method to create one or more search indexes for a data table. When creating an index, you must specify the fields to be queried and can also configure advanced options, such as custom routing keys and pre-sorting.

Prerequisites

Initialize the Tablestore client. For more information, see Initialize the Tablestore Client.
Create a data table that meets the following conditions. For more information, see Create a data table.
- The max versions must be 1.
- The time to live (TTL) is -1, or updates to the data table are prohibited.

Notes

When you create a search index, the data type of a field must match that of the corresponding field in the data table.

Parameters

To create a search index, you must specify the table name (TableName), search index name (IndexName), and index schema (IndexSchema). The IndexSchema includes FieldSchemas (field settings), IndexSetting (index settings), and IndexSort (pre-sorting settings). The following table describes these parameters in detail.

Parameter	Description
TableName	The name of the data table.
IndexName	The name of the search index.
FieldSchemas	A list of FieldSchema objects. Each FieldSchema contains the following parameters: FieldName (Required): The name of the field to add to the search index. This is the column name. The type is String. Fields in a search index can be primary key columns or attribute columns. FieldType (Required): The data type of the field. The type is FieldType.XXX. Array (Optional): Specifies whether the field is an array. The type is Boolean. If you set this parameter to true, the column is an array. When you write data, you must use the JSON array format, such as ["a","b","c"]. Because the Nested type is an array, you do not need to set this parameter when the FieldType is Nested. Index (Optional): Specifies whether to create an index for the field. The type is Boolean. The default value is true. This means an inverted index or a spatial index is created for the column. If you set this parameter to false, no index is created for the column. Analyzer (Optional): The tokenizer type. You can set this parameter when the field type is Text. If you do not set this parameter, the default tokenizer is single-word tokenization. EnableSortAndAgg (Optional): Specifies whether to enable sorting and aggregation for the field. The type is Boolean. You can sort data only by fields for which EnableSortAndAgg is set to true. Important Sorting and aggregation are not supported for fields of the Nested type. However, you can enable sorting and aggregation for sub-columns within a Nested field. IsVirtualField (Optional): Specifies whether the field is a virtual column. The type is Boolean. The default value is false. Set this parameter only when you use a virtual column. SourceFieldNames (Optional): The names of the fields in the data table. The type is String. Important You must set this parameter if you set IsVirtualField to true. DateFormats (Optional): The date formats. The type is String. For more information, see Date and time types. Important You must set this parameter when the field type is Date.
IndexSetting	Index settings, which include the RoutingFields setting. RoutingFields (Optional): Custom routing fields. You can select some primary key columns as routing fields. In most cases, you only need to set one. If you set multiple routing keys, the system concatenates their values into a single value. When you write data to the index, the system calculates the distribution of the index data based on the values of the routing fields. Records with the same routing field values are indexed into the same data partition.
IndexSort	Index pre-sorting settings, which include the Sorters setting. If you do not set this parameter, the data is sorted by primary key by default. Note IndexSort is not supported for indexes that contain Nested fields. These indexes do not have pre-sorting. Sorters (Required): The pre-sorting method for the index. You can sort by primary key or by field value. For more information about sorting, see Sorting and pagination. PrimaryKeySort: Sorts by primary key. It includes the following setting: Order: The sort order. You can sort in ascending or descending order. The default is ascending (DataModel.Search.Sort.SortOrder.ASC). FieldSort: Sorts by field value. It includes the following settings: Only fields that are indexed and have sorting and aggregation enabled can be used for pre-sorting. FieldName: The name of the field to sort by. Order: The sort order. You can sort in ascending or descending order. The default is ascending (DataModel.Search.Sort.SortOrder.ASC). Mode: The sorting method to use when a field has multiple values.
TimeToLive	Optional. The TTL for the data, which is the data retention period in seconds. The default value is -1, which means the data never expires. The minimum value for TTL is 86400 seconds (one day). You can also set it to -1 (never expires). When the data retention period exceeds the specified TTL, the system automatically deletes the expired data.

Examples

Create a search index with default configurations

The following example shows how to create a search index. The search index contains three columns: Keyword_type_col (Keyword), Long_type_col (Long), and Text_type_col (Text). Sorting and aggregation are enabled.

/// <summary>
/// Creates a search index that contains three attribute columns: Keyword_type_col, Long_type_col, and Text_type_col. The types are set to Keyword (non-tokenized string), Long (integer), and Text (tokenized string), respectively.
/// </summary>
/// <param name="otsClient"></param>
public static void CreateSearchIndex(OTSClient otsClient)
{
    // Set the table name and the search index name.
    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 the client to create the search index.
    CreateSearchIndexResponse response = otsClient.CreateSearchIndex(request);
    Console.WriteLine("Searchindex is created: " + IndexName);
}

Create a search index and specify IndexSort

/// <summary>
/// Creates a search index that contains three attribute columns: Keyword_type_col, Long_type_col, and Text_type_col. The types are set to Keyword (non-tokenized string), Long (integer), and Text (tokenized string), respectively.
/// </summary>
/// <param name="otsClient"></param>
public static void CreateSearchIndexWithIndexSort(OTSClient otsClient)
{
    // Set the table name and the search index name.
    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,
        // Pre-sort by the Long_type_col column. The Long_type_col column must be indexed and have EnableSortAndAgg enabled.
        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 a date column and a virtual column

The following example shows how to create a search index. The search index contains the pk0 (Keyword), pk1 (Long), date_col (Date), geo_col (Geo-point), and col0_v1 (Text) fields. The source column for the virtual column col0_v1 is col0. The results are sorted in ascending order by the pk1 column.

/// <summary>
/// Creates a search index that contains a date column and a virtual column.
/// </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 various query types for multi-dimensional data queries, including 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 operations on the result set, such as sorting and pagination or collapse (remove duplicates).
After you create a search index, you can manage it by performing operations such as dynamically modifying the schema, listing search indexes, querying the description of a search index, and deleting a search index.
For data analytics, such as finding the maximum or minimum value, calculating a sum, or counting rows, you can use the statistical aggregation feature or the SQL query feature.
To quickly export data when the order of the result set is not important, you can use the parallel scan feature.