“Learning should be a joy and full of excitement. It is life’s greatest adventure; it is an illustrated excursion into the minds of the noble and the learned.“
– Taylor Caldwell
Perhaps I should have instead started with a better quote like “rumors of my demise have been greatly exaggerated” or something like that – you look like you’ve all seen a ghost! Where have I been you ask? Places. What have I been doing? Things.
I needed a break from blogging, and may still take more time, but I really enjoy playing around with new things and when I saw the hype around ChatGPT I had to get in on the action; it has produced some rather fine results thus far…
Magnificent.
But people have been building out examples of where they can use ChatGPT in wider life, whether it’s producing the easy 80% of content which they then sharpen up and add more unique ideas, producing the wireframes needed to begin some more code or even just looking for a recipe for a vegan Shakshouka; it can pretty much do it all. As someone who has never really played around with APIs all THAT much I wanted to see what I could do with it.
I failed dramatically – turns out writing REST calls is not my favorite thing ever, so I turned back to my good friend PowerShell as it’s much easier to play with and I already understood the syntax etc. etc. and this is the result.
The Idea
A while back I was given a SQL script by someone who wasn’t quite sure what it did and how and they asked me to look at it to see if I could figure it out – I did but it took me a little while, I wanted to see how well ChatGPT handles it and instead of copying and pasting I wanted to see if I could just pass the file across to the OpenAI APIs. All you need for this to work is a directory with SQL files in and an OpenAI API key (and free account unless you wanna pour some money in – keep an eye on that, don’t go crazy on the free tier and end up spending load on it) and this was the code I used:
# Authenticate to the OpenAI API
Write-Host "Authenticating to OpenAI API"
$apiKey = "YourAPIKey"
# Set the path to the directory containing the SQL scripts
$sqlScriptDirectory = "YourSQLDirectoryFullyQualified"
# Get the list of SQL scripts in the directory
Write-Host "Getting list of SQL scripts"
$sqlScriptFiles = Get-ChildItem $sqlScriptDirectory -Filter *.sql
# Loop through each SQL script
Write-Host "Parsing SQL scripts and generating descriptions"
foreach ($sqlScript in $sqlScriptFiles) {
try {
# Read the contents of the SQL script
$sqlScriptContents = Get-Content $sqlScript.FullName
# Use the OpenAI API to generate a description of the script
$requestBody = @{
model = "text-davinci-003"
prompt = "Describe the purpose of the following SQL script: $($sqlScriptContents)"
max_tokens = 256
}
$jsonBody = ConvertTo-Json $requestBody
$description = Invoke-RestMethod -Method Post -Uri "https://api.openai.com/v1/completions" -Headers @{
"Authorization" = "Bearer $apiKey"
"Content-Type" = "application/json"
} -Body $jsonBody
# Write the description to the console
Write-Host "Description for $($sqlScript.Name): $($description.choices[0].text)"
} catch {
Write-Host "An error occurred while processing $($sqlScript.Name): $($_.Exception.Message)"
}
}
It does a pretty good job – I passed it a script from SQL Server: Sample Scripts – TechNet Articles – United States (English) – TechNet Wiki (microsoft.com) to get all the indexes in a database (Clustered and No Clustered), to see how it interpreted it, and you know it was pretty good:
> Authenticating to OpenAI API
> Getting list of SQL scripts
> Parsing SQL scripts and generating descriptions
> Description for V001__StoredProc.sql: This SQL script has the purpose of retrieving all constraints in a database and creating a script to drop them. The script uses an inner join to join the sys.indexes, sys.objects, and sys.schemas tables on their object_
id and schema_id columns, respectively. It also includes a WHERE clause to filter the results by type, primary key, index_id, schema name, and unique constraint. Once the query is executed, it will generate a script that can be used to drop the desired constraints.
The Application
I’ve thought quite a bit about what options we have here – the script itself was a 15/20 minute lunch project is very simple and doesn’t even recursively scan the directory of files… but the potential is so clear.
In my mind the most obvious options to extend this in the future are:
- Code Readability-> often we leave a legacy of code behind us (or certainly people I’ve worked with do) and it’s uncommented or unformatted and we’re not entirely sure what it does – this can help partway to making that headache go away so you can focus on value added tasks
- Data Lineage -> pairing this capability together with transformations and existing code could help give youa better idea of where your change may be disruptive in your pipelines
- DevOps pipelines -> as you deploy changes upstream, maybe with a technology like Flyway, you accumulate a list of scripts to be deployed that have a short description in the filename, and you can do some automated testing, but sometimes you just want the changes boiled down into a single easy to understand summary – you could use this capability to pass in all of the scripts pending deployment to provide a human readable deployment summary, making it easier to catch bad behaviors like where people drop tables, because it’s in clear English.
So maybe give it a try yourself and let me know where you see this capability coming in most handy for you in the future!
PlantBasedSQL 