Edit

Share via

Change column type without data loss

  • Article

Applies to: ✅ Microsoft Fabric ✅ 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:

  1. Create a table NewTable with the correct schema (the right column types and the same column order).

  2. Ingest the data into NewTable from OriginalTable, 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)

  3. 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.

  4. 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 in OriginalTable 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

Related content