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 columns. 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.
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
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?
ReplyDeleteThe 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
ReplyDeleteI 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.
ReplyDeleteOne 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.
ReplyDeleteI studied programming at university. I really enjoy studying this subject. But it was hard for me to write a dissertation on this topic. That’s why I decided to buy dissertation and turned to specialists for help. I think there is nothing wrong with that. Thanks to this service I combined study, work and sports.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGreat article is exceptionally good and straightforward. This is precisely what I require. find ebay offer
ReplyDeleteExplore leading app development companies in the UK. Our curated selection showcases innovative teams that specialize in creating impactful apps, merging technology and creativity to deliver solutions that cater to your digital needs.
ReplyDelete