Tablestore:KNN vector query of search indexes

Usage notes

• To use the KNN vector query feature, the type of the column to which the Vector field is mapped in the data table must be String. When you create a search index, you must set the type of the field to which the String column is mapped to Vector. Then, specify the number of dimensions and data type of the Vector field, and the algorithm that you want to use to measure the distance between vectors.

• To execute SQL statements to use the KNN vector query feature, you must create a mapping table for the search index that is created for the data table. For information about how to create a mapping table for a search index, see Create mapping tables for search indexes.

Data type mappings

Create a mapping table for a search index

If you execute the CREATE TABLE statement to create a mapping table for a search index, the method that you use to create a Vector field is the same as the method that you use to create a field of other types. You only need to specify a valid name and data type for the Vector field. When you create a mapping table for a search index, we recommend that you specify the MEDIUMTEXT type as the mapped data type in SQL for a field of the Vector type.

The following sample SQL statement provides an example on how to create a mapping table for a search index that contains fields of the Vector type:

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

Perform a KNN vector query

Procedure

In this example, a data table named vector_query_table is used. The data table consists of the pk primary key column of the String type, the col_vector attribute column of the String type, and the col_keyword attribute column of the String type.

To perform a KNN vector query in SQL, create a search index for the data table, create a mapping table for the search index, and then execute SQL statements. Procedure:

1. Create a search index and configure a Vector field. For more information, see Use search indexes in the Tablestore console and Use Tablestore SDKs.

   Note

   If no Vector field is specified in the search index that you want to use, you can modify the schema of the search index to configure a Vector field. For more information, see Dynamically modify the schema of a search index.

   • The following figure shows how to create a search index in the Tablestore console.

     In this example, a search index named vector_query_table_index is created. The search index consists of the col_vector field of the Vector type and the col_keyword field of the String type. The col_vector field of the Vector type is mapped to the col_vector column of the String type in the data table.

     

   • 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 and Use Tablestore SDKs to use the SQL query feature.

   1. In this example, a mapping table named vector_query_table__vector_query_table_index is created for the search index. The data type of the field to which the Vector field named col_vector in the mapping table mapped is MEDIUMTEXT in SQL. For more information, see Create mapping tables for search indexes.

      You can execute the following sample SQL statement to create the mapping table:

      CREATE TABLE `vector_query_table__vector_query_table_index`(
          `col_vector` MEDIUMTEXT,
          `col_keyword` MEDIUMTEXT
      )
      ENGINE='searchindex',
      ENGINE_ATTRIBUTE='{"index_name":"vector_query_table_index","table_name":"vector_query_table"}';

   2. (Optional) After you create a mapping table for the search index, you can perform the following operations based on your business requirements.

      Query information about a table

      Execute the following SQL statement to query information about the mapping table for the search index:

      DESCRIBE vector_query_table__vector_query_table_index;

      The following figure shows the query results.

      

      Query data in a table

      Execute the following SQL statement to query data in the mapping table for the search index:

      SELECT * FROM vector_query_table__vector_query_table_index;

      The following figure shows the query results. For example, the vector_query_table__vector_query_table_index mapping table for the search index contains 10 rows of data.

      

3. Perform a KNN vector query to query data. For more information, see Query data.

   The following sample SQL statement provides an example on how to query the top 5 values in the col_vector column that have the highest similarity to [1.5, 2.0, 2.5, 1.7] and display the relevance scores of the query results:

   SELECT *,SCORE() FROM vector_query_table__vector_query_table_index WHERE VECTOR_QUERY_FLOAT32(col_vector, "[1.5, 2.0, 2.5, 1.7]", 5) limit 5;

   The following figure shows the query results.

   

Limits

• You can use the VECTOR_QUERY_FLOAT32 function only when you query data by using a mapping table that is created for a search index.

• When you use the VECTOR_QUERY_FLOAT32 function, you must use the LIMIT clause. You cannot use the HAVING clause.

• The VECTOR_QUERY_FLOAT32 function can only be used as a WHERE clause of a SELECT statement but cannot be used as a column expression of the SELECT statement, as an aggregate function, or for grouping or sorting.

• You must use the SCORE() function together with the VECTOR_QUERY_FLOAT32 function. You cannot use the SCORE() function separately.

• The SCORE() function can only be used as a column expression of a SELECT statement. It cannot be used as a WHERE clause of a SELECT statement, as an aggregation function, or for sorting.