MySQL HeatWave User Guide

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

In versions older than MySQL 9.2.1, 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 as of MySQL 9.0.1-u1.

This topic contains the following sections:

To learn about the privileges you need to run this routine, see Section 7.3, “MySQL HeatWave GenAI Roles and Privileges”.

ML_RAG_TABLE Syntax

mysql> CALL sys.ML_RAG_TABLE('InputTableColumn', 'OutputTableColumn'[, options]);

options: JSON_OBJECT(keyvalue[, keyvalue]...)
keyvalue: 
{
  'vector_store', JSON_ARRAY('VectorStoreTableName'[, 'VectorStoreTableName']...)
  |'schema', JSON_ARRAY('SchemaName'[, 'SchemaName']...)
  |'n_citations', NumberOfCitations
  |'distance_metric', {'COSINE'|'DOT'|'EUCLIDEAN'}
  |'document_name', JSON_ARRAY('DocumentName'[, 'DocumentName']...)
  |'skip_generate', {true|false}
  |'model_options', modeloptions
  |'exclude_vector_store', JSON_ARRAY('ExcludeVectorStoreTableName'[, 'ExcludeVectorStoreTableName']...)
  |'exclude_document_name', JSON_ARRAY('ExcludeDocumentName'[, 'ExcludeDocumentName']...)
  |'batch_size', BatchSize
  |'retrieval_options', retrievaloptions
  |'vector_store_columns', vscoptions
  |'embed_model_id', 'EmbeddingModelID'
  |'embed_column', 'EmbeddedQueriesColumnName'
  |'fail_on_embedding_error', {true|false}
}

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. As of MySQL 9.3.1, default value is '{"model_id": "llama3.2-3b-instruct-v1"}', in previous versions, 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.

    • retrieval_options: specifies optional context retrieval parameters as key-value pairs in JSON format. If a parameter value in retrieval_options is set to auto, the default value for that parameter is used.

      The retrieval_options parameters are available as of MySQL 9.1.2.

      It can include the following parameters: 

      retrievaloptions: JSON_OBJECT(retrievaloptkeyvalue[, retrievaloptkeyvalue]...)
retrievaloptkeyvalue:
{
  'max_distance', MaxDistance
  |'percentage_distance', PercentageDistance
  |'segment_overlap', SegmentOverlap
}

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

        Note

        If this parameter is set, the default value of the n_citations parameter is automatically updated to 10.

        Default value is 0.6 for all distance metrics.

        Possible values are decimal values between 0 and 999999.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.

        Note

        If this parameter is set, the default value of the n_citations parameter is automatically updated to 10.

        Default value is 20 for all distance metrics.

        Possible values are decimal values between 0 and 999999.9999.

        Note

        If both max_distance and percentage_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 is 1. Possible values are integer values between 0 and 5.

    • vector_store_columns: specifies column names for finding relevant vector and embedding tables for context retrieval as key-value pairs in JSON format. If multiple tables contain columns with the same name and data type, then all such tables are used for context retrieval.

      It can include the following parameters: 

      vscoptions: JSON_OBJECT('segment', 'SegmentColName', 'segment_embedding', 'EmbeddingColName'[, vsckeyvalue]...)
vsckeyvalue:
{
  'document_name', 'DocumentName'
  |'document_id', DocumentID
  |'metadata', 'Metadata'
  |'segment_number', SegmentNumber
}

      • segment: specifies the name of the mandatory string column that contains the text segments. Default value is segment.

      • segment_embedding: specifies the name of the mandatory vector column that contains vector embeddings of the text segments. Default value is segment_embedding.

      • document_name: specifies the name of the optional column that contains the document names. This column can be of any data type supported by MySQL HeatWave. Default value is document_name.

      • document_id: specifies the name of the optional integer column that contains the document IDs. Default value is document_id.

      • metadata: specifies the name of the optional JSON column that contains additional table metadata. Default value is metadata.

      • segment_number: specifies the name of the optional integer column that contains the segment numbers. Default value is segment_number.

      Default value is {"segment": "segment", "segment_embedding": "segment_embedding", "document_id: "document_id", "segment_number": "segment_number", "metadata": "metadata"}, which means that by default, the routine uses the default values of all column names to find relevant tables for context retrieval.

      This parameter is available as of MySQL 9.2.1.

    • embed_model_id: specifies the embedding model to use for embedding the input queries. If you are providing the query embeddings, then set this parameter to specify the embedding model to use to embed the queries. The routine uses vector store tables and embedding tables created using the same embedding model for context retrieval.

      As of MySQL 9.3.0, default value is multilingual-e5-small. In earlier versions of MySQL, default value is all_minilm_l12_v2 if the output language is set to English and multilingual-e5-small if the output language is set to a language other than English.

      To view the list of available embedding models, see MySQL HeatWave In-Database Embedding Models and OCI Generative AI Service Embedding Models.

      As of MySQL 9.4.0, to use an OCI Generative AI service Dedicated AI Cluster model, specify the model endpoint OCID. For more information, see Creating an Endpoint in Generative AI.

      This parameter is available as of MySQL 9.2.1.

    • embed_column: specifies the name of the input table colmn which contains vector embeddings of the input queries. If this parameter is set, then the routine skips generating the vector embeddings of the input queries. Instead, it uses the embeddings stored in this column for context retrieval from valid vector store and embedding tables that contain vector embeddings created using the same embedding model.

      This parameter is available as of MySQL 9.2.1.

    • fail_on_embedding_error: if set to true, stops the batch processing of input queries and throws an error in case an error is encountered for an input row. If set to false, allows the batch processing to partially fail for rows where errors are encountered, and lets the routine continue with processing the other rows. Default value is true.

      This parameter is available as of MySQL 9.3.0.

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.

See Also

MySQL Documentation Library