Tablestore:Virtual columns

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

1. Go to the Indexes tab.

   1. Log in to the Tablestore console.

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

   3. On the Overview page, click an instance name, or click Manage Instance in the Actions column.

   4. On the Instance Details tab, in the Tables section, click a data table name, or click Indexes in the Actions column.

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

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

   1. The system automatically generates an index name. You can enter a custom Index Name if needed.

   2. Select a Schema Generation Type.

      • When Schema Generation Type is set to Manual, enter a field name, select a field type, and specify whether the field is an array.

      • When Schema Generation Type is set to Auto Generate, the system automatically uses the primary key columns and attribute columns of the data table as index fields. You can select the field type and enable the array feature as needed.

      Note

      The Field Name and Field Type must be compatible with the data table. For more information about the mappings between data table field types and search index field types, see Data types.

   3. Create a virtual column.

      Important

      When you create a virtual column, the source field must exist in the data table, and its data type must be compatible with the virtual column's field type.

      1. Click the icon.

      2. Enter a Field Name and select a Field Type.

      3. Enable the Virtual Column toggle for the field, and enter the Index Field Name.

   4. To configure a routing key, time to live (TTL), or presorting for the search index, enable the Advanced Settings toggle and configure the parameters. For more information, see Create a search index.

   5. Click OK.

      After the search index is created, you can click Index Details in the Actions column to view its basic information, metrics, routing key, index fields, and presorting settings.

4. Use the virtual column to query data.

   1. In the Actions column of the search index, click Manage Data.

   2. In the Search dialog box, query the data.

      For example, select the virtual index field Col_Long_Virtual_Keyword, set the query type to term query, and enter 1000 as the query value.

      1. By default, all columns are returned. To return specific attribute columns, disable the Get All Columns toggle and enter the names of the attribute columns, separated by commas (,).

      2. Select an index field, click Add, and then set the query type and value for the field.

      3. By default, sorting is disabled. To sort the results by an index field, enable the Sort toggle, add the desired index fields, and configure the sort order.

      4. By default, aggregation is disabled. To perform statistical aggregation on a specific field, turn on Enable Aggregation, add the field for aggregation, and configure the aggregation settings.

      5. Click OK.

         The data that matches the query conditions is displayed on the Indexes tab.

After you create a search index with a virtual column using an SDK, you can use the virtual column to query data.

1. Create a search index with a virtual column.

   Note

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

   The following example creates a search index with two fields, Col_Keyword and Col_Long. It also creates two virtual columns: Col_Keyword_Virtual_Long maps to the Col_Keyword column in the data table, and Col_Long_Virtual_Keyword maps to the Col_Long column.

   private static void createSearchIndex(SyncClient client) {
       CreateSearchIndexRequest request = new CreateSearchIndexRequest();
       // Set the data table name.
       request.setTableName("<TABLE_NAME>"); 
       // Set the search index name.
       request.setIndexName("<SEARCH_INDEX_NAME>"); 
       IndexSchema indexSchema = new IndexSchema();
       indexSchema.setFieldSchemas(Arrays.asList(
           // Set the field name and type.
           new FieldSchema("Col_Keyword", FieldType.KEYWORD), 
           // Set the virtual column name and type.
           new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) 
                // Specify that the field is a virtual column.
               .setVirtualField(true) 
                // The source field in the data table that the virtual column maps to.
               .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); 
   }

2. Use the virtual column to query data.

   The following example queries for rows where the value of the Col_Long_Virtual_Keyword column is "1000". The query returns the total count of matched rows and a sample of them.

   private static void query(SyncClient client) {
       SearchQuery searchQuery = new SearchQuery();
       TermsQuery termsQuery = new TermsQuery(); // Set the query type to TermsQuery.
       termsQuery.setFieldName("Col_Long_Virtual_Keyword"); // Set the field to match.
       termsQuery.addTerm(ColumnValue.fromString("1000")); // Set the value to match.
       searchQuery.setQuery(termsQuery);
       searchQuery.setGetTotalCount(true); // Set to return the total number of matched rows.
       SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
       SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
       columnsToGet.setReturnAll(true); // Set to return all columns. Virtual columns are not returned.
       searchRequest.setColumnsToGet(columnsToGet);
       SearchResponse resp = client.search(searchRequest);
       // The total number of matched rows, not the number of returned rows.
       System.out.println("TotalCount: " + resp.getTotalCount()); 
       System.out.println("Row: " + resp.getRows());
   }