Microsoft SQL Drivers and SDKs: Python, AI, Bulk Copy, Arrow & more! | Data Exposed

Key Takeaways

In the past month or so, we've had seven releases across six drivers and one of them includes PHP for the first time in over 2 years. Learn all about it and see some great Python demos this week on Data Exposed. Hi, I'm Anna Hoffman and welcome back to Data Exposed. Today, I'm joined by Dave Leby. Dave, thanks so much for coming back on Data Exposed and today we're going to be talking about how the SQL drivers team has been busy. So, tell me a little bit about, you know, the drivers team and everything that's going on and and what, you know, maybe even the backup if you're like a developer who's using SQL. How are you interacting with the drivers today and how to get the latest ones? Sure. So, we've got a lot of drivers. Um, it's um, you know, everything ranging from Python, you know, where you've got real high-level languages to like real deep languages like, you know, C++ where you're interacting with ODBC drivers. Um, it's really we really try to cover the full gamut and we've been making a huge investment within Microsoft, within SQL to uh reinvigorate the drivers. So, you may have noticed that we've done a lot of releases lately and that's that's intentional. We're going through and we're optimizing all of our drivers to have the best experience for developers and also to enable them to be able to build new AI applications. Awesome. That's great. That's great to see. So, okay. So, then between last month and this month, you told me that we released like every single one or even more? Well, so we have enough releases to go around, but um we didn't release the OLE DB driver. That's the only one we haven't done a release for. Um, so we did six drivers and seven releases. We we doubled up on Python. I see. I see. I see. Cool. Awesome. Okay, so I mean, what's new? Obviously, there's probably a lot new. So, let's go through it. Yeah, there's a ton. Let's jump into it. So, let's start with Python. Um, that's really where we've got a lot of crowd pleasers. Um, in the past month, we've released both bulk copy and Apache Arrow support. Wow. Bulk copy has been a huge ask. A lot of people don't love the the options that are out there for inserting data via Python into databases. Um, bulk copy takes it from a big process where you have to do lots of inserts or, you know, format special for execute many and it turns it into basically three lines of code. You just pass an iterator to the bulk copy and it will ship all that data up there for you. Stream it all up very, very fast. And that's using the insert bulk API in SQL. So, it's optimized for data loading and transaction handling. Another big one is Apache Arrow. This is actually a community contribution. Can you believe that? What does that mean? Like someone else wrote the code? Yeah. Um, and I'm blanking on the name right now. Um, like the GitHub uh handle is Felix with um a couple of Fs and I just can't believe uh like we're all blown away by the the contribution here. Uh this is something we really wanted to do and all of a sudden Felix showed up and it was done. It's really amazing. It's so fast. Um, rounding that out, we've got, you know, SQL variant and native UUIDs as well as spatial types. Let me keep going here. I know we've only got a few minutes. Um, next one up is SQL client. Um, so SQL client 7.0, we shipped the number one requested change. Wow. >> Um, we're working on the number two actually. So, the Azure dependencies have been abstracted so that we're not shipping all of the authentication pieces that go with um, the SQL client. You'll Those are all libraries that you can install independently and we can share those amongst all of our components. So, we don't have this giant install anymore. Uh we've also added some changes for SSPI context provider and we have a preview of packet multiplexing. Ooh. Yeah, we've started investing in async. I know I know you've seen it too. There's a lot of feedback out there on on SQL client's uh async performance. So, we're starting to work on that. There's a preview feature that you can go ahead and turn on today. Uh it's a couple of commands in or a couple of lines of code to enable it and then you're good to go. Uh next up, we've got JDBC 13.4 and the real focus there was AI and observability. So, we brought the uh half vector out. So, the float 16 vector subtype. That is if you just don't need all of the resolution of a full vector and you want to save space, you want to put more into memory, float 16's perfect. We've also added performance logging. So, we're really working on open telemetry and making it so you can wire in and see exactly what the driver's doing and where it's spending its time. Oops. Next up, let's talk about Django, PHP and ODBC. Now, there's some real crowd pleasers here, too. Uh it's been a little while since we released MS SQL Django and uh we are caught up. Now, we got that out there within a couple weeks of Django 6 going full support. Um, Awesome. >> A couple weeks a little generous, >> [laughter] >> uh it was within a month or so. Um, or within a couple months, I should say. Um, but PHP 5.13, that's our first release in 25 months. Um, we're back. We're investing in that and you'll see a lot more releases there. This was something that, you know, unfortunately, you know, fell by the wayside and we're working to make that right. So, you'll see both Django and PHP have regular releases from now on. Awesome. That is so great and I know like we can't hear them, but every PHP developer out there who was using SQL and wondering if they needed to use something else is rejoicing that they could still use Microsoft SQL. Yeah, and the feedback's been amazing. It it's great to hear from everybody that they love it and that it's working well for them. Um, and then finally, our last driver to show is ODBC. Uh we released ODBC 6.2. Uh the crowd pleaser here is fabric redirection. So, with private link, some folks were running into issues with too many redirects and we now support up to 10 hops. So, you're able to do all kinds of uh custom private communications with fabric now. So, this would apply to like a fabric SQL database or a fabric data warehouse or just any or something else? So, we we cover everything that has a TDS endpoint. Uh whether it be Synapse Fabric uh DW, Fabric uh Analytics endpoints, Fabric SQL database. Um, we cover it all. So, if you've got a private link setup, which a lot of folks do to keep their their internet or keep their data separate from the internet, um, you may have run into this issue and previously, we only allowed two redirects, which is real easy to go through when you get into some of this networking. So, now we allow up to 10. This puts it on par with the other drivers that already work. >> Awesome. That's great. Okay. Um, so I've also got a demo. I wanted to show what it looks like to code uh against Arrow and BCP in Python. Um, so I've got this real simple script and basically, what it's going to do is use Arrow to go do a zero copy fetch from sales LT product. Basically, what that means is we're going to pull this into memory. We're going to pull it directly into memory and we're not going to keep copying data as we move it around. If you've written a lot of Python scripts, you start to realize that memory is a very precious resource and allocating memory gets really expensive and slows things down very fast. So, this is really neat the way it just takes everything, puts it right into the Arrow table and then shares the Arrow table. What we've got then is we're doing some schema fetching. We're setting up perf counters just because we're going to want to see how long it took and we then convert our Arrow to Pandas. So, we have that flip there and then once it's in Pandas, you know, we're working in memory, we're going to sort the values and you'll see how quick we're able to sort through the values. We also then do a group by to show here's how fast we can do aggregates to do counts and means and we'll show that on the screen as well. And that's going to allow us to show the top 10 most expensive products sorted in memory and we're also going to show products by color and we'll show the uh sums and groupings there. Finally, what we're going to do is we're going to take that data, still in memory, and we're going to ship it back up to a SQL database in Fabric. And um like basically, this is just so easy. We we get our rows, which is you know, we get our our pile list from our um from our Arrow table and then we do our bulk copy. So, here we're able to ship our rows. We ship it to a table and um it's just super easy. Let me show you what it looks like. Oh, yeah. Prove that it works. >> [laughter] >> Just got to wait a second here. There we go. Um Let's see. Demo BCP arrow. It's amazing how fast this runs. Of course, I say that before it runs. But, um Wow. >> It did run really, really fast. So, it took us what? 0.17 seconds to get the data. 0.43 seconds to convert it to Pandas. Um and I don't have that great of a machine. It's uh Yeah, I'm running this off a a Mac Mini, one of the basic Mac Minis. So, like your developer machines that you're running on are probably going to run a lot faster than this thing does. Um but here we see here's our results, top 10 most expensive products. And then we have our products by color. We're able to see our count and our mean. So, our Pandas operations took 0.0131 seconds. So fast. Um and then our BCP Yeah, we loaded 295 rows, which isn't a ton, but still we loaded 295 rows in a tenth of a second. Awesome. This is super exciting. And if I wanted to do this myself, like is this one of the sample I know you have a ton of samples out there. I don't have this sample out there yet. I need to get the arrow sample out there. Um I do have a docs update that I'm pushing out that will be similar to this. But if you want to try BCP or any of our other samples, go to aka.ms / mssql-python, so the driver name -qs, as in quick start. And then you'll be able to try out all of these different scripts, and I'll get something out there for you to try out the arrow as well. Awesome. Great. Well, Dave, I learned so much. I'm really excited to see everything happening in the drivers for all of the languages. Uh I'm sure our users are too. Uh if you're watching this episode and you liked it, go ahead give it a like, give us a comment, and let us know what driver you use the most. We'll put some links in the description for you to learn more, including that link to the quick starts uh where you can find all the awesome samples Dave and the team are working on. And we hope to see you next time on Data Exposed.