Query past data with hot windows

Published Oct 29 2021 11:59 AM 1,426 Views
Microsoft

Here is a situation.  Let's say we cache the last month of data in Azure Data Explorer (ADX).  This is great for our operational queries which are all done within that last month of telemetry:  it limits the on-cluster resource we consume while making sure our operational queries are snappy fast.  Now an audit comes in requiring us to look at the entire year of 2019 (2 years ago).

 

What can we do?

 

The fundamental caching image for a Kusto table is as follow (assuming we have 5 years of accumulated data):

 

Cache-Fundamental.png

 

Data is persisted in cold storage and table's cache policy dictates how much of the most recent data gets cached in hot cache on the cluster nodes.  The hot cache offers the best query performance:  an order of magnitude more performant than cold data.  Cache policy can be changed at the database or table level and quickly change the amount of data loaded in hot cache.  This allows cluster operator to easily control how to allocate cluster resources for different workloads.

 

Past-Queries.png

 

In our scenario we want to query that cold data.  This is possible without any modification.  For queries done on a narrow time window, for instance aggregating events of one day of data, the performance are quite reasonable.  Those are called point in time queries or point queries and can be run at any time.

 

If we want to run queries on large amount of data in the past, those will not be point queries.  Let's say in our scenario we would need to run aggregations or analysis (e.g. anomaly detection) over the entire year of 2019.  Those would not perform well against cold data.

 

Audit-window.png

 

We could update the cache policy to go back three years ago.  This would include the year 2019 and enable fast queries over the data of that year.  That would also include the year 2020 and 2021 we do not need though and it might consume more hot cache than we have available on our cluster at the current scale.

 

A new feature we recently introduced is quite useful here:  hot windows.  A hot window is a time window in the past we want to be part of the hot cache.  It doesn't need to be contiguous with the "main hot cache" (latest data).  There can be multiple hot windows for the same table.

 

hot-window.png

 

A hot window is defined within the cache policy.

 

To resolve our situation, we would simply define a hot window for the year 2019.  Once we did our inquiry, we could remove that hot window.

 

This feature is well suited for forensic exploration where we need to double click into a certain time window in the past and have a team of analyst be productive quickly.

 

