MySQL AI User Guide
The ML_GENERATE_TABLE routine runs multiple
text generation or summarization queries in a batch, in
parallel. The output generated for every input query is the same
as the output generated by the
ML_GENERATE
routine.
This topic contains the following sections:
To learn about the privileges you need to run this routine, see Section 5.3, “Required Privileges for using GenAI”.
mysql>CALL sys.ML_GENERATE_TABLE('InputTableColumn', 'OutputTableColumn'[,options]);options: JSON_OBJECT(keyvalue[,keyvalue]...)keyvalue: { 'task', {'generation'|'summarization'} |'model_id', 'LargeLanguageModelID' |'context_column', 'ContextColumn' |'language', 'Language' |'temperature',Temperature|'max_tokens',MaxTokens|'top_k',K|'top_p',P|'repeat_penalty',RepeatPenalty|'frequency_penalty',FrequencyPenalty|'stop_sequences', JSON_ARRAY('StopSequence'[, 'StopSequence'] ...) |'batch_size',BatchSize|'speculative_decoding', {true|false} }
Following are ML_GENERATE_TABLE parameters:
InputTableColumn: specifies the
names of the input database, table, and column that
contains the natural-language queries. The
InputTableColumn is specified
in the following format:
DBName.TableName.ColumnName.
The specified input table can be an internal or external table.
The specified input table must already exist, must not be empty, and must have a primary key.
The input column must already exist and must contain
text or varchar
values.
The input column must not be a part of the primary key
and must not have NULL values or
empty strings.
There must be no backticks used in the
DBName,
TableName, or
ColumnName and there must
be no period used in the
DBName or
TableName.
OutputTableColumn: specifies
the names of the database, table, and column where the
generated text-based response is stored. The
OutputTableColumn is specified
in the following format:
DBName.TableName.ColumnName.
The specified output table must be an internal table.
If the specified output table already exists, then it must be the same as the input table. And, the specified output column must not already exist in the input table. A new JSON column is added to the table. External tables are read only. So if input table is an external table, then it cannot be used to store the output.
If the specified output table doesn't exist, then a new table is created. The new output table has key columns which contains the same primary key values as the input table and a JSON column that stores the generated text-based responses.
There must be no backticks used in the
DBName,
TableName, or
ColumnName and there must
be no period used in the
DBName or
TableName.
options: specifies optional parameters
as key-value pairs in JSON format. It can include the
following parameters:
task: specifies the task expected
from the large language model (LLM). Default value is
generation. Possible values are:
generation: generates
text-based content.
summarization: generates a
summary for existing text-based content.
model_id: specifies the LLM to use
for the task. Default and possible value is
llama3.2-3b-instruct-v1.
To view the lists of available LLMs, see In-Database LLM.
context_column: specifies the table
column that contains the context to be used for
augmenting the queries and guiding the text generation
of the LLM. The specified column must be an existing
column in the input table. Default value is
NULL.
language: specifies the language to
be used for writing queries, ingesting documents, and
generating the output. To set the value of the
language parameter, use the
two-letter ISO 639-1 code for the
language.
Default value is en.
For possible values, to view the list of supported languages, see Section 5.4, “Supported LLM, Embedding Model, and Languages”.
temperature: specifies a
non-negative float that tunes the degree of randomness
in generation. Lower temperatures mean less random
generations.
Default value is 0 for all LLMs.
Possible values are float values between
0 and 5 For the
In-Database LLM.
It is suggested that:
To generate the same output for a particular
prompt every time you run it, set the temperature
to 0.
To generate a random new statement for a particular prompt every time you run it, increase the temperature.
max_tokens: specifies the maximum
number of tokens to predict per generation using an
estimate of three tokens per word. Default value is
256. Possible values are integer
values between1 and
4096.
top_k: specifies the number of top
most likely tokens to consider for text generation at
each step. Default value is 40,
which means that top 40 most likely tokens are
considered for text generation at each step. Possible
values are integer values between 0
and 32000.
top_p: specifies a number,
p, and ensures that only the most
likely tokens with the sum of probabilities
p are considered for generation at
each step. A higher value of p
introduces more randomness into the output. Default
value is 0.95. Possible values are
float values between 0 and
1.
To disable this method, set to
1.0 or 0.
To eliminate tokens with low likelihood, assign
p a lower value. For example,
if set to 0.1, tokens within
top 10% probability are included.
To include tokens with low likelihood, assign
p a higher value. For example,
if set to 0.9, tokens within
top 90% probability are included.
If you are also specifying the
top_k parameter, the LLM considers
only the top tokens whose probabilities add up to
p percent. It ignores the rest of
the k tokens.
repeat_penalty: assigns a penalty
when a token appears repeatedly. High penalties
encourage less repeated tokens and produce more random
outputs. Default value is 1.1.
Possible values are float values between
0 and 2.
frequency_penalty: assigns a
penalty when a token appears frequently. High
penalties encourage less repeated tokens and produce
more random outputs. Default value is
0. Possible values are float values
between 0 and 1.
stop_sequences: specifies a list of
characters such as a word, a phrase, a newline, or a
period that tells the LLM when to end the generated
output. If you have more than one stop sequence, then
the LLM stops when it reaches any of those sequences.
Default value is NULL.
batch_size: specifies the batch
size for the routine. This option is supported for
internal tables only. Default value is
1000. Possible values are integer
values between 1 and
1000.
Generate English text-based content in a batch using the
llama3.2-3b-instruct-v1 model for queries
stored in demo_db.input_table:
mysql> CALL sys.ML_GENERATE_TABLE("demo_db.input_table.Input", "demo_db.output_table.Output", JSON_OBJECT("task", "generation", "model_id", "llama3.2-3b-instruct-v1", "language", "en"));