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

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.

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

    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:

  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:

    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:

    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:

    set @chat_options = json_set(@chat_options, '$.model_options.temperature', 0.5);

• Viewing the chat parameters and session details:

  select JSON_PRETTY(@chat_options);

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