Raymond Raymond

Use Python Kernel in Azure Data Studio Notebook

event 2021-09-12 visibility 1,111 comment 0 insights toc
more_vert
insights Stats

Azure Data Studio provide rich functions to connect to SQL Server. Its Jupyter Notebook enables you to perform all sorts of analytics in Jupyter Notebook easily. You can use run your books with SQL, Python, PySpark, Spark R, Spark Scala and other kernels. 

This article shows you how to run a SQL Server 2019 instance using Docker Desktop for Windows and then connect to it via Azure Data Studio.

Run SQL Server 2019 instance via Docker

Before you can run SQL Server image in docker, please install Docker Desktop. The version I'm using is the latest 4.0.0 version.

For 4.0.0, you can use WSL 2 as engine instead of relying on Hyper-V.

After installation is completed, run the following command to run SQL Server 2019 image:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@SS0rd!" -p 14330:1433 -d mcr.microsoft.com/mssql/server:2019-latest

We are mapping 14330 to 1433 in the container in case you have other SQL Server instances running on your computer. The image is the latest 2019 version. The password for supper account sa is set as "P@SS0rd!".

Test the connection

If you have sqlcmd tool installed on your computer, run the following command to verify:

sqlcmd -S localhost,14330 -U sa -P P@SS0rd!

20210912103047-image.png

Connect to SQL Server via Azure Data Studio

You can download and install Azure Data Studio from here: Download and install Azure Data Studio.

Open Azure Data Studio, click add connection button to establish a new connection.

Input the following details:

  • Server: localhost,14330
  • Authentication type: SQL Login
  • User name: sa
  • Password: P@SS0rd!

20210912103243-image.png

Click Connect button to connect to the server.

If the connection is successful, you can see the following window:

20210912103449-image.png

Create new notebook

Click New Notebook button to create a new notebook.

By default, the kernel is SQL and you can use SQL to query the data.

20210912103606-image.png

Use SQL kernel

Click + Cell button to insert a code cell with the following content:

select * from master.sys.tables;

Click F5 to run the cell and the output looks like the following screenshot:

20210912103901-image.png

You can click the chart icon above the result table to create charts on top of the result set. 

Use Python kernel

Create a new notebook with Python 3 as kernel. It will install Python for you or allow you to choose a local Python if it is already installed. 

20210912104800-image.png

You can install Python packages via Manage Packages icon (on the right top corner):

20210912105234-image.png

Use Spark kernels

You can also choose Spark as kernel if your SQL Server instance has big data cluster (BDC) feature enabled. For this container image, it is not supported. This is very useful when combining with SQL Server Machine Learning Services. 

Summary

Azure Data Studio is a modern tool with a focus with interactive analysis. It is much better than the traditional SSMS if you want to use it to perform all sorts of analytics with SQL Server.

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts