Muokkaa

Jaa


Create and run a notebook with Kqlmagic

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

    Screenshot of a new notebook.

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

    Screenshot of the result - 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
    

    Screenshot of the list.

  4. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Note

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

    Screenshot of the 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.

    Screenshot of 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.

    Screenshot of the kernel change.

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Screenshot of the 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 sign-in 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.

    Screenshot of the Azure Data Explorer authentication.

Query and visualize for Azure Data Explorer

Query data using the render operator and visualize data using the plotly 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.

    Screenshot of the analyze storm events.

  2. Visualize a timeline chart:

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

    Screenshot of a time chart.

  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'
    

    Screenshot of a multiline Query sample.

Kqlmagic with Application Insights

Load and authenticate Kqlmagic for Application Insights

  1. Verify the Kernel is set to Python3.

    Screenshot of a kernel.

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Screenshot of loading 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 plotly library. This query and visualization supplies an integrated experience that uses native KQL.

  1. Show Page Views:

    %%kql
    pageViews
    | limit 10
    

    Screenshot of 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'
    

    Screenshot of the timeline chart.

Kqlmagic with Azure Monitor logs

Load and authenticate Kqlmagic for Azure Monitor logs

  1. Verify the Kernel is set to Python3.

    Screenshot of the change.

  2. Load Kqlmagic:

    %reload_ext Kqlmagic
    

    Screenshot showing to 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'
    

    Screenshot of the log analytics authentication.

Query and visualize for Azure Monitor Logs

Query data using the render operator and visualize data using the plotly 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'
    

    Screenshot showing the Log Analytics Daily Kubernetes Nodes timechart.

Next steps

Learn more about notebooks and Kqlmagic: