Kqlmagic in Azure Data Studio

• 10/29/2020
• 4 minutes to read

In this article

Kqlmagic is a command that extends the capabilities of the Python kernel in Azure Data Studio notebooks. You can combine Python and Kusto query language (KQL) to query and visualize data using rich Plot.ly library integrated with render commands. Kqlmagic brings you the benefit of notebooks, data analysis, and rich Python capabilities all in the same location. Supported data sources with Kqlmagic include Azure Data Explorer, Application Insights, and Azure Monitor logs.

This article shows you how to create and run a notebook in Azure Data Studio using the Kqlmagic extension for an Azure Data Explorer cluster, an Application Insights log, and Azure Monitor logs.

Prerequisites

Install and set up Kqlmagic in a notebook

The steps in this section all run within an Azure Data Studio notebook.

1. Create a new notebook and change the Kernel to Python 3.

   

2. When asked, select Yes to upgrade the Python packages.

   

3. Install Kqlmagic:

   Python

   !pip install Kqlmagic --no-cache-dir --upgrade
   

   Verify it's installed:

   Python

   !pip list
   

   

4. Load Kqlmagic:

   Python

   %reload_ext Kqlmagic
   

   If this step fails, then close the file and reopen it.

   

5. You can test if Kqlmagic is loaded properly by browsing the help documentation or by checking for the version.

   Python

   %kql --help "help"
   

   If Samples@help is asking for a password, then you can leave it blank and press Enter.

   

   To see which version of Kqlmagic is installed, run the command below.

   Python

   %kql --version
   

Kqlmagic with an Azure Data Explorer cluster

This section explains how to run data analysis using Kqlmagic with an Azure Data Explorer cluster.

Load and authenticate Kqlmagic for Azure Data Explorer

1. Verify the Kernel is set to Python3.

   

2. Load Kqlmagic:

   Python

   %reload_ext Kqlmagic
   

   

3. Connect to the cluster and authenticate:

   Python

   %kql azureDataExplorer://code;cluster='help';database='Samples'
   

   If you are using your own ADX cluster, you must include the region in the connection string as follows:
   %kql azuredataexplorer://code;cluster='mycluster.westus';database='mykustodb'

   You use Device Login to authenticate. Copy the code from the output and select authenticate which opens a browser where you need to paste the code. Once you authenticate successfully, you can come back to Azure Data Studio to continue with the rest of the script.

   

Query and visualize for Azure Data Explorer

Query data using the render operator and visualize data using the ploy.ly library. This query and visualization supplies an integrated experience that uses native KQL.

1. Analyze top 10 storm events by state and frequency:

   Python

   %kql StormEvents | summarize count() by State | sort by count_ | limit 10
   

   If you're familiar with the Kusto Query Language (KQL), you can type the query after %kql.

   

2. Visualize a timeline chart:

   Python

   %kql StormEvents \
   | summarize event_count=count() by bin(StartTime, 1d) \
   | render timechart title= 'Daily Storm Events'
   

   

3. Multiline Query sample using %%kql.

   Python

   %%kql
   StormEvents
   | summarize count() by State
   | sort by count_
   | limit 10
   | render columnchart title='Top 10 States by Storm Event count'
   

   

Kqlmagic with Application Insights

Load and authenticate Kqlmagic for Application Insights

1. Verify the Kernel is set to Python3.

   

2. Load Kqlmagic:

   Python

   %reload_ext Kqlmagic
   

   

   Every time you create a new notebook in Azure Data Studio you must load the Kqlmagic extension.

3. Connect and authenticate.

   First, you must generate an API key for your Application Insights resource. Then, use the Application ID and API key to connect to Application Insights from the notebook:

   Python

   %kql appinsights://appid='DEMO_APP';appkey='DEMO_KEY'
   

Query and visualize for Application Insights

Query data using the render operator and visualize data using the ploy.ly library. This query and visualization supplies an integrated experience that uses native KQL.

1. Show Page Views:

   Python

   %%kql
   pageViews
   | limit 10
   

   

   Use your mouse to drag on an area of the chart to zoom in to the specific date(s).

2. Show Page views in a timeline chart:

   Python

   %%kql
   pageViews
   | summarize event_count=count() by name, bin(timestamp, 1d)
   | render timechart title= 'Daily Page Views'
   

   

Kqlmagic with Azure Monitor logs

Load and authenticate Kqlmagic for Azure Monitor logs

1. Verify the Kernel is set to Python3.

   

2. Load Kqlmagic:

   Python

   %reload_ext Kqlmagic
   

   

   Every time you create a new notebook in Azure Data Studio you must load the Kqlmagic extension.

3. Connect and authenticate:

   Python

   %kql loganalytics://workspace='DEMO_WORKSPACE';appkey='DEMO_KEY';alias='myworkspace'
   

   

Query and visualize for Azure Monitor Logs

Query data using the render operator and visualize data using the ploy.ly library. This query and visualization supplies an integrated experience that uses native KQL.

1. View a timeline chart:

   Python

   %%kql
   KubeNodeInventory
   | summarize event_count=count() by Status, bin(TimeGenerated, 1d)
   | render timechart title= 'Daily Kubernetes Nodes'
   

   

Next steps

Learn more about notebooks and Kqlmagic: