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)

1 comment:

  1. Servo Drive offers you long lasting, quality work that will not disappoint you. Reliability, safety and quality control are the advantages of this manufacturer who can give you the best products. It offers high flexibility in integration and data exchange, making it the ideal choice for today's industrial environments. So be sure to make your choice in its favor.

    ReplyDelete