TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
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
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
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, etc. for 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
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
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)
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]
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
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
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
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
Topic: How to Use To Scalar Function in Kusto Query Language.
In this article, we are going to
learn about toscalar function, toscalar function returns a scalar constant
value of the evaluated expression, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.
//to_scalar()- Returns a scalar constant value of the evaluated expression.
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
// Let's get min and max value // study the error
let minid= (TotalSale | summarize min(id));
print minid
let minid= toscalar(TotalSale | summarize min(id));
let maxid= toscalar(TotalSale | summarize max(id)-5);
print minid,maxid
//use the value into next queries
let minid= toscalar(TotalSale | summarize min(id));
let maxid= toscalar(TotalSale | summarize max(id)-5);
TotalSale
| where id>minid and id<=maxid
// let's generate list of SalePersonFistName and use in query
TotalSale
| where SalePersonFName in ("Najaf","John")
let ListofNames=TotalSale | where id<5 | summarize makeset(SalePersonFName);
print ListofNames // it will not print because it is not scalar value
//Let's save a single scalar value then it should be able to print
let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));
print ListofNames // As it is scalar value, it should print fine.
// use this in clause
let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));
//print ListofNames
TotalSale
| where SalePersonFName in(ListofNames)
Video Demo: How to Use To Scalar Function in Kusto Query Language
In this article, we are going to
learn about row rank function in Kusto row rank function returns the current
rows rank in a serialized row set the row index starts by default at one for
the first row and it increments by one whenever the provided term is different
than the previous rows term. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.
//row_rank()- Returns the current row's rank in a serialized row set. The row index starts by default at 1 for the first row,
// and is incremented by 1 whenever the provided Term is different than the previous row's Term.
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
// Giving the rank to each Region
TotalSale
| order by Region
| extend Rank=row_rank(Region)
//let's try to play little more and include Country with it
// Giving the rank to each Region
TotalSale
| extend RegionCountry=strcat(Region,'',Country)
| order by Region,Country
| extend Rank=row_rank(RegionCountry)
Video Demo: How to Use Row Rank Function in Kusto | Row Rank Function
Topic: How to Generate Row Numbers in Kusto Query Language
In this article, we are going to learn
about row number function in Kusto so row number returns the index of the current row in a serialized, row set so the row number index starts with the default one, and then
if you want to increment by one that's what's going to happen by default but
there is an option you can start with the different number as well, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.
//row_number() Returns the current row's index in a serialized row set.
// The row index starts by default at 1 for the first row, and is incremented by 1 for each additional row.
//Optionally, the row index can start at a different value than 1. Additionally, the row index may be reset according to some provided predicate.
//Syntax
//row_number ( [StartingIndex [, Restart]] )
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
//Add RowNumber to results set
TotalSale
| order by id
| extend rn=row_number()
//Add RowNumber to results set by start from specific number
TotalSale
| order by id
| extend rn=row_number(100)
// let's see if we can use partiton of row_number
TotalSale
| order by Region,id asc
| extend rn=row_number(1,Region!=prev(Region))
Video Demo: How to Generate Row Numbers in Kusto | Row Number Function in Kusto
Topic: How to find Running Total in Kusto | Prev Function in Kusto Query.
In this video we are going to learn
about the previous function in Kusto, previous function returns the value of a
specific column in a specified row the specified row is a specified offset from
a current row in the serialized row set so there are three different options we
have with the previous we can simply provide the column or we can provide the offset
value as well or there is a third one if there is a default value you would
like to put for empty values, also you are going to learn how to find or
calculate the running total by entire table or by a specific group
//prev() - Returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set.
//Syntax
//prev(column)
//prev(column, offset)
//prev(column, offset, default_value)
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
//using next() to get next value from row
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(id)
//using next() to get next value from row -- using offset
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(id,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)
// using the third parameter to fill the blanks
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2,"Fill the Blanks")
// More real time scenario
//Running total by Region
TotalSale
| order by Region,ItemsSold
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold)
// Getting Running total for Region base
TotalSale
| order by Region,ItemsSold
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))
Video Demo: How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial
In this video we are going to learn
about row underscore com sum function this function calculates the cumulative
sum of a column in a serialized row set that means we can find the running
total, 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. A Kusto query is a read-only request to process data and return results.
// row_cumsum() - Calculates the cumulative sum of a column in a serialized row set.
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
//how to use running total in Kusto
TotalSale
| order by SoldDate asc
| serialize RunningTotal=row_cumsum(ItemsSold)
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,RunningTotal
//Running total by Region
TotalSale
| order by Region,ItemsSold
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))
Video Demo: How to Calculate Running Total in Kusto | Row cumsum function in Kusto Query Language
Topic: How to Find Duplicate Records in Kusto Query Language.
In this
article, we are going to learn how to find duplicate records in table by using Kusto
query Language, that's very simple and easy to find the
duplicate records, you can easily learn how to find duplicate records by using
the below provided scripts. 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. A Kusto query is a read-only request to process data and return results.
// How to find duplicate records in table by using Kusto Query
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
// Find duplicate records by
TotalSale
| summarize count() by id,SalePersonFName,SalePersonLName
| where count_ >1
TotalSale
| summarize rn=count() by tolower(SalePersonFName),tolower(SalePersonLName)
| where rn>1
Video Demo: How to Find Duplicate Records in Kusto Query Language | Kusto Query Language
Topic: Max and Maxif to get max values in Kusto Query Language
In this video we are going to learn about max and maxif functions, this function returns the max value across the group for which your predicate evaluates to true, 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.
// max and maxif - Returns the maximum value across the group for which Predicate evaluates to true.
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
//Get the Max value across table
TotalSale
| summarize max(SoldPrice)
// get max value for each Region
TotalSale
| summarize max(SoldPrice) by Region
//using max by using Region by using maxif
TotalSale
| summarize maxif(SoldPrice,Region=="Asia")
TotalSale
| summarize maxif(SoldPrice,ItemsSold>3)
Video Demo: Max and Maxif to get max values in Kusto Query Language | Kusto Query Language Tutorial KQL 2022
Topic: How To Use dccount get estimate in Kusto Query Language
In this article, we are going to learn about dccount function, dccount function returns, and estimate for a number of distinct values that are taken by scalar expression in the summary group, 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.
// dcount - Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
// get distinct rows
TotalSale
| distinct Country,City
// get distinct count by using distinct, Distinct is expencive when we have large amount of data
TotalSale
| distinct Country,City
| summarize CityCount=count(City) by Country
// we can use dcount to get estimated counts.
TotalSale
| summarize CityCount=dcount(City) by Country
//Accuracy Error (%) -- By default dcount uses 1 Accuracy
//0 1.6
//1 0.8
//2 0.4
//3 0.28
//4 0.2
// Second parameter let you set the accuracy
TotalSale
| summarize CityCount=dcount(City,0) by Country
TotalSale
| summarize CityCount=dcount(City,1) by Country
TotalSale
| summarize CityCount=dcount(City,2) by Country
TotalSale
| summarize CityCount=dcount(City,3) by Country
TotalSale
| summarize CityCount=dcount(City,4) by Country
Video Demo: How To Use dccount get estimate in Kusto Query Language | Kusto Query Language Tutorial KQL 2022
Topic: How to Use Parse Operator in Kusto Query Language.
In This article, we are going to learn about Parse operator, parse operator evaluates string expression and parses it value into one or more calculated columns so this is a very powerful operator that can help us to break the data into 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. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.
// Parse Operator : Evaluates a string expression and parses its value into one or more calculated columns.
.create table Customer (
id: int ,FName:string ,LName: string,address: string
)
.ingest inline into table Customer <|
1,Aamir,Shahzad,"streetAddress:126,city:San Jone,state:CA,postalCode:394221,othernote: this is fake address,weather:Good"
2,Raza,Ali,"streetAddress:1401 Anderson St,city:Charlotte,state:NC,postalCode:28205,othernote: this is fake address,weather:Ok"
3,List,Robert,"streetAddress:5817 Hunters Crossing Ln #19,city:Charlotte,state:NC,postalCode:28215,othernote: this is fake address,weather:Ok"
//.drop table Customer
//Check all records
Customer
// Let's parse Add Column into multiple columns House#,City,StateZip
Customer
| parse address with "streetAddress:" StreedAddress
",city:" cityName
",state:" stateCode
",postalCode:" ZipCode
",othernote:" othernote
| project id,FName,LName,StreedAddress,cityName,stateCode,ZipCode,othernote
.drop table Customer
//Let's add some garbage data into good data and try to parse
.create table Customer (
id: int ,FName:string ,LName: string,address: string
)
.ingest inline into table Customer <|
1,Aamir,Shahzad," This is address streetAddress:126,city:San Jone,state:CA,Some people call it zip code postalCode:394221,othernote: this is fake address,weather:Good"
2,Raza,Ali,"streetAddress:1401 Anderson St,city:Charlotte,state:NC,postalCode:28205,othernote: this is fake address,weather:Ok"
3,List,Robert,"This is downloaded from zillowstreetAddress:5817 Hunters Crossing Ln #19,city:Charlotte,state:NC,Some Countries don't use zipcode postalCode:28215,othernote: this is fake address,weather:Ok"
Customer
| parse address with * "streetAddress:" StreedAddress ",city:" cityName ",state:" stateCode:string "," * "postalCode:" ZipCode ",othernote:" othernote
| project id,FName,LName,StreedAddress,cityName,stateCode,ZipCode,othernote
Video Demo: Parse Operator In Kusto Query | Kusto Query Language Tutorial KQL 2022
In this article, we are going to learn about next function in Kusto Query Language next returns the value of a column in a row that is at some offset following the current row in the serialize row set, 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.
//next() - Returns the value of a column in a row that is at some offset following the current row in a serialized row set.
//Syntax
//next(column)
//next(column, offset)
//next(column, offset, default_value)
//create Table and Insert Sample Data in Azure Data Explorer DB for Testing
.drop table TotalSale
.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
//using next() to get next value from row
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,next(id)
//using next() to get next value from row -- using offset
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,next(id,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2)
// using the third parameter to fill the blanks
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2,"Fill the Blanks")
Video Demo: Next Function in Kusto Query | Kusto Query Language Tutorial (KQL) 2022
