The ML_RAG_TABLE
routine runs multiple
retrieval-augmented generation (RAG) queries in a batch, in
parallel. The output generated for every input query is the same
as the output generated by the ML_RAG
routine.
To alter an existing table or create a new table, MySQL
requires you to set the
sql-require-primary-key
system variable to 0
.
This routine is available in MySQL 9.0.1-u1 and later versions.
To learn about the privileges you need to run this routine, see Required Privileges.
mysql> call sys.ML_RAG_TABLE('InputTableColumn', 'OutputTableColumn', [options]);
options: {
JSON_OBJECT('key','value'[,'key','value'] ...)
'key','value': {
['vector_store', JSON_ARRAY('VectorStoreTableName1'[,'VectorStoreTableName2'] ...)]
['schema', JSON_ARRAY('Schema1'[,'Schema2'] ...)]
['n_citations', NumberOfCitations]
['distance_metric', {'COSINE'|'DOT'|'EUCLIDEAN'}]
['document_name', JSON_ARRAY('DocumentName1'[,'DocumentName2'] ...)]
['skip_generate', {true|false}]
['model_options', JSON_OBJECT('Key1','Value1'[,'Key2','Value2'] ...)]
['exclude_vector_store', JSON_ARRAY('ExcludeVectorStoreTableName1'[,'ExcludeVectorStoreTableName2'] ...)]
['exclude_document_name', JSON_ARRAY('ExcludeDocumentName1'[,'ExcludeDocumentName2'] ...)]
['batch_size', BatchSize]
}
}
Following are ML_RAG_TABLE
parameters:
-
InputTableColumn
: specifies the names of the input database, table, and column that contains the natural-language queries. TheInputTableColumn
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
orvarchar
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
, orColumnName
and there must be no period used in theDBName
orTableName
.
-
OutputTableColumn
: specifies the names of the database, table, and column where the generated text-based response is stored. TheOutputTableColumn
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
, orColumnName
and there must be no period used in theDBName
orTableName
.
-
options
: specifies optional parameters as key-value pairs in JSON format. It can include the following parameters:vector_store
: specifies a list of loaded vector store tables to use for context retrieval. The routine ignores invalid table names. By default, the routine performs a global search across all the available vector store tables in the DB system.schema
: specifies a list of schemas to check for loaded vector store tables. By default, the routine performs a global search across all the available vector store tables in all the schemas that are available in the DB system.n_citations
: specifies the number of segments to consider for context retrieval. Default value is3
. Possible values are integer values between0
and100
.distance_metric
: specifies the distance metrics to use for context retrieval. Default value isCOSINE
. Possible values areCOSINE
,DOT
, andEUCLIDEAN
.document_name
: limits the documents to use for context retrieval. Only the specified documents are used. By default, the routine performs a global search across all the available documents stored in all the available vector stores in the DB system.skip_generate
: specifies whether to skip generation of the text-based response, and only perform context retrieval from the available or specified vector stores, schemas, or documents. Default value isfalse
.model_options
: additional options that you can set for generating the text-based response. These are the same options that are available in theML_GENERATE
routine, which alter the text-based response per the specified settings. However, thecontext
option is not supported as anML_RAG_TABLE
model option. Default value is'{"model_id": "mistral-7b-instruct-v1"}'
.exclude_vector_store
: specifies a list of loaded vector store tables to exclude from context retrieval. The routine ignores invalid table names. Default value isNULL
.exclude_document_name
: specifies a list of documents to exclude from context retrieval. Default value isNULL
.batch_size
: specifies the batch size for the routine. This parameter is supported for internal tables only. Default value is1000
. Possible values are integer values between1
and1000
.-
retrieval_options
: specifies optional context retrieval parameters as key-value pairs in JSON format. If a parameter value inretrieval_options
is set toauto
, the default value for that parameter is used.The
retrieval_options
parameters are available in MySQL 9.1.2 and later versions.It can include the following parameters:
-
max_distance
: specifies a maximum distance threshold for filtering out segments from context retrieval. Segments for which the distance from the input query exceeds the specified maximum distance threshold are excluded from content retrieval. This ensures that only the segments that are closer to the input query are included during context retrieval. However, if no segments are found within the specified distance, the routine generates an output without using any context.NoteIf this parameter is set, the default value of the
n_citations
parameter is automatically updated to10
.Default value is
0.6
for all distance metrics.Possible values are decimal values between
0
and999999.9999
. -
percentage_distance
: specifies what percentage of distance to the nearest segment is to be used to determine the maximum distance threshold for filtering out segments from context retrieval.Following is the formula used for calculating the maximum distance threshold:
MaximumDistanceThreshold
=DistanceOfInputQueryToNearestSegment
+ [(percentage_distance
/100
) *DistanceOfInputQueryToNearestSegment
]Which means that the segments for which the distance to the input query exceeds the distance of the input query to the nearest segment by the specified percentage are filtered out from context retrieval.
NoteIf this parameter is set, the default value of the
n_citations
parameter is automatically updated to10
.Default value is
20
for all distance metrics.Possible values are decimal values between
0
and999999.9999
.NoteIf both
max_distance
andpercentage_distance
are set, the smaller threshold value is considered for filtering out the segments. segment_overlap
: specifies the number of additional segments adjacent to the nearest segments to the input query to be included in context retrieval. These additional segments provide more continuous context for the input query. Default value is1
. Possible values are integer values between0
and5
.
-
Running retrieval-augmented generation in a batch of 10:
mysql> call sys.ML_RAG_TABLE("demo_db.input_table.Input", "demo_db.output_table.Output", JSON_OBJECT("vector_store", JSON_ARRAY("demo_db.demo_embeddings"), "model_options", JSON_OBJECT("language", "en"), "batch_size", 10));
In this example, the routine performs RAG for 10 input queries
stored in the demo_db.input_table.Input
column, and creates a column of 10 rows
demo_db.output_table.Output
where it stores
the generated outputs.