In my previous post, I wrote about using Jupyter Notebooks to run the Data Migration Assistant. In this post I will show how to use a PowerShell module called PowerShellNotebook to make working with Jupyter Notebooks easier.
When I went to PASS Summit in 2019, I was excited about several things. SQL Server 2019’s general availability, a day-long SQL Server 2019 workshop with Bob Ward, and the chance to spend a week among some of the best minds in my field. I did not expect to be most excited about a tool. Then Bob Ward’s session introduced me to Azure Data Studio (ADS).
ADS is a database tool that runs on Windows, Mac, and Linux. It can connect to any instance of SQL Server on premises or in Azure. It can also connect to PostgreSQL instances. ADS contains an integrated terminal window, allowing use of PowerShell, bash, sqlcmd, and other command-line tools.
But the thing that I found most exciting was ADS support for Jupyter Notebook. Jupyter Notebook is an open-source web application that can combine formatted text, visualization, and executable code in one document. Bob Ward’s workshop used notebooks as the text for the class. I found having executable code inline with the lesson text to be an extremely powerful learning tool.
As I work with notebooks, I continue to find new applications for the technology. I have used then for training materials, runbooks, and documentation. I am currently converting various multi-step processes into single documents. The ability to document the code inline simplifies and clarifies the process.
While the ability to create Jupyter Notebook files in ADS is great, I recently discovered Doug Finke’s PowerShellNotebook PowerShell module. The module contains the following functions:
- Convert-MarkdownToNoteBook – Takes a markdown file and converts it to a Jupyter Notebook. Both text and code cells can be defined.
- ConvertTo-SqlNoteBook – Takes a T-SQL file and converts it to a Jupyter Notebook. Comments are rendered as text cells.
- Export-NotebookToPowerShellScript – Converts a PowerShell Notebook to a .ps1 file. Text cells are converted to comments.
- Export-NotebookToSqlScript – Converts a SQL Notebook to a .sql file. Text cells are converted to comments.
ConvertTo-SqlNotebook has been extremely useful in converting my existing script library to notebooks. Since November of last year, I have built all demo code for my presentations in notebooks. Convert-MarkdownToNotebook allows me to create the markdown code for text cells and copy and paste PowerShell code into code blocks. An example of markdown code and the resulting notebook is shown below.
Markdown Code
Each section requires the Chapter Start and Chapter End comments. To insert a PowerShell code cell, wrap the code in the ```ps-``` tags.
Note: The ps following the first tag will format the code as PowerShell. A list of available language formats can be found here. Test your desired language before using it in markdown.
Converted Notebook
I have used the Export functions to convert the notebook into native code. This allows me to make both available for download. While I recommend everyone learn ADS and Jupyter Notebook, I understand many people do not have the bandwidth to learn a new tool.
Doug Finke has a great introductory video to the PowerShellNotebook module here. As I said before, there is tremendous potential in Jupyter Notebooks. Many thanks to Doug Finke and Aaron Nelson for their work on the PowerShellNotebooks module.