Tablestore:Use the Tablestore CLI

Prerequisites

• The Tablestore CLI is downloaded. For more information, see Download the Tablestore CLI.

• An instance is started and configured. For more information, see Start the Tablestore CLI and configure access information.

• An AccessKey pair is obtained. For more information, see Obtain an AccessKey pair.

• A data table is created and used. For more information, see Create and use a data table.

  The max versions parameter of the data table is set to 1 and the time to live (TTL) of the data table meets one of the following requirements:

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

  • The TTL of the data table is set to a value other than -1, and update operations on the data table are prohibited.

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 Mappings of basic data types.

• If you want to set the TTL of a search index to a value other than -1, make sure that the UpdateRow operation is prohibited on the data table for which the search index is created. The TTL of a search index that is created for a data table must be less than or equal to the TTL of the data table. For more information, see Specify the TTL of a search index.

Step 1: Create a search index

Run the create_search_index command to create a search index named search_index.

1. Run the create_search_index command to create a search index named search_index.

   create_search_index -n search_index

2. Enter the index schema as prompted:

   The index schema includes the settings of the search index (IndexSetting), the list of field schemas (FieldSchemas), and the presorting settings for the search index (IndexSort). For more information about the schema of a search index, see Create a search index.

    {
   
       "IndexSetting": {
           "RoutingFields": null
       },
       "FieldSchemas": [
           {
               "FieldName": "gid",
               "FieldType": "LONG",
               "Index": true,
               "EnableSortAndAgg": true,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": false
           },
           {
               "FieldName": "uid",
               "FieldType": "LONG",
               "Index": true,
               "EnableSortAndAgg": true,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": false
           },
           {
               "FieldName": "col2",
               "FieldType": "LONG",
               "Index": true,
               "EnableSortAndAgg": true,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": false
           },
           {
               "FieldName": "col3",
               "FieldType": "TEXT",
               "Index": true,
               "Analyzer": "single_word",
               "AnalyzerParameter": {
                   "CaseSensitive": true,
                   "DelimitWord": null
               },
               "EnableSortAndAgg": false,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": false
           },
           {
               "FieldName": "col1",
               "FieldType": "KEYWORD",
               "Index": true,
               "EnableSortAndAgg": true,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": false
           },
           {
               "FieldName": "col3V",
               "FieldType": "LONG",
               "Index": true,
               "EnableSortAndAgg": true,
               "Store": true,
               "IsArray": false,
               "IsVirtualField": true,
               "SourceFieldNames": [
                   "col3"
               ]
           }
       ]
   }

Step 2: Use the search index to query and analyze data

Run the search command to query and analyze data by using the search index. Search indexes support query methods such as Term query, Terms query, Match all query, Match query, Match phrase query, Prefix query, Range query, Wildcard query, Fuzzy query, Boolean query, Geo query, Nested query, and Exists query. You can use different query methods to query data from multiple dimensions based on your business requirements. In this example, term query is used.

1. Run the search command to use the search_index search index to query data and return all indexed columns of each row that meets the query conditions.

   search -n search_index --return_all_indexed

2. Enter the query conditions as prompted.

   The following sample code shows how to query the rows in which the value of uid is 10001 and return the average value of the pid column.

   {
       "Offset": -1,
       "Limit": 10,
       "Collapse": null,
       "Sort": null,
       "GetTotalCount": true,
       "Token": null,
       "Query": {
           "Name": "TermQuery",
           "Query": {
               "FieldName": "uid",
               "Term": 10001
           }
       },
       "Aggregations": [{
           "Name": "avg",
           "Aggregation": {
               "AggName": "agg1",
               "Field": "pid"
           }
       }]
   }

FAQ

• How do I use routing fields?

• What do I do if no data can be found by calling the Search operation?

• Differences between the GetRange and Search operations

• Does Tablestore support the storage of data in the JSON format?

• How do I query the total number of rows in a table?

• Does Tablestore support the IN and BETWEEN...AND operators?

• How do I increase the value of the limit parameter to 1000 when I call the Search operation of the search index feature to query data?

• How do I delete multiple rows of data at the same time?

• What do I do if the "field:xx must enable enable_sort_and_agg" error message is returned when I use a search index?

References

• You can also use search indexes in the Tablestore console or by using Tablestore SDKs. For more information, see Use the Tablestore console and Use Tablestore SDKs.

• You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Sorting and paging.

• 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 analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.

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

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

• If you want to query new fields or data of new field types without modifying the storage schema and data in data tables, use the virtual column feature. For more information, see Virtual columns.