Console
    Contact Us Start free

    Cloud SQL functions

    This pages describes Cloud SQL functions.

    Vector conversion functions

    This following table lists the functions that you can use to manipulate vector information in a SELECT statement.

    Function Description
    vector_to_string Input: VECTOR

    Output: STRING

    		 Converts an argument to a string in a human-readable vector format.

    Input:one argument of type VECTOR

    Output:a string

    Syntax:

    vector_to_string( some_embedding )

    string_to_vector Input: STRING

    Output: VECTOR

    		 Converts a string to a human-readable vector format. This lets you write the values you want represented in a vector.

    Input:a string

    Syntax:

    string_to_vector(' some_embedding ')

    Output:one value of type vector.

    Search functions

    This section describes Cloud SQL search functions.

    KNN functions

    This following table lists the functions that you can use to calculate the KNN vector distance.

    Function Data type Description
    vector_distance Input: VECTOR

    Output: REAL

    		 Calculates the vector distance between two VECTOR s. The two VECTOR s must have the same dimensions.

    Input: required. Takes two vector values, An optional third string argument indicates the distance measure. Default is `l2_squared_distance. Other options include `cosine_distance` and `dot_product`.

    Output: the distance between the two vectors.

    For example:

    SELECT vector_distance(string_to_vector('[1,-2,3]'), string_to_vector('[1,2,3]'), 'Distance_Measure=dot_product');

    cosine_distance Input: VECTOR

    Output: REAL

    		 Algorithm to calculate the cosine of the angle between two vectors. A smaller value indicates greater similarity between the vectors.

    Input: takes two vector values. These can be column names or constants.

    Output: the cosine distance between the two vectors.

    For example:

    SELECT cosine_distance(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

    SELECT id FROM t1 ORDER BY cosine_distance(string_to_vector('[1,2,3]'), embedding_column_name) LIMIT 10;

    dot_product Input: VECTOR

    Output: REAL

    		 Algorithm that performs the dot product operation between two input vectors to calculate and output a single scalar value.

    Input: takes two vector values. These can be column names or constants.

    Output: the dot product of the two vectors.

    For example:

    SELECT dot_product(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

    SELECT id FROM t1 ORDER BY dot_product(string_to_vector('[1,2,3]'), embbeding_column_name) LIMIT 10;

    l2_squared_distance Input: VECTOR

    Output: REAL

    		 Algorithm that adds the squared distance on each dimension between two input vectors to measure the Euclidean distance between them.

    Input: takes two vector values. These can be column names or constants.

    Output: the L2 squared distance between the two vectors.

    For example:

    SELECT l2_squared_distance(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

    SELECT id FROM t1 ORDER BY l2_squared_distance(string_to_vector('[1,2,3]'), embbeding_column_name) LIMIT 10;

    ANN function

    This following table lists the function that you can use to calculate vector distance.

    Function
    Data Type
    Description
    approx_distance
    Input: VECTOR

    Output: REAL

    Finds the top K closest rows that satisfy the distance measure using the selected algorithm. This function queries the approximate nearest neighbors from a vector column to a constant value. The two embedding column's VECTOR type and the constant VECTOR must have the same dimensions. There are some cases when this function falls back to a KNN (exact search) search instead of ANN search. You must include a limit with queries that use this function.

    Syntax:

    approx_distance( embedding_name ,
    query_vector ,
    'distance_measure= algorithm_name
    [, num_leaves_to_search= value ]'

    Inputs:

    1. embedding_name: A vector embedding column name from the base table.
    2. query_vector: A constant of type `VECTOR` which can be (but is not required to be) the output of string_to_vector .
    3. The comma-separated search string options include the following:
      • distance_measure: required. Uses an algorithm to measure distance between vectors. It uses the following string literals:
        • L2_SQUARED
        • COSINE
        • DOT_PRODUCT

        For example: distance_measure=cosine

      • num_leaves_to_search : optional. Specifies the number of leaves to probe for an ANN vector similarity search. If you don't specify the number of leaves, CloudSQL for MySQL picks a computed value for num_leaves_to_search which can be viewed in information_schema.innodb_vector_indexes . The computed number is generally a good starting point with good search quality and performance. It's recommended that you tune num_leaves_to_search based on your workload and performance or quality trade off.

      For example:

      'distance_measure=dot_product,
      num_leaves_to_search=100'

      Required LIMIT value:The specified limit is used as the number of neighbors to return (also known as the top K).

      Output: The approximate distance of the top K closest rows in the base table.

      This function can only be used in the ORDER BY or SELECT list.

      For example:

      SELECT id, approx_distance(embedding_column_name
      string_to_vector('[1,1,1]'),
      'distance_measure=cosine') dist from t1
      ORDER BY dist LIMIT 10;

      SELECT id
      FROM t1
      ORDER BY
      approx_distance(
      embedding_column_name
      string_to_vector('[1,1,1]'),
      'distance_measure=dot_product,num_leaves_to_search=100) LIMIT 4;

    What's next
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: