🪄KQLMagic Is Now In Fabric Runtime 1.3

Access and query Eventhouse in Fabric notebook

I wrote a blog last year on the usefulness of KQLMagic command in Fabric notebook and made a suggestion that it should be part of the default runtime. Well, guess what - it’s now in the Fabric Runtime 1.3. No installation necessary and authentication is handled automatically.

Here is how you use it:

  • Load the extension : %reload_ext Kqlmagic in a Fabric notebook.

  • Create a Python cell to define Kusto URI & database name:

      kusto_uri = 'https://xxxx.xx.kusto.fabric.microsoft.com'
      kusto_dbname = '<eventhouse_name>'
    

  • Create connection to the Eventhouse:

    •   %kql kusto://code;cluster=kusto_uri;database=kusto_dbname
      
  • Query using Kqlmagic:

    • The result is a Kqlrowset and not a pandas dataframe. To create a pandas dataframe, you can first assign the result to a variable and then convert to a dataframe which then can be used with any other data from Onelake.

    • This is great for ad-hoc analysis and exploration. If your result set is a large, I recommend using the Kusto spark connector instead as I showed here.

    • Configure Kqlmagic as required: Use %configure Kqlmagic to see the available configuration options. e.g. To limit the number of rows displayed, use Kqlmagic.display_limit. In the below example, the display is limited to 50 rows.

        %config Kqlmagic.display_limit=50
      

      If you have long, multi-line queries, use %%kql cell magic instead.

💡
Hopefully in the future there will be a KQL kernel in the notebook to connect to an Evenhouse and execute KQL queries. Azure Data Studio notebook already supports a KQL kernel.

Did you find this article valuable?

Support Sandeep Pawar by becoming a sponsor. Any amount is appreciated!

Â