`ai_generate_text` function

Applies to: check marked yes Databricks SQL

Preview

Returns text generated by a selected large language model (LLM) given the prompt.

Requirements

This function is only available on Databricks SQL Pro and Serverless.
This feature is in public preview. To enroll in the public preview, please populate and submit the AI Functions Public Preview enrollment form.

Syntax

ai_generate_text(prompt, modelName[, param1, value1] [...])

Arguments

prompt: A string expression, the text prompt that is passed to the selected LLM.
modelName: A STRING literal, only 'openai/gpt-3.5-turbo' and 'azure_openai/gpt-35-turbo' are supported.
paramN and valueN: key-value pairs to authenticate and configure the selected LLM. The keys must be string literals and are case-sensitive. The types of the values depend on the following keys:
- Model 'openai/gpt-3.5-turbo' uses the chat completion API from Open AI. It supports the following parameters:
  - 'apiKey': Required. The OpenAI API key to access the model endpoint. The value specified can not be an explicit constant string. Recommended value includes secret(scope, key) function and SELECT ... scalar subquery.
  - 'temperature': The sampling temperature to use. Its value is a numeric literal between 0 and 2. The default value is 1.0.
  - stop: Stop strings. Its value is a STRING literal or an ARRAY<STRING> of up to 4 string literals. The default value is null.
- Model 'azure_openai/gpt-35-turbo' uses the chat completion API from the Azure OpenAI Service. It accepts all parameters from the above 'openai/gpt-3.5-turbo' model and any additional parameters to construct the endpoint URL. Databricks only supports API Key authentication.
  - 'resourceName': Required. Its value is a string literal to specify the resource name.
  - 'deploymentName': Required. Its value is a string literal to specify the deployment name.
  - 'apiVersion': Required. Its value is a string literal to specify the API version to use.

Returns

A string expression representing the text regenerated from the selected LLM.

Examples

See Analyze customer reviews with ai_generate_text() and OpenAI for an example use case of the ai_generate_text function.

> SELECT ai_generate_text('Hello', 'openai/gpt-3.5-turbo',
    'apiKey', secret('ml', 'key'),
    'temperature', 0.1);

  Hello! How can I assist you today?

> SELECT ai_generate_text('Hello',
    'azure_openai/gpt-35-turbo',
    'apiKey', secret('ml', 'key'),
    'resouceName', 'resource',
    'deploymentName', 'deploy',
    'apiVersion', '2023-03-15-preview',
    'temperature', 0.1);

  Hello! How can I assist you today?

> SELECT ai_generate_text('Hello', 'openai/gpt-3.5-turbo',
    'apiKey', (SELECT secret FROM secrets.open_ai_tokens LIMIT 1),
    'temperature', 0.1);

  Hello! How can I assist you today?

> CREATE FUNCTION summarize(text STRING)
  RETURNS STRING
  RETURN AI_GENERATE_TEXT(
    CONCAT('Summarize the following text: ',
      text),
    'openai/gpt-3.5-turbo',
    'apiKey', (SELECT secret FROM secrets.open_ai_tokens LIMIT 1),
    'temperature', 0.1
  );

  SELECT summarize('This is the text to be summarized.')

  This is the summarization.

> SELECT ai_generate_text('Hello',
    'openai/gpt-3.5-turbo',
    'apiKey', 'sg-xxxxxxxxxxxxxxxxxxxxxx',
    'temperature', 0.1);

 Error: DATATYPE_MISMATCH.INVALID_SECRET
 The parameter value of the "apiKey" argument to the ai_generate_text function can not be a constant 'sg-xxxxxxxxxxxxxxxxxxxxxx'. Recommended usages include `secret(scope, key)` function or a `SELECT ...` subquery.

ai_generate_text function