Limiting Azure data explorer update policy input

user_3710308 0
Viewed 106

We have a use case where we are saving telemetry and statistic data from the machines but the update policy, which is supposed to process the raw data, is giving us trouble and running out of memory.

Aggregation over string column exceeded the memory budget of 8GB during evaluation

We have two tables, the 'ingest-table' where the data is initially being ingested to and the 'main-table' where it should end up. We are in a process of migrating from another solution to ADX and have to ingest a high volume of data.

The raw data is in a matrix format, which means that one message from a machine will end up as multiple rows/records in the ADX database. We use mv-expand for the breakdown and the query is pretty much doing that, among with some data formatting. So, our update policy looks like the following:

['ingest-table']
| mv-expand Counter = Data.Counters
| mv-expand with_itemindex = r Row = Rows
| mv-expand Column = Rows[r].Data
| project ...

I don't see any way how could I improve the processing query itself and I'm looking for a way to somehow limit the number of the record which the update policy function would receive.

I've tried playing around with the ingestion batching (MaximumNumberOfItems = 1000) and also sharding policy (MaxRowCount = 1000) for the 'ingest-table' but it does not have any effect on the number of records the update policy is pulling it at once. My idea is to let only 1000 items at once to be processed by the update policy function because I've manually tested and it works fine to up to 5k record but fails closly above that.

Any suggestion what we could do in this case and how I can achieve that?

EDIT: An example raw message which has to be processed by the update policy. The number of rows the policy has to generate is the number of COUNTERS * ROWS * COLUMNS. In this case it would mean that we end up with ~1200 rows after this single message is processed. I do not see any other way that doing a mv-expand here.

{
  "Name": "StatisicName",
  "TimeInterval": {
    "StartUtc": 1654221156.285,
    "EndUtc": 1654221216.286
  },
  "Legend": {
    "RowLabels": [
      "0",
      "0.04",
      "0.08",
      "0.12",
      "0.16",
      "0.2",
      "0.24",
      "0.28",
      "0.32",
      "0.36",
      "0.4",
      "0.44",
      "0.48",
      "0.52",
      "0.56",
      "0.6",
      "0.64",
      "0.68",
      "0.72",
      "0.76",
      "0.8",
      "0.84",
      "0.88",
      "0.92",
      "0.96",
      "1",
      "1.04",
      "1.08",
      "1.12",
      "1.16",
      "1.2",
      "1.24",
      "1.28",
      "1.32",
      "1.36",
      "1.4",
      "1.44",
      "1.48",
      "1.52",
      "1.56",
      "1.6",
      "1.64",
      "1.68",
      "1.72",
      "1.76",
      "1.8",
      "1.84",
      "1.88",
      "1.92",
      "1.96"
    ],
    "ColumnLabels": [
      "Material1",
      "Material2",
      "Material3",
      "Material4",
      "Material5",
      "Material6",
      "Material7",
      "Material8",
      "Material9",
      "Material10",
      "Material11",
      "Material12"
    ]
  },
  "Counters": [
    {
      "Type": "Cumulative",
      "Matrix": {
        "Rows": [
          {
            "Data": [
              6.69771873292923,
              0,
              0,
              0,
              0.01994649920463562,
              0.017650499296188355,
              0.007246749711036683,
              0.003443999862670899,
              0.1422802443265915,
              0,
              0,
              0.0008609999656677247
            ]
          }
          //,{...} ... for each row of the matrix
        ]
      }
    },
    {
      "Type": "Count",
      "Matrix": {
        "Rows": [
          {
            "Data": [
              0.0001434999942779541,
              0,
              0,
              0,
              0.0001434999942779541,
              0.0001434999942779541,
              0.0001317590856552124,
              0.0001434999942779541,
              0.00014285165093031273,
              0,
              0,
              0.0001434999942779541
            ]
          }
          //,{...} ... for each row of the matrix
        ]
      }
    }
  ]
}
kql azure-data-explorer
1 Answers

The main issue I see in your code is this:

| mv-expand with_itemindex = r Row = Rows
| mv-expand Column = Rows[r].Data

You explode Rows and get the exploded values in a new column called Row, but then instead of working with Row.Data, you keep using the original unexploded Rows, traversing through the elements using r.
This leads to unnecessary duplication of Rows and it is probably what creates the memory pressure.

Check out the following code.
You can use the whole code and get the data formatted as a table with columns Material1, Material2 etc., or exclude the last 2 rows and simply get the exploded values, each in a separate row.

