{"id":2989,"date":"2024-04-03T05:00:43","date_gmt":"2024-04-03T12:00:43","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=2989"},"modified":"2024-04-02T20:37:20","modified_gmt":"2024-04-03T03:37:20","slug":"vanna-ai-and-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/vanna-ai-and-azure-sql-database\/","title":{"rendered":"Vanna.ai and Azure SQL Database"},"content":{"rendered":"

Vanna.ai and Microsoft SQL<\/h2>\n

Vanna.ai<\/a>, in partnership with Azure SQL Database Product Management, bring you a collaboration for using Vanna’s natural language to SQL (NL2SQL) agent with the Azure SQL Database.<\/span><\/span><\/p>\n

How does Vanna.ai work?<\/h2>\n

First, Vanna is an open-source Python RAG (Retrieval-Augmented Generation) framework for SQL generation\/natural language to SQL (NL2SQL) and provides a fully hosted platform for application development. Get started for free <\/span>here<\/a>.<\/span><\/span><\/p>\n

(From the documentation<\/a>)<\/p>\n

At its core, Vanna is a Python package that uses retrieval augmentation to help you generate accurate SQL queries for your database using LLMs.<\/p>\n

\"How<\/p>\n

Vanna works in two easy steps – train a RAG “model” on your data, and then ask questions which will return SQL queries that can be set up to automatically run on your database.<\/p>\n

BYOLLM<\/h2>\n

Already have an LLM and want to bring your own? Or have a vector DB for storage? Or both? No problem! Easily configure an environment here<\/a> and out comes the python notebook needed to get started (Vanna has created notebooks for EVERY combination<\/a>!).<\/p>\n

\"Choosing<\/a><\/p>\n

Vanna.ai has always been simple to use, but now, with the addition of Microsoft SQL Server\/Azure SQL as a database connecter, it’s even easier.<\/p>\n

Why use AI to chat with your database in the first place?\u00a0<\/b><\/h4>\n

To start, it allows you spend less time writing SQL and more time generating insights on your data. You no longer have to be an expert in SQL to get information from the database. And if you are an expert, save some time writing a query when you can just ask for it. In fact, everyone benefits by being able to ask the database about business questions and getting the answer; have Vanna find the relevant tables and columns across your data estate.<\/p>\n

And why Vanna?\u00a0<\/b><\/p>\n

\n

With it’s retrieval framework and feedback loop, allows you to quickly train up an “AI Analyst” in much the same way that you would onboard a human analyst by supervising it. While using it, you can give instant feedback if the generated SQL was correct and have those answers help train the AI for future questions. And as will be seen in the blog, Vanna c<\/span>an be used as a standalone web app, in Jupyter, as part of an embedded widget, or as part of a MS Teams bot. See examples of this at https:\/\/github.com\/vanna-ai<\/a>.<\/p>\n<\/div>\n

Vanna x SQL x Codespace<\/h2>\n

This blog post will go over creating a NL2SQL agent using Vanna.ai and SQL Server\/Azure SQL using a python notebook and a GitHub codespace. The notebook will be based off of the one hosted by Vanna here<\/a> which uses Vanna’s hosted LLM and vector database with SQL Server\/Azure SQL. The codespace will also be using a local free instance of SQL Server using docker in docker loaded with the Adventure Works sample data.<\/p>\n

Get started by:<\/p>\n

    \n
  1. Going to this GitHub repository<\/a><\/li>\n
  2. Clicking on the green Code button<\/strong><\/li>\n
  3. Clicking the 3 dots<\/strong><\/li>\n
  4. Selecting + New with options<\/strong>.<\/li>\n<\/ol>\n

    \"Image<\/a><\/p>\n

    Then on the following page:<\/p>\n

      \n
    1. Click the Default project configuration button<\/strong><\/li>\n
    2. Select Python<\/strong> (we will be adding many more configurations as we add to this repository)<\/li>\n
    3. Click the green Create codespace button<\/strong><\/li>\n<\/ol>\n

      \"Image<\/a><\/p>\n

      Once the codespace has started, use the terminal to run the following code to install sqlcmd:<\/p>\n

      curl https:\/\/packages.microsoft.com\/keys\/microsoft.asc |<\/span> sudo tee \/etc\/apt\/trusted.gpg.d\/microsoft.asc\r\nsudo add-apt-repository \"<\/span>$(<\/span>wget -qO- https:\/\/packages.microsoft.com\/config\/ubuntu\/20.04\/prod.list)<\/span>\"<\/span><\/span>\r\nsudo apt-get update\r\nsudo apt-get install sqlcmd<\/pre>\n
      then, create the database with the following command:<\/p>\n
      sqlcmd create mssql --accept-eula --using https:\/\/aka.ms\/AdventureWorksLT.bak\r\n\r\n\"Image<\/a><\/pre>\n
      and once the database is finished creating, the connection string for ODBC will be needed for the notebook, run the following command to get the connection string:<\/p>\n
      sqlcmd config connection-strings<\/pre>\n
      \"Image<\/a><\/p>\n
      sss<\/p>\n
      <\/i>Security Check<\/strong><\/p>In the connect string, there is TrustServerCertificate=yes. For local development environments, this is OK but NEVER<\/strong> use this setting in live\/production environments. More on this here<\/a><\/div><\/p>\n
      One of the files included in this directory is the odbcDriverInstallUbuntu.txt file. This file contains the commands for installing the Microsoft ODBC driver for SQL Server on Ubuntu (Linux). Run the contents of this file in the terminal to install the driver. Once installed, run the following command for checking the version to ensure it has been installed successfully:<\/p>\n
      more \/etc\/odbcinst.ini<\/pre>\n
      \"Image<\/a><\/p>\n
      Open your notebook to…<\/h2>\n
      Now that the database is created with the Adventure Works data, open the vanna_and_sql.ipynb notebook<\/strong> in the code editor by clicking on it.<\/p>\n
      \"Image<\/a><\/p>\n
      The first Setup step installs the vanna and pyodbc python libraries to be used in the notebook. Click on the Execute Cell button to run this command.<\/p>\n
      \"Image<\/a><\/p>\n
      <\/i>Choose a Kernel<\/strong><\/p>You may see a dialog box asking you to choose a Python Kernal for the notebook. Choose python environment for the first choice and then the python version installed in this codespace.<\/div><\/p>\n
      Next, execute the following cell that prepares the libraries to be used for the python commands that follow:<\/p>\n
      \"Image<\/a><\/p>\n
      The last cell in the Setup section of the notebook sets the Vanna API key and model name to be used for the Vanna hosted LLM and vector database. Remember, you can sign up for these free services here. Once you enter this information, execute the cell with the run button.<\/p>\n
      \"Image<\/a><\/p>\n
      The next step in the notebook defines the database connection. Vanna has help out MS SQL\/Azure SQL users here with vn.connect_to_mssql. All that needs to be passed to this function is the ODBC string that was shown previously by running sqlcmd config connection-strings<\/strong>. Copy and paste the ODBC connection string into this cell of the notebook then run it.<\/span><\/p>\n
      \"Image<\/a><\/p>\n
      Training Grounds<\/h2>\n
      Moving to the training section, if the previous sections were filled out correctly, executing this cell should result in seeing all the tables in the database that the user in the connection string allows (which should be all of Adventure Works tables and views).<\/p>\n
      \"Image<\/a><\/p>\n
      The next cell uses some training to remind the NL2SQL agent to use tables with their schema names. As before, execute this cell:<\/p>\n
      \"Image<\/a><\/p>\n
      Free Apps!<\/h2>\n
      Part of the Vanna python package is a flask app that can be used as a chat session. Execute this cell to start the flask application:<\/p>\n
      \"Image<\/a><\/p>\n
      Once the application has started, a dialog box will appear at the bottom of the code space:<\/p>\n
      \"Image<\/a><\/p>\n
      Click the Open in Browser<\/strong> button to try out the application.<\/p>\n
      Using the chat box, ask the application a question about the Adventure Work data. For example, “What are the top 10 products sold and their colors? Format any currency columns as dollars.”.<\/p>\n
      The result is the SQL query itself, a table with the data as asked:<\/p>\n
      \"Image<\/a><\/p>\n
      as well as a nice chart!<\/p>\n
      \"Image<\/a><\/p>\n
      If the agent doesn’t add the schema to the table name and you get an error, just give it a quick reminder in the question. Ask “What are the top 10 products sold and their colors? Format any currency columns as dollars. Use the schema and table name”<\/div><\/p>\n
      Wrap-up<\/h2>\n
      Again, a huge thank you to Vanna for adding the simple, one line of code function for accessing SQL databases. It makes an already easy to use NL2SQL tool even easier (if that was possible!). The notebook sample is in the Azure SQL AI Samples repository here<\/a> for anyone to try out and start using with their applications. As always, tell us how it goes, and we appreciate any feedback you may have.<\/p>\n
      Resources<\/h3>\n