>> Did you know that Jupyter Notebooks can be automated?
Join this session to see
an example of how you can use Azure Data Studio
and other tools like Azure Logic Apps to automate your workflows.
This week on Data Exposed.
[MUSIC]
>> Hi,
I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today, I'm joined by Julie from the Azure Data Team at Microsoft.
Hi, Julie. Thanks for joining us today.
>> Hi Anna, nice to be here again today.
>> We're happy to have you here again on the show.
Always a pleasure to have you on to talk
about Notebooks and Azure Data Studio,
and lots of exciting stuff that you and the team are working on.
Today, we're going to be talking about
automating some Diagnostic Notebooks.
Can you just tell us a little bit more about why
that's important to know or what you want to show us today?
>> Sure, of course.
In the past, probably a few presentations when I come on
Data Exposed or other user groups or presentations.
I've been talking about using Notebook
specifically for diagnosis or for troubleshooting.
Today, I'm going to show you how
to string it all together so that you can
automate this Diagnostic Notebooks
or Troubleshooting Guides Notebooks.
Maybe it's for routine or regular basis or maybe
it's for perhaps on-call scenario.
>> Cool. That sounds exciting.
I love automating because it makes my life easier.
>> Sure, of course.
Let's get started with this one here.
This is a typical Diagnostic Notebook that someone has created.
This particular one is actually from Glen Barry.
Imagine you have queries like this or
Notebooks with queries like
this that you can execute on regular basis,
perhaps it's for baselining, etc.
But you could use this for on-call scenario.
Now let's take a look at this demo here, let me share my screen.
Imagine you're an on-call DBA and you get
tasked with issues or tickets like this where it says slow query,
and then it's against this database.
Imagine when it's assigned to you,
it will automatically pre-execute
the Diagnostic Notebook so that when you say,
for example, look at the ticket next,
you only need to open UP the notebook itself,
the Pre-Executed notebook,
and review the outcome instead of having
you manually do the execution.
Wouldn't that be nice?
>> Yeah, that sounds really nice.
>> On top of that,
you'll get an email.
Maybe you don't pay attention to the ticketing system perhaps,
but you get an email,
and then simply you click to view this in Azure Data Studio,
and then you'll get to see or review the pre-execute Notebooks.
>> Wow, that seems pretty cool, Julie.
My only question is,
is this hard to set up or was it easy and how did you do it?
>> It's actually pretty easy to set up.
We're actually using this system in-house as well within our team.
I'm going to show you a brief demo of
how you can do this on your own environment.
Let's check this out.
Here's some example of a ticketing system.
Imagine there is a task that
someone has created and maybe you are the person who's triaging.
Imagine it gets assigned to someone.
Say for now, let's just say it's assigned to me,
and then when it gets assigned to me,
it's going to kick off a sudden job actually,
and then it will do all the things that I mentioned earlier,
including the emailing and updating the Notebook.
This is going to take a couple of minutes.
While we do that,
let's take a look at how it works under the hood.
Here, what we have is Azure Logic Apps,
and this is where the brain or the orchestration happens.
When I click on the "Edit" button,
you'll be able to see something like
this where it shows you the workflow.
When a task is assigned to me,
in this case,
my demo ticket existence actually using Planet,
it will actually create
a job that is based on Azure Automation job,
and it will run a runbook called InvokeSqlNotebook.
This one, because it's actually pretty straightforward,
it's just a PowerShell script,
so if you're familiar with PowerShell,
you will love using this.
We will pass the parameter,
which is the title of
the issue so that we can get the ServerName,
that DataBaseName, and after that,
the rest of the workflow is simply updating back to
the planner tasks so that it has all the details to the link,
to the pre-executed Notebook or
the Output Notebook, and then after that,
the next step is simply emailing you the details like
the one that I showed you a little bit earlier.
>> Okay. It's actually pretty nice, isn't it?
>> I like that you didn't have to write a bunch of code.
That's the best part of Logic Apps for me.
>> Here's the InvokeSqlNotebook runbook that I mentioned earlier.
If I click on Edit here,
you'll see the PowerShell.
It's pretty straightforward actually simply,
it takes the title,
it will do some rejects thing and it
will just parsing say for example,
the Contoso DataBaseName and ServerName.
Then next, I'm actually using managed identity here to give
access to my database for this app to execute in my database.
But before that I need to download the Notebook first,
so we're just on GitHub.
The next step here is essentially,
to call the Notebook that I've just
downloaded and run it against
the ServerName database that I got from the title earlier,
and then simply just save it in an output,
and the output is going to be stored in Blob Storage.
Most of this part here,
that makes it sounds long or looks long,
it's actually to do with security,
and it's very important to take care of security.
The overflow today that
I've shown you is simply using Logic Apps,
and then Azure Automation account where
it hosts all the runbooks,
and the runbook is actually written in PowerShell,
and then store the Output notebook in a container.
that's essentially the workflow.
>> Awesome, cool.
It seems like you really mastered this.
Hopefully we can share some of your work later at the end.
>> Yeah, of course.
Let me just take a look here and see
whether the execution has been completed and it looks like it has.
Let me just bring it up. It's actually's
been emailed to me, as you can see.
Then if I go back to this and refresh it,
it looks like it also has been completed.
I have a link to the Notebook,
the Output Notebook,
and then if I click on this here,
it will open Azure Data Studio.
>> Wow, this is just going to make it a lot easier in
this scenario for a DBA to go and troubleshoot,
or in this case,
I think we were analyzing a slow queries so we can go
through and have this pre-run,
so we can just get to work without having to do
all the admin-related tasks.
>> Yeah, indeed.
Let me just scroll down a little bit just to show you here where
the query get executed inside the Notebook,
and then in the next cell here,
I purposely give enough access for
that app to run against my database,
that's why it's giving me the piece of this information.
That's just to show you a quick test that you
can manage the security
in this way so that you don't give all admin access.
You can say that I only want to give some access.
That's pretty cool, right?
>> Yeah, very cool. Julie, a lot of people,
when they're maybe doing
similar tasks or something gets assigned to them,
everyone has their toolbox of scripts,
and they're going to go to those scripts and
copy and paste this scripts
that they need depending on the problem that comes up.
How would you compare what you showed us today
to the Control C Control V method?
>> A very good question.
In the SRE world,
which is Site Reliability Engineering,
we used to consider Control C and Control V as toil.
It's like manual execution,
so instead of spending time on manual execution,
what if you can automate a lot of things so that your time is
better spent on solving better, bigger problems.
If I were to paint
the whole picture of what you could do in your environment,
it would look something like this where perhaps you have
a data engineer or DPA that
creates a Troubleshooting Guides Notebooks,
and you have them checked in
the Troubleshooting Guide Notebooks in GitHub or Azure DevOps.
Then you have someone like a support who might triage the issues,
and then you have someone like Gloria or DPA,
in this case, in this early demo, that's me,
who actually just simply enjoy all this automation output
and get back to work in terms of just reviewing what's going on,
what's wrong, and then mitigate it.
It's pretty nifty.
>> Awesome, that's really cool.
I love the story and I like that you played Gloria.
>> Just to tidy
all the concepts that we've probably been talking about,
Data Exposed and various
Azure Data Studio presentations when we talk about Notebooks,
we talk about manual execution through Azure Data Studio,
which is nice when you develop.
But remember that as much as possible,
if you can reuse the Troubleshooting Guides or those
Notebooks that you run routinely on manual mode,
if you can automate them as well,
that means you don't have to write a lot of things,
so hopefully your notebooks are reusable.
Then last stage is Automatable.
Earlier, I actually showed
you how to use Azure Logic Apps, but underneath,
it's actually essentially just really calling InvokeSqlNotebook,
and that is used for invoking SQL Notebooks through CLI,
or in this case, through Azure Automation.
You can also do it through Azure Function as well if you'd like.
If you have PowerShell Notebooks,
you could also invoke execute Notebook,
and if you're using Python Notebook,
then simply use Papermill.
That's it for me today.
>> Awesome. Julie, this has been so interesting.
I see you have a bunch of resources,
so we'll be sure to put those in
the description so folks can go learn
more and check out these samples and
start automating their workflows.
I just want to thank you so much for coming on the show,
and to our viewers,
if you liked this episode,
go ahead and give it a like, checkout Azure Data Studio,
checkout the automation capabilities,
and we hope to see you next time on Data Exposed.
[MUSIC]