Deleting individual records in a table
source link: https://techcommunity.microsoft.com/t5/azure-data-explorer-blog/deleting-individual-records-in-a-table/ba-p/3166847?WT_mc_id=DOP-MVP-4025064
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Deleting individual records in a table
Azure Data Explorer is a big data analytics platform that takes care of everything required to ensure real time decision making can take place, or at least, near real time. This includes data ingestion, data querying, data visualization and data management.
In this blog post you’ll learn how to delete individual records from a table, and how it works behind the scenes.
There are several scenarios in which you might want to delete individual records:
This blog post will explain how to delete individual records for the last scenario in the list above.
Scenario
A common scenario for deleting individual records is when you have a table with data coming from IOT devices, and one day you find out that some device has recently started reporting wrong data, and then completely broke and stopped reporting data at all. You then wish to delete the records from the table that were produced by the faulty device in the specific timeframe.
Suppose your company manages a large number of office buildings, and in every room of every building there’s a device that measures the temperature and sends it to a table named DeviceTemperature. Here’s a small data sample from the table:
DeviceId
Timestamp
Temperature
2022-01-17 19:58:03
19.53
2022-01-20 02:08:03
18.69
2022-01-20 05:41:03
20.56
2022-01-20 06:59:03
19.76
2022-01-20 23:37:03
19.79
You know that these thermometers sometimes break, and when they do, they start reporting extremely high temperatures for a while, and then they stop sending data at all. So, you created a script that runs a query once a day to detect such devices, and one day you see that the query returned a result indicating that one device broke:
DeviceId
Timestamp
avg_Temperature
2022-01-24 10:00:00
885.86
Your next step is to write a query that returns all the records that were produced by the faulty device, based on these results:
Deleting records by a predicate
To delete records, you’ll need to provide a predicate in the form of a KQL query, that returns all the records that you want to be deleted.
The recommendation is to first run the deletion command in `whatif` mode, in order to see how many records will be deleted if you use the provided predicate, but without deleting anything just yet.
OldExtentId
NewExtentId
RecordsMatchPredicate
405da552-04bf-4138-b764-5fbef6513f04
00000000-0000-0000-0000-000000000000
392d9b4d-0b21-40b1-a6af-10f9132c612c
00000000-0000-0000-0000-000000000000
14779dfc-e261-469e-826f-bbfde3836555
00000000-0000-0000-0000-000000000000
In the result above, you can see that 1857 records were returned by the predicate, and they are spread across three extents.
A quick note on what an “extent” is: Azure Data Explorer is built to support tables with a huge number of records (rows) and large amounts of data. To handle such large tables, each table's data is divided into smaller "chunks" called data shards or extents (the two terms are synonymous). The union of all the table's extents holds the table's data. An extent is a like a type of mini-table.
If the result makes sense to you, then it’s time to actually delete these records, by removing the `with (whatif=true)` part:
OldExtentId
NewExtentId
RecordsMatchPredicate
405da552-04bf-4138-b764-5fbef6513f04
3d93e17d-06d5-4efc-b2f0-209547bf687b
392d9b4d-0b21-40b1-a6af-10f9132c612c
7cb7e4fc-a7bf-4b83-babd-f92bb78568cf
14779dfc-e261-469e-826f-bbfde3836555
ad3e6a5e-dab7-406a-a5c7-cf5854aff4db
How do you read this result? Let’s look at the first record. It says that extent 405da552-04bf-4138-b764-5fbef6513f04 contained 548 records that were returned by the predicate. The command replaced this extent with a new one, 3d93e17d-06d5-4efc-b2f0-209547bf687b, containing all the records from the original extent, except the 548 records that were filtered out by the predicate.
Now when you query the DeviceTemperature table, you won’t get the records that you deleted.
How does this work behind the scenes?
The deletion process is performed using the following steps:
Summary
In this blog post you learnt how to delete individual records by providing a predicate in the form of a KQL query, and how the deletion process works behind the scenes.
Where can I learn more?
You can learn more about this feature here.
Feb 18 2022 05:14 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3166847%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EDeleting%20individual%20records%20in%20a%20table%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3166847%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EAzure%20Data%20Explorer%20is%20a%20big%20data%20analytics%20platform%20that%20takes%20care%20of%20everything%20required%20to%20ensure%26nbsp%3Breal%26nbsp%3Btime%20decision%20making%20can%20take%20place%2C%20or%20at%20least%2C%26nbsp%3Bnear%26nbsp%3Breal%20time.%20This%20includes%20data%20ingestion%2C%20data%20querying%2C%20data%20visualization%20and%20data%20management.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EIn%20this%20blog%20post%20you%E2%80%99ll%20learn%20how%20to%20delete%20individual%20records%20from%20a%20table%2C%20and%20how%20it%20works%20behind%20the%20scenes.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EThere%20are%20several%20scenarios%20in%20which%20you%20might%20want%20to%20delete%20individual%20records%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3CUL%3E%5Cn%3CLI%3EYou%20have%20multiple%20devices%20that%20continuously%20send%20data%20into%20a%20table%2C%20and%20you%20only%20want%20the%20latest%20record%20per%20device.%20For%20this%20scenario%20we%20recommend%20using%20%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fmaterialized-views%2Fmaterialized-view-overview%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EMaterialized%20Views%26lt%3B%5C%2FA%26gt%3B.%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fmaterialized-views%2Fmaterialized-view-overview%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EYou%20sometimes%20have%20duplicate%20records%20ingested%20into%20a%20table%2C%20and%20you%20want%20to%20remove%20the%20duplicates.%20There%20are%20several%20ways%20to%20handle%20this%2C%20please%20read%20more%20details%20in%20the%20%3C%2FA%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fdealing-with-duplicates%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EHandle%20duplicate%20data%26lt%3B%5C%2FA%26gt%3B%20document.%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fdealing-with-duplicates%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EYour%20customer%20asked%20you%20to%20delete%20the%20data%20you%20have%20about%20him%2C%20and%20you%20want%20to%20delete%20the%20relevant%20records%20from%20your%20table%2C%20including%20the%20storage%20artifacts%20in%20which%20the%20data%20is%20stored.%20For%20this%20scenario%20we%20recommend%20using%20%3C%2FA%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fconcepts%2Fdata-purge%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EPurge%26lt%3B%5C%2FA%26gt%3B.%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fconcepts%2Fdata-purge%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3EYour%20pipeline%20sometimes%20ingests%20corrupted%20data%20(for%20example%2C%20a%20faulty%20device%20sends%20corrupted%20telemetry).%20For%20this%20scenario%20you%20would%20want%20to%20delete%20these%20specific%20records%2C%20given%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query.%20This%20blog%20post%20will%20explain%20how%20to%20achieve%20this%20with%20soft-delete.%26lt%3B%5C%2FLI%26gt%3B%5Cn%26lt%3B%5C%2FUL%26gt%3B%5Cn%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EThis%20blog%20post%20will%20explain%20how%20to%20delete%20individual%20records%20for%20the%20last%20scenario%20in%20the%20list%20above.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSTRONG%3EScenario%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EA%20common%20scenario%20for%20deleting%20individual%20records%20is%20when%20you%20have%20a%20table%20with%20data%20coming%20from%20IOT%20devices%2C%20and%20one%20day%20you%20find%20out%20that%20some%20device%20has%20recently%20started%20reporting%20wrong%20data%2C%20and%20then%20completely%20broke%20and%20stopped%20reporting%20data%20at%20all.%20You%20then%20wish%20to%20delete%20the%26nbsp%3Brecords%20from%20the%20table%20that%20were%20produced%20by%20the%20faulty%20device%20in%20the%20specific%20timeframe.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3ESuppose%20your%20company%20manages%20a%20large%20number%20of%20office%20buildings%2C%20and%20in%20every%20room%20of%20every%20building%20there%E2%80%99s%20a%20device%20that%20measures%20the%20temperature%20and%20sends%20it%20to%20a%20table%20named%20DeviceTemperature.%20Here%E2%80%99s%20a%20small%20data%20sample%20from%20the%20table%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3CTABLE%3E%5Cn%3CTBODY%3E%5Cn%3CTR%3E%5Cn%3CTD%3E%3CP%3EDeviceId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ETimestamp%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ETemperature%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3EA%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-17%2019%3A58%3A03%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E19.53%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3EB%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-20%2002%3A08%3A03%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E18.69%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3EC%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-20%2005%3A41%3A03%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E20.56%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3EA%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-20%2006%3A59%3A03%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E19.76%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3ED%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-20%2023%3A37%3A03%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E19.79%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%26lt%3B%5C%2FTBODY%26gt%3B%5Cn%26lt%3B%5C%2FTABLE%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EYou%20know%20that%20these%20thermometers%20sometimes%20break%2C%20and%20when%20they%20do%2C%20they%20start%20reporting%20extremely%20high%20temperatures%20for%20a%20while%2C%20and%20then%20they%20stop%20sending%20data%20at%20all.%20So%2C%20you%20created%20a%20script%20that%20runs%20a%20query%20once%20a%20day%20to%20detect%20such%20devices%2C%20and%20one%20day%20you%20see%20that%20the%20query%20returned%20a%20result%20indicating%20that%20one%20device%20broke%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348289i63CB41A5AC74213B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22slavikn_1-1644920365209.png%22%20alt%3D%22%5C%26quot%3Bslavikn_1-1644920365209.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3CTABLE%3E%5Cn%3CTBODY%3E%5Cn%3CTR%3E%5Cn%3CTD%3E%3CP%3EDeviceId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ETimestamp%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3Eavg_Temperature%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3EE%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E2022-01-24%2010%3A00%3A00%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E885.86%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%26lt%3B%5C%2FTBODY%26gt%3B%5Cn%26lt%3B%5C%2FTABLE%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EYour%20next%20step%20is%20to%20write%20a%20query%20that%20returns%20all%20the%20records%20that%20were%20produced%20by%20the%20faulty%20device%2C%20based%20on%20these%20results%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348290i3778E4CAFA78DF67%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22slavikn_2-1644920412833.png%22%20alt%3D%22%5C%26quot%3Bslavikn_2-1644920412833.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3E%26nbsp%3B%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3EDeleting%20records%20by%20a%20predicate%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20delete%20records%2C%20you%E2%80%99ll%20need%20to%20provide%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query%2C%20that%20returns%20all%20the%20records%20that%20you%20want%20to%20be%20deleted.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EThe%20recommendation%20is%20to%20first%20run%20the%20deletion%20command%20in%20%60whatif%60%20mode%2C%20in%20order%20to%20see%20how%20many%20records%20will%20be%20deleted%20if%20you%20use%20the%20provided%20predicate%2C%20but%20without%20deleting%20anything%20just%20yet.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348291iD055647B4F760456%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22slavikn_3-1644920457950.png%22%20alt%3D%22%5C%26quot%3Bslavikn_3-1644920457950.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3CTABLE%3E%5Cn%3CTBODY%3E%5Cn%3CTR%3E%5Cn%3CTD%3E%3CP%3EOldExtentId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ENewExtentId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ERecordsMatchPredicate%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E405da552-04bf-4138-b764-5fbef6513f04%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E548%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E392d9b4d-0b21-40b1-a6af-10f9132c612c%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E19%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E14779dfc-e261-469e-826f-bbfde3836555%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E1290%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%26lt%3B%5C%2FTBODY%26gt%3B%5Cn%26lt%3B%5C%2FTABLE%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EIn%20the%20result%20above%2C%20you%20can%20see%20that%201857%20records%20were%20returned%20by%20the%20predicate%2C%20and%20they%20are%20spread%20across%20three%20%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fextents-overview%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20noreferrer%3D%22%22%3Eextents%26lt%3B%5C%2FA%26gt%3B.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EA%20quick%20note%20on%20what%20an%20%E2%80%9Cextent%E2%80%9D%20is%3A%20Azure%20Data%20Explorer%20is%20built%20to%20support%20tables%20with%20a%20huge%20number%20of%20records%20(rows)%20and%20large%20amounts%20of%20data.%20To%20handle%20such%20large%20tables%2C%20each%20table's%20data%20is%20divided%20into%20smaller%20%22chunks%22%20called%20%3CSTRONG%3Edata%20shards%26lt%3B%5C%2FSTRONG%26gt%3B%20or%20%3CSTRONG%3Eextents%26lt%3B%5C%2FSTRONG%26gt%3B%20(the%20two%20terms%20are%20synonymous).%20The%20union%20of%20all%20the%20table's%20extents%20holds%20the%20table's%20data.%20An%20extent%20is%20a%20like%20a%20type%20of%20mini-table.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EIf%20the%20result%20makes%20sense%20to%20you%2C%20then%20it%E2%80%99s%20time%20to%20actually%20delete%20these%20records%2C%20by%20removing%20the%20%60with%20(whatif%3Dtrue)%60%20part%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348292i9495615D982B44B2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22slavikn_4-1644920496628.png%22%20alt%3D%22%5C%26quot%3Bslavikn_4-1644920496628.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3CTABLE%3E%5Cn%3CTBODY%3E%5Cn%3CTR%3E%5Cn%3CTD%3E%3CP%3EOldExtentId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ENewExtentId%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ERecordsMatchPredicate%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E405da552-04bf-4138-b764-5fbef6513f04%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E3d93e17d-06d5-4efc-b2f0-209547bf687b%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E548%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E392d9b4d-0b21-40b1-a6af-10f9132c612c%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E7cb7e4fc-a7bf-4b83-babd-f92bb78568cf%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E19%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%5Cn%3CTD%3E%3CP%3E14779dfc-e261-469e-826f-bbfde3836555%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3Ead3e6a5e-dab7-406a-a5c7-cf5854aff4db%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E1290%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FTD%26gt%3B%5Cn%26lt%3B%5C%2FTR%26gt%3B%5Cn%26lt%3B%5C%2FTBODY%26gt%3B%5Cn%26lt%3B%5C%2FTABLE%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EHow%20do%20you%20read%20this%20result%3F%20Let%E2%80%99s%20look%20at%20the%20first%20record.%20It%20says%20that%20extent%20405da552-04bf-4138-b764-5fbef6513f04%20contained%20548%20records%20that%20were%20returned%20by%20the%20predicate.%20The%20command%20replaced%20this%20extent%20with%20a%20new%20one%2C%203d93e17d-06d5-4efc-b2f0-209547bf687b%2C%20containing%20all%20the%20records%20from%20the%20original%20extent%2C%20except%20the%20548%20records%20that%20were%20filtered%20out%20by%20the%20predicate.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3ENow%20when%20you%20query%20the%20DeviceTemperature%20table%2C%20you%20won%E2%80%99t%20get%20the%20records%20that%20you%20deleted.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3EHow%20does%20this%20work%20behind%20the%20scenes%3F%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EThe%20deletion%20process%20is%20performed%20using%20the%20following%20steps%3A%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3COL%3E%5Cn%3CLI%3E%3CSTRONG%3ERun%20predicate%20query%26lt%3B%5C%2FSTRONG%26gt%3B%3A%20The%20table%20is%20scanned%20to%20identify%20data%20extents%20that%20contain%20records%20to%20be%20deleted.%20The%20extents%20identified%20are%20those%20with%20one%20or%20more%20records%20returned%20by%20the%20predicate%20query.%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3CSTRONG%3EExtents%20replacement%26lt%3B%5C%2FSTRONG%26gt%3B%3A%20The%20identified%20extents%20are%20replaced%20with%20new%20extents%20that%20point%20to%20the%20original%20data%20blobs%2C%20and%20also%20have%20a%20new%20hidden%20column%20of%20type%20bool%20that%20indicates%20per%20record%20whether%20it%20was%20deleted%20or%20not.%20Once%20completed%2C%20if%20no%20new%20data%20is%20ingested%2C%20the%20predicate%20query%20will%20not%20return%20any%20records%20if%20run%20again.%26lt%3B%5C%2FLI%26gt%3B%5Cn%26lt%3B%5C%2FOL%26gt%3B%5Cn%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSTRONG%3ESummary%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EIn%20this%20blog%20post%20you%20learnt%20how%20to%20delete%20individual%20records%20by%20providing%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query%2C%20and%20how%20the%20deletion%20process%20works%20behind%20the%20scenes.%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhere%20can%20I%20learn%20more%3F%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%3EYou%20can%20learn%20more%20about%20this%20feature%20%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fdata-explorer%252Fkusto%252Fconcepts%252Fdata-soft-delete%26amp%3Bdata%3D04%257C01%257CSlavik.Neimer%2540microsoft.com%257C5c3111d01f064e6b5e4e08d9df77aede%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637786527395296846%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C3000%26amp%3Bsdata%3D8ZvXsRtsySIOkxicfltRCvWjva3Yilp%252BFL3ssdn1UlE%253D%26amp%3Breserved%3D0%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnoopener%20nofollow%20noopener%20noreferrer%22%20nofollow%3D%22%22%20noreferrer%3D%22%22%3Ehere%26lt%3B%5C%2FA%26gt%3B.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-TEASER%20id%3D%22%5C%26quot%3Blingo-teaser-3166847%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3C%2FLINGO-TEASER%3E%3C%2FA%3E%3C%2FP%3E%3CP%3EThis%20blog%20post%20will%20explain%20how%20to%20delete%20individual%20records%20from%20a%20table%2C%20by%20providing%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-teaser%26gt%3B%3C%2FP%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FP%3E%3C%2FA%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3166847%22%20slang%3D%22en-US%22%3EDeleting%20individual%20records%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3166847%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Data%20Explorer%20is%20a%20big%20data%20analytics%20platform%20that%20takes%20care%20of%20everything%20required%20to%20ensure%26nbsp%3Breal%26nbsp%3Btime%20decision%20making%20can%20take%20place%2C%20or%20at%20least%2C%26nbsp%3Bnear%26nbsp%3Breal%20time.%20This%20includes%20data%20ingestion%2C%20data%20querying%2C%20data%20visualization%20and%20data%20management.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20blog%20post%20you%E2%80%99ll%20learn%20how%20to%20delete%20individual%20records%20from%20a%20table%2C%20and%20how%20it%20works%20behind%20the%20scenes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20several%20scenarios%20in%20which%20you%20might%20want%20to%20delete%20individual%20records%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EYou%20have%20multiple%20devices%20that%20continuously%20send%20data%20into%20a%20table%2C%20and%20you%20only%20want%20the%20latest%20record%20per%20device.%20For%20this%20scenario%20we%20recommend%20using%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fmaterialized-views%2Fmaterialized-view-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMaterialized%20Views%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3EYou%20sometimes%20have%20duplicate%20records%20ingested%20into%20a%20table%2C%20and%20you%20want%20to%20remove%20the%20duplicates.%20There%20are%20several%20ways%20to%20handle%20this%2C%20please%20read%20more%20details%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fdealing-with-duplicates%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHandle%20duplicate%20data%3C%2FA%3E%20document.%3C%2FLI%3E%0A%3CLI%3EYour%20customer%20asked%20you%20to%20delete%20the%20data%20you%20have%20about%20him%2C%20and%20you%20want%20to%20delete%20the%20relevant%20records%20from%20your%20table%2C%20including%20the%20storage%20artifacts%20in%20which%20the%20data%20is%20stored.%20For%20this%20scenario%20we%20recommend%20using%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fconcepts%2Fdata-purge%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPurge%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3EYour%20pipeline%20sometimes%20ingests%20corrupted%20data%20(for%20example%2C%20a%20faulty%20device%20sends%20corrupted%20telemetry).%20For%20this%20scenario%20you%20would%20want%20to%20delete%20these%20specific%20records%2C%20given%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query.%20This%20blog%20post%20will%20explain%20how%20to%20achieve%20this%20with%20soft-delete.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20blog%20post%20will%20explain%20how%20to%20delete%20individual%20records%20for%20the%20last%20scenario%20in%20the%20list%20above.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScenario%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20common%20scenario%20for%20deleting%20individual%20records%20is%20when%20you%20have%20a%20table%20with%20data%20coming%20from%20IOT%20devices%2C%20and%20one%20day%20you%20find%20out%20that%20some%20device%20has%20recently%20started%20reporting%20wrong%20data%2C%20and%20then%20completely%20broke%20and%20stopped%20reporting%20data%20at%20all.%20You%20then%20wish%20to%20delete%20the%26nbsp%3Brecords%20from%20the%20table%20that%20were%20produced%20by%20the%20faulty%20device%20in%20the%20specific%20timeframe.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESuppose%20your%20company%20manages%20a%20large%20number%20of%20office%20buildings%2C%20and%20in%20every%20room%20of%20every%20building%20there%E2%80%99s%20a%20device%20that%20measures%20the%20temperature%20and%20sends%20it%20to%20a%20table%20named%20DeviceTemperature.%20Here%E2%80%99s%20a%20small%20data%20sample%20from%20the%20table%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EDeviceId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ETimestamp%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ETemperature%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EA%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-17%2019%3A58%3A03%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E19.53%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EB%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-20%2002%3A08%3A03%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E18.69%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EC%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-20%2005%3A41%3A03%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E20.56%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EA%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-20%2006%3A59%3A03%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E19.76%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3ED%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-20%2023%3A37%3A03%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E19.79%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20know%20that%20these%20thermometers%20sometimes%20break%2C%20and%20when%20they%20do%2C%20they%20start%20reporting%20extremely%20high%20temperatures%20for%20a%20while%2C%20and%20then%20they%20stop%20sending%20data%20at%20all.%20So%2C%20you%20created%20a%20script%20that%20runs%20a%20query%20once%20a%20day%20to%20detect%20such%20devices%2C%20and%20one%20day%20you%20see%20that%20the%20query%20returned%20a%20result%20indicating%20that%20one%20device%20broke%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22slavikn_1-1644920365209.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348289i63CB41A5AC74213B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22slavikn_1-1644920365209.png%22%20alt%3D%22slavikn_1-1644920365209.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EDeviceId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ETimestamp%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3Eavg_Temperature%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EE%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E2022-01-24%2010%3A00%3A00%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E885.86%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20next%20step%20is%20to%20write%20a%20query%20that%20returns%20all%20the%20records%20that%20were%20produced%20by%20the%20faulty%20device%2C%20based%20on%20these%20results%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22slavikn_2-1644920412833.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348290i3778E4CAFA78DF67%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22slavikn_2-1644920412833.png%22%20alt%3D%22slavikn_2-1644920412833.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3EDeleting%20records%20by%20a%20predicate%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20delete%20records%2C%20you%E2%80%99ll%20need%20to%20provide%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query%2C%20that%20returns%20all%20the%20records%20that%20you%20want%20to%20be%20deleted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20recommendation%20is%20to%20first%20run%20the%20deletion%20command%20in%20%60whatif%60%20mode%2C%20in%20order%20to%20see%20how%20many%20records%20will%20be%20deleted%20if%20you%20use%20the%20provided%20predicate%2C%20but%20without%20deleting%20anything%20just%20yet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22slavikn_3-1644920457950.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348291iD055647B4F760456%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22slavikn_3-1644920457950.png%22%20alt%3D%22slavikn_3-1644920457950.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EOldExtentId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ENewExtentId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ERecordsMatchPredicate%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E405da552-04bf-4138-b764-5fbef6513f04%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E548%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E392d9b4d-0b21-40b1-a6af-10f9132c612c%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E19%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E14779dfc-e261-469e-826f-bbfde3836555%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E00000000-0000-0000-0000-000000000000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E1290%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20result%20above%2C%20you%20can%20see%20that%201857%20records%20were%20returned%20by%20the%20predicate%2C%20and%20they%20are%20spread%20across%20three%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fextents-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eextents%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20quick%20note%20on%20what%20an%20%E2%80%9Cextent%E2%80%9D%20is%3A%20Azure%20Data%20Explorer%20is%20built%20to%20support%20tables%20with%20a%20huge%20number%20of%20records%20(rows)%20and%20large%20amounts%20of%20data.%20To%20handle%20such%20large%20tables%2C%20each%20table's%20data%20is%20divided%20into%20smaller%20%22chunks%22%20called%20%3CSTRONG%3Edata%20shards%3C%2FSTRONG%3E%20or%20%3CSTRONG%3Eextents%3C%2FSTRONG%3E%20(the%20two%20terms%20are%20synonymous).%20The%20union%20of%20all%20the%20table's%20extents%20holds%20the%20table's%20data.%20An%20extent%20is%20a%20like%20a%20type%20of%20mini-table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20result%20makes%20sense%20to%20you%2C%20then%20it%E2%80%99s%20time%20to%20actually%20delete%20these%20records%2C%20by%20removing%20the%20%60with%20(whatif%3Dtrue)%60%20part%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22slavikn_4-1644920496628.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348292i9495615D982B44B2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22slavikn_4-1644920496628.png%22%20alt%3D%22slavikn_4-1644920496628.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EOldExtentId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ENewExtentId%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ERecordsMatchPredicate%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E405da552-04bf-4138-b764-5fbef6513f04%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E3d93e17d-06d5-4efc-b2f0-209547bf687b%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E548%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E392d9b4d-0b21-40b1-a6af-10f9132c612c%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E7cb7e4fc-a7bf-4b83-babd-f92bb78568cf%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E19%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E14779dfc-e261-469e-826f-bbfde3836555%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3Ead3e6a5e-dab7-406a-a5c7-cf5854aff4db%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E1290%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20read%20this%20result%3F%20Let%E2%80%99s%20look%20at%20the%20first%20record.%20It%20says%20that%20extent%20405da552-04bf-4138-b764-5fbef6513f04%20contained%20548%20records%20that%20were%20returned%20by%20the%20predicate.%20The%20command%20replaced%20this%20extent%20with%20a%20new%20one%2C%203d93e17d-06d5-4efc-b2f0-209547bf687b%2C%20containing%20all%20the%20records%20from%20the%20original%20extent%2C%20except%20the%20548%20records%20that%20were%20filtered%20out%20by%20the%20predicate.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20when%20you%20query%20the%20DeviceTemperature%20table%2C%20you%20won%E2%80%99t%20get%20the%20records%20that%20you%20deleted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3EHow%20does%20this%20work%20behind%20the%20scenes%3F%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20deletion%20process%20is%20performed%20using%20the%20following%20steps%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3ERun%20predicate%20query%3C%2FSTRONG%3E%3A%20The%20table%20is%20scanned%20to%20identify%20data%20extents%20that%20contain%20records%20to%20be%20deleted.%20The%20extents%20identified%20are%20those%20with%20one%20or%20more%20records%20returned%20by%20the%20predicate%20query.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EExtents%20replacement%3C%2FSTRONG%3E%3A%20The%20identified%20extents%20are%20replaced%20with%20new%20extents%20that%20point%20to%20the%20original%20data%20blobs%2C%20and%20also%20have%20a%20new%20hidden%20column%20of%20type%20bool%20that%20indicates%20per%20record%20whether%20it%20was%20deleted%20or%20not.%20Once%20completed%2C%20if%20no%20new%20data%20is%20ingested%2C%20the%20predicate%20query%20will%20not%20return%20any%20records%20if%20run%20again.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESummary%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20blog%20post%20you%20learnt%20how%20to%20delete%20individual%20records%20by%20providing%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query%2C%20and%20how%20the%20deletion%20process%20works%20behind%20the%20scenes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EWhere%20can%20I%20learn%20more%3F%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20learn%20more%20about%20this%20feature%20%3CA%20href%3D%22https%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fdata-explorer%252Fkusto%252Fconcepts%252Fdata-soft-delete%26amp%3Bdata%3D04%257C01%257CSlavik.Neimer%2540microsoft.com%257C5c3111d01f064e6b5e4e08d9df77aede%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637786527395296846%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C3000%26amp%3Bsdata%3D8ZvXsRtsySIOkxicfltRCvWjva3Yilp%252BFL3ssdn1UlE%253D%26amp%3Breserved%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3166847%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20blog%20post%20will%20explain%20how%20to%20delete%20individual%20records%20from%20a%20table%2C%20by%20providing%20a%20predicate%20in%20the%20form%20of%20a%20KQL%20query.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Feb 15 2022 05:52 AM
Share
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK