When you use the virtual column feature, you can modify the schema of a search index or create a search index to query new fields and data of new field types without modifying the storage schema and the data of Tablestore.

Purposes

The virtual column feature allows you to map a column in a data table to a virtual column in a search index when you create the search index. The type of the virtual column can be different from that of the column in the data table. This allows you to create a column without modifying the table schema and data. The new column can be used to accelerate queries or can be configured with different analyzers.
  • You can configure different analyzers for a TEXT field.

    A single STRING column can be mapped to multiple TEXT columns of a search index. Different TEXT columns use different tokens to meet various business requirements.

  • Accelerate queries

    You do not need to cleanse data or re-create a table schema. You need only to map required columns of a data table to the columns in a search index. The column types can be different between the data table and the search index. For example, you can convert the numeric type to the KEYWORD type to improve the performance of term query, and convert the STRING type to the numeric type to improve the performance of range query.

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.
    fig_indexsearchvitural
    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 types of column values.
    3. Create a virtual column.
      Notice 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.

      fig_searchindex_details
  6. Use the virtual column to query data.
    1. Click Search in the Actions column that corresponds to the search index.
    2. In the Search dialog box, set filter conditions.
      fig_querdatavirtual
      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
      Create a search index. The search index contains the Col_Keyword and Col_Long columns. Create virtual columns Col_Keyword_Virtual_Long and Col_Long_Virtual_Keyword. Map Col_Keyword_Virtual_Long to the Col_Keyword column in the data table, and Col_Long_Virtual_Keyword to the Col_Long column in the data table.
      private static void createSearchIndex(SyncClient client) {
          CreateSearchIndexRequest request = new CreateSearchIndexRequest();
          request.setTableName(TABLE_NAME); // Set the name of the data table. 
          request.setIndexName(INDEX_NAME); // Set the name of the search index. 
          IndexSchema indexSchema = new IndexSchema();
          indexSchema.setFieldSchemas(Arrays.asList(
              new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Set the field name and field type. 
                  .setIndex(true) // Set the parameter to true to enable the index. 
                  .setEnableSortAndAgg(true), // Set the parameter to true to enable sorting and aggregation. 
                  .setStore(true),
              new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) // Set the name and the type of the field. 
                  .setIndex(true)
                  .setEnableSortAndAgg(true)
                  .setStore(true)
                  .setVirtualField(true) // Specify whether the field is a virtual column. 
                  .setSourceFieldName("Col_Keyword"), // Map the virtual column to the field in the data table. 
              new FieldSchema("Col_Long", FieldType.LONG)
                  .setIndex(true)
                  .setEnableSortAndAgg(true)
                  .setStore(true),
              new FieldSchema("Col_Long_Virtual_Keyword", FieldType.KEYWORD)
                  .setIndex(true)
                  .setEnableSortAndAgg(true)
                  .setStore(true)
                  .setVirtualField(true)
                  .setSourceFieldName("Col_Long")));
          request.setIndexSchema(indexSchema);
          client.createSearchIndex(request); // Call 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 macthed 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());
    }