Change column type without data loss
Applies to: ✅ Azure Data Explorer
The .alter column command changes the column type, making the original data unrecoverable. To preserve preexisting data while changing the column type, create a new, properly typed table.
For each table OriginalTable
you'd like to change a column type in, execute the following steps:
Create a table
NewTable
with the correct schema (the right column types and the same column order).Ingest the data into
NewTable
fromOriginalTable
, applying the required data transformations. In the following example, Col1 is being converted to the string data type..set-or-append NewTable <| OriginalTable | extend Col1=tostring(Col1)
Use the .rename tables command to swap table names.
.rename tables NewTable=OriginalTable, OriginalTable=NewTable
When the command completes, the new data from existing ingestion pipelines flows to
OriginalTable
that is now typed correctly.Drop the table
NewTable
.NewTable
includes only a copy of the historical data from before the schema change. It can be safely dropped after confirming the schema and data inOriginalTable
were correctly updated..drop table NewTable
Example
The following example updates the schema of OriginalTable
while preserving its data.
Create the table, OriginalTable
, with a column, "Col1," of type guid.
.create table OriginalTable (Col1:guid, Id:int)
Then ingest data into OriginalTable
.
.ingest inline into table OriginalTable <|
b642dec0-1040-4eac-84df-a75cfeba7aa4,1
c224488c-ad42-4e6c-bc55-ae10858af58d,2
99784a64-91ad-4897-ae0e-9d44bed8eda0,3
d8857a93-2728-4bcb-be1d-1a2cd35386a7,4
b1ddcfcc-388c-46a2-91d4-5e70aead098c,5
Create the table, NewTable
of type string.
.create table NewTable (Col1:string, Id:int)
Append data from OriginalTable
to NewTable
and use the tostring()
function to convert the "Col1" column from type guid to type string.
.set-or-append NewTable <| OriginalTable | extend Col1=tostring(Col1)
Swap the table names.
.rename tables NewTable = OriginalTable, OriginalTable = NewTable
Drop table, NewTable
with the old schema and data.
.drop table NewTable