{"id":2290,"date":"2023-08-25T12:42:35","date_gmt":"2023-08-25T19:42:35","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=2290"},"modified":"2023-08-25T12:48:00","modified_gmt":"2023-08-25T19:48:00","slug":"building-your-own-db-copilot-for-azure-sql-with-azure-openai-gpt-4","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/building-your-own-db-copilot-for-azure-sql-with-azure-openai-gpt-4\/","title":{"rendered":"Building your own DB Copilot for Azure\u00a0SQL with Azure OpenAI GPT-4"},"content":{"rendered":"

<\/p>\n

Large Language Models (LLMs) have proven exceptional capabilities in chatting with unstructured, text data. But what if we want to interact with structured data?<\/p>\n

To do so, we will need to build an LLM-powered Agent which is able to reason over structured data, in our specific case an Azure SQL DB. Luckily for us, we don\u2019t have to build this connection from scratch.<\/p>\n

LangChain, a lightweight library which make it easy to embed and orchestrate LLMs within applications, provides a set of toolkits to make Agents capable of connecting and execute actions over external tools. In my latest article, we\u2019ve seen how, among the tools LangChain provides out of the box, LLM-powered agents are able to connect and use Azure Cognitive Services skills. In this article, we will use the same framework to connect over an Azure SQL DB where we will upload the Titanic dataset (you can download it from Kaggle here<\/a>).<\/p>\n

Loading Data Into an Azure\u00a0SQL<\/h4>\n

The first thing we need is our structured DB. For this purpose, I will be using an Azure SQL Database, a fully managed DB engine running on Azure. To create your Azure SQL DB, you can follow the tutorial here<\/a>.<\/p>\n

Once we have a DB up and running, we need to populate it with our data. To do so, I will use Python to connect to my DB via ODBC and to push data into it. Thus, we first need to gather our Azure SQL DB credentials and set them as environmental variables:<\/p>\n

import os  \r\n  \r\nos.environ[\"SQL_SERVER_USERNAME\"] = \"username\" \r\nos.environ[\"SQL_SERVER_ENDPOINT\"] = \"endpoint\"\r\nos.environ[\"SQL_SERVER_PASSWORD\"] = \"password\"  \r\nos.environ[\"SQL_SERVER_DATABASE\"] = \"database\"<\/code><\/pre>\n
You can retrieve those information in the Azure SQL DB pane within the Azure Portal, under the tab \u201cconnection\u201d:<\/p>\n
<\/p>\n
Next, let\u2019s save into a pandas dataframe our csv file (which I\u2019ve saved into the folder \u2018data\u2019):<\/p>\n
import pandas as pd\r\ntitanic = pd.read_csv(\"data\/titanic.csv\").fillna(value=0)<\/code><\/pre>\n
Finally, let\u2019s insert our dataframe into the Azure SQL DB using the pyodbc library for connectivity (using the same variables as before).<\/p>\n
# Create dataframe from csv file\r\n\r\ncnxn = pyodbc.connect(f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}')  \r\ncursor = cnxn.cursor()  \r\n\r\n# Define the create table statement\r\ncreate_table_query = \"\"\"\r\nCREATE TABLE Titanic (\r\n    PassengerId int PRIMARY KEY,\r\n    Survived bit NOT NULL,\r\n    Pclass int NOT NULL,\r\n    Name varchar(100) NOT NULL,\r\n    Sex varchar(10) NOT NULL,\r\n    Age float NULL,\r\n    SibSp int NOT NULL,\r\n    Parch int NOT NULL,\r\n    Ticket varchar(20) NOT NULL,\r\n    Fare float NOT NULL,\r\n    Cabin varchar(20) NULL,\r\n    Embarked char(1) NULL\r\n);\r\n\"\"\"\r\n\r\n# Execute the statement using cursor.execute\r\ncursor.execute(create_table_query)\r\n\r\nfor index, row in titanic.iterrows():\r\n     cursor.execute(\r\n    \"INSERT INTO dbo.[Titanic] (PassengerId, Survived, Pclass, Name, Sex, Age, SibSp, Parch, Ticket, Fare, Cabin, Embarked) values(?,?,?,?,?,?,?,?,?,?,?,?)\",\r\n    row.PassengerId,\r\n    row.Survived,\r\n    row.Pclass,\r\n    row.Name,\r\n    row.Sex,\r\n    row.Age,\r\n    row.SibSp,\r\n    row.Parch,\r\n    row.Ticket,\r\n    row.Fare,\r\n    row.Cabin,\r\n    row.Embarked\r\n)\r\ncnxn.commit()\r\ncursor.close()<\/code><\/pre>\n
Let\u2019s see whether our dataframe has been uploaded correctly:<\/p>\n
cnxn = pyodbc.connect(f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}')  \r\ncursor = cnxn.cursor()  \r\nprint(cursor.execute(\"select * from dbo.[Titanic]\"))\r\nresults = cursor.fetchall()\r\n\r\nfor row in results:\r\n    print(row)<\/code><\/pre>\n
<\/p>\n
Nice! Now we can go ahead and initialize our agent with the SQL connection toolkit.<\/p>\n
Initializing the LangChain Agent with SQL\u00a0toolkit<\/h4>\n
To create our LangChain Agent, we need three ingredients:<\/p>\n