Markdown notebooks like Jupyter are all the rage for data science applications, especially given R and Python’s widespread use in advanced analytics. But what about good ole’ SQL? Let’s not forget about our basic SQL abilities to extract and analyze data.
Thankfully, Microsoft now supports creating SQL notebooks in Azure Data studio. Notebooks are a great way to create a repeatable data analysis report and share the results with others.
Why? The steps (code) are reproducible and auditable, so the analysis can be verified by other users. The SQL notebooks can also be added to source/version control. These features are useful when you compare trying to verify Excel analysis spreadsheets (good god!).
To create a SQL notebook for data analysis, first install Azure Data Studio. This program is supported by multiple operating systems, so you can even install Data Studio on [gasp!] a Mac.
Once you’ve installed it, open up the desktop program and select create “New Notebook”:
From here, you’ll want to attach a connection so you can start creating queries from a database:
In this example, I’m connecting to Microsoft’s SQL Server sample Data Warehouse, “AdventureworksDW”, but you would want to connect to whatever data set you’re trying to analyze:
From here, you can start adding code or text snippets. The code snippets are accomplished by writing regular old SQL, and the text snippets use Markdown language to accomplish text formatting.
For example, if you want to make a block of text a larger size to act as a header, you would type “# [TITLE GOES HERE]” and then that line of text would appear bigger in the notebook:
You can also add in SQL code snippets. These are written in SQL. When you press “run”, these scripts are then passed through to the database and the results returned directly under the SQL code snippet. In this example, I have created a query to return the top 10 product subcategories by Sales amounts:
You can continue adding additional text and code snippets as you please. The notebook file is then saved as a .ipynb file, and even formats nicely when uploaded to Github. You can view my example Azure Data Studio SQL Notebook below or on my Github page: