Use virtual columns

Prerequisites

Before you begin, ensure that you have:

An installed Tablestore SDK for Python 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

Considerations

When you create a virtual column, the source field and virtual column must conform to the following type conversion rules. The table lists only virtual column types that can be configured by using Tablestore SDK for Python.

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

Virtual columns can be used only for 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. Tablestore SDK for Python supports configuring virtual columns when a search index is created, but does not support dynamically adding virtual columns to an existing search index schema. Query data with the virtual column name after the mapping is created.

The following examples create a search index with virtual columns and then query existing data with a virtual column.

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.

table_name = "example_table"
index_name = "example_index"

fields = [
    FieldSchema("category", FieldType.KEYWORD, index=True),
    FieldSchema(
        "category_as_long", FieldType.LONG, index=True,
        is_virtual_field=True, source_fields=["category"]),
    FieldSchema("price", FieldType.LONG, index=True),
    FieldSchema(
        "price_as_keyword", FieldType.KEYWORD, index=True,
        is_virtual_field=True, source_fields=["price"]),
]
index_meta = SearchIndexMeta(fields)

client.create_search_index(table_name, index_name, index_meta)

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.

query = TermQuery("price_as_keyword", "1000")
search_query = SearchQuery(
    query,
    get_total_count=True,
    limit=10,
)
columns_to_get = ColumnsToGet(return_type=ColumnReturnType.ALL)

response = client.search(
    "example_table", "example_index", search_query, columns_to_get)
print(response.total_count)
print(response.rows)

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
field_name (required)	`str`	The virtual column name. The name must be unique in the search index.
field_type (required)	`FieldType`	The search index field type for the virtual column. The type must comply with the supported conversion rules.
index (optional)	`bool`	Specifies whether to index the virtual column. Set this parameter to `True` to query data with the virtual column. Default value: `False`.
is_virtual_field (required)	`bool`	Set this parameter to `True` to identify the field as a virtual column.
source_fields (required)	`List[str]`	The source field names mapped to the virtual column. Only one source field is currently supported.
is_array (optional)	`bool`	Specifies whether the virtual column is an array. Default value: `False`.
date_formats (optional)	`List[str]`	The date formats supported by a Date virtual column. This parameter is required for Date virtual columns.