Accessing Data from a Follower Cluster

Introduction

Often, when architecting an ADX cluster, you will leverage a leader and follower cluster. The leader will generally be responsible for ingesting data, and the follower will be available for ad-hoc query loads. Follower databases are read-only. This guide will explain how to set up your follower cluster so that Seeq can successfully index the follower tables in the read-only database.

Why

During indexing, Seeq needs to query for metadata. This metadata typically does not have an available column that would work for paginated queries. Due to default returned row count limitations, Seeq leverages stored queries to paginate the metadata response query as documented by Microsoft here. This stored query functionality requires read/write.

Prerequisites

This guide assumes that you have two ADX clusters arranged in a leader/follower configuration, as described here.
You will need to create a new empty database in the follower cluster. No data will be in this database. It will only hold stored functions to access data in the read-only follower database.

Creation of the Stored Functions

In this example, we will be creating the structure shown above.

Leveraging the example shown here, we will be creating a stored function in our read-write database called SeeqRead that was created as part of prerequisite two above. Within the SeeqRead DB, you must create two stored functions. One for the dimension table and one for the fact table. The stored functions can be created with the following Kusto commands:

CODE

.create-or-alter function with (folder = "Seeq") FactTableFn() {
    database("Documentation").FactTable
}

This will create a stored function in a folder called “Seeq” called FactTableFn. This stored function will perform a cross-database query as described here. Additionally, a second stored function will be used to perform the cross-database query for the dimension data.

CODE

.create-or-alter function with (folder = "Seeq") DimensionTableFn() {
    database("Documentation").DimensionTable
}

Configuration of the ADX Connector

Now that we have created the stored functions, we will configure the connector to leverage them.

CODE

{
    "RootAssetName": "Dimension Model",
    "GenerateTableAsset": false,
    "Tables": [
        {
            "Id": "String Data",
            "Name": "FactTableFn",
            "NameQuery": null,
            "GroupBy": [
                "siteId",
                "lineId",
                "machineId",
                "tag"
            ],
            "GroupByLimit": 0,
            "DataColumns": [
                "Value_S"
            ],
            "DataColumnQuery": null,
            "TimeColumn": "TS",
            "LastGroupAsSignalName": true,
            "TransformQuery": null,
            "TimeUnit": null,
            "ComputedGroupByResult": null,
            "GroupByRequestProperties": null,
            "DataRequestProperties": null,
            "MetadataQuery": {
                "TableName": "DimensionTableFn",
                "Filters": "where valuetype == \"string\"",
                "Columns": [
                    "interpolation",
                    "unit",
                    "siteId",
                    "lineId",
                    "machineId",
                    "tag",
                    "tagId"
                ],
                "UniqueKeyColumnName": "tagId"
            },
            "SignalPropertiesMap": {
                "Interpolation Method": "interpolation",
                "Description": "machineId"
            }
        },
        {
            "Id": "Numeric Data",
            "Name": "FactTableFn",
            "NameQuery": null,
            "GroupBy": [
                "siteId",
                "lineId",
                "machineId",
                "tag"
            ],
            "GroupByLimit": 0,
            "DataColumns": [
                "Value_D"
            ],
            "DataColumnQuery": null,
            "TimeColumn": "TS",
            "LastGroupAsSignalName": true,
            "TransformQuery": null,
            "TimeUnit": null,
            "ComputedGroupByResult": null,
            "GroupByRequestProperties": null,
            "DataRequestProperties": null,
            "MetadataQuery": {
                "TableName": "DimensionTableFn",
                "Filters": "where valuetype == \"numeric\"",
                "Columns": [
                    "interpolation",
                    "unit",
                    "siteId",
                    "lineId",
                    "machineId",
                    "tag",
                    "tagId"
                ],
                "UniqueKeyColumnName": "tagId"
            },
            "SignalPropertiesMap": {
                "Value Unit Of Measure": "unit",
                "Interpolation Method": "interpolation",
                "Description": "machineId"
            }
        }
    ],
     "ApplicationId": "<example application Id>",
    "AccessKey": "<example access Key>",
    "TenantId": "<example tenant ID>",
    "UserName": null,
    "UserPassword": null,
    "Cluster": "https://<cluster name>.<cluster region>.kusto.windows.net",
    "Database": "SeeqRead"
}

In the configuration above, the database field points to the read/write database we created in the follower cluster. The individual tables are configured to connect to the stored functions we created.