Well, welcome happy to have you all here. I'm Vicky Harp. I'm a program manager lead with the SQL server tools team and this is crystal for dinner and he is an engineer on the SQL server tools team and he is a surprise. Guest speaker that you didn't have in your program because basically went down about like this about.
We were in a meeting and talking about this presentation and said, wouldn't it be nice. If we had one of the engineers and so I walked out and walked over to him and said would you like to come to Manchester and so he was a late addition an I think that we're all going to probably really enjoy having his information on it, so we are with the SQL server tools team and so if you're.
If you've been using SQL server. You've been using some of our tools. We are yes go ahead.
I.
Alright I will try to speak a little louder where the SQL server tools team, so if you were using SQL server. You are almost certainly using one of our tools. We are the team that is responsible for the entire stack of client tools from the driver level 2 power shell to smoke to the command line tools like me.
An SQL CMD SSMS Azure Data Studio in SSDT, so all of these tools are in our portfolio. And So what I'm here to talk to you about today is our newest tool in that portfolio, which is as your data studio now? How many of you have heard about your data studio.
That's wonderful. Thank you so much how many of you use it when it SQL operations studio OK great, so SQL operations studio was the original name of this product when it before it went to GA and this product is a cross platform tool for SQL server. So it's based on a fork of VS code, which is the crossbow.
Our visual studio and so is based on a lot of the same gestures is based on the same kind of concept of work has built in source control. It has a very keyboard based interface and it's something that you can use on Windows, Mac and Linux and this isn't support of the fact that SQL server is the platform that gives you choices you can now.
Or in the platform of your choice and you can run the tools on the platform of your choice so this is part of that evolving story.
But I do want to take a moment to talk a little bit about where we come from which is to say.
All right.
SMS so how many of you here use SMS.
Yeah, a higher percentage. I think so, according to our telemetry millions of you use it every week and we do too. So some people have taken as your data studios emergence as something of a threat to SMS and I'm here to tell you that that is not the case as your data studio is a new tool of the mini 15 or so tools that we?
You have the bandwidth to support this and we will continue to support an release SMS. In fact is our intention to have Azure Data Studio in SSMS come closer together as your data studio is a cross platform tool. But it is also a Windows tool. SSMS is windows, only but when the 2 are coexisting together or into.
You move smoothly between them, so that you can choose to work in SSMS and get the functionality of Azure Data Studio in SSMS and vice versa. If you've used SSMS for many years you probably are familiar with the way that it spawns dialogues right when you open something like a window. Wizard is a modeless dialog right. It's disconnected you can have multiple up there.
Kaiser data studio is being a class of that kind of dialogue when you're working in SSMS. You can also choose to use Azure Data Studio as its own tool So what we've got here is actually SMS 18 preview 7, which is not out, yet, but will be coming out soon and so I just wanted to open it up here and show you one of the things we've got going here is now we have a?
Yo node and were able to do new query directly from here, there's launches were calling context switch into Azure data. Studio moving forward into future releases of SSMS will have things where you can actually launch Wizards or master data studio without opening it so I wanted to start with that with kind of way.
In the tooling and then I want to take you into what we consider the future in our tooling decisions. We need to stay a couple years ahead of the market. So the way I view it. We need to stay about 2 years ahead of where people probably are living technologically in order for our tools to be out and stable an ahead of things so that you have heard.
Technologies that are coming down the pipe before you have to deal with them. So if you're hearing a lot about containers and you're hearing a lot about Lennox and you're hearing a lot about communities and you haven't had a need for it. Yet that is no strike against what you're doing our tooling in our strategy is moving toward that because that's where we feel that the technology is going just like when.
Teams were coming up and not everybody had used that and not everything was supported. We don't want to be behind the Times on this and so we're trying to be ahead of times on this so I view this as the literal future. The tools that were showing you now or the direction that we think technology is going and we're really happy to bring you along on this and we're happy to get your feedback and helping to tailor it to.
As well as where you are now so let me cut over to Azure Data Studio.
So this is, after data studio, you can see a lot of the things are familiar to either VS code or 2. SMS so we've got our object Explorer. We've got the ability to add connections. We've got a number of these tabs over here down here. We've got as a resource Explorer, which allows you to see all of your Azure.
If you got them, but in this case all of these are on premises so even though it is named as their data. Studio is allowed you to do on premises and as your connections.
We got this built-in dashboarding, which this is something a little bit new and unique to Azure Data Studio in this case. I've got some business widgets that I put in place for invoices per month, etc and these are going to be refreshed. Every time that I go to this dashboard for this database. So I can kind of show you how you navigate to that.
Manage and it's going to pull up that dashboard here and fill it out. We got the query editor and this is very much. The query editor from SSMS with a couple of the VS code nicety so one of the nice things that we've got in here is snippet support so snippets or little sections of code that either we've.
Team so like create table create SQL database, and it's got some shortcuts so test. Now I've got all of that code written up there and so we put in a number of them that we think that you can use but you can create them yourself and add to the product and make it work, how you need it to do it's got Intellisense and then of course, it's able to run all of this.
In which when you get the results. You're able to export them to Excel and you're able to save them as CSV and a number of other little convenience features based on the feature request. We've had from SSMS, one of the things that.
You're able to do is to chart things so let me come up with.
A new query here.
But my customer by sales and run this on the correct.
Database so in this case, I run a customer. My sales report and I've got this, I can go over here in view chart.
And now I've got a quick little chart. I can save the image and I can also pin it to a dashboard. So if I wanted to have the dashboard. I'd hit create insight and I can go through the process of showing you how to do that. A little bit later. If we have time I very cognizant of the short time that we have on this and given the number of you have said you use the product. I don't want to over index on these more.
So some of the things that are a little bit more new for users who are coming from SSMS is we do have source control in this case. I am using literally today's build so I do not have source control and everything set up. I just installed this maybe 3 hours ago, so it's the.
Shot bits, but this is the same source control that you would see in VS code. We also have an integrated terminal. Shell so you're able to run in here, so I've got right now, it's on the command shell if I wanted to change it to power shell. We just type power Shell and now it's running in the power shell. Shell and you can run all of your commands right in here it's really convenient. Let's say they?
A file that has power shell.
You can even quickly run these in here yourself run that down there and it's going to start running that code down in the snippet for you from above so has some nice little features there for keyboard based optimizations now keep talking about keyboard based and so that's 1 of the things that.
A couple reasons, one thing, one of the things that we are really committed to in this product is making a more accessible experience for users and so keyboard based navigation is a lot easier for people sometimes versus using mousing so we have a very keyboard. Based Optimization and it's also highly customizable so I've been.
Shortcuts I've been doing new queries, etc. But that's something that I've made work for me. One of the ways that I can do that is through these settings at the keyboard shortcuts and let's say. I went through here and I was looking through this and said I just want this to be just like SSMS right, I mean, I don't want to learn all of these new gestures, etc well, we thought of that as well.
We have these this notion of something called an extension and extension is where you take a set of customizations that you've made to the product and you're able to save them and export them and share them with the world. This can be as simple as key bindings and it can be as complex as support for SQL 2019 big data clusters really runs the full gamut of capabilities.
And so in this case, we've got an SMS keymap extension. It was made by one of our engineers that just takes all of the key mappings from SSMS and puts it into Azure data. Studio here in the marketplace. You can see a number of other kinds of extensions. We've got in this case. I got wanted ads new database. I've got support for SQL.
He has an extension, I've got the agent and then down here. We've got some of them from our partners. For example, I've got one here from Red Gate and so we support a mix of first party. Microsoft extensions third party extensions from partners and community extensions, so some of these have actually been made by members of the community who have?
With charting widgets or experiences that are helpful to them and they're sharing them with the entire community. So this is something that we are really excited about and is a differentiator versus SMS in another important way so in SSMS has anybody right clicked on the Tasks area in SSMS perhaps.
And here you got into tasks.
There's a bit there right and has everybody used every single one of those no probably not so this is a bit of a problem with something, as complex SQL server. We've got something like 800. Maybe features in SSMS and we're having to hit everything anyone could possibly ever use, which makes for Eva.
Large download it makes for a tool that's a bit of a 747 when you might want to Cessna. You know if you're just trying to get in and do some coding work. You might not need to pull along with you all of the capabilities of the entire platform, so in this extension based experience. You're able to add in the features that you're going to use and disable them if you're not.
Make our best guess at what are the likely things you want by default so for example. We think you want SQL agent? But if you get in there and you're like this is just getting in my way. I don't really care about it, you can always go into it like profiler and hit disable or uninstall and get rid of it. It also allows us to ship these features individually on their own schedule week.
Video mid month every month, which is already very fast, but in the case of let's say community extensions or extension. Redgate there's a lot of overhead to telling those partners. You have to ship on the same day as us and you have to get into our ship room and get coordinated with us for security checks and everything by X date of the month.
If Agile development, they were wanting to do, and so by having these extensions on their own release schedule whenever the extension updates. You'll get a notification that there's a new version of the extension and that happens independently of the rest of the product also allows us to do things like put support for SQL Server 2019 in preview so we have this little preview flat here.
Azure Data Studio is now GA you can choose to enable or disable preview features at your own convenience and it's very easy for you to go and uninstall or disable them as necessary, so that's kind of the direction that we're going with extensions. I was going to take a look at our time.
I think I have time.
Would anybody like to see how to make an extension?
Yes, alright, OK, so extensions and Azure Data Studio can be like, I said as simple as sharing your snippets. Anas complexes full typescript applications or integrations with some of the you know third party applications and I'm going to show you today is something called a dashboard in sight, so if I go back to that.
Dashboard we were looking at a moment ago.
Pull it up.
I had this customer by sales. I'm going to go ahead and run query on this and this is similar to what we just saw have a chart going to do this hide this.
Now right here, I can hit this create insight button, it's going to create a Jason Document.
That describes what that chart is and you can see it's looking for the SQL query. In this case SQL query. One is this query. But I just ran so with these 2 components. I've got everything. I need to make a chart and embed it on a dashboard like I just showed so I'm going to do. The cooking show version of this so please bear with me to try to make it nice and fast what you would.
So there's a couple of prerequisites that you have to install they're listed on Github under this. Microsoft ignite lab under the wiki so if you go looking for it later. Just go to our wiki and go to ignite lab and we have a list of all of the prerequisites to do development but one of them. We have is this yo generator. So I do Yo Azara.
And let it run now in cooking show fashion because this takes a couple seconds to run. I went ahead and ran it over here already so over here, you can see we have a little. Welcome to the Azure data. Studio extension generator and I can pick the kind of extension that I want to create I'm going to make a dashboard in sight, but you can see I can also do color themes codes.
Mappings extension packs all sorts of things all the way to full typescript applications, so let's go over here to the dashboard insight.
Going to ask me do I want to add it full tab. I'll say yes? What's the name of my extension? Let's say.
Fits test.
We'll call it bits test description. This is Vicki's test.
And with my publisher name going to put Vicki Harp, which is the default.
And Oh I've already got one with that name so no, I'm not going to override it.
But the reason I have the conflict is because I've already created it so once you create this what the generator does is it creates the full VS code.
Application for you with a couple places. You need to fill in so actually. Let me see yeah here. It is so little create this and you can see this.
File format here we've got sequel, and has a query. It's got this file called Readme and it's got a package. JSON file uses pretty small. Let me make it bigger for you.
So here I take this insights that I had made earlier and I copy it over here.
So that's the JSON that's already been generated then I take my SQL that I've already created up here.
And I copy it.
Right over here to the SQL file.
And then I change the read me to say this is my sample application.
And then I run.
The SCE package let that run So what VSC package does is it then compiles this into something called AV 6 format. An now it's it's complete you can see it's created this package bits 2019 and then it has the version of 001, ready for me now. This has been created it's an extent.
To install and share with others so I can go here and say install from V 6. Go to my SQL bits. Go to bits 2019 and there's my extension so that's all it really takes to get started on a basic extension. I know I have flown through this. But it's because I just want to show you the broad gestures of the way you do it, mostly for the simple math.
Matter of making the file you want, which is a SQL file or you know a snippet file something like that, and then, adding a little bit of JSON decoration to say what it is, and then packaging it with the BSE Package Command. Once you've created it. You could put it on a file share you can share it with people by email, etc. If you wanted to publish it to the community. The common way to do that would be to put it.
And then send us a message or a pull request, saying we'd like to put this into your market place in which case it would then appear for everybody else who's using it available to install so this is another one that was created by the community and then contributed to Azure Data Studio so this is something that I want you to feel.
This tool is all about customization you can customize it for your own local workspace. You can customize it for your business. You can customize it for the type of data that you're working with and we want to know where you're finding that you have customizations that don't work for you, where you have things that you feel. You've made customizations that should be the default anyplace.
Errors in our judgment on the way that you are doing your work this tool is for you. This tool is something that we think about a lot and we release it very frequently. We've had people report bugs and had it fixed. The next morning. I include myself in this, I opened some bugs yesterday and they were fixed this morning so.
Is working and we're not working anywhere else other than github? We're working where you open your bugs is where we will fix the bugs you will be able to see what we're doing as we're doing it. So it's something that we're really proud of. Now, from there. I want to move into some of the things that we're adding for the next version of SQL server, and then good news is what we're adding right now is prime.
Available in Downlevel versions as well, I realized I was going to show the Poly base thing. But II might hold off on that. I think we're going to talk about notebooks instead, so here who here is familiar with notebooks wonderful who here saw the keynote in the SQL server notebook earlier alright well. We are wanting to spend a little time talking about that, so I'm going.
To hand this over to you for some.
Let me get over to your notebook or you.
And we're going to hand off the mic.
OK can you guys hear me so Vickie introduced me before from my name is Chris Lafreniere. I'm in engineering the SQL tools team and I've been working on notebooks in Azure Data Studio for 6 months almost now. I'm working on the sequel notebooks for last 2 or?
So great to be here super excited to show you guys, so this morning was actually literally. The first time we'd ever shown. SQL notebooks in public so this is fantastic and we're all the Super excited about it. So we have literally this was the Hello World of notebooks so hello.
For those of you who aren't familiar notebooks and I'm really excited that a number of you are familiar with it. Watch is quickly go through the concepts of what is it really this whole notebook thing is all about so in notebooks that we have this thing called the kernel which is a combination of the language.
That you're going to use In addition to the execution environment. So the execution environment can be localhost or it can be remote. One such as spark so for this example. We're talking to SQL and so we're using T SQL in this case and.
We're targeting the SQL server, so the attached to which is on the toolbar here. Basically, you can think of that for SQL as? What am I connected to here's the connection. So here we're just talking to a server that's local finally or In addition, we
These things called cells and So what is the cell in a notebook a cell is either a little bigger.
Is it good?
OK.
Yeah. Can you see that OK so itself, so notebooks have 2 types of cells we have text sounds, which are marked down and then we have code cells so in this case, the code you're writing is dependent on the kernel so in this case, it's equal so here's an example of a very simple.
Code cell In addition, I can add a markdown sell anywhere. I want in the notebook and so as you'll see whenever I'm writing something here in markdown. We have a live markdown preview, which is I think really cool.
And so you have all the markdown capabilities here, so it's not just oh. Let's create a list. Let's go make something bold. Let's do something like this we also have snippet support. Just like in the query editor. So here not vicki kindly set this up for us, we can have an animated GIF.
So I love that I think it's great, but OK so we have these things called cells. We have these code cells text cells. We have a couple of things for kernel on attached to what do you actually do with this and so?
When you run a code cell you'll see the results just below the cell so if we scroll back up after before this, you'll see that we have a table in this case, and then we'll have some of the messages that you're used to. When you're using SQL, so if we scroll past the guy for one last time so there is.
Right there and there are actually serialized so they are saved when you save a notebook so if you open up. The notebook in another viewer or in our view are you actually going to see the results right there, so this is an advantage over just the query editor where you say. Hey, I just wanted to share our SQL file and then you share with your coat?
Your mom, you know, whatever but in this case. You kind of have a historical record of exactly what the results were of various queries at that specific time, which is pretty awesome. Finally, the last thing that's actually on the toolbar as well is.
Trust and so by default when you create a new notebook or you open an existing notebook see how it says not trusted up here? What that means is that we're going to Sanitise. The HTML that may exist in a notebook and we just disable javascript so that whenever you just open a notebook.
Go haywire with you know, some malicious code. That's basically that means OK any questions, so far is that OK OK cool OK. So even though this concept of notebooks might be new in Azure Data Studio and for SQL people.
Really used to the SQL tools. We have a familiar editing experience. So here in a code cell notice how we have syntax highlighting. We also have intellisense support so let's see if I do this notice how we have connected support and we can literally just query the.
Server for various objects and then we have snippets as well. And so using the CH so basically everything that you're used to. In the query editor itself is just going to show up here and notebooks, which is pretty fantastic.
Um.
Finally, just in terms of editing you can edit a cell or you can create a new cell either by clicking the code plus text here at the top which is going to add. Append cells to the end of your document or when you're in the context of a cell you can either. Add cells before or after using these context menu.
OK, good so.
OK, cool alright. So let's get into the nitty gritty a little bit about how code separation really works and what it means to have code that's in one cell and what that means for subsequent cells as well so right now, you can essentially assume that between.
That there is a ghost statement in between each one so basically each cell is its own batch, so right now, we have an example here that shows what is possible and what our current limitations are so we have a set statement here with no count on or using a specific database for loading importers and notice.
A variable here and just doing a very simple query so I'll run this.
It executed fantastically we got a very simple result now.
If you declare a variable in one cell and you try to use it in a subsequent cell current our current implementation, essentially means that you can actually read the variable that you declared in a previous cell into subsequent self. So this is something that we know? Our customers really want and it's.
Log and we're going to get to it, and it's just something that we don't have quite yet. So here when I try to access that same variable that I declared previously it doesn't actually work 'cause. It says oh it can't find this variable.
Finally, if we re declare I well, we haven't set it to anything so notice that the value of I will be null here in a subsequent so good there.
OK, So what this means is that code cell execution does matter. Because notice even though I. The variables didn't persist. The set and the you statements did persis because we actually use the same connection for every single so.
OK.
So.
For those of you who use are in Java support in SQL server, which was introduced a couple of releases ago and now I think.
There is a generally there's an SP that I can never remember the name of in for how you like actually OK. I have to go and Google, it every time or Bing, it or you know, whatever and so I really just drives me crazy because I actually have to do this pretty often we in note.
Actually have the ability to declare different languages in cells that are different from whatever the kernel is So what this means is that for a particular cell you can say this cell is python and behind the covers were going to do is that in this case, we're going to call that SP that Ford.
With whatever you've put underneath that percent percent laying python thing, so here if we run this.
OK anyway, so this will take just a few a few seconds to run but I promise. It does do the right thing, fantastic, so see you'll see hello world.
So.
We've gone through a number of things, so far, So what do you actually use these things for like? Why do people care about notebooks really well here a couple things that we've come up with ourselves. But we really think that the wider community is going to come up with some things that we
Start up in a million years, but here's what we've come up with just a little sampling so first off a notebook can be used for a teaching tool. I mean look. We're doing right now, we're literally using a notebook to teach about notebooks, which we think is pretty awesome for business reporting and presentations. We actually use notebooks internally.
Talk about our weekly status when we're talking to management right now. In addition, we have notebooks for troubleshooting for deployments and other things like that, and if you'll take a look. I don't know if anyone is familiar with the Tiger tool box, but the Tiger tool box actually has a
Ready that they're going to be introducing or they've already introduced I guess that's in the notebook format and they're super excited about it because ability to show mark down in line with the code itself. They've they just absolutely love.
OK.
So.
So here's some examples if you wanted to sorry if you wanted to run. Here's a server information where board for example, so if one were to run. This you'll see OK. We have a couple of couple of tables here and we want to see.
Here's the number of servers that we have in our enterprise and so let's just say this information and then you can open it at a later time to be able to see exactly what the inventory was at that specific time.
Next we have an example business report that has a chart. So here you'll see that we have the tabular data here, but we also have this chart, which currently is an image because.
Because it's early, yeah, we just need a little more time on it. That's fine. But basically everything that we're going to be that we have in the query editor. Today, that Vicki was showing earlier when when we're talking about charting and exporting and various formats and everything we're going to bring all this stuff over to notebooks. It's just that you know it's March 1st.
Got a little bit of time, but this is just an example of how things will look in the future. Finally we just have a couple of more examples. Basically, this is A. The T SQL way of how do you just show the alphabet without actually you loop through the?
But without actually typing out the alphabet so here we go. Hey, we have a fantastic table here to show you next. We have DDL so not every we know that not every query is going to result in a table so this is something else. You can do and then fine.
Oh something bad happened, though this is crazy notice how the results get updated on demand. So yeah, that's basically what we got to show about notebooks for SQL any questions at the moment yes.
Oh, so the file format oh so we actually use the same file format that sorry that we use an IPYB file format? Which is generally used today, so the same file format already has.
Compatibility with Github and some with other viewers, so it's we're not doing anything crazy or not like we don't have our own clothes format. We're just trying to leverage was already used so.
It's so the question was is it like XML or Jason is Jason.
Yes.
Oh can you store notebooks in conflict so you can store notebooks anywhere, you want?
OK, so anywhere that you can store a file you can store notebook and we're not sure if confluence has a viewer or not, but you can definitely start there.
Yes.
Can you collaborate and share so the answer is right now we have the ability just to save it to a shared location and then you can have users go and edit and save it on themselves. This is one of those things that we'd like to invest more in the future.
Right now we have.
Yeah, and we do have source control that's true yes.
Had how do you pass the results of a query into another cell is everything into R or python so this is another thing that we're looking at right now. Currently, it's not really supported at the moment OK. So yeah, she's going to show some data frame.
With Ipython notebook, so maybe that'll that'll help a little bit.
Yes.
So the question was around MBX and so not quite it's not possible today Unfortunately.
Yes.
So the question was can you run this in SQL agent or SSIS? This is exactly the question that we want the answer is we are looking at it very closely. As of right now. On March 1st. The answer is we don't have anything but in terms of Operationalizing, a notebook this is something we are very interested in.
So we will be putting some decent effort into it in the next 2 or 3 months probably.
Yes.
Yeah.
Yeah, sorry figures just going to demo something quickly, but the answer is yes.
You couldn't hear their question was around stats statistics. And so, yeah, if I don't know if Vicki said. This or not yet but everyone can actually play with this. This is on the insiders build today, so if you go to the Azure Data Studio Github Page, which I believe that he's going to show off in a little bit.
And there are links to download this so OK any other OK well.
Yeah.
Yeah, so the so the question was around the best way to share snippets. So the best way probably would be to create an extension and have everyone use the same extension just have it installed everywhere.
Yeah.
Sorry oh save the notebook as HTML PDF.
Yes, so currently we don't have that functionality, but this is something that we know users want so it's we've we've thought about it? Yes.
Yeah.
So for internal published extensions can you have them auto update so?
Right so I don't I don't know either. Unfortunately, yeah, just to.
Yeah.
You can ask us later and we can definitely get an answer for you.
OK, thank you guys.
Not trying to kick you off but you only have the one headset and I wanted to be able to show the python ones so these are great questions and I think one of the things that some of these questions that were having about how does this work? How can I make this work? How you know this is the thing that I need please? Feel free to open issues on the Azure data stew.
Github and they can be issues, where you're asking questions. This is or you can tweet us, etc and will follow up with you. This isn't just something you have to answer here at the conference. I will show you that in just a minute how we do that. But overall this is something that is extremely hot off the press. This is like I said, just today's build and this is.
Into kind of jelling in the last couple weeks, so this is definitely in preview. I'd say along with the development of SQL Server 2019 is when you're going to start seeing some of these more advanced features coming around questions around. You know how to do a collaboration and sharing some of the charting features.
Things like that, and then we get into other things like the ability to clear all data. You can clear selves now but you know, maybe you want to do some more advanced things and so we're very much looking for your scenarios on that to make sure that whenever your first using this you find it helpful the IP William file format is?
Used file format in data science and so this is something that we're trying to play along nicely with the technologies that already exist. We're not trying to go off on our own this is something that we saw as we're working with data science and our colleagues in Microsoft and in the industry and so our file format is compatible and so.
We started with the SQL notebook, but I want to take you back to a more traditional ipython notebook to show you kind of wear a slightly more mature version of the notebooks is because ours is also a traditional python. Jupiter notebook so Jupiter is the name of the open source server for hosting these kernels and doing these execute.
SQL we didn't need something like Jupiter because basically we're already the SQL server tools that we have all of the context information. We need to be able to operating at SQL server. But in order to operate in some of these other languages were going to lean a little bit more heavily on open source so we ship by default with SQL PY spark spark.
Barks Kayla and python 3. Now, if you're not familiar with these I don't want you to feel like you have to be a lot of these are common in big data scenario, so there for big data clusters. This morning at the keynote. You might have noticed that there was a longer list because you're able to add additional kernels as necessary. So Anna, who is a data scientist wanted to have Julia so she had added Julia.
And packages and so in this demonstration. I'm going to show one of the python packages for data cleansing that's coming out of Microsoft Research. We use it internal to our tools and then we've created this demonstration notebook to show how the package works and we're also distributing the package with Azure Data Studio so that you can use it in your own.
So how many of you have used the flat file import wizard in SSMS Alright. I have a sense that a lot of you have used this because believe it or not. This is the most commonly used wizard in SSMS. You would think it might be something like a restore a backup because I don't know is actually the flat file import.
Only used either all of you are using it or some of you are using it a whole lot. So this is a wizard which if you haven't used it. Before I'm not going to belabor. The point and go through it, but it will allow you to choose a file and it will identify what the delimiters are and what the headers are and what the the column types are automatically.
The underlying technology of that comes out of Microsoft research and it's something called program. Synthesis, based on examples. So the file you're creating in this case is for the ML purposes. It's an example and what we're wanting to do is generate a program with a target of Outputing, a kind of language, so in this package going to go ahead and.
Warm it up, we've got this pros code accelerator package and you can see. I've done an import here. You can also do installation of packages in this case. I'm doing it in code. You can also do it on the server so I've got this artist dot TXT file. Let me open it up and show you what it looks like.
And it's pretty ugly so it's got the column names and we got a blank line. We've got this field, descriptor that doesn't mean anything. We've got these columns here and we've got hat delimited file so in pros were able to tell it hears this artist TXT file. I want you to learn it and output this is.
I used to read it so if I copy this.
And paste it here.
Run it.
Then I'm able to read that file immediately and it's been able to do all of the work for me, so this is an example going back to the How do you pass context from cell to cell this is using something called data frames so you can see this DF equals read file. DF dot head and then I can continue to use that data frame from cell to cell this is a concept.
Twitter notebooks and so as we are getting more into the SQL server notebook world. We're going to look into something like data frames and like other variables that you can set in the context of the notebook. We just have to work that out. SQL server is traditionally been a very disconnected experience and you know, debugging that and everything has been very much tell the server and it tells you and so we have.
Matt but that's definitely a goal of ours and president exists so we're not going to be going, it alone. We're going to be following the way that this is already done in other languages. If you want to play with this more. I'm going to get it up on Github at some point. This particular notebook. It could do a lot of really cool. Things and for the sake of time, I would be happy to show anybody at a demo.
Can do detection of file types of data types so in this case it was wise enough to workout that this field here? This wiki QID is actually an integer preceded by a queue so whenever I run. This you can see has a regex match and it removes the queue.
Is an integer so is able to make not just you know basic assertions about this? Looks like an integer but it's a this looks like it would be an integer if you remove this character and it's more likely that that's the case, whereas if you had a dollar sign there or a pound sign there. Something like that, it would identify it. This is probably a money type so it's going to be a little smarter than maybe.
In environment.
It's also able to do some pattern finding within the data. So I'm not going to run. This 'cause I'm again flying through. But on this you can see this artist bio column has 2 or 3 types of data embedded in it. So it's got column basically it's all comma comma comma separated values within a column.
Strep pros to do elearning on that column.
Find the patterns on artist bio and it's going to identify all the regular expressions that describe all of the patterns of data found within that which you can then turn into something to say. I want to identify the birth country from this so I only want this column of information out of that column of information and it's able to write that code for you, if you've ever had to do this.
It's an agonizing process so you been able to workout. Some of the file formats that are incredibly difficult to work on like this. This is a flat file and so you can see, there's really no delimiters whatsoever. It's basically columns. You know the column number and so it's able to make some good deductions about.
Based on schema files this can save you hours of work. So I'm showing you kind of both what the pros does and the fact that as a learning notebook and as a pattern generation engine. This is something that's available for Azure. Data studio so as you're working on notebooks. If you're using magics if you're using kernels if you're using iPod.
Things from the notebook community are things that we're going to be moving forward with an we're trying to add a little bit of the extra special sauce that you're running from a Microsoft product and that we're trying to make the installation process. A lot smoother than what you might have experienced if you ever tried to deal with python or Jupiter on your own so we're just about out of time now, so.
Quickly show you 2 things the Azure Data Studio is an open source project and so we do all of our coding. Really, in the open here at Github when you make this a little bit bigger. If you go to Azure Data Studio. The build that we're showing you today is as I said literally today's build and you can always access to.
By going down here to say try the insiders build from Master and these are auto generated everyday this is definitely up area that you might find regressions. You might find things that are fully broken and if you do, we would encourage you to open bugs. If you want a stable release. You can either go to the Microsoft. Docs page or you can go over to this, the releases tab.
The ones that we've we've more formally released either. The formal monthly release, which will auto update or occasionally will put kind of special insider builds that have been through a little bit more testing that we're putting out as a special request. Please help us test this before next month. We are very Fonda future requests and bugs so you can open them direct.
By hitting new issue and also I encourage you if you run into a problem in the product. You hit this button hit submit a bug or request. A missing feature and it will help you get started with your new bug on Github, which will honestly you will see that we will try to respond as quickly as possible, and will try to be as transparent as possible.
We're not trying to hide behind a veil if you're asking for something and we're not sure when we can get to it will ask you to find other people who need it. So we're at time. We are very happy to have had you all here and we're really looking forward to what you're going to be doing with your notebooks in with Azure Data Studio.
Microsoft booth and we would encourage you to help the organizers of this conference out by filling out your comment cards. They're not for us, there for them. They help them make this great conference and we're so happy to be here and we hope you have a great rest of the conference.