Azure data explorer update record

Question

I am new to Azure data explorer and I am wondering how you can do update on a record in Azure data explorer using microsoft .NET SDK in C# ?

The Microsoft documentation is really poor

Can we update or you can replace a row only or you?

Answer 1

You can use soft-delete to delete the original record, and then append/ingest the updated record.

Please note that this won't be atomic, meaning if someone queries the table between the soft-delete and the append operations, they won't see neither the old record, nor the updated record.

Answer 2

there is no record "update" mechanism in Azure Data Explorer, even the 'soft delete' removes and replaces the row. this is useful for one-off scenarios, and may not be worth implementing in another language since it should not be used frequently. as the soft delete documentation says, if you plan to update data often, materialize may be a better option.

materialize is a bit more work and abstract, generally being worth the effort if you have a very large table that relies on metadata information like ingestion_time to make sense of records.

in smaller tables (say, less than a gig) i recommend the simple approach of replacing the table with an updated version of itself (just make sure that if you do rely on fields like ingestion_time, you update the schema and extend that data as a field for later use).

You will need to query for the entire table, implement logic for isolating only the row(s) of interest (while retaining all others, and perform an extend function to modify that value. Then, replace (do not append) the entire table.

For example: .set-or-replace MyTable1 <| MyTable1 | extend IncorrectColumn = iif(IncorrectColumn == "incorrectValue", "CorrectValue", IncorrectColumn)

alternatively, you can have the unchanging relevant data and updated data in two tabular results, and perform a union on them to form the final table.

.set-or-replace MyTable1 <| let updatedRows = MyTable1 | where Column1 = "IncorrectValue" | extend Column1 = "CorrectValue"; let nonUpdatedRows = MyTable1 | where Column1 = "CorrectValue"; updatedRows | union nonUpdatedRows

I prefer to write to a temp table, double check the data quality, then replace the final table. This is particularly useful if you're working in batches and want to minimize the risk of data loss if there's a failure halfway through your batches