{"id":14374,"date":"2022-06-13T14:00:24","date_gmt":"2022-06-13T21:00:24","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cse\/?p=14374"},"modified":"2023-07-26T06:19:03","modified_gmt":"2023-07-26T13:19:03","slug":"an-approach-to-unit-testing-adx-functions","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/ise\/an-approach-to-unit-testing-adx-functions\/","title":{"rendered":"An Approach to Unit Testing ADX Functions"},"content":{"rendered":"

Overview<\/h2>\n

Our application contains many functions that return data stored in Azure Data Explorer (ADX). We wrote these functions in Kusto Query Language (KQL) and each function returns a set of data based on the arguments passed. Although developers tested these functions as they wrote them, we needed a way to validate that the functions continued to work as the code and the data changed. Automated Unit testing is an essential part of any application development life cycle. It validates that code works properly and minimizes the risk that future code changes will break existing functionality. In this article, I will discuss the approach we took in automating the testing of ADX functions.<\/p>\n

The KQL Assert Function<\/h2>\n

ADX does not ship with a unit testing framework, but KQL has a static\u00a0assert<\/a>\u00a0function that can be used to test functions and queries.<\/p>\n

The\u00a0assert<\/em>\u00a0function accepts two arguments: a Boolean condition and a string. If the Boolean condition returns false, an error is raised, and the string is returned. We can use this to test that our ADX functions perform as expected.<\/p>\n

A Sample Test<\/h2>\n

For example, the following code tests if\u00a0table1<\/em> contains any rows.<\/p>\n

\n
let testTitle =<\/span> 'table1 should return some rows'<\/span>;\r\nlet checkfn =<\/span> (rowCount: long) \r\n{<\/span>\r\n  assert(rowCount ><\/span> 0<\/span>, testTitle)\r\n}<\/span>;\r\nlet results =<\/span> table1;\r\nprint testTitle, checkfn(toscalar(results\r\n    |<\/span> summarize rowCount =<\/span> count<\/span>()\r\n    ))\r\n<\/pre>\n<\/div>\n
The code above throws an exception and outputs “table1 should return some rows” if no data is in table1. We can be more deliberate in our tests and test the flow of data into the table (via Update Policies or into Materialized views) if we populate our tables with well-known data. ADX provides the\u00a0.ingest<\/em>\u00a0command, which allows us to script data ingestion. For small datasets, we can use the\u00a0.ingest<\/em>\u00a0inline command. For larger datasets, we can ingest from Azure storage. Assume we have an\u00a0AssetHistory<\/em>\u00a0table with the data below:<\/p>\n
<\/code><\/code><\/pre>\n
<\/code><\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
timeStamp<\/th>\norganizationId<\/th>\nassetId<\/th>\nlocation<\/th>\n<\/tr>\n<\/thead>\n
2022-02-08T21:25:03Z<\/td>\nORG-0001<\/td>\n90000001<\/td>\n{“lat”:34.13803101,”lon”:-84.13373566}<\/td>\n<\/tr>\n
2022-02-08T21:25:04Z<\/td>\nORG-0001<\/td>\n99999934<\/td>\n{“lat”:34.13801193,”lon”:-84.13371277}<\/td>\n<\/tr>\n
2022-02-08T21:25:04Z<\/td>\nORG-0001<\/td>\n99999939<\/td>\n{“lat”:34.06805801,”lon”:-84.28517914}<\/td>\n<\/tr>\n
2022-02-08T21:25:06Z<\/td>\nORG-0001<\/td>\n90000003<\/td>\n{“lat”:34.11829376,”lon”:-84.24249268}<\/td>\n<\/tr>\n
2022-02-08T21:25:36Z<\/td>\nORG-0001<\/td>\n99999931<\/td>\n{“lat”:43.05483246,”lon”:-89.44286346}<\/td>\n<\/tr>\n
2022-02-08T21:27:08Z<\/td>\nORG-0001<\/td>\n99999935<\/td>\n{“lat”:40.12084579,”lon”:-75.38314056}<\/td>\n<\/tr>\n
2022-02-08T21:29:28Z<\/td>\nORG-0001<\/td>\n99999936<\/td>\n{“lat”:40.02775955,”lon”:-82.80722046}<\/td>\n<\/tr>\n
2022-02-08T21:29:38Z<\/td>\nORG-0001<\/td>\n99991000<\/td>\n{“lat”:43.86902237,”lon”:-78.85910034}<\/td>\n<\/tr>\n
2022-02-08T21:30:04Z<\/td>\nORG-0001<\/td>\n99999939<\/td>\n{“lat”:34.06805420,”lon”:-84.28518677}<\/td>\n<\/tr>\n
2022-02-08T21:30:04Z<\/td>\nORG-0001<\/td>\n99999934<\/td>\n{“lat”:34.13801193,”lon”:-84.13371277}<\/td>\n<\/tr>\n
2022-02-08T21:30:06Z<\/td>\nORG-0001<\/td>\n90000003<\/td>\n{“lat”:34.11829376,”lon”:-84.24250031}<\/td>\n<\/tr>\n
2022-02-08T21:32:08Z<\/td>\nORG-0001<\/td>\n99999935<\/td>\n{“lat”:40.12084579,”lon”:-75.38313293}<\/td>\n<\/tr>\n
2022-03-12T21:32:08Z<\/td>\nORG-0001<\/td>\n99999935<\/td>\n{“lat”:42.12084579,”lon”:-77.38313293}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The\u00a0getassetHistory<\/em>\u00a0function listed below filters the\u00a0AssetHistory<\/em>\u00a0table by the input arguments. However, it also has some logic to ignore filters if an argument is not passed to the function.<\/p>\n
<\/code><\/code><\/pre>\n
\n
\n
.create<\/span>-<\/span>or<\/span>-<\/span>alter<\/span> function<\/span>\r\n  with<\/span> (docstring =<\/span> \"Find Asset History of an asset\"<\/span>, skipvalidation =<\/span> \"true\"<\/span> )\r\n    getassetHistory(\r\n        organizationIdFilter:string,\r\n        assetIdFilter:string,\r\n        startTimeStamp:datetime,\r\n        endTimeStamp:datetime,\r\n        locationInPolygon: string=<\/span>\"\"<\/span>){<\/span>\r\n        AssetHistory\r\n          |<\/span> where<\/span> isnull<\/span>(startTimeStamp) or<\/span> isnull<\/span>(endTimeStamp)\r\n              or<\/span> TimeStamp<\/span> between<\/span> (startTimeStamp .. endTimeStamp)\r\n          |<\/span> where<\/span> (isempty(organizationIdFilter) or<\/span> organizationId ==<\/span> organizationIdFilter)\r\n              and<\/span> assetId ==<\/span> assetIdFilter\r\n          |<\/span> where<\/span> isempty(locationInPolygon)\r\n              or<\/span> geo_point_in_polygon(todouble(location<\/span>.lon), todouble(location<\/span>.lat), todynamic(locationInPolygon))\r\n          |<\/span> project\r\n                TimeStamp<\/span>,\r\n                assetId,\r\n                organizationId,\r\n                tostring(location<\/span>)\r\n      ;\r\n    }<\/span>\r\n<\/pre>\n<\/div>\n
We can use\u00a0assert<\/em>\u00a0to write some tests. We know in advance the data in the table, so we know how many rows the function should return for a given set of input arguments. Here are some examples:<\/p>\n
<\/code><\/code><\/pre>\n
\n
\n
let testTitle =<\/span> 'getassetHistory: brief time range should return 1 row'<\/span>;\r\nlet results =<\/span> getassetHistory('ORG-0001'<\/span>, '99999935'<\/span>, '2022-02-08T21:27:00Z'<\/span>, '2022-02-08T21:28:00Z'<\/span>);\r\nprint testTitle, assert(toscalar(results |<\/span> count<\/span>) ==<\/span> 1<\/span>, testTitle)\r\n\r\nlet testTitle =<\/span> 'getassetHistory: longer time range should return 3 rows'<\/span>;\r\nlet results =<\/span> getassetHistory('ORG-0001'<\/span>, '99999935'<\/span>, '2022-02-08T21:27:00Z'<\/span>, '2022-03-12T21:35:00Z'<\/span>);\r\nprint testTitle, assert(toscalar(results |<\/span> count<\/span>) ==<\/span> 3<\/span>, testTitle)\r\n\r\nlet testTitle =<\/span> 'getassetHistory: unknown organizationId should return 0 rows'<\/span>;\r\nlet results =<\/span> getassetHistory('ORG-9999'<\/span>, '2022-02-08T21:25:03Z'<\/span>, '2022-02-08T22:33:06Z'<\/span>);\r\nprint testTitle, assert(toscalar(results |<\/span> count<\/span>) ==<\/span> 0<\/span>, testTitle)\r\n<\/pre>\n<\/div>\n
As you can see, each of the tests above passes a distinct set of arguments to the function, so that each test filters the table for a different time range. Each test validates that the function correctly applies the filters by verifying that it returns the correct number of rows. But it also validates that the function handles things correctly when a null argument is passed or when an argument (e.g.,\u00a0locationInPolygon<\/em>) is omitted.<\/p>\n
We can even test passing dynamic data, as in this function call, which passes a polygon object to the function:<\/p>\n
<\/code><\/code><\/pre>\n
\n
\n
let polygon =<\/span> dynamic<\/span>({<\/span>\r\n        \"type\"<\/span>: \"Polygon\"<\/span>,\r\n        \"coordinates\"<\/span>: [ [\r\n            [ -<\/span>75<\/span>.8056640625<\/span>, 40<\/span>.052847601823984<\/span>],\r\n            [ -<\/span>74<\/span>.970703125<\/span>, 40<\/span>.052847601823984<\/span> ],\r\n            [ -<\/span>74<\/span>.970703125<\/span>, 40<\/span>.43022363450862<\/span>  ],\r\n            [ -<\/span>75<\/span>.8056640625<\/span>, 40<\/span>.43022363450862<\/span> ],\r\n            [ -<\/span>75<\/span>.8056640625<\/span>, 40<\/span>.052847601823984<\/span>] ] ]\r\n      }<\/span>);\r\nlet testTitle =<\/span> 'getassetHistory: with Bounding Box polygon should return 2 rows'<\/span>;\r\nlet startTime =<\/span> datetime(2022<\/span>-<\/span>01<\/span>-<\/span>09<\/span>T21:32<\/span>:08<\/span>Z);\r\nlet endTime =<\/span> datetime(2022<\/span>-<\/span>03<\/span>-<\/span>12<\/span>T21:32<\/span>:08<\/span>Z);\r\nlet results =<\/span> getassetHistory('ORG-0001'<\/span>, '99999935'<\/span>, startTime, endTime, polygon);\r\nprint testTitle, assert(toscalar(results |<\/span> count<\/span>) ==<\/span> 2<\/span>, testTitle)\r\n<\/pre>\n<\/div>\n
Each of the tests described above expects the data to be in a well-known state, therefore it is important to initialize the data correctly.<\/p>\n
For our project, we created automated scripts that would recreate all the database objects; then populate the tables with test data. We run this script before each test run, so we always start with a clean database. This made the data predictable, and it made our function calls idempotent with a given set of inputs.<\/p>\n
Other Testing Approaches<\/h2>\n
Alternatively, we can test a query by using the appropriate SDK to call it from a program written in Java, .NET, or some other high-level language. This has the advantage of allowing us to write tests in a robust testing framework, such as\u00a0xUnit<\/a>, or\u00a0JUnit<\/a>.<\/p>\n
However, this approach adds complexity to our tests and introduces the possibility that errors may occur in our code outside ADX. Keeping all our tests in ADX tends to reduce the amount of code we need to write and focuses the tests on the KQL functions we have written.<\/p>\n
Contributors<\/h2>\n
The code in this article is based on work done by the Microsoft Commercial Software Engineering GT team. Many of the original tests were written for our customer by Braden Eriksen<\/a>.<\/p>\n
Conclusion<\/h2>\n
The KQL tests above show how to use the\u00a0assert<\/em>\u00a0function to create automated Unit Tests that validate that our ADX functions are working correctly. This is a powerful tool for improving and maintaining code quality.<\/p>\n
You can read more about this pattern at the\u00a0Microsoft Documentation site<\/a>.<\/p>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Overview Our application contains many functions that return data stored in Azure Data Explorer (ADX). We wrote these functions in Kusto Query Language (KQL) and each function returns a set of data based on the arguments passed. Although developers tested these functions as they wrote them, we needed a way to validate that the functions […]<\/p>\n","protected":false},"author":73158,"featured_media":14378,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[3354,3355,367],"class_list":["post-14374","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cse","tag-adx","tag-kql","tag-unit-testing"],"acf":[],"blog_post_summary":"
Overview Our application contains many functions that return data stored in Azure Data Explorer (ADX). We wrote these functions in Kusto Query Language (KQL) and each function returns a set of data based on the arguments passed. Although developers tested these functions as they wrote them, we needed a way to validate that the functions […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/14374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/users\/73158"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/comments?post=14374"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/14374\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media\/14378"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media?parent=14374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/categories?post=14374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/tags?post=14374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}