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

  • Client is initialized. For more information, see Initialization.
  • A data table is created for which timeToLive is set to -1 and maxVersions 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 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 a virtual column when you create a search index.
    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());
    }