CREATE CORTEX SEARCH SERVICE¶
Creates a new Cortex Search service or replaces an existing one.
Syntax¶
CREATE [ OR REPLACE ] CORTEX SEARCH SERVICE [ IF NOT EXISTS ] <name>
ON <search_column>
ATTRIBUTES <col_name> [ , ... ]
WAREHOUSE = <warehouse_name>
TARGET_LAG = '<num> { seconds | minutes | hours | days }'
COMMENT = '<comment>'
AS <query>;
Parameters¶
name
String that specifies the identifier (i.e. name) for the Cortex Search service; must be unique for the schema in which the service is created.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example,
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more information, see Identifier requirements.
ON search_column
Specifies the text column in the base table that you wish to search on. This column must be a text value.
ATTRIBUTES col_name [ , ... ]
Specifies comma-separated list of columns in the base table that you wish to filter on when issuing queries to the service.
WAREHOUSE = warehouse_name
Specifies the warehouse to use for running the source query, building the search index, and keeping it refreshed per the TARGET_LAG target.
TARGET_LAG = 'num { seconds | minutes | hours | days }'
Specifies the maximum amount of time that the Cortex Search service content should lag behind updates to the base tables specified in the source query.
COMMENT = 'comment'
Specifies a comment for the service.
AS query
Specifies a query defining the base table from which the service is created.
Access Control Requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
---|---|
CREATE CORTEX SEARCH SERVICE |
Schema in which you plan to create the search service. |
SELECT |
Tables, and views that you plan to query for the new service. |
USAGE |
Warehouse that you plan to use to refresh the service. |
Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Attention
To create a Cortex Search Service, your role must have the same privileges required to use Cortex LLM Functions. See Cortex LLM Functions Required Privileges.
Usage Notes¶
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
The size of the Warehouse used to run the Cortex Search service source query does impact the speed and cost of each refresh. A larger warehouse decreases build and refresh time. However, during this preview, Snowflake recommends using a warehouse size no larger than MEDIUM for Cortex Search services.
Snowflake recommends using a dedicated warehouse for each Cortex Search service so as to not interfere with other workloads.
The search index is built as part of the create statement, which means the CREATE CORTEX SEARCH SERVICE statement may take longer to complete for larger datasets.
Example¶
The following example creates a Cortex Search service named mysvc
:
USE SCHEMA support_db.public;
CREATE OR REPLACE CORTEX SEARCH SERVICE mysvc
ON transcript_text
ATTRIBUTES region,agent_id
WAREHOUSE = mywh
TARGET_LAG = '1 hour'
AS (
SELECT
transcript_text,
date,
region,
agent_id
FROM support_db.public.transcripts_etl
);