Let Operator in Kusto Query Language (KQL)

 Topic: Let Operator in Kusto Query Language (KQL)

In this article we are going to learn about let operator in Kusto, so uses the let statement to set a variable name equal to an expression or a function or  to create a view, so that's a very powerful and very helpful operator so let's go ahead and experiment this operator and see how it works in Kusto by using the below provided statements, in the bottom we have the Video tutorial for the Let Operator.

 

 // let operator: Use the let statement to set a variable name equal to an expression or a function, or to create views.  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
    

    
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
   

 // let's set a variable Region  
 let RegionName="Asia";  
 TotalSale  
 | where Region ==RegionName  
  

 
 // use multiple variables in query   
 let varCountry="Pakistan";  
 let varCity="Karachi";  
 TotalSale  
 | where Country ==varCountry and City ==varCity  
   

 // print the values  
 let varCountry="Pakistan";  
 let varCity="Karachi";  
 print strcat(varCountry,' ',varCity)  
   

 // Save entire data sets to variables and then use them.  
 let AsiaRegion= TotalSale  
 | where Region =="Asia";  
   
 let EuropRegion=TotalSale  
 | where Region=="Europe";  
   
 union withsource="TableName"  
 AsiaRegion,EuropRegion  
   
 

  
 // let can hold your function definition  
 let fullname=(FName:string,LName:string)  
 {strcat('MyFullName is:',FName,' ',LName)};  
 print fullname('Aamir','Shahzad')  
  

 
 //use the function for Table Results  
 let fullname=(FName:string,LName:string)  
 {strcat('CustomerFullName: ',FName,' ',LName)};  
 TotalSale  
 | project SalePersonFName,SalePersonLName,FullName=fullname(SalePersonFName,SalePersonLName)  
  

 
   
 // use let to create view  
 //Views are virtual tables based on the result-set of a Kusto Query Language query.   
 //Just like a real table, a view contains rows and columns. Unlike a real table, a view doesn't hold its own data storage.  
   
 let Range10 = view () { range IdColumn from toint(1) to toint(10) step 1 };  
 let TotalSaleofAsia = view () { TotalSale | where Region=="Asia" };  
 TotalSaleofAsia  
 | join kind=inner Range10 on $left.id==$right.IdColumn  
   

Video Demo: Using Let in Kusto Query language to Declare Variable, Functions and Views | Kusto Query Tutorial