Kqlmagic in Azure Data Studio

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.

    New Notebook

  2. You may be prompted to upgrade your Python packages when your packages need updating.

    Yes

  3. Install Kqlmagic:

    import sys
    !{sys.executable} -m pip install Kqlmagic --no-cache-dir --upgrade
    

    Verify it's installed:

    import sys
    !{sys.executable} -m pip list
    

    List

  4. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Note

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

    Load the Kqlmagic extension

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

    %kql --help "help"
    

    Note

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

    Help

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

    %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

Note

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

  1. Verify the Kernel is set to Python3.

    Kernel change

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Load the Kqlmagic extension

  3. Connect to the cluster and authenticate:

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

    Note

    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.

    Azure Data Explorer authentication

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:

    %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.

    Analyze storm events

  2. Visualize a timeline chart:

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

    visualize timechart

  3. Multiline Query sample using %%kql.

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

    Multiline Query sample

Kqlmagic with Application Insights

Load and authenticate Kqlmagic for Application Insights

  1. Verify the Kernel is set to Python3.

    Kernel

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Load the Kqlmagic extension

    Note

    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:

    %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:

    %%kql
    pageViews
    | limit 10
    

    Page Views

    Note

    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:

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

    Timeline Chart

Kqlmagic with Azure Monitor logs

Load and authenticate Kqlmagic for Azure Monitor logs

  1. Verify the Kernel is set to Python3.

    Change

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Load the Kqlmagic extension

    Note

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

  3. Connect and authenticate:

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

    Log Analytics auth

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:

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

    Log Analytics Daily Kubernetes Nodes timechart

Next steps

Learn more about notebooks and Kqlmagic: