Search functions

GoogleSQL for SecOps supports the following search functions.

Function list

Name	Summary
VECTOR_SEARCH	Performs a semantic search on embeddings to find similar entities.

VECTOR_SEARCH

VECTOR_SEARCH(
  TABLE base_table,
  column_to_search,
  TABLE query_table,
  [, query_column_to_search => string_value]
  [, top_k => int64_value]
  [, distance_type => string_value]
  [, max_distance => double_value]
  [, options => ]
)

Description

Searches embeddings in multiple rows in a table, known as batch vector searches, to find semantically similar entities. The VECTOR_SEARCH function returns the nearest neighbors from the base_table for each row in the query_table, based on the distance function applied to their vector embeddings.

Embeddings are high-dimensional numerical vectors that represent a given entity, like a piece of text or an audio file. Machine learning (ML) models use embeddings to encode semantics about such entities to make it easier to reason about and compare them. For example, a common operation in clustering, classification, and recommendation models is to measure the distance between vectors in an embedding space to find items that are most semantically similar.

Definitions

• base_table: The table to search for nearest neighbor embeddings.
• column_to_search: The name of the base table column to search for nearest neighbor embeddings. The column must be of type ARRAY<DOUBLE>, ARRAY<FLOAT>, or STRING.
• query_table: Query data containing the search field.

• query_column_to_search: A named argument with a STRING value. string_value specifies the name of the column in the query table or statement that contains the strings or embeddings for which to find nearest neighbors.

  • The value must be a single column name with valid types as ARRAY<DOUBLE>, ARRAY<FLOAT>, or STRING. The data type of this column must match the column_to_search column type.

  • If the type is ARRAY<DOUBLE> or ARRAY<FLOAT>, then all element values must be non-NULL. The dimension of all elements in this column must match the search column in base_table.

  • If the column is of type STRING, embeddings are automatically generated using the same model associated with column_to_search in base_table. System-specific metadata must be present to indicate how to generate the embeddings synchronously for the query. If embedding generation fails for the query, the VECTOR_SEARCH query fails.

  • If you don't specify query_column_to_search, the function uses the column_to_search value or picks the most appropriate column.

• top_k: A named argument with an INT64 value. top_k_value specifies the number of nearest neighbors to return. The default is 10. If the value is negative, all values are counted as neighbors and returned.

• distance_type: A named argument with a STRING value. string_value specifies the type of metric to use to compute the distance between two vectors. Supported distance types are EUCLIDEAN (default), COSINE, and DOT_PRODUCT.

• max_distance: A named argument with a DOUBLE value. double_value specifies the exclusive upper bound on the distance of returned neighbors. Neighbors with distances exceeding this value are filtered out. When max_distance is used with a DOT_PRODUCT distance type, the max_distance must be equivalent to -1 * DOT_PRODUCT to get the correct upper bound. The max_distance is useful for reducing data volume.

• options: A named argument with a value.

Output

For each row in the query data, the output contains multiple rows from the base table that satisfy the search criteria. The number of results rows per query table row is either 10 or the top_k value if it's specified. The order of the output isn't guaranteed.

The output includes the following columns:

• query: A STRUCT value that contains all selected columns from the query data.
• base: A STRUCT value that contains all columns from base_table for the neighbor row.
• distance: A DOUBLE value that represents the distance between the base data and the query data. If the distance_type argument is DOT_PRODUCT, the returned distance value is a negative DOT_PRODUCT value between the query and base vector.

Examples

For the first set of examples that follow, add the following sample data at the top of each query:

