How to use Append Variable activity in Azure Data Factory - Azure Data Factory Tutorial 2022

Topic: How to use Append Variable activity in Azure Data Factory 


In this article we are going to learn how to use append variable activity in Azure Data Factory, we are going to perform a real-time example in which what we will do, we will be emailing the list of the files which you have processed in your data factory so we will concatenate all those lists of the files and then email. let's start our demonstration.

First of all, open your Azure Data Factory studio, go to the author tab, click on the + button to create a new pipeline, then find and drag the Get Metadata activity.


Rename the Get Metadata activity for your convenience, then go to the Dataset tab, click on the + New button to create a new dataset


 Select Azure Blob Storage and then click on Continue.


Select the File format and then click on continue.


Name your dataset, select the linked service if you have already created, or create a new one, provide the input file path, select the none as import schema as per your requirement, then click on ok.


Go to the dataset tab, and select the Child Items in Field list, as we are going to read the list of the files.

Find and drag the ForEach Loop Activity, connect with the Get Metadata activity, go to the settings tab then click on the Add dynamic content.


Select the Metadata activity, expressions will be shown in the above box, then add ".Childitems" and then click on Ok



Go to the variable tab, click on the  + button to create a new variable, name your variable, and select the type.


Go inside ForEach activity, bring the wait activity or copy data activity as per your scenario, then find and bring the append variable activity, connect both of them then go to the Variables tab, select the name and click on Add Dynamic content.


Click on ForEach, then the expression will be shown in the above box, then add ".name" and click on Ok.


Outside the ForEach activity, find and bring the Set variable, then connect with the ForEach, then create a string type variable and then click on the set variable activity, go to the Variables tab, select the variable and then click on Add dynamic content.



Click on the variable name which we have created earlier, then add the curly parentheses around and click on ok.


Next. go to the Azure portal, find and open the Logic Apps, then click on the + Add button to create a new Logic app, in the basics tab select your Azure subscription, select the resource group, select the logic app type, name your logic app, select your region then click on Review + create and then click on the create.



Once the logic app is created, Open the resource and here we will setup the Email part, search for the "Request", then click on the request and then select http request.


Once the HTTP request is received click on the Use sample payload to generate schema.


In this window enter the expressions and then click on Done.



Click on the Method then select Post then click on Next.
.

Select the Email service, in my case it is Gmail, then provide a connection name, select the authentication type and click on Sign In, it will redirect to a new window and then you have to provide your user name and password for the email account. 




Once you are signed in, it will redirect you to another window where you have to provide the email address where you want to send the email, then provide and subject and the message the select the List of files variable from the drop down window, and then finally click on Save.



Once you are done with the email part, click on the HTTP box and then copy the HTTP POST URL link.


Next, go to the Azure Data Factory studio, then find and drag the web activity, then connect with the set variable activity, go to the settings tab provide the HTTP URL link, select the method, provide the body, then publish the pipeline, and then debug your pipeline.






Video Demo: How to use Append Variable activity in Azure Data Factory






How to use Union Operator in Kusto Query Language | Kusto Query Language

Topic: How to use Union Operator in Kusto Query Language 


