My old friend Aaron Nelson (@sqlvariant) told me about this back in the middle of last year – the concept of executing a Jupyter Notebook (.ipynb file) against a SQL instance and storing a copy of it (complete with the results of the various queries) in a separate file. Aaron and I have had a lot of conversations about SQL and PowerShell over the years, and Invoke-SqlNotebook fits nicely.
I don’t think I was particularly convinced right away. For a start, not many of my customers were using Azure Data Studio, which is probably the easiest way to look at Notebook files. But mostly, I didn’t consider I had really had a gap to fill.
The concept is that if I have a notebook with a bunch of queries in it, I can easily call that using Invoke-SqlNotebook, and get the results of the queries to be stored in an easily-viewable file. But I can also just call Invoke-SqlCmd and get the results stored. Or I can create an RDL to create something that will email me based on a subscription. And I wasn’t sure I needed another method for running something.
But the materialised notebook fits into a useful spot in between these. It gets created in an environment that is convenient for running queries, which is harder to argue for RDLs. And it contains multiple queries complete with nicely-formatted documentation, where the results of queries are displayed in amongst the text. SQL files created in SSMS don’t fit that description.
So these days one useful option that’s open to me is to schedule a call to Invoke-SqlNotebook, telling it to materialise a notebook which a bunch of useful queries, so that it can be stored somewhere in a file system and easily opened with the results. I can then give that to just about anyone to digest, because the combination of documentation and results can easily be made understandable and useful.
If there are actions to be made based on the results of queries, I’m still going to use PowerShell to run the queries, and then take further actions based on the results. But when I’m just wanting to create something that shows data and explains what it is – Invoke-SqlNotebook does the job. Well done Aaron.
This post fits into Steve Jones (@way0utwest)’s T-SQL Tuesday this month. For more on notebooks, go to Steve’s post and read some of the other posts linked to via the comments.
