<\/i>Guest Post<\/strong><\/p>Valentina is a Microsoft Specialist, focusing on Azure OpenAI. She has been working with Azure OpenAI and Azure SQL lately and in this post she’s sharing how to start using Azure OpenAI and Azure SQL or SQL Server to build next-gen solutions. You can find more of her articles in her blog: https:\/\/medium.com\/@valentinaalto<\/a>. Thanks Valentina! <\/div><\/p>\n In January 2023, Microsoft announced the General Availability of the Azure OpenAI Service (AOAI), which allows Azure customers to access OpenAI models directly within their Azure subscription and with their own capacity.<\/p>\n There are four models families available at the moment:<\/p>\n <\/p>\n In this post, we will see how you can use those models to query your SQL tables. The idea is that of generating SQL queries using Azure OpenAI models’ API with Python code.<\/p>\n The first thing we want to do is import one of our SQL tables into a pandas dataframe. To do so, we can use the pyodbc <\/span>library in Python, which you can easily install via pip install pyodc<\/span>. To connect with my Azure SQL DB, I used an ODBC connection. You can find the information endpoints under the “Connection Strings” tab of your SQL DB instance:<\/p>\n The result will be something like:<\/p>\n Note: while creating my Azure SQL DB, I’ve also created the sample database AdventureWorks<\/a>. You can use any SQL table you wish.<\/p>\n The next thing we need to do is initialize our Azure OpenAI model. To do so, we will use LangChain, a Python library that makes it easier to combine the power of Large Language Models (LLMs) with the logic of an application.<\/p>\n To initialize your model, you also need to provide your keys and endpoint for your Azure OpenAI models’ (you can find them under the “Keys and Endpoints” tab in your Azure OpenAI resource) as well as the name of your deployment and model. You can find all this information from the Azure OpenAI playground when exporting your code. Click on the\u00a0 “View Code” link on the top right:<\/p>\n And then get the needed informations from the sample code:<\/p>\n For this demo, I’ve used a text-davinci-003<\/span> model.<\/p>\n Let’s have a look at the code:<\/p>\n Finally, we can use the pandas<\/span> DataFrame agent<\/a> in LangChain and start asking questions against our table:<\/p>\n As you can see, the agent, powered by Azure OpenAI models, is able to provide the right output. Also, thanks to the LangChain Agent, it is also able to show which is the thought process behind and which are the queries that it used to retrieve the answer.<\/p>\n Let’s try with other queries. For example, let’s check “what is the frequency of each country region”:<\/p>\n Another interesting query – just to check how “clever” the AI is, is to ask for the distinct values of city names available in our database:<\/p>\n Or maybe ask for the count the unique items. State and Provinces in this sample:<\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-05-203209-300x169.png\")
<\/a><\/p>\n\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-05-203817-300x170.png\")
<\/a><\/p>\nPrerequisites<\/h3>\n
\n
Quickstart Demo<\/h3>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-05-210810-300x100.png\")
<\/a><\/p>\nimport pyodbc\r\nimport pandas as pd\r\nserver = 'servername'<\/span> \r\ndatabase = 'AdventureWorks'<\/span> \r\nusername = 'yourusername'<\/span> \r\npassword = 'databasename'<\/span> \r\n\r\ncnxn = pyodbc.connect(\"Driver={ODBC Driver 18 for SQL Server};Server=tcp:server,1433;Database=database;Uid=username;Pwd=password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;\")\r\ncursor = cnxn.cursor()\r\n# select 10 rows from SQL table to insert in dataframe.\r\nquery = \"select * from [SalesLT].[Address];\"\r\ndf = pd.read_sql(query, cnxn)\r\nprint(df.head(10))<\/code><\/pre>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-06-085152-300x133.png\")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-05-211525-300x153.png\")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-05-211602-300x188.png\")
<\/a><\/p>\n# Import Azure OpenAI\r\nfrom langchain.llms import AzureOpenAI\r\nimport openai\r\nimport os<\/code><\/pre>\nos.environ[\"OPENAI_API_TYPE\"] = \"azure\"\r\nos.environ[\"OPENAI_API_KEY\"] = \"xxxx\"\r\nos.environ[\"OPENAI_API_BASE\"] = \"xxxx\"\r\nos.environ[\"OPENAI_API_VERSION\"] = \"2022-12-01\"\r\n<\/code>\r\nllm = AzureOpenAI(deployment_name=\"text-davinci-003\", model_name=\"text-davinci-003\") <\/code><\/pre>\nfrom langchain.agents import create_pandas_dataframe_agent\r\nagent = create_pandas_dataframe_agent(llm, df, verbose=True)\r\nagent.run(\"how many rows are there?\")<\/code><\/pre>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-06-085801-300x136.png\")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-06-091557-300x172.png\")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-06-091331-300x194.png\")
<\/a><\/p>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/04\/Screenshot-2023-04-06-091231-300x172.png\")
<\/a><\/p>\n