AI_FILTER¶

区域可用性¶

下表显示了可以在其中使用 AI_FILTER 函数来处理文本和图像的区域：

语法¶

将 AI_FILTER 应用于输入字符串：

AI_FILTER( <input> [, <return_error_details>] )

将 AI_FILTER 应用于单张图像：

AI_FILTER( <predicate> , <input> [, <return_error_details>] )

将 AI_FILTER 应用于包含文本和图像的多列，利用 PROMPT：

AI_FILTER( PROMPT('<template_string>',  <col_1>, … ) [, <return_error_details>] )

实参¶

必填：

如果您要指定输入字符串：

input

包含要分类的文本的字符串。

如果您要筛选一个文件：

predicate

一个字符串，其中包含将文件输入分类为 TRUE 或 FALSE 的指令。

file

根据 predicate 中指定的指令对文件进行分类的列。您可以用 IMAGE FILE 作为 AI_FILTER 函数的输入。

如果您使用 PROMPT() 函数来格式化输入：

对于更复杂的提示，尤其是具有多个文件列的提示，您可以使用 PROMPT 来帮助创建 input。

PROMPT() 函数支持跨字符串和 FILE 数据类型进行格式化。有关详细用法，请参阅 示例。

Optional:

return_error_details

A boolean flag that indicates whether to return error details for rows with non-fatal errors. When set to TRUE, the function returns a JSON object with the response value and error message. To return error details, you must also set the AI_SQL_ERROR_HANDLING_USE_FAIL_ON_ERROR session parameter to FALSE.

Default: FALSE

访问控制要求¶

Users must use a role that has been granted the SNOWFLAKE.CORTEX_USER database role. See Cortex LLM privileges for more information on this privilege.

Error handling¶

By default, an AI_FILTER terminates the query when it encounters a non-fatal error on a specific row. This behavior can disrupt large batch operations where an error can stop all processing. For rows with non-fatal errors, you can enable error handling during your session. When you have error handling enabled, AI_FILTER returns NULL for rows that failed, allowing the rest of the query to complete successfully.

Use the following code to enable error handling for your session:

ALTER SESSION SET AI_SQL_ERROR_HANDLING_USE_FAIL_ON_ERROR=false;

返回¶

When return_error_details is set to TRUE and AI_SQL_ERROR_HANDLING_USE_FAIL_ON_ERROR is set to FALSE, returns a JSON object containing the following keys:

• "value": A JSON object containing the classification results. NULL if there's an error.

• "error": A string containing the error details. NULL if no error occurred.

When return_error_details is set to FALSE and AI_SQL_ERROR_HANDLING_USE_FAIL_ON_ERROR is set to FALSE, returns NULL for columns with errors.

性能和成本优化¶

默认情况下，AI_FILTER 会对符合条件的查询应用内置的性能优化。该优化可使性能提升 2 到 10 倍，使令牌使用量最多降低 60%，同时对质量的影响极小。

当查询引擎检测到合适的模式时，此优化会自动触发。与其他查询优化类似，Snowflake 不保证此优化将应用于每个查询。该引擎利用自适应路由和上下文感知重写，在可能的情况下执行更高效的 AI 操作。

要为您的账户禁用此优化，请联系您的客户经理。

使用说明¶

为了获得最佳性能，请遵循以下指导原则：

• 确保发送到 AI_FILTER 的列不包含 NULL 值。

• 使用英语纯文本作为输入字符串或 PROMPT() 实参。

• 请提供输入文本说明的详细信息。例如，不使用“听起来很满意”之类的陈述，而是使用“在以下支持记录中，客户听起来很满意”。

• 考虑以问题的形式来表述输入内容。例如，“在以下支持记录中，客户听起来满意吗？”

示例¶

SELECT AI_FILTER('Is Canada in North America?');

TRUE

WITH reviews AS (
            SELECT 'Wow... Loved this place.' AS review
  UNION ALL SELECT 'The pizza is not good.'
)
SELECT * FROM reviews
WHERE AI_FILTER(CONCAT('The reviewer enjoyed the restaurant: ', review));

WITH reviews AS (

SELECT 'Wow... Loved this place.' AS review
UNION ALL SELECT 'The pizza is not good.'
)
SELECT * FROM reviews
WHERE AI_FILTER(PROMPT('The reviewer enjoyed the restaurant: {0}', review));

+--------------------------+
| REVIEW                   |
|--------------------------+
| Wow... Loved this place. |
+--------------------------+

WITH country AS (
          SELECT 'Switzerland' AS country,
UNION ALL SELECT 'Korea'
),
region AS (
            SELECT 'Asia' AS region,
  UNION ALL SELECT 'Europe'
)
SELECT country,
      region,
      AI_FILTER(PROMPT('{0} is in {1}', country, region)) AS result
FROM country CROSS JOIN region ;

+-------------+-------+--------+
| COUNTRY     |REGION | RESULT |
|-------------+-------+--------+
| Switzerland |Europe | TRUE   |
|-------------+-------+--------+
| Switzerland | Asia  | FALSE  |
|-------------+-------+--------+
| Korea       |Europe | FALSE  |
+-------------+-------+--------+
| Korea       | Asia  | TRUE   |
+-------------+-------+--------+

SELECT *
FROM RESUMES
JOIN JOBS
ON AI_FILTER(PROMPT('Evaluate if this resume {0} fits this job description {1}', RESUME.contents, JOBS.jd));

WITH pictures AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT
FL_GET_RELATIVE_PATH(img) AS file_path FROM pictures
WHERE AI_FILTER('Is this a picture of a cat?', img);

WITH pictures AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT
    FL_GET_RELATIVE_PATH(img) AS file_path FROM pictures
WHERE AI_FILTER(PROMPT('{0} is a cat picture', img));

+--------------------------+
|        FILE_PATH         |
|--------------------------+
|        2cats.jpg         |
+--------------------------+
|        cat1.png          |
+--------------------------+
|      orange_cat.jpg      |
+--------------------------+

WITH images AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT AI_FILTER(PROMPT('Is this the photo {0} about food', img))
FROM reviews
LIMIT 3;

true
false
null

ALTER SESSION SET AI_SQL_ERROR_HANDLING_USE_FAIL_ON_ERROR=false;

WITH images AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT AI_FILTER(
  prompt => PROMPT('Is this the photo {0} about food', img),
  return_error_details => TRUE
)
FROM reviews
LIMIT 3;

{ "value": true, "error": null }
{ "value": false, "error": null }
{ "value": null, "error": "invalid image file"}

限制¶

• Snowflake AI 函数不适用于通过以下暂存区类型的暂存区文件创建的 FILEs：

  • 具有加密模式 TYPE = 'SNOWFLAKE_FULL' 的内部暂存区

  • 使用任何客户端加密模式的外部暂存区：

    • TYPE = 'AWS_CSE'

    • TYPE = 'AZURE_CSE'

  • 用户暂存区、表暂存区

  • 带有双引号名称的暂存区