Custom prompt template

[PradiptaAtByteIQMind-Graph2023]

Hi Team

How can i pass custom prompt template to the SQLAutoVectorQueryEngine.query method to as to llm can understand my database well.

Thanks

[dosubot[bot]]

🤖

Hey again, @PradiptaAtByteIQMind-Graph2023! Nice to see you diving into more features.

To pass a custom prompt template to the SQLAutoVectorQueryEngine.query method, you need to create an instance of the BasePromptTemplate class with your custom prompt template and pass it to the SQLJoinQueryEngine or SQLAugmentQueryTransform during initialization.

Here is an example of how you can do this:

from llama_index.prompts.base import BasePromptTemplate
from llama_index.query_engine.sql_join_query_engine import SQLJoinQueryEngine, SQLAugmentQueryTransform
from llama_index.tools.query_engine import QueryEngineTool

# Define your custom prompt template
custom_prompt_template = """
Your custom prompt template goes here.
"""

# Create an instance of BasePromptTemplate with your custom prompt
custom_prompt = BasePromptTemplate(custom_prompt_template)

# Create an instance of SQLAugmentQueryTransform with your custom prompt
sql_augment_query_transform = SQLAugmentQueryTransform(sql_augment_transform_prompt=custom_prompt)

# Create an instance of SQLJoinQueryEngine with your custom prompt
sql_join_query_engine = SQLJoinQueryEngine(
    sql_query_tool=QueryEngineTool(...),  # Fill in with your SQL query tool
    other_query_tool=QueryEngineTool(...),  # Fill in with your other query tool
    sql_join_synthesis_prompt=custom_prompt,
    sql_augment_query_transform=sql_augment_query_transform,
)

In this example, replace QueryEngineTool(...) with your actual query tools. The ... is a placeholder and needs to be replaced with your actual code.

This will pass your custom prompt template to the SQLJoinQueryEngine and SQLAugmentQueryTransform, which will then be used in the query method.

For more details, you can refer to the source code of the SQLJoinQueryEngine and SQLAugmentQueryTransform classes in the LlamaIndex repository.

Regarding the BasePromptTemplate class, it has the following structure and parameters:

• metadata: A dictionary (Dict) of type str: Any. This is used to store metadata related to the prompt template.
• template_vars: A list (List) of strings (str). These are the variables that are used in the template.
• kwargs: A dictionary (Dict) of type str: str. These are the keyword arguments that are passed to the template.
• output_parser: An optional instance of BaseOutputParser. This is used to parse the output of the template.
• template_var_mappings: An optional dictionary (Dict) of type str: Any. This is used to map template variables to their respective values. It defaults to an empty dictionary.
• function_mappings: An optional dictionary (Dict) of type str: Callable. This is used to map template variable names to functions that take in the current kwargs and return a string. It defaults to an empty dictionary.

You can find more details about the BasePromptTemplate class in the LlamaIndex repository.

I hope this helps! If you have any more questions, feel free to ask.

-Dosu

Sources

• llama_index/query_engine/sql_join_query_engine.py
• llama_index/prompts/base.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[PradiptaAtByteIQMind-Graph2023]

@dosu-bot

suppose i want to pass the table information and some natural language query and corresponding sql queries as custom prompt and while building the NLSQLTableQueryEngine(
service_context=self.service_context,
sql_database=sql_db,
tables=engine.table_names()
)
i want to pass the text_to_sql_prompt= my-own-custom-prompt

    so as to understand better
    can you give me the sample for this 

[dosubot[bot]]

🤖

To pass a custom prompt template to the SQLAutoVectorQueryEngine.query method in the LlamaIndex framework, you need to create a new instance of PromptTemplate with your custom template and then pass it to the NLSQLTableQueryEngine during its initialization. Here's how you can do it:

from llama_index.prompts import PromptTemplate, PromptType

# Define your custom prompt template
MY_CUSTOM_PROMPT_TMPL = (
    "Your custom template here..."
)

# Create a new instance of PromptTemplate with your custom template
my_custom_prompt = PromptTemplate(
    MY_CUSTOM_PROMPT_TMPL, prompt_type=PromptType.TEXT_TO_SQL
)

# Pass your custom prompt to the NLSQLTableQueryEngine during its initialization
nlsql_engine = NLSQLTableQueryEngine(prompt_template=my_custom_prompt)

Please replace "Your custom template here..." with your actual custom template. The PromptType.TEXT_TO_SQL is used as an example, you should replace it with the appropriate prompt type based on your use case.

Please note that this is a general guide and the actual code may vary depending on the specific implementation of the NLSQLTableQueryEngine and SQLAutoVectorQueryEngine.query method in your version of the LlamaIndex framework.

