MySQL AI User Guide

7.2.6 HEATWAVE_CHAT

The HEATWAVE_CHAT routine automatically calls the ML_RAG routine which loads an LLM and runs a semantic search on the available vector stores by default. If the routine cannot find a vector store, then it calls the ML_GENERATE routine and uses information available in LLM training data, which is primarily information that is available in public data sources, to generate a response for the entered query.

This topic contains the following sections:

HEATWAVE_CHAT Syntax

mysql> CALL sys.HEATWAVE_CHAT('QueryInNaturalLanguage');

The HEATWAVE_CHAT routine accepts one input parameter:

  • QueryInNaturalLanguage: specifies the query in natural language.

For specifying additional chat parameter settings, the HEATWAVE_CHAT routine reserves a variable, @chat_options. When you run the routine, it also updates the @chat_options variable with any additional information that is used or collected by the routine to generate the response.

@chat_options Parameters

Following is a list of all the parameters that you can set in the @chat_options variable:

  • Input only: you can set these parameters to control the chat behavior. The routine cannot change the values of these parameters.

    • schema_name: specifies the name of a schema. If set, the routine searches for vector store tables in this schema. This parameter cannot be used in combination with the tables parameter. Default value is NULL

    • report_progress: specifies whether information such as routine progress detail is to be reported. Default value is false.

    • skip_generate: specifies whether response generation is skipped. If set to true, the routine does not generate a response. Default value is false.

    • return_prompt: specifies whether to return the prompt that was passed to the ML_RAG or ML_GENERATE routines. Default value is false.

    • re_run: if set to true, it indicates that the request is a re-run of the previous request. For example, a re-run of a query with some different parameters. The new query and response replaces the last entry stored in the chat_history parameter. Default value is false.

    • include_document_uris: limits the documents used for context retrieval by including only the specified document URIs. Default value is NULL.

    • retrieve_top_k: specifies the context size. The default value is the value of the n_citations parameter of the ML_RAG routine. Possible values are integer values between 0 and 100.

    • chat_query_id: specifies the chat query ID to be printed with the chat_history in the GUI. This parameter is reserved for GUI use. By default, the routine generates random IDs.

    • history_length: specifies the maximum history length, which is the number of question and answers, to include in the chat history. The specified value must be greater than or equal to 0. Default value is 3.

    • vector_store_columns: optional parameter which 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: 

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

    • embed_model_id: specifies the embedding model to use for embedding the input query. The routine uses vector store tables and embedding tables created using the same embedding model for context retrieval. Default value is multilingual-e5-small.

      To view the list of available embedding models, see In-Database Embedding Model.

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

      It can include the following parameters: 

      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 fails to run.

        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.

  • Input-output: both you and the routine can change the values of these parameters.

    • chat_history: JSON array that represents the current chat history. Default value is NULL.

      Syntax for each object in the chat_history array is as follows: 

      JSON_OBJECT('key','value'[,'key','value'] ...)
  'key','value': {
  ['user_message','Message']
  ['chat_bot_message','Message']
  ['chat_query_id','ID']
}

      Each parameter value in the array holds the following keys and their values:

      • user_message: message entered by the user.

      • chat_bot_message: message generated by the chat bot.

      • chat_query_id: a query ID.

    • tables: JSON array that represents the following:

      • For providing input, represents the list of vector store schema or table names to consider for context retrieval.

      • As routine output, represents the list of discovered vector store tables, if any. Otherwise, it holds the same values as input.

      Default value is NULL.

      Syntax for each object in the tables array is as follows: 

      JSON_OBJECT('key','value'[,'key','value'] ...)
  'key','value': {
  ['schema_name','SchemaName']
  ['table_name','TableName']
}

      Each parameter values in the array holds the following keys and their values:

      • schema_name: name of the schema.

      • table_name: name of the vector store table.

    • task: specifies the task performed by the LLM. Default value is generation. Possible value is generation.

    • model_options: optional model parameters specified as key-value pairs in JSON format. These are the same options that are available in the ML_GENERATE routine, which alter the text-based response per the specified settings. Default value is '{"model_id": "llama3.2-3b-instruct-v1"}'.

  • Output only: only the routine can set or change values of these parameters.

    • info: contains information messages such as routine progress information. Default value is NULL. This parameter is populated only if report_progress is set to true.

    • error: contains the error message if an error occurred. Default value is NULL.

    • error_code: contains the error code if an error occurred. Default value is NULL.

    • prompt: contains the prompt passed to the ML_RAG or ML_GENERATE routine. Default value is NULL. This parameter is populated only if report_prompt is set to true.

    • documents: contains the names of the documents as well as segments used as context by the LLM for response generation. Default value is NULL.

    • request_completed: set to true when a response is the last response message to a request. Default value is NULL.

    • response: contains the final response from the routine. Default value is NULL.

Syntax Examples

  • Entering a natural-language query using the HEATWAVE_CHAT routine: 

    mysql> CALL sys.HEATWAVE_CHAT("What is Lakehouse?");

  • Modifying chat parameters using the @chat_options variable:

    • Modifying a chat parameter, tables, to specify the vector store table to use for context retrieval in the next chat session: 

      mysql> SET @chat_options = '{"tables": [{"table_name": "demo_embeddings", "schema_name": "demo_db"}]}';

      This example resets the chat session and uses the specified vector store table in the new chat session.

    • Modifying a chat parameter, tables, to specify the vector store table to use for context retrieval in the same chat session: 

      mysql> SET @chat_options = JSON_SET(@chat_options,'$.tables', JSON_ARRAY(JSON_OBJECT("table_name", "demo_embeddings", "schema_name", "demo_db")));

      This example uses the specified vector store table in the ongoing chat session. It does not reset the chat session.

    • Modifying a chat parameter, temperature, without resetting the chat session: 

      mysql> SET @chat_options = json_set(@chat_options, '$.model_options.temperature', 0.5);

  • Viewing the chat parameters and session details: 

    mysql> SELECT JSON_PRETTY(@chat_options);

See Also

MySQL Documentation Library