Query a Cortex Search Service¶

Python API¶

Cortex Search Services may be queried using version 0.8.0 or later of the Snowflake Python APIs. See Snowflake Python APIs: Managing Snowflake objects with Python for more information on the Snowflake Python APIs.

pip install snowflake -U

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)

REST API¶

Cortex Search exposes a REST API endpoint in the suite of Snowflake REST APIs. The REST endpoint generated for a Cortex Search Service is of the following structure:

https://<account_url>/api/v2/databases/<db_name>/schemas/<schema_name>/cortex-search-services/<service_name>:query

Where:

• <account_url>: Your Snowflake Account URL. See Finding the organization and account name for an account for instructions on finding your account URL.

• <db_name>: Database in which the service resides.

• <schema_name>: Schema in which the service resides.

• <service_name>: Name of the service.

• :query: The method to invoke on the service; in this case, the query method.

For additional details, see the REST API reference for Cortex Search Service.

SQL SEARCH_PREVIEW function¶

The SNOWFLAKE.CORTEX.SEARCH_PREVIEW function allows you to preview the results of individual queries to a Cortex Search Service from within a SQL environment such as a worksheet or Snowflake notebook cell. This function makes it easy to interactively validate that a service has populated correctly and is serving reasonable results.

Usage notes¶

• Matching against NaN (‘not a number’) values in the source query is handled as described in Special values.

• Fixed-point numeric values with more than 19 digits (not including leading zeroes) do not work with @eq, @gte, or @lte and will not be returned by these operators (although they could still be returned by the overall query with the use of @not).

• TIMESTAMP and DATE filters accept values of the form: YYYY-MM-DD and, for timezone aware dates: YYYY-MM-DD+HH:MM. If the timezone offset is not specified, the date is interpreted in UTC.

• @primarykey is only supported for services configured with a primary key. The value of the filter must be a JSON object mapping every primary key column to its corresponding value (or NULL).

These operators can be combined into a single filter object.

Example¶

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

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

  Copy

• Filtering to a row with the specified primary key values us-west-1 in the region column and abc123 in the agent_id column:

  { "@primarykey": { "region": "us-west-1", "agent_id": "abc123" } }
  

  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": "val2" } },
      { "@contains": { "array_col": "val3" } }
    ]
  }
  

  Copy

Numeric boosts and time decays¶

You can boost or apply decays search results based on numeric or timestamp metadata. This feature is useful when you have structured metadata (e.g., popularity or recency signals) per result that can help determine the relevance of documents at query time. You can specify two categories of ranking signals when making a query:

Boost and decay metadata come from columns in the source table from which a Cortex Search Service is created. You specify the metadata columns to use for boosting or decaying when you make the query, but those columns must be included when creating the Cortex Search service.

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.

Reranking¶

By default, queries to Cortex Search Services leverage semantic reranking to improve search result relevance. While reranking can measurably increase result relevance, it can also noticeably increase query latency. You can disable reranking in any Cortex Search query if you’ve found that the quality benefit that reranking provides can be sacrificed for faster query speeds in your business use case.

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.

Named scoring profiles¶

Boosts/decays and reranker settings together form a scoring configuration, which can be specified in the scoring_config parameter when making a query. Scoring configurations can also be given a name and attached to the Cortex Search service.

Using a named scoring profile lets you easily use a scoring configuration across applications and queries without having to specify the full scoring configuration each time. If you change the scoring configuration, you only need to update it in one place, not in every query.

To add a scoring profile to your Cortex Search Service, use the ALTER CORTEX SEARCH SERVICE … ADD SCORING PROFILE command, as shown in the following example:

ALTER CORTEX SEARCH SERVICE my_search_service
  ADD SCORING PROFILE heavy_comments_with_likes IF NOT EXISTS
  '{
    "functions": {
            "numeric_boosts": [
                { "column": "comments", "weight": 6 },
                { "column": "likes", "weight": 1 }
            ]
    }
  }'

The syntax of the scoring profile definition is the same schema used in the scoring_config parameter when making a query.

Scoring profiles can’t be modified once created; to change a profile, drop it and recreate it with the new scoring configuration. To delete a named scoring profile, use ALTER CORTEX SEARCH SERVICE … DROP SCORING PROFILE.

To query a Cortex Search Service using a named scoring profile, specify the profile name in the scoring_profile parameter when making a query, as shown in the following examples:

