Tablestore:Virtual columns

Purposes

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.

Use the virtual column feature in the Tablestore console

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. Log on to the Tablestore console.
2. On the Overview page, click the name of the required instance or click Manage Instance in the Actions column that corresponds to the instance.
3. In the Tables section of the Instance Details tab, click the name of the data table whose search index you want to view and then click the Indexes tab. You can also click Indexes in the Actions column that corresponds to the data table.
4. On the Indexes tab, click Create Search Index.
5. In the Create Index dialog box, specify virtual columns when you create a search index.
   
   1. The system generates a search index name. You can also set Index Name to a specific value.
   2. Set Schema Generation Type.
      • If you set Schema Generation Type to Manual, enter the field names. Set supported data types for the field values. Specify whether to turn on Array.
      • 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. Set supported data types for the field values. Specify whether to turn on Array.

      Note The Field Name and Field Type values must be the same as those of the data table. For more information about the mapping of field types between the data table and the search index, see Data type mappings.
   3. Create a virtual column.
      Important When you create a virtual column, the data table must contain the name of the source field and the data type of the source field must match that of the virtual column.

      1. Click Add an Index Field.
      2. Set Field Name and Field Type.
      3. Turn on Virtual Column. Set Index Field Name.
   4. Click OK.

      After the search index is created, click Index Details in the Actions column that corresponds to the search index. You can view the information about the search index, such as the metering information and index fields.

6. Use the virtual column to query data.
   1. Click Manage Data in the Actions column that corresponds to the search index.
   2. In the Search dialog box, set filter conditions.
      

      1. By default, the system returns all columns. To return specified attribute columns, turn off All Columns. Enter the attribute columns to return. Separate multiple attribute columns with commas (,).
      2. Select index fields. Click Add. Set query methods and values for the fields.
      3. By default, the sorting feature is disabled. To enable sorting, turn on Sort to sort query results based on the index fields. Add the index fields and configure sorting methods.
      4. Click OK.

         Data that meets the filter conditions is displayed in the specified order on the Data Editor tab.

Use Tablestore SDKs to manage the virtual column feature

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. Specify a virtual column when you create a search index.
   • Parameters

     For more information about parameters, see Create search indexes.

   • Examples
     The following sample code shows how to create a search index that contains columns Col_Keyword and Col_Long. Each of the columns has a virtual column. The virtual column of the Col_Keyword column is Col_Keyword_Virtual_Long and that of the Col_Long column is Col_Long_Virtual_Keyword. 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();
         request.setTableName(tableName); // Specify the name of the data table. 
         request.setIndexName(indexName); // Specify the name of the search index. 
         IndexSchema indexSchema = new IndexSchema();
         indexSchema.setFieldSchemas(Arrays.asList(
             new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Specify the name and type of the field. 
             new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) // Specify the name and type of the field. 
                 .setVirtualField(true) // Specify whether the field is a virtual column. 
                 .setSourceFieldName("Col_Keyword"), // Specify the name of the source field to which the virtual column is mapped in the data table. 
             new FieldSchema("Col_Long", FieldType.LONG),
             new FieldSchema("Col_Long_Virtual_Keyword", FieldType.KEYWORD)
                 .setVirtualField(true)
                 .setSourceFieldName("Col_Long")));
         request.setIndexSchema(indexSchema);
         client.createSearchIndex(request); // Call a client to create the search index. 
     }

2. Use a virtual column to query data.

   Query the Col_Long_Virtual_Keyword column from a data table whose value can match "1000". Specify that 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 type to TermsQuery. 
       termsQuery.setFieldName("Col_Long_Virtual_Keyword"); // Set 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("tableName", "indexName", searchQuery);
       SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
       columnsToGet.setReturnAll(true); // Set ReturnAll to true to return all columns without returning the virtual column. 
       searchRequest.setColumnsToGet(columnsToGet);
   
       SearchResponse resp = client.search(searchRequest);
       System.out.println("TotalCount: " + resp.getTotalCount()); // Display the total number of matched columns but not returned rows. 
       System.out.println("Row: " + resp.getRows());
   }