Tablestore:Virtual columns

Prerequisites

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

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

  • The maximum number of versions is set to 1.

  • The time-to-live (TTL) is set to -1, or the data table prohibits updates.

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
  	FuzzyKeyword and FuzzyKeyword array
  	Text and Text array
  	Long and Long array
  	Double and Double array
  	Date and Date array
  	IP and IP array
  	Geo-point and Geo-point array
  Long	Keyword
  	FuzzyKeyword
  	Text
  Double	Keyword
  	FuzzyKeyword
  	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 creating a search index.

   This example creates a search index with two columns: Col_Keyword and Col_Long. It also creates two virtual columns: Col_Keyword_Virtual_Long mapped to the Col_Keyword column, and Col_Long_Virtual_Keyword mapped 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)
                // Mark this field as a virtual column.
               .setVirtualField(true)
                // Set 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.

   This example queries rows where the Col_Long_Virtual_Keyword column value matches "1000" and returns the total number of matched rows along with a subset of matching 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 are not returned.
       searchRequest.setColumnsToGet(columnsToGet);
   
       SearchResponse resp = client.search(searchRequest);
       System.out.println("TotalCount: " + resp.getTotalCount()); // Total matched rows, not the number of returned rows.
       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.