When I first think about automation, I generally think in the following way: in order to automate a script, we want to ensure that the script itself can be run via a command line interface (CLI) and with almost no user interaction (except for input and output parameters). Now, how do we apply this to Jupyter Notebooks so that we can automate SQL notebooks or PowerShell Notebooks?
The good news is that these SQL notebooks and PowerShell notebooks that we’ve created using Azure Data Studio, can be run on PowerShell CLI. If these notebooks can be run on PowerShell CLI, that means any automation systems or serverless architecture (Azure Automation combined with Azure Logic Apps as an example) should be able to run these notebooks also.
In this blog post, I’ll cover examples on using Invoke-SqlNotebook, using Invoke-ExecuteNotebook and putting it together with Azure Automation.
Executing SQL Notebooks via Invoke-SqlNotebook
To execute SQL notebook, you’ll need to use Invoke-SqlNotebook. Here’s an example of running BPCheck notebook against SQLDEMO2019 server and database master.
Invoke-SqlNotebook -ServerInstance SQLDEMO2019 -Database master -InputFile '.\BPCheck.ipynb' -OutputFile 'BPCheck_output.ipynb';
In your automation code you can parameterize the SQL Server instance name and the database name. If you are using Azure SQL, no worries! It should work too, see an example here.
Executing PowerShell Notebooks via Invoke-ExecuteNotebook
For PowerShell Notebooks, you can do something even fancier with parameterization support. Essentially you can use the parameter inputs and use them as variables throughout your notebook. To do this, you’ll need to use Invoke-ExecuteNotebook from PowerShellNotebook module created by Doug Finke.
Useful resources:
- Learn more how to use Invoke-PowerShellNotebook here from Doug Finke’s video.
- Watch Aaron Nelson’s demo video on PowerShell Notebook parameterization in Azure Data Studio.
For PowerShell Notebook parameterization, don’t forget to make one of the cells as Parameter Cell. Read more about Set up Parameterized Notebook at Microsoft Docs.
Examples on Azure Automation to run PowerShell
Now that we have covered the basics, let’s try using Azure Automation runbook which supports PowerShell, to execute a SQL Notebook.
Check out this Azure Automation Tutorial where you can learn how to set up Azure Automation to run Invoke-SqlNotebook, and also use the code cell as a template to run DBDiagnostics (SQL) notebook stored on GitHub against an Azure SQL DB.
Watch From Oops to Ops: Incident Response with Jupyter Notebooks for Cloud Summit 2021 (http://azuresummit.live) which show cases Notebook automation with serverless architecture for Incident Response. Below is to illustrate the workflow of what you can do with automating notebooks in an incident response scenario:
Wrap up
As you have learned in this blog post, your SQL Notebooks and PowerShell Notebooks can be made reusable and automatable. Imagine your diagnostic notebooks can be included as part of CI/CD process and auto-testing!
For more feedback / questions on Azure Data Studio, feel free to submit them at https://github.com/microsoft/azuredatastudio/issues.
Looking forward to hearing what cool automated SQL or PowerShell notebooks you’ve come up with!
No responses yet