We just published an article in the Azure Data Explorer documentation about hot windows.

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2848114%22%20slang%3D%22en-US%22%3EQuery%20past%20data%20with%20hot%20windows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2848114%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20a%20situation.%26nbsp%3B%20Let's%20say%20we%20cache%20the%20last%20month%20of%20data%20in%20Azure%20Data%20Explorer%20(ADX).%26nbsp%3B%20This%20is%20great%20for%20our%20operational%20queries%20which%20are%20all%20done%20within%20that%20last%20month%20of%20telemetry%3A%26nbsp%3B%20it%20limits%20the%20on-cluster%20resource%20we%20consume%20while%20making%20sure%20our%20operational%20queries%20are%20snappy%20fast.%26nbsp%3B%20Now%20an%20audit%20comes%20in%20requiring%20us%20to%20look%20at%20the%20entire%20year%20of%202019%20(2%20years%20ago).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20can%20we%20do%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20fundamental%20caching%20image%20for%20a%20Kusto%20table%20is%20as%20follow%20(assuming%20we%20have%205%20years%20of%20accumulated%20data)%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%22Cache-Fundamental.png%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321545i07204DC3A8796DB2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Cache-Fundamental.png%22%20alt%3D%22Cache-Fundamental.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20is%20persisted%20in%20cold%20storage%20and%20table's%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fcachepolicy%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ecache%20policy%3C%2FA%3E%26nbsp%3Bdictates%20how%20much%20of%20the%20%3CU%3Emost%20recent%3C%2FU%3E%20data%20gets%20cached%20in%20hot%20cache%20on%20the%20cluster%20nodes.%26nbsp%3B%20The%20hot%20cache%20offers%20the%20best%20query%20performance%3A%26nbsp%3B%20an%20order%20of%20magnitude%20more%20performant%20than%20cold%20data.%26nbsp%3B%20Cache%20policy%20can%20be%20changed%20at%20the%20database%20or%20table%20level%20and%20quickly%20change%20the%20amount%20of%20data%20loaded%20in%20hot%20cache.%26nbsp%3B%20This%20allows%20cluster%20operator%20to%20easily%20control%20how%20to%20allocate%20cluster%20resources%20for%20different%20workloads.%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%22Past-Queries.png%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321546iA117963BE3FE00F8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Past-Queries.png%22%20alt%3D%22Past-Queries.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20our%20scenario%20we%20want%20to%20query%20that%20cold%20data.%26nbsp%3B%20This%20is%20possible%20without%20any%20modification.%26nbsp%3B%20For%20queries%20done%20on%20a%20narrow%20time%20window%2C%20for%20instance%20aggregating%20events%20of%20one%20day%20of%20data%2C%20the%20performance%20are%20quite%20reasonable.%26nbsp%3B%20Those%20are%20called%20point%20in%20time%20queries%20or%20%3CEM%3Epoint%20queries%3C%2FEM%3E%20and%20can%20be%20run%20at%20any%20time.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20want%20to%20run%20queries%20on%20large%20amount%20of%20data%20in%20the%20past%2C%20those%20will%20not%20be%20point%20queries.%26nbsp%3B%20Let's%20say%20in%20our%20scenario%20we%20would%20need%20to%20run%20aggregations%20or%20analysis%20(e.g.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fanomaly-detection%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eanomaly%20detection%3C%2FA%3E)%20over%20the%20entire%20year%20of%202019.%26nbsp%3B%20Those%20would%20not%20perform%20well%20against%20cold%20data.%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%22Audit-window.png%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321547iE05028EF3B70D6B0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Audit-window.png%22%20alt%3D%22Audit-window.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20%3CEM%3Ecould%3C%2FEM%3E%20update%20the%20cache%20policy%20to%20go%20back%20three%20years%20ago.%26nbsp%3B%20This%20would%20include%20the%20year%202019%20and%20enable%20fast%20queries%20over%20the%20data%20of%20that%20year.%26nbsp%3B%20That%20would%20also%20include%20the%20year%202020%20and%202021%20we%20do%20not%20need%20though%20and%20it%20might%20consume%20more%20hot%20cache%20than%20we%20have%20available%20on%20our%20cluster%20at%20the%20current%20scale.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20new%20feature%20we%20recently%20introduced%20is%20quite%20useful%20here%3A%26nbsp%3B%20hot%20windows.%26nbsp%3B%20A%20hot%20window%20is%20a%20time%20window%20in%20the%20past%20we%20want%20to%20be%20part%20of%20the%20hot%20cache.%26nbsp%3B%20It%20%3CEM%3Edoesn't%20need%20to%20be%20contiguous%3C%2FEM%3E%20with%20the%20%22main%20hot%20cache%22%20(latest%20data).%26nbsp%3B%20There%20can%20be%20multiple%20hot%20windows%20for%20the%20same%20table.%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%22hot-window.png%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321548i35E8E19BEAAE9CE8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22hot-window.png%22%20alt%3D%22hot-window.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20hot%20window%20is%20defined%20within%20the%20cache%20policy.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20resolve%20our%20situation%2C%20we%20would%20simply%20define%20a%20hot%20window%20for%20the%20year%202019.%26nbsp%3B%20Once%20we%20did%20our%20inquiry%2C%20we%20could%20remove%20that%20hot%20window.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20feature%20is%20well%20suited%20for%20forensic%20exploration%20where%20we%20need%20to%20double%20click%20into%20a%20certain%20time%20window%20in%20the%20past%20and%20have%20a%20team%20of%20analyst%20be%20productive%20quickly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%26nbsp%3Bjust%20published%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fhot-windows%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ean%20article%20in%20the%20Azure%20Data%20Explorer%20documentation%20about%20hot%20windows%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2848114%22%20slang%3D%22en-US%22%3E%3CP%3ECaching%20is%20about%20the%20latest%20data%20only%2C%20right%3F%26nbsp%3B%20Not%20anymore.%26nbsp%3B%20With%20hot%20windows%2C%20we%20can%20cache%20time-windows%20in%20the%20past%20and%20achieve%20the%20same%20world-class%20performance%20as%20with%20recent%20data.%26nbsp%3B%20We%20can%20now%20do%20forensic%20on%20old%20data%20as%20efficiently%20as%20with%20yesterday's.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎Oct 29 2021 11:56 AM
Updated by: