All Products
Search
Document Center

Tablestore:Virtual columns

Last Updated:Feb 20, 2024

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 in Tablestore tables.

Feature description

The virtual column feature allows you to map a column in a 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 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.

  • Query acceleration

    You do not need to cleanse data or re-create a table schema. You need to only map required columns of a table to the columns in a search index. The column types can be different between the 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.

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

    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 need to specify the columns to which the virtual columns are mapped.

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. Go to the Indexes tab.

    1. Log on to the Tablestore console.

    2. In the top navigation bar, select a region and a resource group.

    3. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.

    4. In the Tables section of the Instance Details tab, click the name of the data table for which you want to create a search index. On the Manage Table page, click Indexes. You can also click Indexes in the Actions column of the data table.

  2. On the Indexes tab, click Create Search Index.

  3. In the Create Index dialog box, specify virtual columns when you create a search index.

    1. By default, the system generates a name for the search index. You can also specify a name for the search index.

    2. Configure the Schema Generation Type parameter.

      • 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 match those in the data table. For more information about the mappings between the field types in data tables and those in search indexes, 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. Configure the Field Name and Field Type parameters.

      3. Turn on Virtual Field for the field and specify a value for Index Field Name.

    4. If you want to specify the routing key, time to live (TTL), or sorting method for the search index, turn on Advanced Settings. For more information, see Create search indexes.

    5. Click OK.

      After you create a search index, you can click Index Details in the Actions column of the search index to query information about the search index, such as the metering information and index fields.

  4. Use the virtual column to query data.

    1. Click Manage Data in the Actions column of the search index.

    2. In the Search dialog box, configure the parameters.

      1. By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate attribute columns with commas (,).

      2. Select index fields. Click Add. Specify 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 based on which the query results are sorted and configure sorting methods.

      4. Click OK.

        Data that meets the filter conditions is displayed in the specified order on the Indexes 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. Create a search index and specify virtual columns.

    • Parameters

      For 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 the virtual columns to query data.

    Query the rows in which the value of the Col_Long_Virtual_Keyword column matches "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 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 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 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());
    }