>> Did you know that you can parameterize your Notebooks in
Azure Data Studio for Python as
well as other kernels like PowerShell,
learn more in this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman, and
welcome to this episode of Data Exposed.
Today, I'm joined by Aaron Nelson.
Aaron, thanks for joining us again today.
>> Thanks for having me back, Anna.
>> Yeah. Always a pleasure to have you on the show.
Today, we're going to be talking about
automation and parameterization using Azure Data Studio,
and parameterization to Azure Data Studio Notebooks
is something that's relatively new.
I love Aaron, if you can tell our viewers a little bit about it,
why it exists, how to use it,
and then maybe show us a cool demo.
>> Sure. Absolutely. Jupyter Notebooks
have been gaining popularity in the SQL community,
PowerShell community all through .NET communities.
Always been a big thing in other communities.
But now we've taken on and learned a lot about it.
As we're progressing on that journey,
people have learned the basics
of Jupyter Notebooks and then Jupyter Books.
Now there's times where this ability to mix
the documentation and the code into
one single file that you can add someone is terrific.
If you're handing it off to someone who maybe is starting out,
like a new developer in your group
and they need to set up their environment,
so that they can go just as efficiently as
everyone else, these are terrific.
But then you get into certain scenarios where you need to
build something out and you need to do it repeatedly.
Maybe you're setting up some automated testing.
Maybe you're just trying to recreate
a scenario or just trying to build things out,
build pipeline, all kinds of uses.
But at the end of the day, you need to be able to
repeat what you're doing.
Sometimes you need to spin up five
of these things and they each need to have a different name.
That's great, except your Notebooks
previously in Azure Data Studio didn't have
this ability to accept a new name
and for you to be able to tell it what the name was.
With parameterization, we're able to tell it, "Hey,
here's your name," and go on to the next step and run with that.
>> Awesome. It's pretty cool capability.
Of course, I'm a huge fan of Notebooks
and Python and that sort of thing.
But I think today, you're actually going to show
us how do it in another language.
>> That's right. Yes. We have
the availability to do it with Python.
We can do these perimeters with T-SQL.
Today, I'm actually going to show you
how to do it with PowerShell,
one of the options that you've got available to you.
If we want to go over and take a look,
first thing I figure it's
probably relevant to start at the docs page.
If you need some more information,
a link for this will be down in
the show notes that you can take a look
at how to set up
your parameterization inside of Azure Data Studio.
Honestly, it's pretty straightforward.
You just come over here into Azure Data Studio.
When you find a cell that you want to
make into a parameterized cell,
you just come in here, you hit the "Ellipsis" and you
tell it that you want to make it a perimeter cell.
It's easy as that. From there,
depending on what technology
you're using to inject the parameters,
it may not even need to have
the perimeters right there in that cell declared.
It could be later on the script that they get picked up and used.
That's actually going to be the case
with what we're looking at here today.
For this first little demo that I want to give you,
we're going to take a look at just really quickly building out
a SQL-on-Docker instance and being able
to spin it up and give it a port number and an essay password.
As you can see down here on the bottom,
I've got everything set up for this.
I'm going to execute my Notebook down here.
It might be hard to see in the highlighting.
I'm going to pass it some parameters,
one of which is my essay password,
and the second of which is going to be
a string and it's going to used as my port number.
Because I deal with a lot of Docker instances,
it's also going to be part of my container name as well.
What I want to go ahead and do that,
all I have to do is go ahead and run
this. That's going to kick it off.
While this is working,
is really just coming in here and working on this notebook,
and it's running through the steps there.
Let me just take you another
one of the links that we've got in the show notes is
just the link over to where you can do this
yourself to get
some basic instructions are pretty straightforward.
Lets you know that you can just go ahead and get
the PowerShell module you'll need to run this command.
The invoke execute notebook is inside of here.
Then you can actually go ahead
and download this notebook with PowerShell, of course.
Then all you need is this one little crane,
which is basically what I'm doing right here.
This allows you to pass in those parameters,
so that you can pass in whatever you want for
your essay password and make
it for whatever your port number is and let you go on from there.
>> Aaron, this is pretty cool.
It seems like this is the ability to do PowerShell scripting,
like you would, have a script, pass parameters,
except now you can do it with
a Notebook which allows you some more,
maybe deeper error tracing or just a more detailed type of report.
Is that what I'm getting?
>> That's right. In addition to just being able
to execute the notebook and get the results back,
what you are also going to be able to see is inside of a Notebook.
It's going to save an output notebook that has everything,
so that you can go back and later on
verify that everything went right.
As you can see right now,
the demo that I was running, it failed.
I'm just going to go right ahead and see if I can
run it again and see if it'll work the second time.
But there it goes,
it looks like it actually worked that time.
When it gets done, all those results
are saved into an output Notebook.
I can go ahead and pull that up and take a look at it.
Like a week later, I need to go back.
As your saw down there's 26 different steps inside that notebook.
It's like, "Well, did 24 get done?
I don't remember it."
A week ago, two weeks ago,
I moved on to other stuff.
I don't remember. That's the great thing about Notebook.
You pop it open and tells you right there whether that step got
completed successfully or if there's
an error, like I got the first time.
It's right there in the error message inside yourself.
So all your documentation, right there.
>> Very cool. Aaron, when we think about this,
why do you think it's important for people to know about this?
>> It's because the popularity
of Notebook's definitely out there and you might be saying,
"Well, I've got a script.
Why do I really need to do anything more than this?"
Well, it's when you come back and you want to work with it again,
but then I don't know about you,
but I've had this situation where I've got one script,
but then I copy it and make it slightly different,
and I copy it again and make it slightly different.
Then I copy it again and make it slightly different.
Then I've got all these different versions laying around.
With this, I can have
a really good incentive to tighten
those reins back down a little bit.
Being able to parameterize a few things and be able to
use maybe just one version of the script.
That's actually what I'm down to when it
comes to building a Docker container.
One script, whether I'm building it on my machine,
on someone else's machine,
on machine up in Azure.
I've just got this one Notebook that I use and
I don't have to remember
all these different places where the different versions are.
All the documentation is inside the Notebook.
>> Yeah. No. I really like it.
I hadn't seen this before.
I have to play with it myself.
Was there anything else you wanted to share us today?
>> Yeah. Definitely. I've got one more demo for you.
I was doing a quick little on-prem demo for you,
just how to build out my test containers.
They're all working right now.
So they're up and running. Just to
give you a little lay of the land on those.
They not only spin up SQL Server on a Linux container,
but they will also go ahead and
download and restore the AdventureWorks 2016 database for you.
That's just an example, so that you can
take this script and use as
a template to download a backup out of your,
wherever your repo is and maybe restored
in your environment as like a dev instance.
Just to give you a template to work with that
will make it easy step-by-steps of how to do that.
Next up I want to show you one
for working with data up in the Cloud.
In this one, we'll take a look as
a bunch of Power BI assets that I want to take and I want
to go ahead and move them actually into
an Excel workbook that I wanted to
basically scrape up all my data governance information.
Just to give you a quick look at it here,
we're going to be up in Power BI.
I've got this Power BI tenant.
It's got some workspaces in there.
Some of these workspaces have
their reports inside of them and their datasets.
I want to go ahead and scrape
all that information and store it
somewhere for data governance reasons.
The really, really cool thing here is that I can use,
not only a PowerShell Notebook to get this information,
but using that same technique,
what I can do is I can go ahead and
login using the login-PowerBI commandlet,
connect to my tenant.
Once I'm authenticated through,
then I can just go ahead and execute
this command that is super long,
but executes that notebook and not only tells it to execute,
but also store the results into another notebook for results.
Now I'll go ahead and kick that off right now,
so we can get that running.
All that's done without having to make me re-authenticate.
I only come in, I did
login-PowerBI right before we started filming a few minutes ago.
Now it's still retain that login,
and it's able to connect,
use that Notebook, go grab
that information, and start stuffing it into my output for me.
As you can see, I've already run this notebook once before.
Let's go ahead and take a look at
those results that came out of my tenant.
I've got an Excel file that should have
all my information that I need with
everything from this notebook that ran,
all those run scripts that I executed.
Here we're working with my workspaces.
Here are their IDs.
Here are the names of those workspaces.
We can take a look further down
the datasets and the reports within those workspaces.
Now, I've just gone through and collected
all this information that I need for data governance reasons,
and I've been able to extract that all,
put it into a location.
In this case, I'm using cell for a demo,
but I could have just as easily written this to
an Azure SQL database or
something else and sort the results there.
>> Wow. This is really powerful.
You open up the doors of opportunities here where we're
not just dealing with Azure SQL database or SQL Server,
but also leveraging the power of PowerShell.
No pun intended.
We can do things with Power BI as well
or other services that have PowerShell enabled.
I think it's really interesting to show people
think outside the box of what's
possible when you look at
these PowerShell notebooks and parameterization.
Aaron, as people look at this video and want to get started,
what would be your advice?
>> I've got quite a few assets out there.
There's also assets built
into Azure Data Studio to get you started.
It's pretty easy. Start small.
Start with query in
your own local database or something like that.
There's plenty of information out there in the docs file.
Then there's plenty of community examples.
I'm not the only one doing this.
People are loving this.
This solves a lot of different issues that we've been having
for generation some IT workers.
Go out there, check out the samples.
People have got other better ideas than I've got.
I've just got some simple things here,
but there's some other workbooks out there that you can test out.
There's the best practices to check from the Tiger team.
There's all kinds of stuff out there.
Check out something that meets what you're trying to
do and give it a whirl. I bet you're going to love it.
>> Awesome. Well, thanks so much,
Aaron, for coming on the show again.
It was really useful for me and I think
our viewers are really going to enjoy it as well.
If you liked this video,
go ahead and like this video.
Give us a subscribe and leave us a comment and
let us know what you're doing with parameterization.
We hope to see you next time on Data Exposed.
[MUSIC]