`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 function is deprecated. Databricks recommends ai_query with external models.

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