Lookup Operator in Kusto Query (KQL) Kusto Query Language Tutorial 2022

 Topic: Lookup Operator in Kusto Query Language (KQL).


In this article, we are going to learn about the lookup operator in Kusto Query Language, the lookup operator extends the columns of effect table with the values looked up in the dimension table so the lookup operator pretty much works with the same as the join type. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //Lookup Operator: The lookup operator extends the columns of a fact table with values looked-up in a dimension table.  
 //kind: An optional instruction on how to treat rows in LeftTable that have no match in RightTable. By default, leftouter is used,   
 //which means all those rows will appear in the output with null values used for the missing values of RightTable columns added by the operator.  
 // If inner is used, such rows are omitted from the output. (Other kinds of join are not supported by the lookup operator.)  
   
 // .drop table Customer  
 // .drop table Order  
 .create table Customer(CustomerId:int, Name:string)   
   
  .ingest inline into table Customer <|  
 1,Aamir  
 1,Raza  
 2,Lisa  
 3,steve  
   
 .create table Order(OrderId:int, OrderName:string,CustomerId:int)   
 .ingest inline into table Order <|  
 1, 2 Laptops,1  
 1, 2 Computers,1   
 2, 10 TV ,2  
 3,10 cell phones,4  
   

 // Lookup works like Joins, if you have Fact and Dimension table then you can use between them  
 Customer  
 | lookup  Order on CustomerId  
   
 Customer  
 | lookup  Order on $left.CustomerId==$right.CustomerId  
   
 Customer  
 | lookup kind=leftouter Order on $left.CustomerId==$right.CustomerId  
   
 Customer  
 | lookup kind=inner Order on $left.CustomerId==$right.CustomerId  
   
 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/lookupoperator  

Video Demo: Lookup Operator in Kusto Query (KQL) Kusto Query Language Tutorial 2022

No comments:

Post a Comment