The SQLAutoVectorQueryEngine.query method accepts a custom prompt in the form of a QueryType object. The QueryType can be either a QueryBundle object or a string (str).

If the QueryType is a QueryBundle, the query_str attribute of the QueryBundle is used as the query string. If the QueryType is a string, it is used directly as the query string.

Here is the relevant code:

def query(self, str_or_query_bundle: QueryType) -> RESPONSE_TYPE:
    with self.callback_manager.as_trace("query"):
        # if query bundle, just run the query
        if isinstance(str_or_query_bundle, QueryBundle):
            query_str = str_or_query_bundle.query_str
        else:
            query_str = str_or_query_bundle
        raw_response = self.custom_query(query_str)
        return (
            Response(raw_response)
            if isinstance(raw_response, str)
            else raw_response
        )

The QueryBundle object is not defined in the provided context, so I can't provide details about its structure.

For more details, you can refer to the source code in the LlamaIndex repository:

• llama_index/prompts/default_prompts.py
• llama_index/query_engine/custom.py

I hope this helps! If you have any more questions, feel free to ask.

Sources

• llama_index/prompts/default_prompts.py
• llama_index/query_engine/custom.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[PradiptaAtByteIQMind-Graph2023]

@dosu-bot

can you give one example to send natural language queries and its corresponding sqls as my CustomPrompt?

[dosubot[bot]]

🤖

Sure, I can provide an example of how to send natural language queries and their corresponding SQL queries as a custom prompt.

First, you need to define your custom prompt template. This template should include placeholders for the table name, table schema, and the natural language question. Here's an example:

CUSTOM_PROMPT_TMPL = (
    "You are given a table named: '{table_name}' with schema, "
    "generate SQLite SQL query to answer the given question.\n"
    "Table schema:\n"
    "{table_schema}\n"
    "Question: {question}\n\n"
    "SQLQuery: "
)

Next, you need to create a new instance of the PromptTemplate class with your custom prompt template and the PromptType.TEXT_TO_SQL prompt type:

from llama_index.prompts import PromptTemplate, PromptType

CUSTOM_PROMPT = PromptTemplate(
    CUSTOM_PROMPT_TMPL, prompt_type=PromptType.TEXT_TO_SQL
)

Now, you can pass your custom prompt to the SQLAutoVectorQueryEngine.query method:

engine = SQLAutoVectorQueryEngine(...)
result = engine.query(query, prompt=CUSTOM_PROMPT)

In this example, query is the natural language query that you want to convert into an SQL query, and CUSTOM_PROMPT is the custom prompt template that you created. The query method will use your custom prompt to generate the SQL query.

Please note that the SQLAutoVectorQueryEngine.query method's exact structure and parameters are not documented in the LlamaIndex repository. The example provided is based on common usage patterns and may not work as expected if the method's actual implementation differs. If you encounter any issues, please provide more details so I can assist you better.

You can find more information about the PromptTemplate class and the PromptType enumeration in the llama_index/prompts/default_prompts.py file in the LlamaIndex repository.

Sources

• llama_index/prompts/default_prompts.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[PradiptaAtByteIQMind-Graph2023]

@dosu-bot
I got it. but suppose if i want to pass list of custom templates then what to do? give one demostrated example.

[dosubot[bot]]

🤖

Sure, you can pass a list of custom prompt templates to the SQLAutoVectorQueryEngine.query method. Here's an example of how you can do this:

from llama_index.indices.struct_store.sql_query import BasePromptTemplate, NLSQLTableQueryEngine, SQLDatabase

# Define your custom prompt templates
custom_prompts = [
    BasePromptTemplate(
        "Given a natural language question, convert it to SQL.\n"
        "Question: {query_str}\n"
        "SQL: ",
        prompt_type=PromptType.TEXT_TO_SQL,
    ),
    BasePromptTemplate(
        "Given an input question, synthesize a response from the query results.\n"
        "Query: {query_str}\n"
        "SQL: {sql_query}\n"
        "SQL Response: {context_str}\n"
        "Response: ",
        prompt_type=PromptType.SQL_RESPONSE_SYNTHESIS,
    ),
    # Add more custom prompt templates as needed
]

# Create a new instance of the NLSQLTableQueryEngine class
query_engine = NLSQLTableQueryEngine(
    sql_database=SQLDatabase(...),  # replace with your SQLDatabase instance
    text_to_sql_prompt=custom_prompts,
    synthesize_response=True,
    service_context=ServiceContext(...),  # replace with your ServiceContext instance
    sql_only=False,
    verbose=True,
)

