4.7.5 ML_RAG_TABLE

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.

Note

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.

ML_RAG_TABLE Syntax

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. 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:

  • 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 is 3. Possible values are integer values between 0 and 100.

  • distance_metric: specifies the distance metrics to use for context retrieval. Default value is COSINE. Possible values are COSINE, DOT, and EUCLIDEAN.

  • 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 is false.

  • model_options: additional options that you can set for generating the text-based response. These are the same options that are available in the ML_GENERATE routine, which alter the text-based response per the specified settings. However, the context option is not supported as an ML_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 is NULL.

  • exclude_document_name: specifies a list of documents to exclude from context retrieval. Default value is NULL.

  • batch_size: specifies the batch size for the routine. This parameter is supported for internal tables only. Default value is 1000. Possible values are integer values between 1 and 1000.

Syntax Examples

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.