Querying KQL Database in Fabric Notebook
Fabric Notebooks don't yet have KQL kernel but you can still query KQL databases in the notebooks using Python SDK
Table of contents
In Azure Data Studio, you can install the KQL extension to connect to and query Azure Data Explorer clusters. This allows you to access the KQL database and query/plot right in the notebook. Microsoft Fabric notebook does not have such an extension nor does it support a KQL kernel. But we can still use the Azure Kusto Python SDK to do the same thing. With the SDK you can write the KQL queries and create pandas dataframes for further analysis.
Steps:
In the Fabric notebook, install the Azure Kusto SDK
!pip install azure-kusto-data
!pip install azure-kusto-ingest ## this is optional
Next, we need three things - tenant ID, Kusto cluster URL and the Kusto DB name.
Tenant ID: You can get tenant ID from the spark configuration
AAD_TENANT_ID = spark.conf.get("trident.tenant.id")
Kusto Cluter URL and DB name : You can get this from the Kusto DB in Fabric directly.
Copy the Kusto DB name and the URI and assign it to global variables similar to tenant ID.
KUSTO_CLUSTER = "https://<cluster_id>.z0.kusto.data.microsoft.com"
KUSTO_DATABASE = "<kusto_db_name>"
Connect to the Kusto DB and query the database by writing the KQL directly in the notebook. You will be prompted to authenticate yourself using device authentication, i.e. click on the link provided and paste the code.
from azure.kusto.data import KustoClient, KustoConnectionStringBuilder
from azure.kusto.data.exceptions import KustoServiceError
from azure.kusto.data.helpers import dataframe_from_result_table
import pandas as pd
## Device authentication
KCSB = KustoConnectionStringBuilder.with_aad_device_authentication( KUSTO_CLUSTER)
KCSB.authority_id = AAD_TENANT_ID
KUSTO_CLIENT = KustoClient(KCSB)
## Write KQL query below
KUSTO_QUERY = """<write your KQL query here>"""
## Execute KQL
RESPONSE = KUSTO_CLIENT.execute(KUSTO_DATABASE, KUSTO_QUERY)
df = dataframe_from_result_table(RESPONSE.primary_results[0])
Note here that, unlike Azure Data Studio KQL extension, there is no KQL intellisense and you will primarily be analyzing the data using pandas.
You can also use username/password and AAD authentication.
## Using user name and password instead of device authentication
KCSB = KustoConnectionStringBuilder.with_aad_user_password_authentication(cluster, username, password, authority_id)
Using AAD app auth:
# AAD application
KCSB = KustoConnectionStringBuilder.with_aad_application_key_authentication(cluster, client_id, client_secret, authority_id)
You can read more about other authentication types supported and the SDK here.