-- Sample data for the base table
WITH Songs AS (
  SELECT 1 AS song_id, 'Bohemian Rhapsody' AS title, 'Queen' AS artist, 'A Night at the Opera' AS album, 'Rock' AS genre, [0.1, 0.9, 0.2] AS song_embedding
  UNION ALL
  SELECT 2, 'Like a Rolling Stone' AS title, 'Bob Dylan' AS artist, 'Highway 61 Revisited' AS album, 'Rock' AS genre, [0.2, 0.8, 0.3] AS song_embedding
  UNION ALL
  SELECT 3, 'Stairway to Heaven' AS title, 'Led Zeppelin' AS artist, 'Led Zeppelin IV' AS album, 'Rock' AS genre, [0.15, 0.85, 0.25] AS song_embedding
  UNION ALL
  SELECT 4, 'What a Wonderful World' AS title, 'Louis Armstrong' AS artist, 'What a Wonderful World' AS album, 'Jazz' AS genre, [0.8, 0.1, 0.9] AS song_embedding
  UNION ALL
  SELECT 5, 'So What' AS title, 'Miles Davis' AS artist, 'Kind of Blue' AS album, 'Jazz' AS genre, [0.9, 0.2, 0.8] AS song_embedding
  UNION ALL
  SELECT 6, 'Hallelujah' AS title, 'Leonard Cohen' AS artist, 'Various Positions' AS album, 'Folk' AS genre, [0.5, 0.5, 0.5] AS song_embedding
),
-- Sample data for the query table
QuerySimilarSongs AS (
  SELECT 'query1' AS query_id, [0.12, 0.88, 0.22] AS search_embedding, 'Classic Rock' AS description
  UNION ALL
  SELECT 'query2', [0.85, 0.15, 0.85], 'Smooth Jazz'
)

The following examples search the song_embedding column of the Songs base table for the top two embeddings that match each row in the QuerySimilarSongs query table. Each example uses a different distance_type value.

SELECT
  query.query_id,
  query.description AS query_description,
  base.title,
  base.artist,
  distance
FROM VECTOR_SEARCH(
  TABLE Songs,
  'song_embedding',
  TABLE QuerySimilarSongs,
  query_column_to_search => 'search_embedding',
  top_k => 2
  -- No distance type specified, so defaults to EUCLIDEAN.
);

/*----------+-------------------+------------------------+-----------------+----------+
 | query_id | query_description | title                  | artist          | distance |
 +----------+-------------------+------------------------+-----------------+----------+
 | query1   | Classic Rock      | Bohemian Rhapsody      | Queen           | 0.0034   |
 | query1   | Classic Rock      | Stairway to Heaven     | Led Zeppelin    | 0.0519   |
 | query2   | Smooth Jazz       | What a Wonderful World | Louis Armstrong | 0.086    |
 | query2   | Smooth Jazz       | So What                | Miles Davis     | 0.0860   |
 +----------+-------------------+------------------------+-----------------+----------*/

SELECT
  query.query_id,
  query.description AS query_description,
  base.title,
  base.artist,
  distance
FROM VECTOR_SEARCH(
  TABLE Songs,
  'song_embedding',
  TABLE QuerySimilarSongs,
  query_column_to_search => 'search_embedding',
  top_k => 2,
  -- COSINE distance type
  distance_type => 'COSINE'
);

/*----------+-------------------+------------------------+-----------------+------------+
 | query_id | query_description | title                  | artist          | distance   |
 +----------+-------------------+------------------------+-----------------+------------+
 | query1   | Classic Rock      | Bohemian Rhapsody      | Queen           | 0.00061686 |
 | query1   | Classic Rock      | Stairway to Heaven     | Led Zeppelin    | 0.00147881 |
 | query2   | Smooth Jazz       | What a Wonderful World | Louis Armstrong | 0.00250705 |
 | query2   | Smooth Jazz       | So What                | Miles Davis     | 0.0025863  |
 +----------+-------------------+------------------------+-----------------+------------*/

SELECT
  query.query_id,
  query.description AS query_description,
  base.title,
  base.artist,
  distance  -- Negative dot product value
FROM VECTOR_SEARCH(
  TABLE Songs,
  'song_embedding',
  TABLE QuerySimilarSongs,
  query_column_to_search => 'search_embedding',
  top_k => 2,
  -- DOT_PRODUCT distance type as a negative DOT_PRODUCT value between the
  -- query and base vector. Smaller values (more negative) are more similar.
  distance_type => 'DOT_PRODUCT'
);

/*----------+-------------------+------------------------+-----------------+----------+
 | query_id | query_description | title                  | artist          | distance |
 +----------+-------------------+------------------------+-----------------+----------+
 | query1   | Classic Rock      | Bohemian Rhapsody      | Queen           | -0.848   |
 | query1   | Classic Rock      | Stairway to Heaven     | Led Zeppelin    | -0.821   |
 | query2   | Smooth Jazz       | So What                | Miles Davis     | -1.475   |
 | query2   | Smooth Jazz       | What a Wonderful World | Louis Armstrong | -1.46    |
 +----------+-------------------+------------------------+-----------------+----------*/

For the next set of examples that follow, add the following sample data at the top of each query:

