{"id":1691,"date":"2019-02-11T00:00:00","date_gmt":"2019-02-11T00:00:00","guid":{"rendered":""},"modified":"2025-06-17T00:29:48","modified_gmt":"2025-06-17T07:29:48","slug":"controlling-costs-in-azure-data-explorer-using-down-sampling-and-aggregation","status":"publish","type":"post","link":"https:\/\/azure.microsoft.com\/en-us\/blog\/controlling-costs-in-azure-data-explorer-using-down-sampling-and-aggregation\/","title":{"rendered":"Controlling costs in Azure Data Explorer using down-sampling and aggregation"},"content":{"rendered":"\n

Azure Data Explorer (ADX) is an outstanding service for continuous ingestion and storage of high velocity telemetry data from cloud services and IoT devices. Leveraging its first-rate performance for querying billions of records, the telemetry data can be further analyzed for various insights such as monitoring service health, production processes, and usage trends. Depending on data velocity and retention policy, data size can rapidly scale to petabytes of data and increase the costs associated with data storage. A common solution for storage of large datasets for a long period of time is to store the data with differing resolution. The most recent data is stored at maximum resolution, meaning all events are stored in raw format. While the historic data is stored at reduced resolution, being filtered and\/or aggregated. This solution is often used for time series databases to control hot storage costs.<\/p>\n\n\n\n

In this blog, I\u2019ll use the GitHub events public dataset as the playground. For more information read about how to stream GitHub events into your own ADX cluster by reading the blog, \u201cExploring GitHub events with Azure Data Explorer<\/a>.\u201d I\u2019ll describe how ADX users can take advantage of stored functions, the \u201c.set-or-append\u201d command, and the Microsoft Flow Azure Kusto connector. This will help you to create and update tables with filtered, down-sampled, and aggregated data for controlling storage costs. The following are steps which I performed.<\/p>\n\n\n\n

Create a function for down-sampling and aggregation<\/h2>\n\n\n\n

The ADX demo11 cluster contains a database named GitHub. Since 2016, all events from GHArchive<\/a> have been ingested into the GitHubEvent <\/strong>table and now total more than 1 billion records. Each GitHub event is represented in a single record with event-related information on the repository, author, comments, and more.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Initially, I created the stored function AggregateReposWeeklyActivity<\/strong> which counts the total number of events in every repository for a given week.<\/p>\n\n\n

\n.create-or-alter function with (folder = \"TimeSeries\", docstring = \"Aggregate Weekly Repos Activity\u201d)\nAggregateReposWeeklyActivity(StartTime:datetime)\n{\n     let PeriodStart = startofweek(StartTime);\n     let Period = 7d;\n     GithubEvent\n     | where CreatedAt between(PeriodStart .. Period)\n     | summarize EventCount=count() by RepoName = tostring(Repo.name), StartDate=startofweek(CreatedAt)\n     | extend EndDate=endofweek(StartDate)\n     | project StartDate, EndDate, RepoName, EventCount\n}\n<\/pre><\/div>\n\n\n
I can now use this function to generate a down-sampled dataset of the weekly repository activity. For example, using the AggregateReposWeeklyActivity<\/strong> function for the first week of 2017 results in a dataset of 867,115 records.<\/p>\n\n\n\n
\"Screenshot<\/figure>\n\n\n\n
Using Kusto query, create a table with historic data<\/h2>\n\n\n\n
Since the original dataset starts in 2016, I formulated a program that creates a table named ReposWeeklyActivity<\/strong> and backfills it with weekly aggregated data from the GitHubEvent<\/strong> table. The query runs in parallel ingestion of weekly aggregated datasets using the \u201c.set-or-append\u201d command. The first ingestion operation also creates the table that holds the aggregated data.<\/p>\n\n\n
\n.show table GithubEvent details\n| project TableName, SizeOnDiskGB=TotalExtentSize\/pow(1024,3), TotalRowCount\n\n.show table ReposWeeklyActivity details\n| project TableName, SizeOnDiskGB=TotalExtentSize\/pow(1024,3), TotalRowCount\n\nCode sample:\nusing Kusto.Data.Common;\nusing Kusto.Data.Net.Client;\nusing System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Text;\nusing System.Threading.Tasks;\n\nnamespace GitHubProcessing\n{\n     class Program\n     {\n         static void Main(string[] args)\n         {\n             var clusterUrl = \"https:\/\/demo11.westus.kusto.windows.net:443;Initial Catalog=GitHub;Fed=True\";\n             using (var queryProvider = KustoClientFactory.CreateCslAdminProvider(clusterUrl))\n             {\n                 Parallel.For(\n                     0,\n                     137,\n                     new ParallelOptions() { MaxDegreeOfParallelism = 8 },\n                     (i) =>\n                     {\n                         var startDate = new DateTime(2016, 01, 03, 0, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromDays(7 * i);\n                         var startDateAsCsl = CslDateTimeLiteral.AsCslString(startDate);\n                         var command = $@\"\n                         .set-or-append ReposWeeklyActivity <|\n                         AggregateReposWeeklyActivity({startDateAsCsl})\";\n                         queryProvider.ExecuteControlCommand(command);\n\n                        Console.WriteLine($\"Finished: start={startDate.ToUniversalTime()}\");\n                     });\n             }\n         }\n     }\n}\n<\/pre><\/div>\n\n\n
Once the backfill is complete, the ReposWeeklyActivity <\/strong>table will contain 153 million records.<\/p>\n\n\n\n
\"Screenshot<\/figure>\n\n\n\n
Configure weekly aggregation jobs using Microsoft Flow and Azure Kusto connector<\/h2>\n\n\n\n
Once the ReposWeeklyActivity<\/strong> table is created and filled with the historic data, we want to make sure it stays updated with new data appended every week. For that purpose, I created a flow in Microsoft Flow that leverages Azure Kusto connector to ingest aggregation data on a weekly basis. The flow is built of two simple steps:<\/p>\n\n\n\n
    \n
  1. Weekly trigger of Microsoft Flow.<\/li>\n\n\n\n
  2. Use of \u201c.set-or-append\u201d to ingest the aggregated data from the past week.<\/li>\n<\/ol>\n\n\n\n
    \"image\"<\/figure>\n\n\n\n
    For additional information on using Microsoft Flow with Azure Data Explorer see the Azure Kusto Flow connector<\/a>.<\/p>\n\n\n\n
    Start saving<\/h2>\n\n\n\n
    To depict the cost saving potential of down-sampling, I\u2019ve used \u201c.show table<\/p>\n\n\n\n
    details\u201d command to compare the size of the original GitHubEvent<\/strong> table and the down-sampled table ReposWeeklyActivity<\/strong>.<\/p>\n\n\n
    \n.show table GithubEvent details\n| project TableName, SizeOnDiskGB=TotalExtentSize\/pow(1024,3), TotalRowCount\n\n.show table ReposWeeklyActivity details\n| project TableName, SizeOnDiskGB=TotalExtentSize\/pow(1024,3), TotalRowCount\n<\/pre><\/div>\n\n\n
    The results, summarized in the table below, show that for the same time frame the down-sampled data is approximately 10 times smaller in record count and approximately 180 times smaller in storage size.<\/p>\n\n\n\n
     <\/td>Original data<\/strong><\/td>Down-sampled\/aggregated data<\/strong><\/td><\/tr>
    Time span<\/strong><\/td>2016-01-01 \u2026 2018-09-26<\/td>2016-01-01 \u2026 2018-09-26<\/td><\/tr>
    Record count<\/strong><\/td>1,048,961,967<\/td>153,234,107<\/td><\/tr>
    Total size on disk (indexed and compressed)<\/strong><\/td>725.2 GB<\/td>4.38 GB<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Converting the cost savings potential to real savings can be performed in various ways. A combination of the different methods are usually most efficient in controlling costs.<\/p>\n\n\n\n