In this example, custom_prompts is a list of your custom prompt templates. You can add as many custom prompt templates as you need to this list. The text_to_sql_prompt argument of the NLSQLTableQueryEngine constructor accepts a list of BasePromptTemplate instances, so you can pass your custom_prompts list directly to it.

Please replace SQLDatabase(...) and ServiceContext(...) with your actual instances of SQLDatabase and ServiceContext.

After creating the query_engine instance, you can use its query or aquery method to execute queries. The query method is synchronous, while the aquery method is asynchronous. Both methods take a QueryBundle instance as an argument.

For more information, you can refer to the LlamaIndex codebase.

Sources

• llama_index/indices/struct_store/sql_query.py
• llama_index/prompts/base.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[PradiptaAtByteIQMind-Graph2023]

@dosu-bot

So can i pass both sample natural language query and its corresponding sql query in the prompt template?

[dosubot[bot]]

🤖

Based on the information you've provided, it seems like you want to pass a custom prompt template to the SQLAutoVectorQueryEngine.query method. This can be done by providing the custom prompt when initializing the NLSQLTableQueryEngine, PGVectorSQLQueryEngine, or SQLTableRetrieverQueryEngine classes. These classes accept a response_synthesis_prompt parameter in their constructors, which is used to format the SQL query.

Here's an example of how you can do this:

from llama_index.indices.struct_store.sql_query import BasePromptTemplate, NLSQLTableQueryEngine, SQLDatabase

# Define your custom prompt template
class MyCustomPromptTemplate(BasePromptTemplate):
    template_str = "My custom template: {query_str}, {sql_query}, {context_str}"
    prompt_type = "MyCustomPromptType"

# Initialize the query engine with your custom prompt
engine = NLSQLTableQueryEngine(
    sql_database=SQLDatabase(...),  # provide your SQLDatabase instance
    response_synthesis_prompt=MyCustomPromptTemplate(),
    # other parameters...
)

In this example, MyCustomPromptTemplate is a custom prompt template that you define. It must be a subclass of BasePromptTemplate and define template_str and prompt_type class variables. The template_str is the string template that will be used to format the SQL query, and prompt_type is a string that identifies the type of the prompt.

If you want to pass multiple custom prompt templates, you can do so by passing them as a list to the text_qa_template and refine_template parameters of the query method. Here's an example:

from llama_index.prompts import PromptTemplate

# Define your custom prompt templates
text_qa_template_str1 = (
    "Context information is"
    " below.\n---------------------\n{context_str}\n---------------------\nUsing"
    " both the context information and also using your own knowledge, answer"
    " the question: {query_str}\nIf the context isn't helpful, you can also"
    " answer the question on your own.\n"
)
text_qa_template1 = PromptTemplate(text_qa_template_str1)

text_qa_template_str2 = (
    "Additional context information is"
    " below.\n---------------------\n{context_str}\n---------------------\nUsing"
    " both the additional context information and also using your own knowledge, answer"
    " the question: {query_str}\nIf the context isn't helpful, you can also"
    " answer the question on your own.\n"
)
text_qa_template2 = PromptTemplate(text_qa_template_str2)

refine_template_str = (
    "The original question is as follows: {query_str}\nWe have provided an"
    " existing answer: {existing_answer}\nWe have the opportunity to refine"
    " the existing answer (only if needed) with some more context"
    " below.\n------------\n{context_msg}\n------------\nUsing both the new"
    " context and your own knowledge, update or repeat the existing answer.\n"
)
refine_template = PromptTemplate(refine_template_str)

# Pass the templates to the query method
print(
    index.as_query_engine(
        text_qa_template=[text_qa_template1, text_qa_template2], 
        refine_template=refine_template
    ).query("Who is Joe Biden?")
)

Please note that the above code assumes that the as_query_engine method and query method support passing multiple templates as a list. If they don't, you might need to modify the LlamaIndex codebase to support this feature.

You can find more information about this in the LlamaIndex documentation.

Regarding your question about passing both a sample natural language query and its corresponding SQL query in the prompt template, it's possible if you define your custom prompt template to include both. The template_str of your custom prompt template can include both {query_str} (for the natural language query) and {sql_query} (for the SQL query). Here's an example:

class MyCustomPromptTemplate(BasePromptTemplate):
    template_str = "My custom template: {query_str}, {sql_query}"
    prompt_type = "MyCustomPromptType"

In this example, whenever the query method is called, the query_str and sql_query will be replaced with the actual natural language query and SQL query, respectively.

I hope this helps! If you have any other questions, feel free to ask.

Sources

• llama_index/indices/struct_store/sql_query.py
• docs/examples/customization/prompts/completion_prompts.ipynb

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.