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.

Prerequisites

  • A Tablestore client is initialized. For more information, see Initialization.
  • A data table is created for which the value of the timeToLive parameter is set to -1 and the value of the maxVersions parameter is set to 1.

Precautions

  • The following table describes the data type conversion between virtual columns and columns in data tables.
    Field type of data tablesField type of virtual columns
    StringKEYWORD and KEYWORD ARRAY
    StringTEXT and TEXT ARRAY
    StringLONG and LONG ARRAY
    StringDOUBLE and DOUBLE ARRAY
    StringGEOPOINT and GEOPOINT ARRAY
    LongKeyword
    LongText
    DoubleKeyword
    DoubleText
  • 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 a virtual column when you create a search index.
    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 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());
    }