HeatWave User Guide  /  ...  /  HEATWAVE_CHAT

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

HEATWAVE_CHAT Syntax

Press CTRL+C to copy
 
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 behaviour. 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:

      • 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 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 in MySQL 9.2.1 and later versions.

    • embed_model_id: specifies the embedding model to use for embedding the input query. If you are providing the query embedding, then set this parameter to specify the embedding model to use to embed the query. The routine uses vector store tables and embedding tables created using the same embedding model for context retrieval. 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.

      This parameter is available in MySQL 9.2.1 and later versions.

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

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

      Press CTRL+C to copy
       
      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 loaded to HeatWave, 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: 

      Press CTRL+C to copy
       
      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": "mistral-7b-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: 

    Press CTRL+C to copy
     
    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: 

      Press CTRL+C to copy
       
      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: 

      Press CTRL+C to copy
       
      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: 

      Press CTRL+C to copy
       
      set @chat_options = json_set(@chat_options, '$.model_options.temperature', 0.5);

  • Viewing the chat parameters and session details: 

    Press CTRL+C to copy
     
    select JSON_PRETTY(@chat_options);

    For more information about the output generated by this command, see Section 4.7.2, “Viewing Chat Session Details”.


PREV   HOME   UP   NEXT
Related Documentation
HeatWave Release Notes
Download this Manual
PDF (US Ltr) - 2.2Mb
PDF (A4) - 2.2Mb