Query a Cortex Search Service¶

Install the Snowflake Python APIs library¶

First, install the latest version of the Snowflake Python APIs package from PyPI. See Install the Snowflake Python APIs library for instructions on installing this package from PyPI.

pip install snowflake -U

Connect to Snowflake¶

Connect to Snowflake using either a Snowpark Session or a Python Connector Connection and create a Root object. See Connect to Snowflake with the Snowflake Python APIs for more instructions on connecting to Snowflake. The following example uses the Snowpark Session object and a Python dictionary for configuration.

import os
from snowflake.core import Root
from snowflake.snowpark import Session

CONNECTION_PARAMETERS = {
    "account": os.environ["snowflake_account_demo"],
    "user": os.environ["snowflake_user_demo"],
    "password": os.environ["snowflake_password_demo"],
    "role": "test_role",
    "database": "test_database",
    "warehouse": "test_warehouse",
    "schema": "test_schema",
}

session = Session.builder.configs(CONNECTION_PARAMETERS).create()
root = Root(session)

Query the service¶

Query the service using the following syntax:

# fetch service
my_service = (root
  .databases["<service_database>"]
  .schemas["<service_schema>"]
  .cortex_search_services["<service_name>"]
)

# query service
resp = my_service.search(
  query="<query>",
  columns=["<col1>", "<col2>"],
  filter={"@eq": {"<column>": "<value>"} },
  limit=5
)
print(resp.to_json())

Parameters¶

Configure REST API authentication¶

Snowflake REST APIs currently support authentication via key pair authentication and OAuth. For details, see Authenticating Snowflake REST APIs with Snowflake.

Example of querying the service¶

To query the service using curl and key pair authentication:

curl --location https://<ACCOUNT_URL>/api/v2/databases/<DB_NAME>/schemas/<SCHEMA_NAME>/cortex-search-services/<SERVICE_NAME>\:query \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header "Authorization: Bearer $CORTEX_SEARCH_JWT" \
--data '{
  "query": "<search_query>",
  "columns": ["col1", "col2"],
  "filter": <filter>
  "limit": <limit>
}'

Example¶

The following example previews the service with the preview query query string and parses the results into a VARIANT object.

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'my_search_service',
      '{
         "query": "preview query",
         "columns":[
            "col1",
            "col2"
         ],
         "filter": {"@eq": {"col1": "filter value"} },
         "limit":10
      }'
  )
)['results'] as results;

Example¶

• Filtering on rows where string-like column string_col is equal to value value.

  { "@eq": { "string_col": "value" } }
  

  Copy

• Filtering on rows where ARRAY column array_col contains value value.

  { "@contains": { "array_col": "arr_value" } }
  

  Copy

• Filtering on rows where NUMERIC column numeric_col is between 10.5 and 12.5 (inclusive):

  { "@and": [
    { "@gte": { "numeric_col": 10.5 } },
    { "@lte": { "numeric_col": 12.5 } }
  ]}
  

  Copy

• Filtering on rows where TIMESTAMP column timestamp_col is between 2024-11-19 and 2024-12-19 (inclusive).

  { "@and": [
    { "@gte": { "timestamp_col": "2024-11-19" } },
    { "@lte": { "timestamp_col": "2024-12-19" } }
  ]}
  

  Copy

• Composing filters with logical operators:

  // Rows where the "array_col" column contains "arr_value" and the "string_col" column equals "value":
  {
      "@and": [
        { "@contains": { "array_col": "arr_value" } },
        { "@eq": { "string_col": "value" } }
      ]
  }
  
  // Rows where the "string_col" column does not equal "value"
  {
    "@not": { "@eq": { "string_col": "value" } }
  }
  
  // Rows where the "array_col" column contains at least one of "val1", "val2", or "val3"
  {
    "@or": [
        { "@contains": { "array_col": "val1" } },
        { "@contains": { "array_col": "val1" } },
        { "@contains": { "array_col": "val1" } }
    ]
  }
  

  Copy