In this article we are going to talk about union operator in Kusto and how it work there are a little changes or there are small differences of  a union operator in Kusto as compared to the SQL where you have uh less or more columnsKusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 create tables  
 .create table TotalSaleEurope (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,Region : string  
   )  
      //Insert data  
       .ingest inline into table TotalSaleEurope <|  
 12,Aamir,Nawaz,Cell Phone,2,1200,2015-03-03,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Europe  
 // Using Search operator  
 .create table TotalSaleAsia (  
   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 TotalSaleAsia <|  
 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  
 14,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,South America  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 // Using Search operator  
 .create table TotalSaleAsia (  
   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 TotalSaleAsia <|  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 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  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 // Using Search operator  
 .create table TotalSaleEurope (  
   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 TotalSaleEurope <|  
 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  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 // UNION OPERATOR  
 //Takes two or more tables and returns the rows of all of them.  
 //Union Multiple tables   
 // Table1 | union Table2,Table3,Table4...  
 TotalSaleEurope  
 TotalSale  
 | union TotalSaleAsia  
 //Using kind=outer -- > outer - (default). The result has all the columns that occur in any of the inputs. Cells that weren't defined by an input row are set to null.  
 union kind=outer withsource="TableName"  
 TotalSaleAsia,TotalSaleEurope,TotalSale  
 //Using kind=inner --inner - The result has the subset of columns that are common to all of the input tables.  
 union kind=inner withsource="TableName"  
 TotalSaleAsia,TotalSaleEurope,TotalSale  
 //Union Tables with Different Listof Columns  
 TotalSale  
 | union TotalSaleAsia,Customer  
 //Union Different Result Sets  
 TotalSale  
 | where Region=="Asia"  
 | union TotalSaleAsia  
 | union TotalSaleEurope   
 //Union Multiple Tables with specified string value  
 union TotalSale* | where * has "aamir"  
 //Add Table Name in output  
 union withsource="SourceTable" TotalSale* | where * has "aamir"  
 //Add a Source Table Name to output query   
 TotalSale   
 | union withsource="SourceTable" TotalSaleAsia,TotalSaleEurope  
 //Union Multiple Result Sets  
 union withsource="SourceTable"  
 (TotalSale | project SalePersonFName,SalePersonLName,Region) ,  
 (TotalSaleAsia | project SalePersonFName,SalePersonLName,SoldPrice),  
 (TotalSaleEurope | project SalePersonFName,SalePersonLName)   
 // Apply further Filter to your Union results  
 union withsource="SourceTable"  
 (TotalSale | project SalePersonFName,SalePersonLName) ,  
 (TotalSaleAsia | project SalePersonFName,SalePersonLName),  
 (TotalSaleEurope | project SalePersonFName,SalePersonLName) //| where SalePersonFName =="aamir"  
  | count  
 //Get the Table Name instead of union_age0,Union_arg1...  
 let TotalSaleDrv=view() {TotalSale | project SalePersonFName,SalePersonLName}; // Add a Column that does not include will produce results for only that dataset.  
 let AsiaTotalSaleDrv=view() {TotalSaleAsia | project SalePersonFName,SalePersonLName,Region};  
 let EuropTotalSaleDrv =view() {TotalSaleEurope | project SalePersonFName,SalePersonLName,Region};  
 union withsource=TableName TotalSaleDrv,AsiaTotalSaleDrv,EuropTotalSaleDrv  
.
Video Demo: How to use Union Operator in Kusto Query Language

IsNull and IsEmpty Functions in Kusto Query Language | Kusto Query Functions | KQL Tutorial 2022

 Topic: IsNull and IsEmpty Functions in Kusto Query Language


In this article we are going to learn about isempty and isnull function in custom isempty can be used to find out if a string data column value is empty isnull can be used for integer type columns so these both functions can help us to find empty or null valuesKusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //isempty and isnull  
 //isempty can be used to find out if string data column value is empty. is null can be used for integer type columns  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad  
 2,Raza,ALI  
 3,Lisa,  
 4,steve,Ladson  
 5,Robert,Jr  
 ,aamir,ali  
 // get all records so you can see empty rows  
 Customer  
 // get all records where integer type column has null values  
 Customer  
 | where isnull( CustomerId)  
 //get all records where string type columns are empty  
 Customer  
 | where isnull(LName) // this does not work on string type columns  
 Customer  
 | where isempty( LName)  

Video Demo: IsNull and IsEmpty Functions in Kusto Query Language 

Cross Cluster and Cross Database Queries | Join Data Across Different Databases in Kusto Query (KQL)

 Topic: Cross Cluster and Cross Database Queries 

in this video we are going to learn how to perform cross database and across cluster queries in Kusto so that's very easy actually and if you have come with the background with sql server or oracle and other databases you have used across databases queries often and often if you want to do cross data servers whether you do link server in a sql server and all that here you will not be creating link server you will be just pointing onto this cluster and you will be able to doKusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // cross database and cross cluster queries  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .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  
 // cross database queries  
 database("tbdb").TotalSale   
 TotalSaleAsia  
 | join database("tbdb").TotalSale on id  
 //Cross cluster queries  
 TotalSaleAsia  
 | join (cluster('techbrotheradx').database("tbdb").TotalSale) on id  

Video Demo: Cross Cluster and Cross Database Queries

Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

 Topic: Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

In this video we are going to learn about summarize  so summarize produce a table that aggregates the contents of input table with summarize we will be using a lot of functions such as count some and different other ones. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //summarize -- Produces a table that aggregates the content of the input table.  
 //  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary:int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
 // Get Count of all records ( Count of CustomerId)  
 Customer  
 | summarize TotalRows=count(CustomerId)  
 // get Sum of all salaries  
 Customer  
 | summarize TotalSalaryamount=sum(Salary)  
 // Get Count of All rows and Sum of Salary  
 Customer  
 | summarize CountofEmployees=count(),Totalsalary=sum(Salary)  
 // Get Count by Region  
 TotalSale  
 | summarize SalePersonCnt=count() by Region  
 // Group by Multiple Columns   
 TotalSale  
 | summarize CountofItems=count() ,sum(ItemsSold) by Region,Country  
 //using arg_max() -- > Finds a row in the group that maximizes ExprToMaximize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the most(max) Items.  
 TotalSale  
 | summarize arg_max(ItemsSold, Country) by Region  
 //using arg_min() -- >Finds a row in the group that minimizes ExprToMinimize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the least(minimum) Items.  
 TotalSale  
 | summarize arg_min(ItemsSold, Country) by Region  
 // using avg() - Calculates the average (arithmetic mean) of Expr across the group.  
 // Find out Avg sold price for each of product  
 TotalSale  
 | summarize avg(SoldPrice) by ProductName  
 //using max  
 TotalSale  
 | summarize  max(SoldPrice)  
 // using min   
 TotalSale  
 | summarize min(SoldPrice)  
 // get min sold price by Region  
 TotalSale  
 | summarize min(SoldPrice) by Region  
 // using sum -- Getting Sole price of all items  
 TotalSale  
 | summarize sum(SoldPrice)  
 // get sold price by Product  
 TotalSale  
 | summarize sum(SoldPrice) by ProductName  
 // take_any -- Arbitrarily chooses one record for each group in a summarize operator,  
 // and returns the value of one or more expressions over each such record.  
 TotalSale  
 | summarize take_any(ItemsSold) by Region,Country  
 // make_set --> Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group.  
 TotalSale  
 | summarize make_set(Region)  
 //dcount - Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.  
 TotalSale  
 | summarize count(City), dcount(City)   
 //make_list() -- Returns a dynamic (JSON) array of all the values of Expr in the group.  
 TotalSale  
 | summarize make_list(Region)  
 TotalSale  
 | distinct Region  
 | summarize make_list(Region)  
 // helpful link  
 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/hll-aggfunction  

Video Demo: Summarize Aggregate Functions in Kusto Query Language

How to Format Date and Time in Kusto Query | DateTime Format Function in Kusto

Topic: How to Format Date and Time in Kusto Query Language

In this article, we are going to learn about to format_date time functions so often we need to format the date and time according to our requirement maybe we want to create a report and export to excel and all that and we would like to export that date time specific formatKusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //format_datetime() -- Formats a datetime according to the provided format.  
 // This has alot of formats, check this out  
 //https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/format-datetimefunction  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary: int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
 Customer  
 | extend   
 SampleFormat1=format_datetime(hiredate,'y-M-d')  
 ,SampleFormat2=format_datetime(hiredate,'yy-MM-dd')  
 ,SampleFormat3=format_datetime(hiredate,'yyyy:MM:dd')  
 ,SampleFormatTime=format_datetime(hiredate,'h:m:s')  

Video Demo: How to Format Date and Time in Kusto Query

Kusto Query Language (KQL) Tutorial

 

  1. How to Install Azure Data Explorer and Start Writing Kusto Query
  2. Tools to Connect to Azure Data Explorer and Write Kusto Query
  3. Database Management Commands For Azure Data Explorer by using Kusto Query
  4. Data Types and How to Create Table by using Kusto Query
  5. How to Add or Remove Columns in Table by Kusto Query
  6. Docstring in Kusto Add descriptive information for Table, Column or Function Kusto
  7. How to move Tables in Folders and Subfolders by Using Kusto 
  8. How to Create Empty table by Using Definition of Another Existing Table in Kusto Query
  9. How to Create Table, Drop Table and Rename Table in Kusto
  10. How to Create Single or Multiple Tables in Single Statement by using Kusto
  11. How to get List of Tables, Schema of Table and Definition of Tables in Kusto
  12. How to Recover Dropped Table in Kusto
  13. How to Clear Table Data by using Kusto Query Language
  14. How to Change the Data Type of a Column in Table by Using Kusto Query 
  15. How to Drop Single or Multiple Columns from Table by using Kusto Query 
  16. How to Rename Column of a Table by Using Kusto Query 
  17. How to use StormEvents Sample Table for Kusto Queries
  18. How to Select Data from Table by Using Kusto Query
  19. How to use Where Clause in Kusto Query 
  20. Kusto String Functions in Kusto Query Language 
  21. How to use Search Operator in Kusto to find Records for specific Keywords
  22. How to Use Count Operator in Kusto Query 
  23. Kusto Limit Operator and Take Alias 
  24. How to use Find Operator to Search Data in Multiple Tables in Azure Data Explorer DB by using Kusto
  25. Print Operator in Kusto Query 
  26. How to Use Ago and Now Functions in Kusto 
  27. How to Use Sort Operator in Kusto 
  28. How to Use Datatable Operation in Kusto to Create Temporary Table 
  29. How to Use Distinct Operator in Kusto to Get Unique Records 
  30. How to Use Extend to Add Calculated Columns in Kusto 
  31. How to Use Top Operator in Kusto Query to Get Top N Records 
  32. Serialize Operator Add Row Numbers to Result Sets 
  33. Kusto String Functions with Case Sensitivity 
  34. Kusto Query String Functions with Not
  35. Case Function in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)
  36. How to use iif for IF ELSE in Kusto Query 
  37. How to Use Min and Max Function in Kusto Query 
  38. Kusto Query Using Sum and Sumif 
  39. Getschema Operator in Kusto Query Get Data Types of Columns of Table 
  40. Min and MinIf in Kusto Query | Azure Data Explorer 
  41. Kusto Query strcat How to Concatenate Columns in Kusto 
  42. How to Use Between and Not Between in Kusto Query 
  43. How to use project operator to select required columns in Kusto
  44. Next Function in Kusto Query 
  45. Parse Operator In Kusto Query 
  46. How To Use dccount get estimate in Kusto Query Language 
  47. Max and Maxif to get max values in Kusto Query Language 
  48. Using Let in Kusto Query language to Declare Variable, Functions and Views 
  49. How to Find Duplicate Records in Kusto Query Language 
  50. How to Calculate Running Total in Kusto | Row cumsum function in Kusto Query Language | KQL Tutorial
  51. How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial
  52. How to Generate Row Numbers in Kusto | Row Number Function in Kusto | Kusto Query Language Tutorial
  53. How to Use Row Rank Function in Kusto | Row Rank Function | Kusto Query Language Tutorial 
  54. How to Use To Scalar Function in Kusto | To Scalar Function in Kusto Query Language | KQL Tutorial
  55. How to Generate Series in Kusto | Kusto Make series function | Kusto Query Language Tutorial 
  56. Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial
  57. External Table Read Data From File Blob Storage in Kusto Query | Kusto Query Language Tutorial 
  58. Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language 
  59. Split Function in Kusto Query (KQL) How to split string into values in Kusto Query Language 
  60. Lookup Operator in Kusto Query (KQL) 
  61. Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query 
  62. Join Operator in Kusto Query | How to Do inner join ,Left Join, Right Join, Full Outer Join (KQL)
  63. DateTime part function in Kusto | How to get Year, Month and Day from DateTime 
  64. How to Format Date and Time in Kusto Query 
  65. Summarize Aggregate Functions in Kusto Query Language 
  66. Cross Cluster and Cross Database Queries | Join Data Across Different Databases in Kusto Query (KQL)
  67. IsNull and IsEmpty Functions in Kusto Query Language 
  68. How to use Union Operator in Kusto Query Language 

DateTime part function in Kusto | How to get Year, Month and Day from DateTime | KQL Tutorial 2022

Topic: DateTime part function in Kusto Query Language.

In this article, we are going to learn about datetime_part function, this function is very helpful and we can extract different parts of the data by using this function so it extracts the requested data part as the integer value. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //datetime_part -- Extracts the requested date part as an integer value.  
 Part supported  
 Year  
 Quarter  
 Month  
 week_of_year  
 Day  
 DayOfYear  
 Hour  
 Minute  
 Second  
 Millisecond  
 Microsecond  
 Nanosecond  
   

 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary: int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
   
 Customer  
 | extend   
 year=datetime_part('year',hiredate)  
 ,Quarter=datetime_part('Quarter',hiredate)  
 ,Month=datetime_part('Month',hiredate)  
 ,week_of_year=datetime_part('week_of_year',hiredate)  
 ,Day=datetime_part('Day',hiredate)  
 ,DayOfYear=datetime_part('DayOfYear',hiredate)  
 ,Hour=datetime_part('Hour',hiredate)  
 ,Minute=datetime_part('Minute',hiredate)  
 ,Second=datetime_part('Second',hiredate)  
 ,Millisecond=datetime_part('Millisecond',hiredate)  
 ,Microsecond=datetime_part('Microsecond',hiredate)  
 ,Nanosecond=datetime_part('Nanosecond',hiredate)  

Video Demo: DateTime part function in Kusto | How to get Year, Month and Day from DateTime | KQL Tutorial 2022

Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto

Topic: Join Operator in Kusto Query Language.


In this article, we are going to learn about a different types of joins in Kusto, we are going to learn about the inner join left join, and right join full outer join and some other types of joins will be learned in Kusto Query Language. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 // .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  
   
 // By default the type of Join is innerunique  
 Customer   
 | join Order on CustomerId  
   
 Customer  
 | join kind=innerunique Order on CustomerId  
   
 //using the column names  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
   
 // if we want to get the Records for other left record, then we can specify  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
 | where Name=="Raza"  
   
   
 // Inner Join will give us all the records  
 Customer  
 | join kind=inner Order on $left.CustomerId==$right.CustomerId  
   
 // show all records from Left , even no match in Right table  
 Customer  
 | join kind=leftouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from right, even no matching records in left   
 Customer  
 | join kind=rightouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from both tables , matching and not matching   
 Customer  
 | join kind=fullouter  Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from left table which have matching records in right table- display only left table columns  
 Customer  
 | join kind=leftsemi Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from right table which have matching records in right table - display only right table columns  
 Customer  
 | join kind=rightsemi  Order on $left.CustomerId==$right.CustomerId  
   
 //leftanti -- the records that does not match with right table but does exists in left table  
 Customer  
 | join kind=leftanti  Order on $left.CustomerId==$right.CustomerId  
   
   
   
 //rightanti -- shows the records from right table which does not exists in left table  
 Customer  
 | join kind=rightanti Order on $left.CustomerId==$right.CustomerId  
   
   

Video Demo: Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto

Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query Language

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


In this article, we are going to learn about the range operator, range operator is used to generate a single-column table of values. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etcfor exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, single-column The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //range operator : Generates a single-column table of values.  
 // syntax : range columnName from start to stop step step  
   
 //create sequence no  
 range Steps from 1 to 8 step 1  
   
 //create range of dates  
 range LastWeek from ago(7d) to now() step 1d  
   
   
 //create range of even numbers  
 range Steps from 2 to 100 step 2  
   
   
 //create range of odd numbers  
 range Steps from 1 to 100 step 2  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, Name: string)   
  .ingest inline into table Customer <|  
 1,Aamir  
 2,Raza  
 3,Lisa  
 4,steve  
 5,Robert  
   
 //you can use range output in many ways // try to get all records with odd and even CustomerId  
 range CustomerId from 1 to 5 step 2  
 | join kind=inner (Customer ) on $left.CustomerId==$right.CustomerId  
 | project CustomerId,Name  
   
   
   

Video Demo: Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query Language

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

How to Use Extend to Add Calculated Columns in Kusto | Kusto Query Language Tutorial (KQL)

Topic: How to Use Extend to Add Calculated Columns in Kusto Query Language.

In this article, we are going to learn about the extend operator that creates a calculated columns and append them to the result set. Azure Data Explorer is a fast, fully managed data analytics service for real-time analysis on large volumes of data streaming from applications, websites, IoT devices, and more. Ask questions and iteratively explore data on the fly to improve products, enhance customer experiences, monitor devices, and boost operations. Quickly identify patterns, anomalies, and trends in your data.

 // extend operator : Create calculated columns and append them to the result set.  
 .clear table TotalSale data   
 .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  
 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  
 id=Null,bisma,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  

 // extend operator : Create calculated columns and append them to the result set.  
 //get all columns with data from table  
 TotalSale  

 //add a calculated columns  
 TotalSale  
 | extend fullname=strcat(SalePersonLName,' ',SalePersonLName),DayofYear=dayofyear(SoldDate)  

 // extend the columns to result set  
 TotalSale  
 | project id,SalePersonFName,SalePersonLName,ItemsSold, SoldDate  
 | extend fullname=strcat(SalePersonLName,' ',SalePersonLName),DayofYear=dayofyear(SoldDate)  

Video Demo: How to Use Extend to Add Calculated Columns in Kusto | Kusto Query Language Tutorial (KQL)

Split Function in Kusto Query (KQL) How to split string into values in Kusto Query Language - 2022

Topic:  Split Function in Kusto Query (KQL) How to split string  into values in Kusto Query Language


In this article, we are going to learn about Split function in Kusto Query Language, Splits a given string according to a given delimiter and returns a string array with the contained substrings, optionally, a specific substring can be returned if exists. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.


 //split  
 // Splits a given string according to a given delimiter and returns a string array with the contained substrings.  
 //Optionally, a specific substring can be returned if exists.  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string,FileLocation:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,C:\Documents\Newsletters\Summer2018.pdf  
 2,Raza,ALI,D:\Documents\MyTestFileLocation\Tax_2019.pdf  
 3,Lisa,  
 4,steve,Ladson  
 5,Robert,Jr  
 ,aamir,ali  
   

   
 // split the values into an Array  
 Customer  
 | extend split(FileLocation,'\\')  
   

 // Let's divide the results into sub arrays  
 Customer  
 | extend DriveName=split(FileLocation,'\\',0), MainFolder=split(FileLocation,'\\',1),Subfolder=split(FileLocation,'\\',2),FileName=split(FileLocation,'\\',3)  
   
 
  
 //Let's get the value from the arrary and print in nice readable way  
 Customer  
 | extend MyArrary=split(FileLocation,'\\')  
 | extend DriveName=MyArrary[0],Mainfolder=MyArrary[1],SubFolder=MyArrary[2],FileName=MyArrary[3]  

Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language 2022

Topic:  Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language


In this article, we are going to learn about facet Operator in Kusto Query Language, facet operator returns a set of tables, one for each specified column, Each table specifies the list of values taken by its column an additional table can be created by using the ‘’With’’ clause. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.


 // facet operator : Returns a set of tables, one for each specified column. Each table specifies the list of values taken by its column  
 //. An additional table can be created by using the with clause.  
   
 TotalSale  
 | facet by SalePersonFName,SalePersonLName,ProductName  
   

   
 //Add new table by using with  
 TotalSale  
 | facet by ProductName,SalePersonLName  
  with (where Region =="Asia" | take 2 )  


Video Demo:  Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language

External Table Read Data From File Blob Storage in Kusto Query | Kusto Query Language Tutorial 2022

Topic: External Table Read Data From File Blob Storage in Kusto Query

In this article, we are going to learn about the Eeternaldata operator. The externaldata operator returns a table whose schema is defined in the query itself, and whose data is read from an external storage artifact, such as a blob in Azure Blob Storage or a file in Azure Data Lake Storage. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.


 // externaldata operator : The externaldata operator returns a table whose schema is defined in the query itself,   
 //and whose data is read from an external storage artifact, such as a blob in Azure Blob Storage or a file in Azure Data Lake Storage.  
   
 //Sample Code  
 externaldata (UserID:string) [  
   @"https://storageaccount.blob.core.windows.net/storagecontainer/users.txt"   
    h@"?...SAS..." // Secret token needed to access the blob  
   ]  
   
   //Generate SAS for File in Blob Storage  
 externaldata (Id:int,FName:string,LName:string) [  
   @"https://ashahzadblobstorage.blob.core.windows.net/input/Customer.txt"   
    h@"?sp=r&st=2021-11-26T18:08:41Z&se=2021-11-27T02:08:41Z&spr=https&sv=2020-08-04&sr=b&sig=zwx8IKwChrcCNwVi73fGrn29nXHlAbAfSz%2FAJHaYGwE%3D" // Secret token needed to access the blob  
   ]  
   with (ignoreFirstRecord=true)  
   
   

Video Demo: External Table Read Data From File Blob Storage in Kusto Query

Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

Topic: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg,


In this article, we are going to learn about series_stats, Series stats returns statistics for a numerical series using multiple columns. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.


 //series_stats - series_stats() returns statistics for a numerical series using multiple columns.  
 //   
 //The series_stats() function takes an expression returning a dynamical numerical array as input, and calculates the following statistics:  
   
 Statistic           Description  
 min               Minimum value in the input array.  
 min_idx             The first position of the minimum value in the input array.  
 max               Maximum value in the input array.  
 max_idx             First position of the maximum value in the input array.  
 avg               Average value of the input array.  
 variance           Sample variance of input array.  
 stdev             Sample standard deviation of the input array.  

   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
   
 .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  
   
 print x=dynamic([23,46,23,87,4,8,3,75,2,56,13,75,32,16,29])   
 | project series_stats(x), series_stats_dynamic(x)  
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | project Region,TotalItemsSold,series_stats(TotalItemsSold),series_stats_dynamic(TotalItemsSold)  
   

 // show only selected values  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | extend series_stats(TotalItemsSold),arryofvalues=series_stats_dynamic(TotalItemsSold)  
 | project Region,arryofvalues.min,arryofvalues.max,arryofvalues.avg  
   

Video Demo: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

How to Generate Series in Kusto | Kusto Make series function | Kusto Query Language Tutorial 2022

Topic: How to Generate Series in Kusto | Kusto Make Series Function 

In this article, we are going to learn about the ''Make series'' in Kusto Query Language, we will learn how to create a series of specified aggregated values along with a specified Axis. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.



 //make-series - Create series of specified aggregated values along a specified axis.  
 // Make series take values and create array out for those values.  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
   
 .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  
   
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | render timechart    
   
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0, avg(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region, Country  
 | render timechart   
   
   

Video Demo: