Text generation AI functions

This topic describes the text generation AI functions supported by seekdb.

AI functions integrate AI model capabilities directly into data processing within the database through SQL expressions. This greatly simplifies operations such as data extraction, analysis, summarization, and saving using large AI models. It is a significant new feature in the field of databases and data warehouses. seekdb provides AI model and endpoint management through the DBMS_AI_SERVICE package, introduces several built-in AI function expressions, and supports monitoring AI model calls through views.

Prerequisites

• Before you use the text generation AI function, make sure that you have the required permissions. For more information, see AI function service permissions.
• Before you use the text generation AI function, make sure that you have registered an AI model and endpoint. For more information, see Manage AI models.

AI_COMPLETE and AI_PROMPT

The AI_COMPLETE function specifies a registered large language model (LLM) by using the model_key parameter, processes the prompt and data provided by you, and returns the generated text. You can customize the prompt and data format in the prompt parameter. This way, you can process text data in various ways and batch process data in the database, thus avoiding the overhead of copying data between the database and the LLM.

In many AI applications, prompts are highly structured and require dynamic injection of specific data. Manually using functions such as CONCAT to concatenate prompts and input content is not only costly in terms of development but also prone to errors that can lead to formatting issues. To address the need for prompt reuse and dynamic combination of prompts with data, seekdb provides the AI_PROMPT function. The AI_PROMPT function upgrades prompts from static text to reusable, parameterizable function templates. These templates can be used directly in the AI_COMPLETE function, significantly simplifying the prompt construction process and improving development efficiency and accuracy.

AI_PROMPT function

The AI_PROMPT function is used to build and format prompts, supporting dynamic data insertion.

Syntax

The syntax of the AI_PROMPT function is as follows:

AI_PROMPT('template', expr0 [ , expr1, ... ]);

Parameters:

Parameter	Description	Type	Nullable
template	The prompt template provided by you.	VARCHAR(max_length)	No
expr	The data provided by you.	VARCHAR(max_length)	No

Both the template and expr parameters are required and cannot be empty. The expr parameter only supports the VARCHAR type and does not support the JSON type.

Return value:

• JSON, the formatted prompt string.

Examples

The AI_PROMPT function organizes the template string and dynamic data into a JSON format:

• The first parameter (template string) is placed in the template field of the returned JSON.
• Subsequent parameters (data values expr0, expr1, etc.) are placed in the args array of the returned JSON.
• Placeholders such as {0} and {1} in the template correspond to the data in the args array by index and are automatically replaced when used in the AI_COMPLETE function.

For example:

SELECT AI_PROMPT('Recommend {0} of the most popular {1} to me.', 'ten', 'mobile phones');

The result is as follows:

{
  "template": "Recommend {0} of the most popular {1} to me.",
  "args": ["ten", "mobile phones"]
}

In the previous example, the AI_PROMPT function is used in the AI_COMPLETE function:

SELECT AI_COMPLETE("ob_complete",AI_PROMPT('Recommend {0} of the most popular {1} to me.just output name in json array format', 'two', 'mobile phones')) AS ans;

The result is as follows:

+--------------------------------------------------+
| ans                                              |
+--------------------------------------------------+
| ["iPhone 15 Pro Max","Samsung Galaxy S24 Ultra"] |
+--------------------------------------------------+

AI_COMPLETE function

Syntax

The syntax of the AI_COMPLETE function is as follows:

AI_COMPLETE(model_key, prompt[, parameters])
--If you use the AI_PROMPT function, replace the prompt parameter with the AI_PROMPT function. For an example, see the AI_PROMPT function.
AI_COMPLETE(model_key, AI_PROMPT(prompt_template, data))

Parameters:

Parameter	Description	Type	Nullable
model_key	The registered model in the database.	VARCHAR(128)	No
prompt	The prompt provided by you.	VARCHAR/TEXT(LONGTEXT)	No
parameters	Optional parameters provided by the API. These optional fields of the model are directly included in the generated message body, which may vary by vendor. Common optional parameters include temperature, top_p, and max_tokens. Generally, you do not need to specify these parameters; the default settings are used.	JSON	Yes

If you do not specify the model_key and prompt parameters, or if one of them is NULL, an error is returned.

Return value:

• text, the text generated by the LLM based on the prompt.

Examples

1. Sentiment analysis example

   SELECT AI_COMPLETE("ob_complete","Your task is to perform sentiment analysis on the provided text and determine whether the sentiment is positive or negative.
   The following text needs to be analyzed:
   <text>
   The weather is really nice.
   </text>
   The judgment criteria are as follows:
   If the text expresses a positive sentiment, output 1; if the text expresses a negative sentiment, output -1. Do not output anything else.\n") AS ans;

   The result is as follows:

   +-----+
   | ans |
   +-----+
   | 1   |
   +-----+

2. Translation example

   CREATE TABLE comments (
       id INT AUTO_INCREMENT PRIMARY KEY,
       content TEXT
   );
   
   INSERT INTO comments (content) VALUES ('hello world!');
   
   -- Replace the data to be processed with the column names of the table in the database using a concat expression. This way, you can batch process data in the database without copying data from the database to the LLM and then back to the database.
   SELECT AI_COMPLETE("ob_complete",
   concat("You are a translation master. You need to translate the following English text into Chinese. The text to be translated is:<text>",
       content,
       "</text>")) AS ans FROM comments;

   The result is as follows:

   +-------------+
   | ans         |
   +-------------+
   | Hello, world!  |
   +-------------+

3. Classification example

   SELECT AI_COMPLETE("ob_complete","You are a classification master. You will receive a set of question texts and need to classify them into one of the following categories: [\"Hardware Department\",\"Software Department\",\"Other\"]. The text to be analyzed is:
   <text>
   The screen quality is really poor.
   </text>") AS res;

   The result is as follows:

   +--------+
   | res    |
   +--------+
   | Hardware Department |
   +--------+

References

• Use the AI_EMBED function
• Re-ranking search