How can i query a large result set in Kusto explorer?

Question

I am trying to return more than 1 million records from Kusto database in Kusto explorer but I am getting this error below

Query result set has exceeded the internal record count limit 500000 (E_QUERY_RESULT_SET_TOO_LARGE; see http://aka.ms/kustoquerylimits)

I think the limit is 5000000. Any ideas how can I achieve this? thanks

Answer 1

set notruncation;

It's strongly recommended that, in this case, some form of limitation is still put in place.

set truncationmaxsize=YOUR_LIMIT;
set truncationmaxrecords=YOUR_LIMIT;

Reference : https://docs.microsoft.com/en-us/azure/data-explorer/kusto/concepts/querylimits#limit-on-result-set-size-result-truncation

Answer 2

For large data exports - please use server-export options described here:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/

Service-side export (push): The methods above are somewhat limited since the query results have to be streamed through a single network connection between the producer doing the query and the consumer writing its results. For scalable data export, Kusto provides a "push" export model in which the service running the query also writes its results in an optimized manner. This model is exposed through a set of .export control commands, supporting exporting query results to an external table, a SQL table, or an external Blob storage.

Answer 3

Additional option is to use Kusto Explorer "run query into csv" button, this will add the set notruncation; for you and will save the results directly to disk, so that you can easily open the results in other tools such as Excel.