In April, I said I was going to start learning Jupyter Notebooks. It’s November. Let’s get going with your first Jupyter Notebook.
A quick aside before we start. I think one of the huge strengths that is going to come out of these things is as a runbook. You can share a notebook with someone, they can run the queries on it against their own systems and return the book, with the results to you. That’s going to be extremely useful as a troubleshooting tool, but has all sorts of other functionality as well. I strongly suggest you start learning these things, as I am.
Azure Data Studio
There are a number of ways to create and consume Jupyter Notebooks, but I want to focus on the functionality around data and data management, so I’m going to start in Azure Data Studio. You can just hit File, New Notebook to get started. It will open a window with a tool bar like this:
Starting from the top left, you see ‘Code’ and ‘Text’ with little plus signs. These are the fundamentals of how you start to work on Jupyter Notebooks. Next, you have to specify a Kernel. The default in ADS is SQL, so YAY! Then, you define the server to which you want to attach. I have a local instance, actually, a container, that I’m going to use. We’ll get to the other buttons later.
Now, I’m going to add some text by choosing that option and then typing in the window:
The top of the box is for typing and you can see the output below. To format the text, you have to use markdown commands (here’s a handy cheat sheet):
You can see how I’ve added a second level heading by using ‘##’ ahead of the text. It’s reflected in the output. After that, it’s just text and markdown. So, let’s add a code block.
Code Block
The code we add can be anything. In this case, I’m going to create a new database:
When you’re working with the Jupyter Notebook, it’s just the code you defined with the kernel, in this case T-SQL. Further, the Notebook’s code is using the editing environment you’re in, so, for example, we have the rudimentary type-ahead that ADS offers:
Yes, I desperately want Redgate SQL Prompt to work in Azure Data Studio. Please, contact them and let them know.
Anyway, you can see that there is a little “run” arrow to the left of the code box. Clicking that will, are you ready, run the code:
You can also take more control using the little ellipses button on the right:
Lots of choices, but for our purposes at the moment, focus at the bottom of the list on the “Clear Result” selection. That will remove the results from that section of code in the Jupyter Notebook. Also, if you look at the toolbox at the top, there’s a way to remove the results across the entire notebook. Again, think of this as a runbook or troubleshooting tool that you can use multiple times. It’s not simply documentation or code, it’s a functional document.
Adding Data
Since the commands you’re running through the Jupyter Notebook are just T-SQL, you can do anything. Here’s more that I’ve added to create a table in my database and put some data into it:
With data in the database and table, we can query it to return a result set to the Notebook:
And that is a fully functional ADS grid control, so everything you can do in ADS, you can do from that grid. Further, if you save the document after retrieving this data, the data will be stored with the document. This is how you can have, let’s say a remote client, run some scripts and send you back the results, without you ever needing to connect to their system.
Conclusion
Jupyter Notebooks have a lot of functionality. I’ve seen people embedding graphics in along with the text & code and using them for presentations. You’ve got a documentation tool, a teaching tool, and a way to walk inexperienced or junior people through specific processes. You can get all the results back yourself. I’m frankly surprised I’m not seeing a wider adoption of these things.
Now, I’ve walked you through the basics of creating a very simplistic Jupyter Notebook using the blog, text and graphics. However, if you’re hesitant to just pick up and start your own, go and download the Notebook I used to make this blog post from my GitHub location. You’ll notice I have started playing with these more, but I’ve got a long ways to go.