With the release of Microsoft Fabric, we also got the ability to use the Kusto engine in the platform. Not only can we now use our Kusto database and all the great stuff from the KQL language – we can also store KQL querysets and use those on our reporting platform.
The new magic
All of the above abilities are great additions to the Fabric service. But whats even more exciting is the new magic in Jupyter Notebooks. Yes, you read it right – we can now write KQL language in our Notebooks with the use of the new magic command “%kql”.
Load the new extension to your notebooks
In order to use the kql magic in Notebooks, you need to import adn extension to your Notebooks. The kqlmagic extension gives you the ability to write KQL query code against your KQL database – in both Fabric and your ADX/SDX cluster.
Run the following code to import and reload your libraries:
Import kqlmagic to notebooks
!pip install Kqlmagic --no-cache-dir --upgrade
Reload the extension
%reload_ext Kqlmagic
Use KQL expressions in your Notebooks
After the import of the extension you are ready to connect to your Kusto cluster or database write KQL code against your data.
To connect to the Microsoft HELP cluster (the free cluter for all to play around with), you can use below snippet.
%kql azureDataExplorer://code;cluster='help';database='Samples' -try_azcli_login
NOTE the “-try_azcli_login” option is the flag type to try to login using your current credentials from the Azure portal. If that does not work, you will be prompted with a normal MFA login screen.
The complete list of login option flags are found here: Login flag options.
After the login to the HELP cluster, you can now write KQL queries in Notebooks as you would any other SQL, .NET, Python, R or Scala in Notebooks.
Example KQL code:
%%kql
StormEvents
| summarize eventcount=count() by State
| sort by eventcount
| take 10
| render barchart title = "Events by State"
Above KQL query reads data from the StormEvents table in the HELP cluster, takes the first 10 rows from a count of events grouped by state. Lastly it renders the data as a barchart with the title “Events by state” as seen below.
Take of from here
What about the result set from a KQL query you might ask? Well – thats a part of the coming posts, so stay tuned and read more about this in one of the next posts on this blog.
You can sign up at the bottom of the page “Get Notified of new posts”.
Until then, have fun with your KQL magic.
Find more blogs on the Learning Hub.
About the Author:
Hello there! I’m Brian. Technology Evangelist loving data and business intelligence. I’ve been working with data for the past 2 decades and I tend to see my work as my hobby – almost like a soccerplayer, just without all the injuries. Some people would call me geeky, I know I do😊.
I find myself in the breaking point between tech and business and try to help organizations to achieve the most out of their investments.
I love playing around with new stuff from Microsoft Azure and are also trying to keep an eye out for the business value.
When I’m not working, you can find me speaking all around the world or blogging about what I love – data. From this work, I’ve been so honored to have recieved the Microsoft Data Platform MVP Award.
I’m very honored to be given the certification FTRSA – Power BI (the first and only one in Denmark in 2022).
A bunch of Microsoft certifications on Data and Analytics – incl. MCSA and MCSE levels, CBIP from The Datawarehouse Institute in Data Analytics and Design and Leadership and Management Enterprise Architecture within TOGAF and project management from PRINCE2 is also in the books.
As a Microsoft Certified Trainer, I’m also fond of teaching and speaking.
You can see my latest and upcoming speaks and training sessions in the section Speaks.
I hope to see you around – either at the communities or on social media. Feel free to link up on LinkeIn.
Reference:
Bønk, B. (2023). Kusto in Fabric, with Magic. Available at: https://brianbonk.dk/blog/kusto-in-fabric-with-magic/ [Accessed: 25th September 2023].
