This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 53%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENS%3C/text%3E%3C/svg%3E)
Hi, I am working on building a GenAI solution that will allow users to get a SQL generated based on a prompt they provide, I want the generated SQL to use my own custom database schema and its own tables and columns, How can I train \ fine tune the model to be aware of the custom database schema. Please note I am using AzureOpen AI service and not looking forward to a separate python and langchain based solution at this stage. I want to know if this is possible through azure portal - OpenAI service I found the below document that explains pre-training the model for specific prompts, Azure OpenAI Service fine-tuning gpt-3.5-turbo - Azure OpenAI | Microsoft Learn However my requirement is to inform model about my own custom schema with some 50+ tables and 5000 columns, I can not follow the process mentioned in this document. Please advise. Thanks, Neeraj.
Hi Maui,
I was able to make use of the solution you shared and provided my DB schema details (Tables and Schema) in database_schema_string and provided the details to the ChatCompletion.create via Tools parameter using the function (ask_database) you provided. It all worked out great, Thanks a million for sharing it. However I do have a concern with this approach
To avoid hitting the token limit error in future, is there any solution that you can suggest for example Chunking mechanism of tokens that azure supports internally OR Is there any other alternative option for me to let the model know to use my database schema, I tried Upload your own data method where in JSONL format I provided my table details with each prompt but somehow it did not consider it. I am not sure if this supported or am i providing the data in wrong format, I tried to use Create your own model but got an error as this feature is not available in your region. Please advise. Thanks, Neeraj
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(300.8, 34%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJV%3C/text%3E%3C/svg%3E)
We are doing a POC in this area. Is it possible to train the model with the schema once and then send in repeated user prompts (over days/weeks) without having to 'remind/send' the database schema with every prompt every time over multiple sessions?
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(182.40000000000003, 64%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMR%3C/text%3E%3C/svg%3E)
Hi @Jignesh Vaducha cc @Neeraj Shrivastava thank you for your query and sorry for the delay.
For the use case you mentioned, it would be helpful to fine-tune a model using a dataset containing natural language queries and their corresponding SQL queries. This will enable the model to learn how to generate SQL queries from natural language inputs. The training data for fine-tuning the model should be in the form of question-answer pairs in JSONL format, as discussed in the following documentation. Customize a model with Azure OpenAI Service - Azure OpenAI | Microsoft Learn
You can also use the On Your Data feature to provide your schema, and if you have a large knowledge base, you can use the chunking option. For more information, please refer to the documentation. Using your data with Azure OpenAI Service - Azure OpenAI | Microsoft Learn
The Chat Playground of Azure OpenAI Studio can be used to test this. Alternatively, you can also implement a python script, which can be found in the "View code" section of the playground. Below is an example of a basic script.
import openai, os, requests
openai.api_type = "azure"
# Azure OpenAI on your own data is only supported by the 2023-08-01-preview API version
openai.api_version = "2023-08-01-preview"
# Azure OpenAI setup
openai.api_base = "https://YOUR_AOAI_RESOURCE_NAME.openai.azure.com/" # Add your endpoint here
openai.api_key = os.getenv("OPENAI_API_KEY") # Add your OpenAI API key here
deployment_id = "NAME_OF_YOUR_DEPLOYMENT" # Add your deployment ID here
# Azure AI Search setup
search_endpoint = "https://YOUR_AI_SEARCH_RESOURCE_NAME.search.windows.net"; # Add your Azure AI Search endpoint here
search_key = os.getenv("SEARCH_KEY"); # Add your Azure AI Search admin key here
search_index_name = "NAME_OF_SEARCH_INDEX"; # Add your Azure AI Search index name here
def setup_byod(deployment_id: str) -> None:
"""Sets up the OpenAI Python SDK to use your own data for the chat endpoint.
:param deployment_id: The deployment ID for the model to use with your own data.
To remove this configuration, simply set openai.requestssession to None.
"""
class BringYourOwnDataAdapter(requests.adapters.HTTPAdapter):
def send(self, request, **kwargs):
request.url = f"{openai.api_base}/openai/deployments/{deployment_id}/extensions/chat/completions?api-version={openai.api_version}"
return super().send(request, **kwargs)
session = requests.Session()
# Mount a custom adapter which will use the extensions endpoint for any call using the given `deployment_id`
session.mount(
prefix=f"{openai.api_base}/openai/deployments/{deployment_id}",
adapter=BringYourOwnDataAdapter()
)
openai.requestssession = session
setup_byod(deployment_id)
message_text = [{"role": "user", "content": "What are the differences between Azure Machine Learning and Azure AI services?"}]
completion = openai.ChatCompletion.create(
messages=message_text,
deployment_id=deployment_id,
dataSources=[ # camelCase is intentional, as this is the format the API expects
{
"type": "AzureCognitiveSearch",
"parameters": {
"endpoint": "'$search_endpoint'",
"indexName": "'$search_index'",
"semanticConfiguration": "default",
"queryType": "semantic",
"fieldsMapping": {},
"inScope": true,
"roleInformation": "You are an AI assistant that helps Data Engineers with queries on their database schema. You are an expert in generating SQL statements. Analyze the attached schema and generate a fully formed SQL based on the user's question together with a dataframe of minimum 3 rows of sample data.",
"filter": null,
"strictness": 3,
"topNDocuments": 5,
"key": "'$search_key'"
}
}
],
enhancements=undefined,
temperature=0,
top_p=1,
max_tokens=800,
stop=null,
stream=true
)
print(completion)
I have tested this via the Chat Playground and have the below actual results.
This is possible by leveraging the "On Your Data" containing your database schema and function_calling features. I'm working on a similar use case last week. I'll clean up the Notebook file and share my solution here.
Hi, please feel free to reference the following Notebook demonstrating how to generate SQL statements from user prompt and using your own database schema. This also generates a description of the generated query as well as sample data. This Notebook also demonstrates how to create a database schema from an ERD (Entity Relationship Diagram) image without the need to connect to a physical database. If you have questions, kindly post them via Issues in the repository. Hope this helps. Thank you! https://github.com/iamramengirl/azure-samples/blob/main/azure-openai-sql-generator/gpt4-function-calling-sql-generator.ipynb (Note: This is the only the initial version, and I will be updating the notebook as appropriate.)
Hi Mauri, Thank you very much, I am really grateful to you for sharing this solution. I will be testing it and share the update early next week. Thanks, Neeraj
Hi Maui,
I was able to make use of the solution you shared and provided my DB schema details (Tables and Schema) in database_schema_string and provided the details to the ChatCompletion.create via Tools parameter using the function (ask_database) you provided. It all worked out great, Thanks a million for sharing it.
However I do have a concern with this approach
To avoid hitting the token limit error in future, is there any solution that you can suggest for example Chunking mechanism of tokens that azure supports internally
OR
Is there any other alternative option for me to let the model know to use my database schema, I tried Upload your own data method where in JSONL format I provided my table details with each prompt but somehow it did not consider it. I am not sure if this supported or am i providing the data in wrong format, I tried to use Create your own model but got an error as this feature is not available in your region.
Please advise.
Thanks,
Neeraj
Hello Neeraj, Thank you very much for your valuable feedback. I appreciate your time and effort in reviewing the notebook. Yes, I recognize that this is a current limitation that I also would like to address. I am planning to incorporate a chunking mechanism in the next version of the sample. However, this may take some time but I will update here as soon as the next version is ready. Please let me know if you have any other suggestions or comments. In the meanwhile, it may be worth exploring the Assistants API (Preview) which allows both the use of BYOD and function calling features. This may be helpful for your requirement. I will also share any other alternative solutions if I find any. Thank you very much!
Thanks Maui, I will be looking forward to the chunking solution. In the meantime I wanted to test if Upload Data Files approach would work, Please refer the screen shot of the UI I am talking about, I uploaded a JSONL file Database_Schema_Training.txt (changed extension to txt for upload) , this file contains few of my tables and column in the JSONL format. After the file got processed I tested. However there was no impact on the deployed model it did not get tuned or developed any knowledge of the tables I provided using the data Files method. Can you please advise if this is even supported or am I making any mistake.AzureOpenAI-DataFile.JPG
Sorry for the delay and thank you for sharing the jsonl file. I checked the file and maybe this is not the complete one. However, it looks fine and in order. Kindly also ensure to increase more quality samples as per the following documentation. Create your training and validation datasets
The more training examples you have, the better. The minimum number of training examples is 10, but such a small number of examples is often not enough to noticeably influence model responses. OpenAI states its best practice to have at least 50 high quality training examples. However, it is entirely possible to have a use case that might require 1,000's of high-quality training examples to be successful.
Hi Maui, One Limitation with Azure Function calling example of converting text to SQL is that i dont see an option to fine tune the model or please guide if my understanding is correct. Can we provide some training samples for the prompts and expected response in function code itself, Is that possible, if yes can you provide an example. As per Azure documentation the only fine tuning option appears to be deployment of a custom model which is quite hostly as there are charges for every hour of running the model. Thanks, Neeraj
Hello Neeraj, thank you for the follow up query. The fine-tuning feature has a REST API method that can potentially be executed from an Azure Function. You can find a list of available operations with the fine-tuning feature in the following link: Fine Tuning - REST API (Azure Cognitive Services - Azure OpenAI) | Microsoft Learn. However, using an Azure Function to perform this operation is only one way to interact with the feature programmatically, and it will still result in the deployment of a custom model. It's important to note that fine-tuning, particularly for large language models, is a compute-intensive process. If this feature doesn't meet your needs, you might want to consider the "On Your Data" feature, which generates responses based on the provided source data.
Take care of the Prerequisites section of the Azure OpenAI GPT 3.5 Turbo fine-tuning tutorial and make sure you have prepared correctly. Specifically, note the model, service version, and resource region:
gpt-35-turbo-0613 - fine-tuning of this model is limited to a subset of regions, and is not available in every region the base model is available.
You can read more details below.
Hope this helps.