Topic: How to Change The Type of a Column in Table by Using Kusto Query Language.
How to change the type of a column in a table by using Kusto Query | Kusto Query Language Tutorial (KQL) 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. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.
//Let's create sample table TotalSale and insert some data.
//.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.50,2015-03-03,Frankfurt,Hesse,Germany,Europe
9,Petra,Henry,TV,10,5000.89,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
//select data from table
TotalSale
// Let's say if we want to change the value of SoldPrice to int instead of real
//Syntax: .alter column ['Table'].['ColumnX'] type=string
.alter column TotalSale.SoldPrice type=int
//select data from table
TotalSale
// notice that the SoldPrice is blank for any history data.
// you can insert new data
.ingest inline into table TotalSale <|
100,Tamara,Tony,Cell Phone,2,1201,2015-03-03,Frankfurt,Hesse,Germany,Europe
900,Petra,Henry,TV,10,5001,2015-04-08,Paris,Île-de-France,France,Europe
//check the data
TotalSale
// How to keep the history data, as if we change the data type of column we are losing the data.
// we can make a copy of the table and then use union
//clear the table
.clear table TotalSale data
//Insert data
.ingest inline into table TotalSale <|
11,Tamara,Tony,Cell Phone,2,1200.50,2015-03-03,Frankfurt,Hesse,Germany,Europe
9,Petra,Henry,TV,10,5000.89,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
//check the data in TotalSale
TotalSale
// let's create History Total Sale table
.create table H_TotalSale(
id: int
,SalePersonFName: string
,SalePersonLName : string
,ProductName : string
,ItemsSold : int
,SoldPrice :real
,SoldDate: date
,City : string
,State : string
,Country : string
,Region : string
)
//Check the data for TotalSale and H_TotalSale
TotalSale
H_TotalSale
// swap the TotalSale table to H_TotalSale
.rename tables H_TotalSale=TotalSale, TotalSale=H_TotalSale
//Check the data for TotalSale and H_TotalSale
TotalSale
H_TotalSale
// Change the data type of column
.alter column TotalSale.SoldPrice type=int
// let's insert new records in TotalSale
.ingest inline into table TotalSale <|
100,Tamara,Tony,Cell Phone,2,1201,2015-03-03,Frankfurt,Hesse,Germany,Europe
900,Petra,Henry,TV,10,5001,2015-04-08,Paris,Île-de-France,France,Europe
// Get History Data and new data
//Check the data for TotalSale and H_TotalSale
TotalSale
H_TotalSale
// You will be using Union to get old and new data
H_TotalSale
| union TotalSale
No comments:
Post a Comment