Tablestore:Virtual columns

Prerequisites

• An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

• A data table for which the maxVersions parameter is set to 1 is created. The timeToLive parameter of the data table must meet one of the following conditions. For more information, see Create a data table.

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

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

Precautions

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

  Field type of data tables	Field type of virtual columns
  String	KEYWORD and KEYWORD ARRAY
  String	TEXT and TEXT ARRAY
  String	LONG and LONG ARRAY
  String	DOUBLE and DOUBLE ARRAY
  String	GEOPOINT and GEOPOINT ARRAY
  Long	Keyword
  Long	Text
  Double	Keyword
  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 can specify that the system returns the source columns of the virtual columns.

Parameters

For more information, see Create search indexes.

Examples

1. Specify virtual columns when you 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 the client to create the search index. 
       client.createSearchIndex(request); 
   }

2. Use a virtual column 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, 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 use to perform the query. 
       termsQuery.addTerm(ColumnValue.fromString("1000")); // Specify the value of the field based on which you want to perform the query. 
       searchQuery.setQuery(termsQuery);
       searchQuery.setGetTotalCount(true); // Specify that the total number of rows that meet the query condition 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 attribute 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 returned. 
       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, Boolean query, KNN vector query, nested query, and exists query. 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 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.

• 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.