Tablestore:Virtual columns of search indexes

Usage notes

• You cannot query data of GEOPOINT fields in search indexes by executing SQL statements.

• You cannot query virtual columns of the ARRAY type by executing SQL statements.

Data type mappings

Create a mapping table for a search index

If you want to query virtual columns of a search index by executing SQL statements, you must create a mapping table for the search index. For more information, see Create mapping tables for search indexes.

In the CREATE TABLE statement, you must specify the valid names and data types of the virtual columns.

Sample SQL statement:

CREATE TABLE `test_table__test_table_index`(
    `id` MEDIUMTEXT,
    `col_keyword` MEDIUMTEXT,
    `col_keyword_virtual_long` BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';

Query data by executing SQL statements

When you execute SQL statements to query data, you can use virtual columns in the following ways:

• You can use virtual columns together with the WHERE clause in a SELECT statement to query the data that meets the specified conditions.

  If you want to use a virtual column in a query condition, the virtual column must be of the same data type as the parameter in the query condition.

• You can use aggregate functions together with virtual columns to analyze data. The data types of the source columns of the virtual columns must meet the requirements of aggregate operations on a search index. For example, if you want to obtain the sum of values for a column in a search index, the column must be of the LONG or DOUBLE type. If the source column of a virtual column in the search index is of the KEYWORD type, you cannot obtain the sum of values for the virtual column.

• You can group results based on virtual columns. The data types of the source columns of the virtual columns must meet the requirements of grouping operations on a search index. For example, you cannot group data of the TEXT type in search indexes. Therefore, the values of a virtual column of the TEXT type cannot be grouped.

• You can execute SQL statements to return top n query results based on virtual columns.

• You can sort data based on virtual columns. However, you must use the LIMIT clause if you want to sort data based on virtual columns. You cannot sort data based on virtual columns without the LIMIT clause.

When you execute SQL statements to query data based on virtual columns, take note of the following limits:

• To use virtual columns in a search index, you must create a mapping table for the search index.

• Virtual columns can be used only in SQL query statements. However, a SELECT statement cannot directly return the values of virtual columns. If you want to return the values of a virtual column, specify the source column of the virtual column in the SELECT statement. For example, you can execute the SELECT * statement to query data based on the mapping table of a search index that contains the column_virtual virtual column as expected. However, the virtual column is automatically ignored in the query result. You cannot execute the SELECT column_virtual statement on the mapping table.

• You cannot perform comparisons, calculations, or join operations between virtual columns.

• You cannot perform calculations that involve type casting on virtual columns. In addition, you cannot use virtual columns in functions that are not supported by search indexes. Search indexes support only aggregate functions in SQL queries.

Example

In the example, the data table is named test_table. The table contains the id column of the LONG type and the col_keyword column of the STRING type.

If you want to execute SQL statements to query data in the data table based on virtual columns, you must create a search index for the data table, and then create a mapping table for the search index. To use virtual columns, perform the following steps:

1. Create a search index for the table and specify virtual columns when you create the search index. For more information, see Use the virtual column feature in the Tablestore console or Use Tablestore SDKs to manage the virtual column feature.

   Note

   If virtual columns are not specified in the search index to be used, you can modify the schema of the search index to add virtual columns. For more information, see Dynamically modify schemas.

   In the example, the search index is named test_table_index. The search index contains the id column of the String type, the col_keyword column of the KEYWORD type, and the col_keyword_virtual_long virtual column of the LONG type. The source column of the virtual column is the col_keyword column.

   The following figure shows how to configure the search index in the Tablestore console.

   

   The following figure shows the schema of the search index.

   

2. Create a mapping table for the search index. For more information, see Use the SQL query feature in the Tablestore console or Use Tablestore SDKs.

   In the example, the mapping table of the search index is named test_table__test_table_index. The SQL data type of the col_keyword_virtual_long virtual column of the LONG type in the mapping table is BIGINT. For more information, see Create mapping tables for search indexes.

   Sample SQL statement:

   CREATE TABLE `test_table__test_table_index`(
       `id` MEDIUMTEXT,
       `col_keyword` MEDIUMTEXT,
       `col_keyword_virtual_long` BIGINT
   )
   ENGINE='searchindex',
   ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';

   After the mapping table is created for the search index, execute the following statement to query data from the mapping table:

   SELECT * FROM `test_table__test_table_index`;

   The following figure shows the query result. In the example, the test_table__test_table_index mapping table contains 10 data records.

   

3. Execute SELECT statements to query data. For more information, see Query data.

   • The following example shows how to obtain the average value of all values that are less than 50 in the col_keyword_virtual_long virtual column.

     SELECT AVG(col_keyword_virtual_long) FROM `test_table__test_table_index` WHERE col_keyword_virtual_long < 50; 

     The col_keyword_virtual_long virtual column has the following four data records whose value is less than 50: 10, 20, 30, and 40. The calculated average value is 25.0000. The following figure shows the query result.

     

   • The following example shows how to sort other columns in descending order based on the col_keyword_virtual_long virtual column and return the top five data records.

     SELECT * FROM `test_table__test_table_index` ORDER BY col_keyword_virtual_long DESC LIMIT 5;

     The following figure shows the query result.