Stored query results

Stored query results is a mechanism that stores the result of a query on the service for up to 24 hours. The same principal identity that created the stored query can reference the results in later queries.

Stored query results can be useful in the following scenarios:

• Paging through query results. The initial command runs the query and returns the first "page" of records. Later queries reference other "pages" without the need to rerun the query.
• Drill-down scenarios, in which the results of an initial query are then explored using other queries.

Updates to security policies, such as database access and row level security, aren't propagated to stored query results. Use .drop stored_query_results if there's user permission revocation.

Stored query results behave like tables, in that the order of records isn't preserved. To paginate through the results, we recommended that the query includes unique ID columns. If there are multiple result sets returned by a query, only the first result set will be stored.

Note

• When you have more than 500 columns, an error is raised and the results aren't stored.
• Query results are stored in a storage account associated with the cluster; the data is not cached in local SSD storage.

Prerequisites

• EngineV3
• Database Viewer or higher access role

Syntax

.set [async] stored_query_result StoredQueryResultName [with (PropertyName = PropertyValue , ... )] <| Query

.set-or-replace [async] stored_query_result StoredQueryResultName [with (PropertyName = PropertyValue , ... )] <| Query

Parameters

Name	Type	Required	Description
async	string		If specified, the command will return and continue ingestion in the background. Use the returned OperationId with the .show operations command to retrieve the ingestion completion status and results.
StoredQueryResultName	string	✓	Stored query result name that adheres to entity names rules.
PropertyName, PropertyValue	string		One or more supported properties.
Query	string	✓	The text of a query whose results will be stored.

Note

If the StoredQueryResultName exists, .set will fail with an error. In contrast, .set-or-replace will delete the existing stored-query-result if it exists and then create a new one with the same name.

Supported properties

Property	Type	Description
expiresAfter	timespan	Determines when the stored query result will expire. Maximum is 24 hours.
previewCount	int	The number of rows to return in a preview. Setting this property to 0 (default) makes the command return all the query result rows. The property is ignored when the command is invoked using async mode.
distributed	bool	If true, the command will ingest from all nodes executing the query in parallel. Default is true. Set the flag to false when the amount of data produced by a query is small, or the number of cluster nodes is large, to prevent creating many small data shards.

Returns

A tabular subset of the records produced by the query, referred to as the "preview", or all records. Regardless of how many records are shown on return, all records are stored.

Character limitation

The command will fail if the query generates an entity name with the $ character. The entity names must comply with the naming rules, so the $ character must be removed for the ingest command to succeed.

For example, in the following query, the search operator generates a column $table. To store the query results, use project-rename to rename the column.

Run the query

.set stored_query_result Texas <| search ['State']:'Texas' | project-rename tableName=$table

Retrieve a stored query result

To retrieve a stored query result, use stored_query_result() function in your query:

stored_query_result ( 'StoredQueryResultName' ) | ...

Examples

Simple query

Storing a simple query result:

Run the query

.set stored_query_result Numbers <| range X from 1 to 1000000 step 1

X
1
2
3
...

Retrieve stored query result:

Run the query

stored_query_result("Numbers")

X
1
2
3
...

Pagination

Retrieve clicks by Ad network and day, for the last seven days:

.set stored_query_result DailyClicksByAdNetwork7Days with (previewCount = 100) <|
Events
| where Timestamp > ago(7d)
| where EventType == 'click'
| summarize Count=count() by Day=bin(Timestamp, 1d), AdNetwork
| order by Count desc
| project Num=row_number(), Day, AdNetwork, Count

Num	Day	AdNetwork	Count
1	2020-01-01 00:00:00.0000000	NeoAds	1002
2	2020-01-01 00:00:00.0000000	HighHorizons	543
3	2020-01-01 00:00:00.0000000	PieAds	379
...	...	...	...

Retrieve the next page:

stored_query_result("DailyClicksByAdNetwork7Days")
| where Num between(100 .. 200)

Num	Day	AdNetwork	Count
100	2020-01-01 00:00:00.0000000	CoolAds	301
101	2020-01-01 00:00:00.0000000	DreamAds	254
102	2020-01-02 00:00:00.0000000	SuperAds	123
...	...	...	...

Control commands

.show stored_query_results

Shows information on active stored query results.

Note

• Users with DatabaseAdmin or DatabaseMonitor permissions can inspect the presence of active stored query results in the context of the database.
• Users with DatabaseUser or DatabaseViewer permissions can inspect the presence of active stored query results created by their principal.

Syntax

.show stored_query_results

Returns

StoredQueryResultId	Name	DatabaseName	PrincipalIdentity	SizeInBytes	RowCount	CreatedOn	ExpiresOn
c522ada3-e490-435a-a8b1-e10d00e7d5c2	Events	TestDB	aadapp=c28e9b80-2808-bed525fc0fbb	104372	1000000	2020-10-07 14:26:49.6971487	2020-10-08 14:26:49.6971487

.show stored_query_result schema

Shows schema of active stored query result.

Syntax

.show stored_query_result StoredQueryResultName schema

Database Viewer permission is required for invoking this command.

Returns

StoredQueryResult	Schema
Events	[{"Column":"ID","Type":"guid"},{"Column":"EventName","Type":"string"},{"Column":"Time","Type":"datetime"}]

.drop stored_query_result

Deletes an active stored query result created in the current database by the current principal.

Syntax

.drop stored_query_result StoredQueryResultName

Database Viewer permission is required for invoking this command.

Returns

Returns information about deleted stored query results, for example:

StoredQueryResultId	Name	DatabaseName	PrincipalIdentity	SizeInBytes	RowCount	CreatedOn	ExpiresOn
c522ada3-e490-435a-a8b1-e10d00e7d5c2	Events	TestDB	aadapp=c28e9b80-2808-bed525fc0fbb	104372	1000000	2020-10-07 14:26:49.6971487	2020-10-08 14:26:49.6971487

.drop stored_query_results

Deletes active stored query results created in the current database by the specified principal.

Database Admin permission is required for invoking this command.

Syntax

.drop stored_query_results by user PrincipalName

Returns

Returns information on deleted stored query results.

Example:

.drop stored_query_results by user 'aadapp=c28e9b80-2808-bed525fc0fbb'

StoredQueryResultId	Name	DatabaseName	PrincipalIdentity	SizeInBytes	RowCount	CreatedOn	ExpiresOn
c522ada3-e490-435a-a8b1-e10d00e7d5c2	Events	TestDB	aadapp=c28e9b80-2808-bed525fc0fbb	104372	1000000	2020-10-07 14:26:49.6971487	2020-10-08 14:26:49.6971487
571f1a76-f5a9-49d4-b339-ba7caac19b46	Traces	TestDB	aadapp=c28e9b80-2808-bed525fc0fbb	5212	100000	2020-10-07 14:31:01.8271231	2020-10-08 14:31:01.8271231