Topic: How to Add or Remove Columns in Table by Kusto Query | Kusto Query Language Tutorial (KQL)
How to Add or remove columns in the table by Kusto Query language | 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.
// How to Add or Drop Columns to existing table by using .alert-merge and .alter table
//.alter-merge
//Columns that don't exist and which you specify, are added at the end of the existing schema.
//If the passed schema doesn't contain some table columns, the columns won't be deleted.
//If you specify an existing column with a different type, the command will fail.
.create table MyTable(id:int)
//ingest some data into the table
.ingest inline into table MyTable <|
100
// Add two more columns
MyTable
.alter-merge table MyTable (Name:string, Address:string)
MyTable
//insert some data
.ingest inline into table MyTable <|
101,aamir,fake address
// run the statement with existing columns
.alter-merge table MyTable (Address:string,phonenumber:string)
//.alter table
//The table will have exactly the same columns, in the same order, as specified. Specify the table columns:
//If existing columns aren't specified in the command, they'll be dropped and data in them will be lost, like with the .drop column command.
//When you alter a table, altering a column type isn't supported. Use the .alter column command instead.
//.alter table works in little different way
.create table Customer(Customerid:int)
.ingest inline into table Customer <|
100
// check data
Customer
//check the schema of a table in Kusto
.show table Customer cslschema
.alter table Customer(customerName:string,address:string)
.alter table Customer(address:string,customerName:string)
// check the data
Customer
//ingest data
.ingest inline into table Customer <|
Aamir,fakeaddress1
//add new column phone number and make sure existing columns address and CustomerName do not drop
.alter table Customer(address:string,customerName:string,phonenumber:string)
//check the data
Customer
// ingest some data
.ingest inline into table Customer <|
Aamir,fakeaddress1,505-xxxx-0000
//check the data
Customer
//drop column phone number from Customer table, simple exclude the column from .alter table list you want to drop
.alter table Customer(customerName:string,address:string)
//Check if column dropped
Customer
No comments:
Post a Comment