// Data sample generation. Not part of the solution
let p_matrixes  = 3;
let p_columns   = 12;
let p_rows      = 50;
let ['ingest-table'] =
range i from 1 to p_matrixes step 1 
| extend StartUtc            = floor((ago(28d + rand()*7d) - datetime(1970))/1ms/1000,0.001)
| extend EndUtc              = floor((ago(rand()*7d) - datetime(1970))/1ms/1000,0.001)
| extend RowLabels           = toscalar(range x from todecimal(0) to todecimal(0.04 * (p_rows - 1)) step todecimal(0.04) | summarize make_list(tostring(x)))
| extend ColumnLabels        = toscalar(range x from 1 to p_columns step 1 | summarize make_list(strcat("Material",tostring(x))))
| extend Counters_Cumulative = toscalar(range x from 1 to p_rows step 1 | mv-apply range(1, p_columns) on (summarize Data = pack_dictionary("Data", make_list(rand()))) | summarize make_list(Data))
| extend Counters_Count      = toscalar(range x from 1 to p_rows step 1 | mv-apply range(1, p_columns) on (summarize Data = pack_dictionary("Data", make_list(rand()))) | summarize make_list(Data))
| project i, Data = pack_dictionary("Name", "StatisicName", "TimeInterval", pack_dictionary("StartUtc", StartUtc, "EndUtc",EndUtc), "Legend", pack_dictionary("RowLabels", RowLabels, "ColumnLabels", ColumnLabels), "Counters", pack_array(pack_dictionary("Type", "Cumulative", "Matrix", pack_dictionary("Rows", Counters_Cumulative)), pack_dictionary("Type", "Count", "Matrix", pack_dictionary("Rows", Counters_Count))))
;
// Solution starts here
// Explode values
['ingest-table']
| project Name = tostring(Data.Name), StartUtc = todecimal(Data.TimeInterval.StartUtc), EndUtc = todecimal(Data.TimeInterval.EndUtc), RowLabels = Data.Legend.RowLabels, ColumnLabels = Data.Legend.ColumnLabels, Counters = Data.Counters
| mv-apply Counters on (project Type = tostring(Counters.Type), Rows = Counters.Matrix.Rows)
| mv-apply RowLabels to typeof(decimal), Rows on (project RowLabels, Data = Rows.Data)
| mv-expand ColumnLabels to typeof(string), Data to typeof(real)
// Format as table
| evaluate pivot(ColumnLabels, take_any(Data))
| project-reorder Name, StartUtc, EndUtc, RowLabels, Type, * granny-asc

"Explode values" sample

Name StartUtc EndUtc ColumnLabels RowLabels Type Data
StatisicName 1658601891.654 1660953273.898 Material4 0.88 Count 0.33479977032253788
StatisicName 1658601891.654 1660953273.898 Material7 0.6 Cumulative 0.58620965468565811
StatisicName 1658801257.201 1660941025.56 Material1 0.72 Count 0.23164306814350025
StatisicName 1658601891.654 1660953273.898 Material4 1.68 Cumulative 0.47149864409592157
StatisicName 1658601891.654 1660953273.898 Material12 1.08 Cumulative 0.777589612330022

"Format as table" Sample

Name StartUtc EndUtc RowLabels Type Material1 Material2 Material3 Material4 Material5 Material6 Material7 Material8 Material9 Material10 Material11 Material12
StatisicName 1658581605.446 1660891617.665 0.52 Cumulative 0.80568785763966921 0.69112398516227513 0.45844947991605256 0.87975011678339887 0.19607303271777138 0.76728212781319993 0.27520162657976527 0.48612400400362971 0.23810927904958085 0.53986865017468966 0.31225384042818344 0.99380179164514848
StatisicName 1658581605.446 1660891617.665 0.72 Count 0.77601864161716061 0.351768361021601 0.59345888695494731 0.92329751241805491 0.80811999338933449 0.49117503870065837 0.97871902062153937 0.94241064167069055 0.52950523227349289 0.39281849330041424 0.080759530370922858 0.8995622227351241
StatisicName 1658345203.482 1660893443.968 1.92 Count 0.78327575542772387 0.16795871437570925 0.01201541525964204 0.96029371013283549 0.60248327254185241 0.019315208353334352 0.4828009899119266 0.75923221663483853 0.29630236707606555 0.23977292819044668 0.94531978804572625 0.54626985282267437
StatisicName 1658345203.482 1660893443.968 1 Count 0.65268575186841382 0.61471913013853441 0.80536656853846211 0.380104887115314 0.84979344481966745 0.68790819414895632 0.80862491082567767 0.083687871352600765 0.16707928827946666 0.4071460045501768 0.94115460659910444 0.25011225557898314
StatisicName 1658581605.446 1660891617.665 1.6 Count 0.75532393959433786 0.71081551001527776 0.9757484452705758 0.55510969429009 0.055800808878012885 0.74924458240427783 0.78706505608871058 0.18745675452118818 0.70192553697345517 0.39429935579653647 0.4048784200404818 0.14888395753558561

Fiddle

user_63364790
Related