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