Voice of the DBA

Tag: AI

A Real World Clash with AIs

A group of people are protesting more cars in San Francisco by disabling self-driving cars with a cone on their hood. It is likely that the cone disables some sensors and won’t let the cars move. For truly autonomous cars, this could be an issue if people could prevent them from working with small hacks. I could imagine a strip of duct tape or some other smaller obstacle that might also work and be less noticeable to anyone looking at the vehicle.

Ignoring the humor here, or the more-cars-in-cities debate, this is an interesting place where computing systems can fall down. People do find ways to hack sensors or other ways in which our software gets data from the real world, which can force behaviors we didn’t expect. We might allow for these, or we might not. In this case, I don’t know that the developers could do anything different, other than perhaps detect and alert the owners that the vehicle can’t move until someone clears the obstacle. They could add more sensors, but I suspect hackers would just block those.

The bigger issue is that as we depend on computing devices that interact with and need to work in the real world, we’ll have more problems like this. We might see simple hacks, or even everyday events that can interfere with the machines completing their tasks. If we get very dependent on these AIs, then these hacks become more than just vandalism. Imagine getting in this vehicle in an emergency to go to the hospital, and it won’t move because a sensor is blocked? That could be more than inconvenient.

We certainly have similar issues with people in the real world. People flatten tires (how does an autonomous car change a tire), they padlock doors and gates, and they might do any number of things that interfere with others’ ability to live their lives. Humans can often adapt to these issues with small behavior changes, but what about computing devices? Will a new class of jobs come about to correct issues like this? Will we see “fix-it” robots that autonomously move around and remove cones from hoods or change tires?

It’s hard to even think about all the crazy permutations of how autonomous computing devices can evolve and the types of interactions we might have. I’m not even sure if I think that the way we are approaching AI and autonomous systems is good. We’re often applying similar frameworks to them that we do for other humans, which I’m not sure if the best way to approach these decisions.

The future is looking very strange, and it’s coming quickly. I’m not even sure what to think about issues like this.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

August 21, 2023
Using AI with Data Tasks

The hot new technology of the year is AI. Between ChatGPT, Copilot, and generative AI, it seems that this is invading the world of computing at an incredible rate. Whether this becomes really useful and valuable or not is something that we will seem over time. There have been plenty of trends in this area in the past that haven’t become as ubiquitous as the hype would lead you to believe.

I’ve done some light experiments with AI on my blog. To date, I haven’t found this to be that useful, other than a few cases where I basically used an AI to search the web for me. Rather than read a bunch of SSC or Stack Overflow results, the AI summarized things.

Somewhat.

I definitely had to test and verify the code more than I feel I’ve done with code posted in a forum. Of course, I do less experimenting because the AI results were a little more targeted to what I needed, rather than my cobbling knowledge and partial solutions together. I’m also not sure which I prefer.

I would like to use AI for data work, and there is an article that talks about some of the ways that we’ve used AI in the past. Data profiling has made sense, and I can see value here. For data security, I’m not sure how helpful AIs have been. I’ve looked at some products, and I don’t know that I think any of them do a great job of identifying data. They do make it easy for whoever is assigned the task by doing some of the work, but they aren’t a panacea. They make mistakes, just like humans do.

I do think data observation and looking for anomalies is a place where AI can really shine, but that’s not the data work that many of us do. It matters, but for most of us, this isn’t something we deal with.

The future of AI was more interesting. The idea of data homogenization, taking data from different sources, and fitting it to a data model is interesting. Of course, the AI can’t make too many mistakes, or the time correcting might overwhelm the time saved. I think we see that now with humans who we ask to ETL data. If they aren’t good at it, or make lots of mistakes, those of us overseeing them might just do the entire job ourselves.

I know that AIs are still new and immature, and while there is a lot of potential, they feel like junior staffers now, needing more handholding and micromanaging than I like to do. Perhaps they will change our careers and the way we work, but I don’t know how quickly, or even how deeply. Already I find lots of companies putting restrictions on what their employees can do with AI, which makes me think this might be more a targeted, niche technology more than a general, use-it-everywhere solution.

Steve Jones

August 19, 2023
Adding Copilot to Azure Data Studio and a Few Experiments

As a part of a recent Data Exposed that I was on, there was an ADS update which mentioned Copilot being added. Since I’ve been experimented, I decided to give this a try.

This is part of a series of experiments with the ChatGPT and other AI systems. Lots of Copilot lately.

Installation

One of the easy things about VSCode and ADS is that you can install things from the marketplace very easily. You search and it appears, as shown here. Click Install to add it.

Once you do this, it’s enabled. I have access to the Copilot with my GitHub account, so I assume this picked this up.

Using Copilot in ADS

