SQL in Fabric–Copilot, Data Virtualization, Notebook Integration & Backup/Restore | Data Exposed

Key Takeaways

There's a lot going on in SQL database in fabric. Here, get a sneak peek or what's new in the areas of copilot, data virtualization, notebooks, and backup restore, and more this week on data exposed. [Music] Hi, welcome to Data Expose. We have a super exciting, jam-packed episode planned for you today on SQL database in fabric. We're going to take you through a sneak peek and quite frankly a really fast show of all the new things the team has been working on and depending on when you watch this episode, they're either coming soon or available now. So this is the wonderful product management, some of the product managers from the team. We're going to get right into it. Uh starting with uh Yle. So uh Yolay, thanks so much. I took Yolay out of the stream, not Dineker. Uh, Yle, thanks so much for joining us. We're gonna go right into it. Tell us what's new in the co-pilot space. >> Thank you so much, Anna. So, my name is Yo. Today, I just like kind of kick everyone off. We're going to go like with some a lot of demos in these feature areas including copilot, either virtualizations, notebook integrations, backup, and restore experiences. So today we're going to talk about uh what's new or giving you like a sneak peek demo of um what's coming down the road. So I am the PN for Copilot. So let's get started with Copilot. First just some like very high overview of what we have today in our copilot experiences. We have the inline code completion feature. We have two quick actions, fix query errors, explain the code. We also have a sitecart chat that is powered by copilot. This is kind of our focus today. Um and what I really want to talk about is that today we have two scenarios that we are supporting. The first one is natural language to SQL conversion and the other one is um documentation documentation based Q&A. So you can ask a pilot questions and it will give you a response that's backed by our MSARN docs. So these are these all these like existing features we we talked about actually like previously in a a older episode with Anna. If you're interested to check out more feel free to go to the ak.ms link below or look at our MS learn box. And then what I really want to talk about today is something really exciting. We are going to have support for more scenarios coming soon later this year. So on top of the two tools that we have uh we're going to have support for performance monitoring. We're going to have support for schema design, troubleshooting errors, code analysis. So, a lot of really good stuff coming soon. And this is kind of like what I want to show you in our demo later. So, if you've never seen the editor before, this is how it looks like. If you click the copilot button, it'll open up the sidecard chat. You can choose if you want to be in readonly mode or if you want to be in read write with approval mode. So, today I'm asking Copilot to help me refactor this query. So after just kind of like analyzing for a little bit, loading for a little bit, you can see that copilot actually helps identify that there is an anti pattern in this query. So it kind of helped me refactor this query so that it's more readable and more efficient. And another scenario that we are supporting now is um performance monitoring. So you can ask copilot to help you generate a report at my database performance. This scenario is actually something our public preview customers have been really looking forward to and now we have support for this as well. And over here you can see that there is a lot of information. I'm going to scroll up a little bit. There is gives you like a historical CPU usage summary. There's planned cache bloat. Um there's also other information like historical worker load uh session load this space usage and all the good stuff. And at the very bottom it gives you a summary of findings telling you like oh everything seems pretty okay low CPU session load some brief spikes in CPU but the real problem is that there seems to be high percentage of single use plans. So now that we have all this information it just need to go further to investigate more for optimization. So this is really great for our developers or people who doesn't really have that monitoring expertise. And if you are very interested or there's like a scenario that you really want, please comment below in this video and that the team is awesome. Great. Thanks Y. Okay, so that was the first stop on our tour of what's going on in SQL database and fabric lately. Um, thanks so much Yle. Uh next we're going to go into Hugo's space which happens to be the data virtual data virtualization space and one I'm super excited about. So Hugo what's going on in this space? >> Quite a lot Anna. So today we are going to be announcing pretty soon the preview of open rule set and external table for data for fabric database. That means our customers can now query CSV in park files either for read purposes or ingestion purposes. All of them will work. We we established the security integration using HID and now because we are announcing integr SQL database now we have the same consistency experience across the board in all the flavors of SQL. So either that SQL server onrem Azure SQL database SQL MII it works the same way across the board now. Awesome. That is great to see. Okay. And then can we take a look? >> Yes. >> Awesome. Let's take a look. >> With data vitalization, fabric SQL database can now access CSV and park files from one lake without the need to ingest them into the database. In this example, we have a lakehouse called code lake inside au folder with sales data from past years. I can use data visualization to ingest the data into my database but I don't need to. I can still leverage all the SQL capabilities but leaving the data where it is without causing any data duplication. I can use open rule set for all my ad hoc needs. I can combine it with views book for data ingestion or even together with joins with other SQL tables. All that I need is the ABFS location of the file to access it. I can also create an external table that will act like a pointer like a shortcut to the file. It will behave just like a regular SQL table but keeping the data where it is in one link. This way your application can use fabric SQL database as a data hub and access files across fabric without needing to change the context. With external tables created, I can use that in combination with copilot. And even though there is no data loaded into SQL, Copilot can still leverage the context of external tables to provide accurate insights. For example, I want Copilot to help me find the most popular items sold by year and I want to group that by age groups. You can see the command Copilot provides understands the external table context, columns, the schema. It takes full advantage of the SQL with multiple joins and functions and does all of that without loading the data into SQL. This is the power of copilot and data stization for fabric SQL database. >> Wow, that is super cool, Hugo. I've been waiting for this for so long. I know people have been waiting as well. Uh any tips or tricks for folks who maybe are just getting started with this new new capability? >> I would say pay attention to this space. There's a lot of things going on. We're going to be making some announcements pretty soon as well. So pay attention to that. That's number number one. >> Awesome. Great. Okay. Awesome. Thanks so much, Hugo. Uh we are going to roll right on with our show. Bringing up Saquant. Uh Squant, thanks so much uh for coming on the show to talk about to talk to us about what's new in the integration space. >> So today Anna, as you know, integration is is huge in in fabric. Today I'm going to be talking specifically about notebook integration. I got three very exciting things to share with the with everybody here and I'm thinking let's roll the our um >> our demo and show everybody through the demo and then I can talk about a little bit. >> Awesome. Great. Yeah, let's uh roll the demo. >> In this demo, I will show how you can use Pispark Scala and TSQL magic in notebook to connect to your SQL database. when you once you create a notebook uh you need to make sure if you want to use pi spark make sure your um environment has u runtime 1.3 because that contains spark 3.5 in there and in the code you have to make sure in the cell you are saying import uh and this library that's another important part and in your URL Well, uh you are going to be using the silver name. Make sure you do not forget uh count 4033 in there and your database name. um you are uh over here here on line number three you notice that I'm creating the raw data and on line number four I'm assigning the header and then I'm creating a data frame using the spark and then I'm writing it and I'm through line number six I'm saying create a table called public sample pi and add the data to frame like our table got created and uh I did another read so we have the data here. So this is your pi spark. Now let's move on to scala. In scala we have to import these three uh libraries for it to make work. and uh for the URLs are going to make sure that that you are adding um 1433 here and uh severing and the uh database name and uh the syntax for Scala is that you know this is how you create the raw data and here's your schema and then you create a data frame here and we're going to do the same thing we're going to uh create a new table add the data and then we are going to read it back again. All right, looks like we successfully uh ran the code. Uh we created the table and when we read it, we got the data back. Now let's go back to our T-magic SQL. TSQL magic. What you have to do is you have to make sure you are doing percent TSQL and our artifact name which is we were entering all our data in a database called DA test. It is of type SQL database. it's inside this workspace. So this whole line is very useful. Now just imagine if you're you have two different SQL databases in two different workspaces, you could have two separate cells and you could call data and put it into your data frame and do whatever you want to do. So over here just for simplicity sake so we are running this TSQL magic to see how quickly we can get the data back. Looks like we got this data. I put in the pretty much same data in both the tables. So, we got this. Uh, in the next cell, what I'm going to show you is how easy it is to, you know, use the same command. And I'm going to create a table called employee. All right, we got that created. And the next one, I am inserting some data into this table. And in the next one, I'm creating a store procedure from here. Then I'm going to just execute it. All right. So, we executed. I want to go back to our database. Want to show you all the stuff that got created there. So, we created an employee table. We created public example PI one. And then we created a table called public example spark. That's it for this demo. Thank you so much. All right, Anna. So you saw how exciting it is. We had a lot of customers who were waiting to use SQL with the notebook and you know advantages are numerous. All the data engineers know how important the notebook piece is and they can create data flows. They can bring the data they can combine the data from multiple places data warehouse SQL or lake and uh you know sky is the limit when you look at it. here. This is a very important integration piece that people are waiting for it. I want to let everybody know anybody who was waiting for Spark and Scala for a while, it's uh going to be deployed worldwide starting today. So by the end of this week, uh hopefully you guys can all try these. >> Awesome. Great, Sukan. And so that means by the time you guys are watching this episode, it's probably going to be available. So go check it out. My recommendation is to bookmark this video so you can reference exactly how Squantan did it the first time because the first time you're getting connected in notebook uh in my opinion in my experience has always been the biggest challenge. Um so thank you Squan for documenting that so everyone can reference us later. Yes. >> Yes. And one one last thing there's so much more that's coming um hopefully in um in few more months. So uh stay tuned um in this space. Thank you. >> Watch this space. I think that's what everyone has said which we love to see all the things everyone's working on. Um, thanks Squant. Uh, last but not least in our stop of what's going on uh across SQL database and fabric, we have Denker who works on backup reser store across uh SQL and I'd love to understand like what's going on in the SQL DB and fabric space. >> Yeah. Um so today we are announcing the public preview of the ability to change your retention for your automatic backups and we will GA pretty soon. Um as you all know uh built-in automatic backups is a fundamental requirement for any pass or a SAS provider. Um for fabric SQL database the default retention today is fixed at 7 days. Uh with this enhancement which is rolling out this week um you will have the ability to change it from uh anywhere from 1 day to 35 days depending on your business needs. Uh look forward to more capabilities in the in this area and we have a demo coming up. >> Awesome. Cool. Let's let's roll it. >> Hi, my name is Dukar and I'm a product manager in the Azure SQL team. In this demo, I will show you two capabilities in the backup restore area that are coming up shortly. Having a built-in database backups is a fundamental feature for any managed database provider. FabricSQL database also comes with built-in automated backups. The first demo I'm going to show you is the ability to configure a retention period for this automated built-in backups. For that, first I will go to my workspace and find the database for which I want to configure the retention and then select the database. Once I'm connected to the database, I will find the settings icon on the top left and then click on it. In the settings blade, one of the parameters is the backup retention policy. When I click on it, I can see the current retention period which right now says 7 days. From here, I can update this value to any time between 1 and 35 days. Once I update and save this setting, Fabric will keep the backups for up to 35 days from this point on. And in the next 35 days, I can restore back to any point within this last 35 days. I can exit out of this window. Now for the second demo or the second capability I want to show you today is the restore database. For this I will go back to the workspace dashboard and I will select the three dots next to the database for which I want to perform the restore and then select the restore database option. In this window, first I need to type the name of the database. Verify the source database information. Now I can see the earliest restore point and the latest restore point. Here this is the restorable time range and I can issue a restore to any point within this range. By default, the most recent point is selected. I can click on the calendar and choose any time within this range. Depending on the size of the database, the restore option operation should be completed in a few minutes. So I can click create and let the restore operation succeed. In this demo, first we saw how to configure the retention period for the built-in automated backups. Next, we performed a point in time restore and created a new database with the restored data. This concludes the demo. Thank you. >> Awesome. Great to see that. Love to see these things finally start to light up uh in SQL database in fabric. Uh I know there's some other things we're working on. Anything that you can comment on right now or anything else you want to share on backup and restore? Um yeah so we have uh additional capabilities that we are planning um things like uh automated uh ARS uh as your backup storage um so that your backups can be zone redundant um if there is a failure in one zone um the backups are available in the other zone and the failures are automatic so it's pretty seamless um seamless uh interruptions for your uh for your databases. >> Awesome. >> We have a lot more coming up uh pretty soon. >> Awesome. Yeah, I know there's a lot of planned uh in this space. So, I guess just like every other space, we're going to say watch this space. Um, thanks so much, Dicker. Uh, it was great to see uh this in action. I'm going to bring everybody else on. Uh, folks, you got a glimpse today of what's going on in SQL database and fabric. We have more episodes that are be going to be dropping in the coming weeks. Uh, we'll put some links in the description for you to learn more. Just know that we are a space to watch. So definitely watch this space if you learn one thing about what's going on here. Uh if you like this episode, go ahead, give it a like, leave us a comment, and let us know uh which space you are most excited to watch and actually get hands-on with. And we hope to see you next time on Data Exposed. [Music]