MSSQL with ChatGPT: How to Analyze your Microsoft SQL Server Data with AI

hi I'm Dave from notable and today let's learn about how to use notable's chat GPT plugin to query a Microsoft SQL database let's get started alright so first thing we need to do is connect notable to our Microsoft SQL database so we can do that within notable easiest place to do that is within the notebook page so here's just a blank notebook it's my first notebook I actually created a whole new project for this effort over on the left in the database icon this is where we manage our data connections you can see here I already have two data connections set up one for a bigquery database and one for a postgres one but I want to add a new one so let's get add data connection now this video will give an overview of a Ms SQL database but all the steps that I take here include the steps right here in an add a database will be the same across any database type you use so if you have a different one you can still follow along and you'll still get a lot out of this video hopefully will go there I will call this my Ms SQL demo database and now I have to add in the details here so let me just hostname I know the port I know my username here I know my password Here [Music] we go and the database is called development here we go we set up a data connection you can have a private data connection or a Space level data connection a private one will follow you around wherever you are with a notable regardless of what space you're in and only you'll have access to it a space level data connection is then shared within the space so if you're collaborating across a team and you want to set up a data connection that your entire team can use you can create a space and then create a space level data connection as you add other users to that space they'll have access to that same data connection so I want to make this a space level one that goes within this notable overview space which is exactly where that notebook and that project you just created were living in so I'm going to go ahead and hit create here all right so now let's save that connection you see it's over on the left there now it's available for me to use within chat GPT so let's get started right now using notable chat tpd plugin right you have currently you have to be a chat TPT plus subscriber use the gpt4 model and use the beta plugin feature you can enable the plugin feature within the settings page of your account and if you haven't installed the notable plugin yet you just go to the plugin store notable is one of the popular plugins you should see at the first page if you don't just search for it and go ahead and click install now I already have it installed so I'm not going to go through those steps but we have more docs to describe that on our docs page if you want to follow along there all right and so now I'll say I want to create a notebook to query data within my Ms SQL demo data connection I like talking type at the same time but you get the point there all right and so now it's going to use the plugin within notable to start Along on this effort so you can see here if you've never used plugins before when you see a little green kind of bar there that little square that means an action is happening between chat GPT and notable and when it turns gray and it says use notable it means it's finished doing that if you ever want if you ever want to you can inspect write what gets sent and go it gets returned there but otherwise it's just best to kind of you know watch those little you know bars that progress along the way and then see the output that chat gbt types back to you here so it said I created a notebook for you you can access it here now let's work in this notebook I need to fetch available data sources let's do that found your data connection great we just let me just add it let's create a SQL cell to use this right another things I want to kind of add here and just I just do this in general right that kind of help make sense of the notebooks that chat tpd writes I just give a little prompt that says you know when writing in this notebook always use markdown cells to provide explanation of these code cells so I can better understand what you're doing so I'll give it that prompt to get started there right perfect and it's going to go ahead and just um you know get um add a little explanation in there to that notebook now I can follow along right remember it gave me the link before so I can open the link and we can view that notebook you can see here right it just created the first cell to test what the data connection was and now it's writing markdown in there and the one down below right and so now I can start asking questions of data contained in that database now I know there's a Sharks table in there that I want to query this data just has information about a bunch of geotag sharks as there's something about in the ocean and just makes for a fun uh demo to play around with here so one of the first questions I might want to do is say I want uh sorry I am pinterested in data contained in these sharks table how many unique species of sharks on the table and what are they right let's go ahead and send that over and the really powerful thing here about the plug-in is that chat gbt is going to help generate content for that notebook both the markdown cells as I previously asked it to do as well as the actual code cells and the SQL cells in order to return the results for me so I can be interfacing with a notebook and interfacing with my database using the natural language capabilities and power of chat GPT and again when you're executing things in notebooks here you might see kind of you know multiple of these you know Green sections as it makes this progress it makes its way the notebook and that's totally normal here this case it only took two and it did there was it did the exact query they wanted it to do for me and it found the results and usually it's always going to also say I can view the results in the notebook it'll give me a link to view that here and I already have that open in a different tab so I can go ahead and look at the results there there's the query there's the results but again it returned a summary of the results to chat GPT which is really helpful so now I know how many unique shark types are contained in that database right what if I want to find right what species of shark had the most individual tags right within that within that table and again we're going to see it interface with notable right in those little sections here it's going to go out it's going to create those cells it's going to execute the cells use the data connection that we had already set up and return the results back and again any point you can examine right what gets sent to notable what gets returned from notable but usually if you're just patient and wait for the the cells here to finish you'll get the results back right within the prompt here so this is interesting to see too right this we will often see as well where chat GPT will make a mistake right in some of the syntax that it uses in this case here it's saying that the original syntax it used wasn't compatible with the msql server that I'm using but it can correct itself which is really powerful right so it it generated an error it saw the results of that error and was able to correct in the second attempt here so let's see if the second attempt works there we go right again it's giving me the answer back to the question that I asked but it's also giving me a link to the notebook I have the notebook over here right so I can see the results um in the notebook itself right and just so you know in the notebook it's using SQL cells connecting to that data connection to do the SQL right let's just do two more quick examples of some more advanced type queries I can ask of it right so one of the ones here is all right of all these sharks and they're individually named sharks right which has the biggest Geographic box that is you know from the largest distance from the Min lat long location to the max lat long location right I can just explain you know tell chat ubt the question I want to ask it will then translate that into the code in the SQL cell that it wants to run within the notebook and it will give me the results back which is really powerful and there we go great so particular shark named Catherine has the largest Geographic box I can view uh the results with a notable it's also going to add a markdown cell that explains those results to help you kind of again better contextualize uh what the SQL code was doing let's ask one last one let's make something a little more complicated because one of the really neat things about notable is that any SQL cell query that you do Returns the results as a pandas data frame and then you can then take that pandas data frame and do pythonic things on it later right so in this case here I want to create a map that plots out all the locations of a particular shark and I want to use as a python Library called folium which is great for interactive maps I want to use that python library to create the results so I'm going to tell it to use folium to plot the first ping the last ping and a line connecting each pings for a particular tiger shark is named Emma I want to make the icon of the first thing be a play icon the icon the last ping be a stop icon and I'm telling chats if you do here do the SQL query first right which returns results data frame and then create the map based on that pandas data frame and then lastly just send me a link to view the results with a notable because it's going to be better to view the interactive map with the notable than it is with just returning the text back to check TPT all right so it's got intermediate results here it's telling us that the SQL cell has fetched all the data for that shark name Emma it's now contained in a pandas data frame called Emma data and now it's going to create a python cell in that notebook to create the map visualization from the the exact specifications that I said before the play icon the stop icon and the line between all the points and there we go it's finished doing that it's going to give me a link here in a second to view that result again I have it open on another tab here so I have to click the link let's go down to the bottom of the notebook here right here's the query where it gets all the information about Emma here's the python code to create the folium map and here's that interactive foliant map all that's inside notable super powerful right just using natural language to express what I want either in SQL or in Python and have chat GPT create it for me giving me the notebook at the end of the day right that I can now share out with others using the series feature here I can publish this into a published artifact I can share with others and when you publish something in notable you can say only display these elements and don't display other elements because maybe I want to hide the code and just display the results and the markdown sections of the notebook I can fully do that in here as well anytime we return a data frame with a notable either from python or from a result of a SQL query we have our own Rich no code data visualization capability inside notable here so I can just here I can look at the table the data here you can go I can filter down through it I can brush filters on it easily I can rename the columns sort the columns hide columns Etc this only has three columns so it's not terribly interesting on that front but I can create a visualization super easily right so I can create new visualization it can be my own guide there's over 40 different visualization types of notable support including a tile map view so I know before I had asked uh Chachi petite to create a folding map for me it you know create it needed a little bit of python code to do that but even as a no code user within the notebook here I can go on and even create visualizations um out of my data just by clicking around with some of the options that we provide with our data Explorer called Dex all right so I hope that was helpful and I hope this really helped you get started with how to use data connections with a notable and query Microsoft SQL Server again everything that I demoed today would be just the same if you use any other database type so you want to use snowflake or a bigquery or Etc it all would work the same way if you have any questions please don't hesitate to reach out to us we have a community Forum at community.motable.io or reach out to our support team at support notable.io hope this helps and good luck thanks

Channel: Noteable

Views: 8,024

Keywords:

Id: fySK9h5eEx8

Length: 11min 38sec (698 seconds)

Published: Fri Jun 30 2023