There’s a blog about what this tech is and a few workflows. However, I decided to try something first. I picked a table in this connection and started typing. Copilot added a suggestion.

It’s not a good one. That’s a table, not a function.

Yeah, some AI isn’t reading my schema and sending it back to GitHub. Boo, this isnt’ helpful. I can only guess that seeing “StringSplit” had the AI guessing this was related to string_split(). Something a new dev might think.

OK, let’s pick a table and see if intellisense works better. I’m guessing it doesn’t. Here’s a suggestion based on what I typed.

Not a bad guess. I have some schema in here, and there is an OrderHeader related to a Customer table. However…

There is no orderdetail. This clearly isn’t intellisense. It’s an AI taking guesses.

Trying Comments as Prompts

Let me try a few things from the blog. I’ll enter a comment.

I don’t like that. While I think this is valid for MySQL or PostgreSQL, it isn’t for SQL Server. Setting aside why LIMIT isn’t in T-SQL, why doesn’t copilot recognize this is T-SQL? Let me help it.

Better, but I don’t want to add “in T-SQL” to everything.

Another experiment. I tried a couple of prompts, but realized that asking for “orders” doesn’t mean that the AI knows I mean orderheader. Context around what or where an order is stored is something humans might include in natural language. However, I got this:

Which doesn’t work. I’d left the “in t-sql” from the previous prompt, so I’m not annoyed there, but this isn’t looking at schema. I also find the Oracle help not helpful.

However, this was interesting. I wanted a shipdate (this is a column) in 2023. The first suggestion was = Jan 1, but when I deleted that and hit enter again, I got this.

Not bad. I tried a longer prompt, with a few comments. All of the code was suggested a line at a time, and when I hit Enter, I got the next line.

The blog does note that adding the table create at the top of the query helps. Let’s try that.

I’ll add creates for customer and orderheader. I then try a simple prompt and get this.

Note, I tried “write a query”, but that didn’t work. I used “fetch” from the blog and that got me something. Though, not sure why this term is needed.

I also changed my prompt trying to get all matching orders and got this instead.

I also tried changing this to get matching orders. No idea why the customer name is limited in here.

What about aggregates? I know some people have trouble with these.

Not bad.

Conclusion

Adding Copilot to ADS is easy, but using it isn’t. The code it produces can have lots of mistakes, and not even work. It can make some strange assumptions, like junior devs might do.

Adding context makes things better, but for many of these simple queries, I have to describe things enough that I might as well write the query. Especially with SQL Prompt, I’d use SSMS instead.

However, learning to use prompts is a skill and it’s one to work on. Hopefully the AI will improve over time and I’ll start to see more useful suggestions, especially with different types of code.

So far, I’m impressed very little with this tool.

August 16, 2023
Copilot Experiments: A little PowerShell help
It’s been a little while since I’ve had time to relax a bit and try some AI help. This is another experiment I made.

A user on SSC asked about PowerShell to copy files with a date appended.

This is part of a series of experiments with the ChatGPT and other AI systems. Lots of Copilot lately.

I added some code to a new file and typed a prompt:

If I run this, it does work. Sort of.

It made a folder copy, not a file copy. However, the filter worked.

Let’s try again. I’ll modify the prompt and get Copilot to explain what it’s doing in the code. I get this:

Which works:

Hmmm, can it do what I want.

I tried a few prompts in the code window, but I kept getting things that wouldn’t work, like call copyfiles.bat, or something that didn’t work.

Let’s move on.

Copilot Chat

I got access to the Copilot Chat as part of Redgate. There is a new chat extension to add to VS Code, which I did. I opened it and got this with my prompt:

Good, this code will copy the files, but does all of them.

One advantage of AI bots is I don’t need to start over. I did this:

This worked correctly.

This was a simple example, but it produced about the same code as I did, albeit slightly cleaner. Mine was this:
```
$source="c:\fileloading" #location of starting directory
$destination="c:\filecopy"; #location where files will be copied to
$files="*dys_ihhist*" #files matching this pattern

# write a powershell command to get a list of files in $source matching the $files pattern
$a = get-childitem $source -filter $files
$a | foreach {write-host $($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}

# write a powershell command to copy files from source to destination appending the date to the filename
$a | foreach {copy-item $_.fullname $destination\$($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}
```
I don’t know enough PoSh to know which is really better. And honestly, I don’t feel like testing at scale. Let me know if you have knowledge here.

However, the chat window for copilot produced this quicker than I did, without me having to try and remember the PoSh parameters and structures of the functions. I had to dig around on SO to remember basename was what I needed and look up the parameter for get-date.

The code window isn’t great, and partially I think because I don’t know how to get prompts to work in the comments, but I do like the chat window. I’ll keep playing.
August 4, 2023