Using Transform Queries in the Seeq ADX Connector
Overview
The TransformQuery parameter takes an optional Kusto query which is applied to the original table to produce a result table. This parameter can be useful when initially designing your query to perform any sort of preprocessing.
Examples
The following examples show how the TransformQuery parameter can be used.
Unpacking JSON Data
For example, say you have the following table, called DynamicData:
A suggested TransformQuery for the table would be:
| extend EventType = extractjson("$.EventType", tostring(Data), typeof(string))
| extend EventValue = extractjson("$.EventValue", tostring(Data), typeof(long))
| project Time, EventType, EventValueThis TransformQuery would produce the following result:
Once you have the data within this format, this represents a simple Entity-Attribute-Value model and can be configured using the guide here: Example: Accessing Data from an Entity-Timestamp-Value Data Model .
Changing a Value Type
In this example, all of the data is incorrectly stored as a string. Ideally, this would be corrected in the data model, but we are assuming that this cannot be easily adjusted.
Looking at the table below:
Assume the data type of Val is a string. This can be determined by running a getschema on the table. This would produce the following result:
We can see from the result of the getschema query above that Val is indeed a string; however, we would like to represent that as a numeric value in Seeq. This can be done with a transform query. By setting the TransformQuery to | extend ValNumeric = toreal(Val) we will create a new value called ValNumeric which will be a real representation of the Val value. We can then proceed to configure the connector.
You should be cautioned against doing this sort of manipulation, however. Because you are storing all values as a string, you cannot guarantee that you have a numeric type when you try to cast to a real. Additionally, there are performance implications to having to cast every value.
Maintainability and Performance
The TransformQuery is specified within the ADX connector configuration. For maintainability and governance, it is much better to utilize a materialized view or stored tabular function to maintain the transformations of the data.
Additionally, when creating transform queries, follow the Microsoft-suggested query best practices.
Multiple options can be selected.