Querying a service with boost or decay signals¶

When querying a Cortex Search Service, specify the columns to use for boosting or decaying in the optional numeric_boosts and time_decays fields in the scoring_config.functions field. You can also specify the weight for each boost or decay.

{
  "scoring_config": {
    "functions": {
      "numeric_boosts": [
        {
          "column": "<column_name>",
          "weight": <weight>
        },
        // ...
      ],
      "time_decays": [
        {
          "column": "<column_name>",
          "weight": <weight>,
          "limit_hours": <limit_hours>
        },
        // ...
      ]
    }
  }
}

Properties:

• numeric_boosts (array, optional):

  • <numeric_boost_object> (object, optional):

    • column_name (string): Specifies the numeric column to which the boost should be applied.

    • weight (float): Specifies the weight or importance assigned to the boosted column in the ranking process. When multiple columns are specified, a higher weight increases the influence of the field.

• time_decays (array, optional):

  • <time_decay_object> (object, optional):

    • column_name (string): Specifies the time or date column to which the decay should be applied.

    • weight (float): Specifies the weight or importance assigned to the decayed column in the ranking process. When multiple columns are specified, a higher weight increases the influence of the field.

    • limit_hours (float): Sets the boundary after which time starts to have less effect on the relevance or importance of the document. For example, a limit_hours value of 240 indicates that documents with timestamps greater than 240 hours (10 days) in the past from the now timestamp do not receive significant boosting, while documents with a timestamp within the last 240 hours should receive a more significant boost.

    • now (string, optional): Optional reference timestamp from which decays are calculated in ISO-8601 format yyyy-MM-dd'T'HH:mm:ss.SSSXXX. For example, "2025-02-19T14:30:45.123-08:00". Defaults to the current timestamp if not specified.

Example¶

Create sample data and Cortex Search Service

This example uses table named business_documents has two timestamp columns (last_modified, created_timestamp) and two integer columns (likes, columns) which can be used for boosts and decays on search results.

CREATE OR REPLACE TABLE business_documents (
    document_contents VARCHAR,
    last_modified_timestamp TIMESTAMP,
    created_timestamp TIMESTAMP,
    likes INT,
    comments INT
);

INSERT INTO business_documents (document_contents, last_modified_timestamp, created_timestamp, likes, comments)
VALUES
    ('Quarterly financial report for Q1 2024: Revenue increased by 15%, with expenses stable. Highlights include strategic investments in marketing and technology.',
     '2024-01-12 10:00:00', '2024-01-10 09:00:00', 10, 20),

    ('IT manual for employees: Instructions for usage of internal technologies, including hardware and software guides and commonly asked tech questions.',
     '2024-02-10 15:00:00', '2024-02-05 14:30:00', 85, 10),

    ('Employee handbook 2024: Updated policies on remote work, health benefits, and company culture initiatives. Includes new guidelines on hybrid working models.',
     '2024-02-10 15:00:00', '2024-02-05 14:30:00', 85, 10),

    ('Marketing strategy document: Target audience segmentation for upcoming product launch. Detailed plans for social media, influencer partnerships, and content creation.',
     '2024-03-15 12:00:00', '2024-03-12 11:15:00', 150, 32),

    ('Product roadmap 2024: Key milestones for tech product development, including the launch of new features, bug fixes, and performance improvements.',
     '2024-04-22 17:30:00', '2024-04-20 16:00:00', 200, 45),

    ('Annual performance review process guidelines: Procedures for managers to conduct employee evaluations, set goals, and provide constructive feedback.',
     '2024-05-02 09:30:00', '2024-05-01 08:45:00', 60, 5);

Next, create a Cortex Search Service named business_documents_css on the document_contents column of the business_documents table.

CREATE OR REPLACE CORTEX SEARCH SERVICE business_documents_css
    ON document_contents
    WAREHOUSE = <warehouse_name>
    TARGET_LAG = '1 minute'
AS SELECT * FROM business_documents;

Query the service with numeric boosts

