kql - Kusto Query to update table column type with out impacting data

Question

I have a kusto table with id:int and name:string fields with data in it. I am trying to alter table schema types for id: int to id:long. I tried the below but it throws the below error. I also tried .alter instead of .alter-merge but no luck. What is the procedure to update Kusto table column type for existing table with data and without disturbing the current data?

.alter-merge table mytable
(Id: long, Name: string)

Error: 'Alter table does not support column data type change for existing columns (Id). Current type=I32, requested type=I64'.

question from:https://stackoverflow.com/questions/65915915/kusto-query-to-update-table-column-type-with-out-impacting-data

Answer 1

Here's the process you should follow to achieve what you want:

1. Create a new table named OldTable with the updated schema
2. Create a function named Table (should be exact name as your original table) that will return union (OldTable | project id = tolong(id), name), (Table | project id = tolong(id), name) - this way, whenever someone writes Table, he'll call the function that will return data from both tables in the correct schema
3. Swap Table and OldTable
4. When the data in the OldTable table ages out (at the end of the retention period), it will become empty, and you'll have to first delete the Table function, then the OldTable table