-- Sample data for the Songs table
WITH Songs AS (
  SELECT 1 AS song_id, 'Bohemian Rhapsody' AS title, 'Queen' AS artist, 'A Night at the Opera' AS album, 'Rock' AS genre, [0.1, 0.9, 0.2] AS song_embedding
  UNION ALL
  SELECT 2, 'Like a Rolling Stone' AS title, 'Bob Dylan' AS artist, 'Highway 61 Revisited' AS album, 'Rock' AS genre, [0.2, 0.8, 0.3] AS song_embedding
  UNION ALL
  SELECT 3, 'Stairway to Heaven' AS title, 'Led Zeppelin' AS artist, 'Led Zeppelin IV' AS album, 'Rock' AS genre, [0.15, 0.85, 0.25] AS song_embedding
  UNION ALL
  SELECT 4, 'What a Wonderful World' AS title, 'Louis Armstrong' AS artist, 'What a Wonderful World' AS album, 'Jazz' AS genre, [0.8, 0.1, 0.9] AS song_embedding
  UNION ALL
  SELECT 5, 'So What' AS title, 'Miles Davis' AS artist, 'Kind of Blue' AS album, 'Jazz' AS genre, [0.9, 0.2, 0.8] AS song_embedding
  UNION ALL
  SELECT 6, 'Hallelujah' AS title, 'Leonard Cohen' AS artist, 'Various Positions' AS album, 'Folk' AS genre, [0.5, 0.5, 0.5] AS song_embedding
),
-- Sample data for the query table
QuerySimilarSongs AS (
  SELECT 'queryA' AS query_id, [0.6, 0.4, 0.4] AS search_embedding, 'Folk Rock' AS description
  UNION ALL
  SELECT 'queryB', [0.1, 0.1, 0.8], 'Mellow Jazz'
)

The following example searches the song_embedding column of the Songs base table for embeddings that are within a EUCLIDEAN maximum distance of 0.6 for each row in the QuerySimilarSongs query table.

SELECT
  query.query_id,
  query.description AS query_description,
  base.title,
  base.artist,
  distance
FROM VECTOR_SEARCH(
  TABLE Songs,
  'song_embedding',
  TABLE QuerySimilarSongs,
  query_column_to_search => 'search_embedding',
  distance_type => 'EUCLIDEAN',
  -- Only neighbors with distance < max_distance are returned.
  max_distance => 0.6
);

/*----------+-------------------+----------------------+---------------+----------------------+
 | query_id | query_description | title                | artist        | distance             |
 +----------+-------------------+----------------------+---------------+----------------------+
 | queryA   | Folk Rock         | Hallelujah           | Leonard Cohen | 0.17320508075688773  |
 | queryA   | Folk Rock         | So What              | Miles Davis   | 0.5385164807134505   |
 | queryA   | Folk Rock         | Like a Rolling Stone | Bob Dylan     | 0.5744562646538029   |
 +----------+-------------------+----------------------+---------------+----------------------*/

The following example searches the song_embedding column of the Songs base table for embeddings that are within a DOT_PRODUCT maximum distance of -0.8 for each row in the QuerySimilarSongs query table.

SELECT
  query.query_id,
  query.description AS query_description,
  base.title,
  base.artist,
  distance  -- Negative dot product value
FROM VECTOR_SEARCH(
  TABLE Songs,
  'song_embedding',
  TABLE QuerySimilarSongs,
  query_column_to_search => 'search_embedding',
  distance_type => 'DOT_PRODUCT',
  max_distance => -0.8  -- Equivalent to DOT_PRODUCT > 0.8
);

/*----------+-------------------+------------------------+-----------------+----------+
 | query_id | query_description | title                  | artist          | distance |
 +----------+-------------------+------------------------+-----------------+----------+
 | queryA   | Folk Rock         | So What                | Miles Davis     | -0.94    |
 | queryA   | Folk Rock         | What a Wonderful World | Louis Armstrong | -0.88    |
 | queryB   | Mellow Jazz       | What a Wonderful World | Louis Armstrong | -0.81    |
 +----------+-------------------+------------------------+-----------------+----------*/

In the previous example, DOT_PRODUCT > -max_distance. Remember that when max_distance is used with a DOT_PRODUCT distance type, the max_distance must be equivalent to -1 * DOT_PRODUCT. The previous example follows the logic DOT_PRODUCT > -max_distance, which is equivalent to the expected logic -1 * DOT_PRODUCT < max_distance.