The query below applies numeric boosts to both the likes and comments columns, with twice the boost weight on comments values relative to likes values. The query uses the SQL SEARCH_PREVIEW function to search for “technology”.

SELECT
    index,
    value['DOCUMENT_CONTENTS']::string as DOCUMENT_CONTENTS,
    value['LIKES']::int as LIKES,
    value['COMMENTS']::int as COMMENTS,
FROM TABLE(FLATTEN(PARSE_JSON(SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'business_documents_css',
    '{
      "query": "technology",
      "columns": ["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
      "scoring_config": {
        "functions": {
          "numeric_boosts": [
            {"column": "comments", "weight": 2},
            {"column": "likes", "weight": 1}
          ]
        }
      }
    }'
))['results'] ))

In the results, note:

• With the boosts, the "Product roadmap 2024:..." document is the top result because of its large number of likes and comments, even though it has slightly lower relevance to the query "technology"

• Without any boosts, the top result for the query is "IT manual for employees:..."

Query the service using time decays

The following query applies time decays based on the LAST_MODIFIED_TIMESTAMP column, where:

• Documents with more recent LAST_MODIFIED_TIMESTAMP values, relative to the now timestamp, are boosted

• Documents with a LAST_MODIFIED_TIMESTAMP value greater than 240 hours from the now timestamp receive little boosting

SELECT
  value['DOCUMENT_CONTENTS']::string as DOCUMENT_CONTENTS,
  value['LAST_MODIFIED_TIMESTAMP']::timestamp as LAST_MODIFIED_TIMESTAMP
FROM TABLE(FLATTEN(PARSE_JSON(SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'business_documents_css',
    '{
      "query": "technology",
      "columns": ["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP", "CREATED_TIMESTAMP", "LIKES", "COMMENTS"],
      "scoring_config": {
          "functions": {
              "time_decays": [
                {"column": "LAST_MODIFIED_TIMESTAMP", "weight": 1, "limit_hours": 240, "now": "2024-04-23T00:00:00.000-08:00"}
              ]
            }
        }
    }'
))['results'] ));

In the results, note:

• With the decays, the "Product roadmap 2024:..." document is the top result because of its recency to the now timestamp, even though it has slightly lower relevance to the query "technology"

• Without any decays, the top result for the query is "IT manual for employees:..."

Querying a Cortex Search Service without the reranker¶

You can disable the reranker for an individual query at query time in the scoring_config.reranker field in the following format:

{
  "scoring_config": {
      "reranker": "none"
}

Properties:

• reranker (string, optional): Parameter that can be set to “none” if the reranker should be turned off. If excluded or null, the default reranker is used.

Examples¶

Querying a Search Service without the reranker (Python)

The following code queries the service without the reranking step using the Python API:

resp = business_documents_css.search(
  query="technology",
  columns=["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP"],
  limit=5,
  scoring_config={
    "reranker": "none"
  }
)

Querying a Service without the reranker (SQL)

The following SQL statement queries the service without the reranking step using the SEARCH_PREVIEW function.

SELECT
    value['DOCUMENT_CONTENTS'], value['LAST_MODIFIED_TIMESTAMP']
FROM TABLE(FLATTEN(PARSE_JSON(SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'business_documents_css',
    '{
      "query": "technology",
      "columns": ["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP"],
      "scoring_config": {
        "reranker": "none"
      }
    }'
))['results'] ));

Querying with owner’s rights¶

Cortex Search Services perform searches with owner’s rights and follow the same security model as other Snowflake objects that run with owner’s rights.

In particular, this means that any role with sufficient privileges to query a Cortex Search Service may query any of the data the service has indexed, regardless of that role’s privileges on the underlying objects (such as tables and views) referenced in the service’s source query.

For example, for a Cortex Search Service that references a table with row-level masking policies, querying users of that service will be able to see search results from rows on which the owner’s role has read permission, even if the querying user’s role cannot read those rows in the source table.

Use caution, for example, when granting a role with USAGE privileges on a Cortex Search Service to another Snowflake user.