results = svc.search(
    query="technology",
    columns=["comments", "likes"],
    scoring_profile="heavy_comments_with_likes",
    limit=10
)

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 $PAT" \
--data '{
  "query": "technology",
  "columns": ["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
  "scoring_profile": "heavy_comments_with_likes,
  "limit": 10
}'

SELECT SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
  'my_search_service',
  '{
    "query": "technology",
    "columns": ["comments", "likes"],
    "scoring_profile": "heavy_comments_with_likes",
    "limit": 10
  }'
);

To see a service’s stored scoring profiles, query the CORTEX_SEARCH_SERVICE_SCORING_PROFILES view in the INFORMATION_SCHEMA schema, as shown in the following example:

SELECT *
  FROM my_db.INFORMATION_SCHEMA.CORTEX_SEARCH_SERVICE_SCORING_PROFILES
  WHERE service_name = 'my_search_service';

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.

Setup for examples¶

The following examples use a table named business_documents with timestamp and numeric columns for demonstrating various features:

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.',
     '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.',
     '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.',
     '2024-02-10 15:00:00', '2024-02-05 14:30:00', 85, 10),

    ('Marketing strategy document: Target audience segmentation for upcoming product launch.',
     '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.',
     '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.',
     '2024-05-02 09:30:00', '2024-05-01 08:45:00', 60, 5);

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

Filter examples¶

resp = business_documents_css.search(
    query="technology",
    columns=["DOCUMENT_CONTENTS", "LIKES"],
    filter={"@eq": {"REGION": "US"}},
    limit=5
)

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 $PAT" \
--data '{
  "query": "technology",
  "columns": ["DOCUMENT_CONTENTS", "LIKES"],
  "filter": {"@eq": {"REGION": "US"}},
  "limit": 5
}'

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'business_documents_css',
      '{
         "query": "technology",
         "columns": ["DOCUMENT_CONTENTS", "LIKES"],
         "filter": {"@eq": {"REGION": "US"}},
         "limit": 5
      }'
  )
)['results'] as results;

resp = business_documents_css.search(
    query="business",
    columns=["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
    filter={"@and": [
        {"@gte": {"LIKES": 50}},
        {"@lte": {"COMMENTS": 50}}
    ]},
    limit=10
)

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 $PAT" \
--data '{
  "query": "business",
  "columns": ["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
  "filter": {"@and": [
    {"@gte": {"LIKES": 50}},
    {"@lte": {"COMMENTS": 50}}
  ]},
  "limit": 10
}'

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'business_documents_css',
      '{
         "query": "business",
         "columns": ["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
         "filter": {"@and": [
           {"@gte": {"LIKES": 50}},
           {"@lte": {"COMMENTS": 50}}
         ]},
         "limit": 10
      }'
  )
)['results'] as results;

Scoring examples¶

resp = business_documents_css.search(
    query="technology",
    columns=["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
    scoring_config={
        "functions": {
            "numeric_boosts": [
                {"column": "comments", "weight": 2},
                {"column": "likes", "weight": 1}
            ]
        }
    }
)

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 $PAT" \
--data '{
  "query": "technology",
  "columns": ["DOCUMENT_CONTENTS", "LIKES", "COMMENTS"],
  "scoring_config": {
    "functions": {
      "numeric_boosts": [
        {"column": "comments", "weight": 2},
        {"column": "likes", "weight": 1}
      ]
    }
  }
}'

SELECT 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'] as results;

resp = business_documents_css.search(
    query="technology",
    columns=["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP"],
    scoring_config={
        "functions": {
            "time_decays": [
                {"column": "LAST_MODIFIED_TIMESTAMP", "weight": 1, "limit_hours": 240, "now": "2024-04-23T00:00:00.000-08:00"}
            ]
        }
    }
)

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 $PAT" \
--data '{
  "query": "technology",
  "columns": ["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP"],
  "scoring_config": {
    "functions": {
      "time_decays": [
        {"column": "LAST_MODIFIED_TIMESTAMP", "weight": 1, "limit_hours": 240, "now": "2024-04-23T00:00:00.000-08:00"}
      ]
    }
  }
}'

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

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

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 $PAT" \
--data '{
  "query": "technology",
  "columns": ["DOCUMENT_CONTENTS", "LAST_MODIFIED_TIMESTAMP"],
  "scoring_config": {
    "reranker": "none"
  }
}'

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