Tablestore:Virtual columns

Prerequisites

• A Tablestore client is initialized. For more information, see Initialize a Tablestore client.

• A data table is created and meets the following conditions. For more information, see Create a data table.

  • The max versions must be 1.

  • The time to live is -1, or updates to the data table are prohibited.

Usage notes

• The following table describes the data type conversion between virtual columns and columns in tables.

  Field type in data tables	Field type of virtual columns
  String	Keyword and Keyword array
  String	FuzzyKeyword and FuzzyKeyword array
  String	Text and Text array
  String	Long and Long array
  String	Double and Double array
  String	Geo-point and Geo-point array
  Long	Keyword
  Long	FuzzyKeyword
  Long	Text
  Double	Keyword
  Double	FuzzyKeyword
  Double	Text

• Virtual columns can be used only in query statements and cannot be used in ColumnsToGet to return column values. To return column values, you need to specify the columns to which the virtual columns are mapped.

Parameters

For more information, see Create a search index.

Examples

1. Specify virtual columns when you create a search index.

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

   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 field name and type.
           new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) 
                // Specify whether the field is a virtual column.
               .setVirtualField(true) 
                // The source field 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);
       // Create the search index.
       client.createSearchIndex(request); 
   }

2. Use a virtual column to query data.

   The following example shows how to query for rows where the value of the Col_Long_Virtual_Keyword column is "1000". The query returns the total number of matched rows and a subset of those rows.

   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); // 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); // Return all columns. Virtual columns cannot be returned.
       searchRequest.setColumnsToGet(columnsToGet);
   
       SearchResponse resp = client.search(searchRequest);
       System.out.println("TotalCount: " + resp.getTotalCount()); // The total number of matched rows, not the number of rows in this response.
       System.out.println("Row: " + resp.getRows());
   }

References

• When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, geo query, KNN vector query, Boolean query, nested query, and exists query. 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.

  You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform 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 table, you can call the Search operation to use the aggregation feature or use the SQL query feature. 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 call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.