Kusto: How to query large tables as chunks to export data?

user_6534818 0
Viewed 545

How can I structure a Kusto query such that I can query a large table (and download it) while avoiding the memory issues like: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/concepts/querylimits#limit-on-result-set-size-result-truncation

set notruncation; only works in-so-far as the Kusto cluster does not run OOM, which in my case, it does.

I did not find the answers here: How can i query a large result set in Kusto explorer?, helpful.

What I have tried:

  1. Using the .export command which fails for me and it is unclear why. Perhaps you need to be the cluster admin to run such a command? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/export-data-to-storage

  2. Cycling through row numbers, but run n times, you do not get the right answer because the results are not the same, like so:

let start = 3000000;
let end = 4000000;
table
| serialize rn = row_number()
| where rn between(start..end)
| project col_interest;
kql azure-data-explorer
1 Answers

"set notruncation" is not primarily for preventing an Out-Of-Memory error, but to avoid transferring too much data over-the-wire for an un-suspected client that perhaps ran a query without a filter.

".export" into a co-located (same datacenter) storage account, using a simple format like "TSV" (without compression) has yielded the best results in my experience (billions of records/Terabytes of data in extremely fast periods of time compared to using the same client you would use for normal queries).

What was the error when using ".export"? The syntax is pretty simple, test with a few rows first:

.export to tsv (
    h@"https://SANAME.blob.core.windows.net/CONTAINER/PATH;SAKEY"
) with (
    includeHeaders="all"
)
<| simple QUERY | limit 5

You don't want to overload the cluster at the same time by running an inefficient query (like a serialization on a large table per your example) and trying to move the result in a single dump over the wire to your client.

Try optimizing the query first using the Kusto Explorer client's "Query analyzer" until the CPU and/or memory usage are as low as possible (ideally 100% cache hit rate; you can scale up the cluster temporarily to fit the dataset in memory as well).

You can also run the query in batches (try first to use time-filters, since this is a time-series engine) and save each batch into an "output" table (using ".set-or-append"), in this way you split the load by first using the cluster to process the dataset, and then exporting the full "output" table into an external storage.

If for some reason you absolutely most use the same client to run the query and consume the (large) result, try using database cursors instead of serializing the whole table, it's the same idea, but pre-calculated, so you can use a "limit XX" where "XX" is the largest dataset you can move over the wire to your client, so you can run the same query over and over moving the cursor, until you are finished moving the whole dataset:

user_72640980
Related