Edit

Share via

Tutorial: Use a Fabric notebook with Kqlmagic to query a KQL database

Fabric notebooks allow you to create and share documents containing live code, equations, visualizations, and narrative text. It's useful for a wide range of tasks, such as data cleaning and transformation, numerical simulation, statistical modeling, data visualization, and machine learning.

Kqlmagic extends the capabilities of the Python kernel in Fabric notebooks so you can run Kusto Query Language (KQL) queries natively from notebook cells. You can combine Python and KQL to query and visualize data using the rich Plotly library integrated with the render operator.

For more information on notebooks, see How to use Microsoft Fabric notebooks.

In this tutorial, you learn how to use Kqlmagic to run advanced queries and visualizations from data in a KQL database. It uses pre-created datasets and notebooks in both the Real-Time Intelligence and the Data Engineering environments in Microsoft Fabric.

In this tutorial, you learn how to:

  • Create a KQL database
  • Get data
  • Import a notebook with Kqlmagic
  • Run the notebook

Prerequisites

1. Create a KQL database

In this step, you create an empty KQL database named NYCTaxiDB in your workspace or in an existing Eventhouse.

  1. Select your workspace from the left navigation bar.

  2. Follow one of these steps to start creating a KQL database:

    • Select New item and then Eventhouse. In the Eventhouse name field, enter NYCTaxiDB, then select Create. A KQL database is generated with the same name.
    • In an existing eventhouse, select Databases. Under KQL databases select +, in the KQL Database name field, enter NYCTaxiDB, then select Create.

  3. Select the NYCTaxiDB database, expand Database details, copy the Query URI and paste it somewhere, like a notepad, to use in a later step.

    Screenshot of the database details card that shows the database details. The Query URI option titled Copy URI is highlighted.

2. Get data

In this step, you use a script to first create a table with specified mapping, and then get data from a public blob into this table.

  1. Copy the KQL script from the Fabric samples repository on GitHub

    Screenshot of GitHub repository showing the KQL script for the NYC Taxi demo notebook. The copy icon is highlighted.

  2. Browse to your KQL database.

  3. Select Query with code to open an empty tab in the NYCTaxiDB_queryset.

  4. Paste the KQL script from step 1. and select the Run button.

    The first query creates the table and schema mapping. The output of this query shows the table and mapping creation information, including the type of command and the result of Completed when finished. The second query loads your data. It might take a few minutes for the data loading to complete.

    Screenshot of the queryset window showing the completed state of the table mapping and data ingestion.

  5. Refresh the queryset and select Tables to see an overview of the newly created table named trips2. From here you can expand the table schema, preview the data, and view query insights.

    Screenshot of the Tables tab showing the trips2 table.

3. Download the NYC Taxi demo notebook

Use a sample notebook to query and visualize the sample data you loaded in your KQL database.

  1. Open the Fabric samples repository on GitHub and download the NYC Taxi KQL Notebook..

    Screenshot of GitHub repository showing the NYC Taxi demo notebook. The option titled Raw is highlighted.

  2. Download the notebook locally to your device.

    Note

    The notebook must be saved in the .ipynb file format.

4. Import the notebook

The rest of this workflow uses Kqlmagic to query and visualize the data in your KQL database.

  1. In your Workspace, select Import > Notebook < From this computer.

    Screenshot of item options in Data Engineering. The item titled Import notebook is highlighted.

  2. In the Import status pane, select Upload.

    Screenshot of Import status window. The button titled Upload is highlighted.

  3. Select the NYC Taxi KQL Notebook that you downloaded in step 3.

  4. Once the import is complete, select Go to workspace and open this notebook.

    Screenshot of upload completed successfully and go to workspace.

5. Run the notebook

Select the play button to run each cell sequentially, or select the cell and press Shift+ Enter. Repeat this step for each package.

Note

Wait for the completion check mark to appear before running the next cell.

Screenshot of cell block showing import command. The Play button is highlighted.

Run the remaining cells sequentially to create a heatmap of NYC taxi pickups. For more information on Kqlmagic, see Use a Jupyter Notebook and Kqlmagic extension to analyze data

  1. The following cell aggregates all pickups within the specified geographic boundary.

    Screenshot of code cell showing aggregation query.

  2. Run the following cell to draw a heatmap of NYC taxi pickups.

    Screenshot of code cell showing query to create heatmap.

    In the resulting heatmap, you can see that most of the taxi pickups are in the lower Manhattan area. In addition, there are also many taxi pickups at JFK and La Guardia airport.

    Screenshot of notebook showing a heatmap of NYC taxi pickups.

6. Clean up resources

Clean up the items created by navigating to the workspace in which they were created.

  1. In your workspace, hover over the notebook you want to delete, select the More menu [...] > Delete.

    Screenshot of workspace showing the drop-down menu of the NYC Taxi notebook. The option titled Delete is highlighted.

  2. Select Delete. You can't recover your notebook once you delete it.

Related content