Tablestore:Virtual columns

After you specify a field as a virtual column when you create a search index in the Tablestore console, you can use the virtual column to query data.

1. Go to the Indexes tab.

   1. Log on to the Tablestore console.

   2. In the top navigation bar, select a region and a resource group.

   3. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.

   4. In the Tables section of the Instance Details tab, click the name of the data table for which you want to create a search index. On the Manage Table page, click Indexes. You can also click Indexes in the Actions column of the data table.

2. On the Indexes tab, click Create Search Index.

3. In the Create Index dialog box, specify virtual columns when you create a search index.

   

   1. By default, the system generates a name for the search index. You can also specify a name for the search index.

   2. Configure the Schema Generation Type parameter.

      • If you set Schema Generation Type to Manual, enter the field name and specify the field type and whether to turn on Array for each field that you want to add.

      • If you set Schema Generation Type to Auto Generate, the system automatically uses the primary key columns and attribute columns of the data table as the index fields. Specify the field type and whether to turn on Array for each field that is automatically added by the system based on your business requirements.

      Note

      The Field Name and Field Type values must match those in the data table. For information about the mappings between field types in data tables and field types in search indexes, see Mappings of basic data types.

   3. Create a virtual column.

      Important

      To create a virtual column, the data table must contain the source field and the data type of the source field must match the data type of the virtual column.

      1. Click Add an Index Field.

      2. Configure the Field Name and Field Type parameters.

      3. Turn on Virtual Field for the field and specify a value for Index Field Name.

   4. If you want to specify the routing key, time to live (TTL), or presorting method for the search index, turn on Advanced Settings. For more information, see Create a search index.

   5. Click OK.

      After you create the search index, click Index Details in the Actions column of the search index on the Indexes tab. On the Index Details page, you can view the parameter settings in the Basic Index Information, Index Meters, Routing Key, Index Fields, and Pre-sorting sections.

4. Use the virtual column to query data.

   1. Click Manage Data in the Actions column of the search index.

   2. In the Search dialog box, configure the parameters.

      

      1. By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate attribute columns with commas (,).

      2. Select an index field and click Add to add the index field as a query condition. Specify the query type and value for the index field. Repeat this step to add more query conditions based on your business requirements.

      3. By default, the sorting feature is disabled. To enable sorting, turn on Sort and add the index fields based on which the query results are sorted and configure sorting methods.

      4. By default, the aggregation feature is disabled. If you want to collect statistics on a specific field, turn on Collect Statistics, specify the field based on which you want to collect statistics, and then configure the information that is required to collect statistics.

      5. Click OK.

         Data that meets the query conditions is displayed based on the specified order on the Indexes tab.

After you specify a field as a virtual column when you use a Tablestore SDK to create a search index, you can use the virtual column to query data.

1. Create a search index and specify virtual columns.

   Note

   For information about the parameters, see Create a search index.

   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 a client to create the search index. 
       client.createSearchIndex(request); 
   }

2. Use the virtual columns to query data.

   The following sample code provides an example on how to query the rows in which the value of the Col_Long_Virtual_Keyword column matches "1000". In this example, some of the matched rows and the total number of matched rows are returned.

   private static void query(SyncClient client) {
       SearchQuery searchQuery = new SearchQuery();
       TermsQuery termsQuery = new TermsQuery(); // Set the query method to TermsQuery. 
       termsQuery.setFieldName("Col_Long_Virtual_Keyword"); // Specify the name of the field that you want to match. 
       termsQuery.addTerm(ColumnValue.fromString("1000")); // Set the value that you want to match. 
       searchQuery.setQuery(termsQuery);
       searchQuery.setGetTotalCount(true); // Specify that the total number of matched rows is returned. 
       SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
       SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
       columnsToGet.setReturnAll(true); // Set ReturnAll to true to return all columns without returning the virtual columns. 
       searchRequest.setColumnsToGet(columnsToGet);
   
       SearchResponse resp = client.search(searchRequest);
       System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed. 
       System.out.println("Row: " + resp.getRows());
   }