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

5 comments:

  1. You know, I was looking for the MBA Essay Help UK based service when I landed on your platform. I think this is the reason, I have landed here. So, I want to know, do you provide a finance assignment help service? If so, what are the average charges you charge to complete an assignment?

    ReplyDelete
  2. The specialists of our company thesis writer approach each work individually, trying to make it as interesting and complete as possible. Our lawyers will provide coursework with real examples from their own judicial practice, economists will supplement the work with the latest statistics, and the unusual presentation of information by practicing chemists will allow you to buy a course project that can interest even the most strict teacher. in original content

    ReplyDelete
  3. I was recently providing r programming help to a college student and found this database guide to be extremely helpful. It outlines the different functions of the r programming language and how to use them effectively. The guide also provides examples of how to use the r programming language to solve real-world problems. This was incredibly useful for me, as it allowed me to see how the r programming language can be applied in practice. As a result, I was able to provide better r programming help to the college student. I would highly recommend this guide to anyone who is looking for r programming help.

    ReplyDelete
  4. One and two months after the test, the PSEB board will release the PSEB 9th Question Paper 2023. Its examination has been held at various examination centres across Punjab since March. It will be announced on the PSEB 9th Question Paper 2023. Punjab 9th Exam Pattern 2023 On the Punjab board's official website, applicants may download the PSEB 9th Question Paper 2023 by syllabus and subject. PSEB 9th Board Question Paper 2023 is crucial for all students. Everywhere in school and for careers, 9th grade marks are essential. It is an essential exam for the future.

    ReplyDelete
  5. Brothers/Sister's. Are you looking for loan to finance your large or small business,we'll help you get the large amount of loan you desire for your business,we offer first class business and commercial loan to enable small scale business attain success in obtaining start up or refinance their business Bad credit rating accepted and poor business performance are accepted.apply today

    Are you a business man or woman? Do you need funds to start up your own business? Do you need loan to settle your debt or pay off your bills or start a nice business? Do you need funds to finance your project? We Offers guaranteed loan services of any amount and to any part of the world for (Individuals, Companies, Realtor and Corporate Bodies) at our superb interest rate of 3%. For application and more information send replies to the

    via email ronniefinancehome247@gmail.com
    Whats App +91 93118 56893
    LOAN APPLICATION FORM
    Name:
    Sex:
    Address:
    Occupation:
    State:
    Country:
    Phone Number:
    Loan Duration:
    Loan Needed:
    Have you applied before?:
    Email us ronniefinancehome247@gmail.com
    Whats-App +91 93118 56893
    Dr. Mark Thomas

    ReplyDelete