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?
ReplyDeleteThis comment has been removed by the author.
DeleteThe 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
ReplyDeleteHighly recommended for any educator committed to upholding academic honesty. As an educator, I've found this plagiarism checker for teachers https://plagiarismcheck.org/plagiarism-checker-for-teachers/ to be an indispensable asset in promoting academic integrity. Its user-friendly interface and robust scanning capabilities make it a breeze to ensure that student work is original and properly cited. Beyond just identifying plagiarism, it serves as a valuable teaching tool, sparking discussions about the importance of attribution and responsible research practices. Integrating this tool into my classroom workflow has not only streamlined the grading process but has also empowered my students to develop critical thinking skills and a deeper understanding of ethical writing practices.
DeleteI 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.
ReplyDeleteUnderstanding the union operator in Kusto Query Language (KQL) is pivotal for comprehensive data retrieval and analysis. Incorporating this operator seamlessly merges datasets, aiding in efficient comparisons and aggregations. Leveraging the union operator optimizes data consolidation, a fundamental aspect in enhancing embroidery digitizing processes. Mastering KQL’s union operator elevates data manipulation proficiency, enabling tailored insights crucial for refining embroidery digitizing workflows, ultimately ensuring precision and quality in design conversions.
ReplyDeleteRECOVER STOLEN CRYPTO ASSETS-SYNACK HACKERS
ReplyDeleteThe last thing I expected to be able to do was recover a total of $ 57530 USDT lost in my account, but fortunately Synack Hackers, a special team of authorized hackers, came to my aid. I had to seek assistance from the team who were able to regain my account in a matter of 48 hours after it had been lost to an internet imposter who pretended to be a broker and had denied me access for more than a month. They provided amazing services and were a pleasure to work with. Should you require their assistance? Synack Hackers is reachable via [s y n a c k h a c k e r s @ p r o t o n (.) m e ]