Azure Data Explorer (ADX)
JVM AGENT
Overview
The ADX connector enables Seeq to access data from Azure Data Explorer clusters. Configuring the connector with more complex data requires some knowledge of Microsoft’s Kusto query language. The Seeq connector for ADX is capable of replicating Asset Trees, accessing sample data, and synching signal properties (metadata).
Prerequisites
There are a couple of pieces of information that should be gathered prior to configuring a connection to ADX.
Cluster Information
The Seeq connector will need the URI of the Azure Data Explorer cluster. This can be found within the Azure Portal in the Overview section for the Azure Data Explorer cluster you wish to connect to. You will want to take note of the value in the URI property.
Authentication
ADX must be appropriately configured with Azure Active Directory in order to connect it with Seeq.
In order to connect Seeq to your Azure Data Explorer cluster you will need to enable application authentication for Azure Active Directory as described here. Once the application registration is completed and the client secrets are generated you will need to take note of three properties:
TenantIDApplicationIdAccessKey- Note that it is recommended that you reference a secret file for this field
Either authentication option requires the Azure AD TenantId.
ADX Database Access
The service principal created for access to ADX must have Viewer rights to the database and associated data.
Configuration
This is an example configuration template that is displayed in the Additional Configuration box that appears when you click Configure for an existing datasource (or if a new datasource is being created, in the Create new datasource connection modal that appears after clicking Add Datasource) on the Datasources administration page.
{
"RootAssetName": null,
"GenerateTableAsset": true,
"Tables": [
{
"Id": "ADX Table Definition 1",
"Name": null,
"NameQuery": null,
"GroupBy": null,
"GroupByLimit": 0,
"DataColumns": null,
"DataColumnQuery": null,
"TimeColumn": null,
"LastGroupAsSignalName": false,
"TransformQuery": null,
"TimeUnit": null,
"ComputedGroupByResult": null,
"GroupByRequestProperties": null,
"DataRequestProperties": null,
"MetadataQuery": {
"TableName": null,
"Filters": null,
"Columns": null,
"UniqueKeyColumnName": null
},
"SignalPropertiesMap": null
}
],
"ApplicationId": null,
"AccessKey": null,
"TenantId": null,
"UserName": null,
"UserPassword": null,
"Cluster": null,
"Database": null
}ADX Additional Configuration
Property Name | Default Value | Data Type | Description |
|---|---|---|---|
| null | String | If null, the connector will use the Database Name as the root asset within an Asset Tree in Seeq. If a value is filled in, then the value entered will be used as the root asset in Seeq. |
|
| Boolean | If true, the ADX table name will be used as the first level child to the Root Asset in the Seeq Asset Tree. If false, the table name will not be used in the Asset Tree. |
| Empty TableDefinition | TableDefinition | An array of table definitions, each table definition can target one ADX table or stored function. You can have multiple table definitions per connection. See: TableDefinition below |
| null | String | The Application ID that was generated as the result of creating an Azure Active Directory application registration in Azure Data Explorer |
| null | String/SecretFile | This is the Secret that is generated as the result of creating a new client secret for the service principal. It is recommended to leverage a Secrets File to store this information. |
| null | String | The ID of the Active Directory Tenant in which the Service Principal was created. |
| null | String | The URI of the Azure Data Explorer cluster. |
| null | String | The ADX Database that will be accessed with this connector. |
TableDefinition Configuration
Property Name | Default Value | Data Type | Description |
|---|---|---|---|
| ADX Table Definition 1 | String | Each table definition in the |
| null | String | The name of the ADX Table or Stored function where the data resides. Must be NULL if using a |
| null | KQL String | Specifies a Kusto query, allowing you to use the table definition as a template for multiple tables with the same schema. This query must return a tabular result with a single column containing the names of the tables you wish to index. |
| null | Array[String] | Specifies the columns (in order) whose values should form the asset tree. In other words, the values in the data columns will be filtered into separate signals based on each unique path formed by the values in the |
| 0 | integer | Limits the number of group by matches. 0 is unlimited. Should only be used if there is a performance problem during indexing. This is only a troubleshooting step to identify the bottleneck. If this addresses the issue, an alternate data model should be considered to ensure Seeq has access to all the intended data. |
| null | Array[String] | An array of columns that specify in which columns the data is stored. |
| null | String | A Kusto query that identifies columns that contain data values. |
| null | String | The column name in the table that contains the time index. This column can either be a DateTime or a numeric value in seconds, milliseconds, or nanoseconds from the epoch. The numeric representation must be used in conjunction with
|
| false | Boolean | If false, the last element in the asset tree will not be used as the signal name, and the column name will be used as the signal name. If true, the last element in the asset tree will be used as the signal name. For usage see: Example: Accessing Data from an Entity-Timestamp-Value Data Model |
| null | String | Takes an optional Kusto query which is applied to the original table to produce a result table. See: Using Transform Queries in the Seeq ADX Connector |
| null | Time String (“s”, “ms”, “ns”) | When using a numeric value as time from the epoch as the timestamp, specify if the numeric value is seconds, milliseconds, or nanosecond from the epoch. Must be |
| null | String | A Kusto query that defines a pre-computed distinct asset group. This is generally used in conjunction with a materialized view to identify the asset hierarchies. Note that the |
| null | Map{“property”:”value”} | Allows for Seeq to pass Client Request Properties during the indexing queries. The property is the name of the client request property to set, the value is the value for that property. (i.e. |
| null | Map{“property”:”value”} | Allows for Seeq to pass Client Request Properties during the data queries. The property is the name of the client request property to set, the value is the value for that property. (i.e. |
| Empty MetadataQuery | MetadataQuery | Allows for a metadata table (dimension table) to be specified to identify asset hierarchies and signal properties. For type definition see: MetadataQuery. For usage see: Example: Accessing Metadata and Value Data |
| null | Map{“property”:”columnName”} | A dictionary that maps metadata columns to signal properties. The property is the Seeq Signal property, the column name is the MetadataQuery column name that the value should be provided by. For usage see: Example: Accessing Metadata and Value Data |
MetadataQuery Configuration
Property Name | Default Value | Data Type | Description |
|---|---|---|---|
| null | String | The ADX table name to use for the metadata (dimension) information. |
| null | String | A Kusto predicate that can be used to filter the metadata results. |
| null | Array[String] | An array of columns that includes any group by information or signal property information. |
| null | String | The column name used as a key between the dimension (metadata) and fact (data) tables |
Examples
Getting Started With Accessing Tabular Data
This guide introduces the concepts of the Seeq connector for ADX and shows how to connect to a tabular data model in ADX from Seeq.
Accessing Data from an Entity-Timestamp-Value Data Model
This guide will explain how to leverage ADX to access data that has been stored in an Entity-Timestamp-Value model. This model is most useful when there are a large number of signals that need to be accessed from a single table.
Accessing Asset Tree Data from an Entity-Timestamp-Value Model
This guide expands on accessing the Entity-Timestamp-Value model and adds the ability to retrieve asset tree (hierarchy) information from ADX metadata.
Accessing Metadata and Value Data from ADX from within Seeq
This guide shows you how to access data that is stored in a dimension and fact table in ADX. This is a popular model choice as it allows for flexibility in the metadata (the dimension table) while allowing the value table (fact table) to grow.
Using Transform Queries in the Seeq ADX Connector
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.
Using Materialized Views to Increase Indexing Performance
If you a leveraging a single large denormalized table that contains both metadata and data, especially if there is a large number of group by columns, the indexing performance of the connector might be slow or unable to complete without a timeout. To overcome this, a materialized view can be used within ADX to precompute the Asset Tree information. Note: this is not generally an ideal model, see here: Example: Accessing Data from an Entity-Timestamp-Value Data Model.
Querying ADLS using the ADX Connector and External Tables
An effective mechanism to query data in an Azure Data Lake Gen 2 storage account is to leverage external tables through ADX and then use the ADX connector to read the data. Do note that performance is highly dependent on the source data format and the partitioning scheme within ADLS. You can find more information about external tables here https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/external-tables-azurestorage-azuredatalake .
Known Issues
No BigDecimal Support
There is currently an issue with the Big Decimal type received via the Kusto Client that the Seeq connector uses to connect to ADX. If the column in the ADX table is typed as System.Data.SqlTypes.SqlDecimal, then the client will cast that value to a string. A user would see this problem as a red triangle on the signal with the following error message.
A simple (yet potentially limiting) workaround is to leverage a transform function such as | extend Temp = toreal(Temperature) to cast the value to a real instead of a decimal. This issue will be addressed in a future version of Seeq.
Using a String-typed UniqueKeyColumnName That has Special Characters
If a value for the column in UniqueKeyColumnName has special characters, it is suggested to use a stored function to escape the result.
Leveraging GroupBy or Signal names containing the | operator
When trying to trend values for signals with a name that has a | operator or signals that are contained in assets with | in the name, the query will fail. This is a known issue, and is being worked on. At the moment the only resolution is to avoid using that operator in asset or signal names in ADX.
Please report any other issues you find to our support portal.
Troubleshooting
If you are running into issues with connecting to or access data from ADX, view our Troubleshooting guide to ADX or our general guidance on troubleshooting datasource issues.
Performance considerations
The ADX connector has some specific Performance Considerations to consider. Additionally, view our guide on optimizing datasource performance for general guidance.