How to Add or Remove Columns in Table by Kusto Query | Kusto Query Language Tutorial (KQL)

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  
   

Video Demo: How to Add or Remove Columns in Table by Kusto Query | Kusto Query Language Tutorial (KQL)