jupyter lab - Parametrize and loop KQL queries in JupyterLab

Question

My question is how to assign variables within a loop in KQL magic command in Jupyter lab. I refer to Microsoft's document on this subject and will base my question on the code given here: https://docs.microsoft.com/en-us/azure/data-explorer/kqlmagic

1. First query below

%%kql  
StormEvents  
| summarize max(DamageProperty) by State  
| order by max_DamageProperty desc  
| limit 10  

2. Second: Convert the resultant query to a dataframe and assign a variable to 'statefilter'

df = _kql_raw_result_.to_dataframe()  
statefilter =df.loc[0].State  
statefilter  

3. This is where I would like to modify the above query and let statefilter have multiple variables (i.e. consist of different states):

df = _kql_raw_result_.to_dataframe()  
statefilter =df.loc[0:3].State  
statefilter

4. And finally I would like to run my kql query within a for loop for each of the variables within statefilter. This below syntax may not be correct but it can give an example for what I am looking for:

dfs = [] # an empty list to store dataframes  

for state in statefilters:  
  %%kql  
  let _state = state;  
  StormEvents  
  | where State in (_state)  
  | do some operations here for that specific state   
  df = _kql_raw_result_.to_dataframe()  
  dfs.append(df)  # store the df specific to state in the list  

The reason why I am not querying all the desired states within the KQL query is to prevent resulting in really large query outcomes being assigned to dataframes. This is not for this sample StormEvents table which has a reasonable size but for my research data which consists of many sites and is really big. Therefore I would like to be able to run a KQL query/analysis for each site within a for loop and assign each site's query results to a dataframe. Please let me know if this is possible or perhaps there may other logical ways to do this within KQL...

question from:https://stackoverflow.com/questions/66056001/parametrize-and-loop-kql-queries-in-jupyterlab

Answer 1

There are few ways to do it.

The simplest is to refractor your %%kql cell magic to a %kql line magic. Line magic can be embedded in python cell.

Other option is to: from Kqlmagic import kql The Kqlmagic kql method, accept as a string a kql cell or line. You can call kql from python.

Third way is to call the kql magic via the ipython method: ip.run_cell_magic('kql', {your kql magic cell text}) You can call it from python.