Use virtual columns - Tablestore SDK for Java - Tablestore

Prerequisites

Before you begin, ensure that you have:

An installed Tablestore SDK for Java and an initialized client
A table whose maximum number of versions is set to 1
A table TTL of -1, or table updates with UpdateRow disabled

Usage notes

When you create a virtual column, the source field and virtual column must conform to the following type conversion rules.

Source field type	Virtual column type
String	Keyword (including arrays), FuzzyKeyword (including arrays), Text (including arrays), Long (including arrays), Double (including arrays), Date (including arrays), IP (including arrays), Geo-point (including arrays)
Long	Keyword, FuzzyKeyword, Text, Date
Double	Keyword, FuzzyKeyword, Text

Virtual columns can be used only in queries and cannot be returned by ColumnsToGet. To retrieve a value, return the source field mapped to the virtual column.

Description

A virtual column maps a source table field to a different search index field type. Configure virtual columns when you create a search index, or add them to an existing index by dynamically modifying its schema. Query data with the virtual column name after the mapping is created.

The following examples show the complete workflow: create a search index with virtual columns, and then query data with one of the virtual columns.

Create virtual columns

The following example maps the Keyword source field category to a Long virtual column and the Long source field price to a Keyword virtual column.

String tableName = "example_table";
String indexName = "example_index";
IndexSchema indexSchema = new IndexSchema();
indexSchema.setFieldSchemas(Arrays.asList(
        new FieldSchema("category", FieldType.KEYWORD),
        new FieldSchema("category_as_long", FieldType.LONG)
                .setVirtualField(true)
                .setSourceFieldName("category"),
        new FieldSchema("price", FieldType.LONG),
        new FieldSchema("price_as_keyword", FieldType.KEYWORD)
                .setVirtualField(true)
                .setSourceFieldName("price")));

CreateSearchIndexRequest request = new CreateSearchIndexRequest();
request.setTableName(tableName);
request.setIndexName(indexName);
request.setIndexSchema(indexSchema);
client.createSearchIndex(request);

After the request succeeds, wait until the search index finishes synchronizing data before you query it.

Query data with a virtual column

The following example queries rows whose price_as_keyword virtual column equals the string 1000 and returns source table columns.

String tableName = "example_table";
String indexName = "example_index";
TermsQuery query = new TermsQuery();
query.setFieldName("price_as_keyword");
query.addTerm(ColumnValue.fromString("1000"));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(query);
searchQuery.setGetTotalCount(true);

SearchRequest request = new SearchRequest(tableName, indexName, searchQuery);
SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAll(true);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println(response.getTotalCount());
System.out.println(response.getRows());

Parameters

FieldSchema contains the following parameters for defining a virtual column. For other search index field parameters, see Create a search index.

Name	Type	Description
fieldName (required)	`String`	The virtual column name. The name must be unique in the index.
fieldType (required)	`FieldType`	The search index field type for the virtual column. The type must comply with the supported conversion rules.
isVirtualField (required)	`Boolean`	Set this parameter to `true` to identify the field as a virtual column.
sourceFieldNames (required)	`List<String>`	The source field names mapped to the virtual column. Only one source field is currently supported. Configure it with `setSourceFieldName`.