Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
554 questions
ADX CSV ingestion mapping swapped Ordinals
Kovács, Ferenc
61
Reputation points
We have automated creating IoT Hub ingestion via the C# libraries:
var dataConnection = kustoClient.DataConnections.CreateOrUpdate(
this.config.ResourceGroupName,
this.config.ClusterName,
this.userData.DatabaseName,
dataConnectionName.ToLowerInvariant().Substring(0, Math.Min(dataConnectionName.Length, 40)),
new IotHubDataConnection(
iotHubClient.IotHubResource.Get(this.userData.IotHubResourceGroupName, this.userData.IotHubName).Id,
sharedAccessPolicyName: this.config.IotHubSharedAccessPolicy,
consumerGroup: this.config.IotHubConsumerGroupName,
location: this.config.ClusterLocation,
tableName: this.config.LandingZoneTableName,
eventSystemProperties: new[]
{
"iothub-connection-device-id",
"iothub-enqueuedtime
},
mappingRuleName: this.config.MappingRuleName,
dataFormat: IotHubDataFormat.SOHSV
));
For 3 or 4 years, Ordinal 0 was the enqueued time and Ordinal 1 was device ID.
Now, when I rerun the exact same procedure to set up ingestion from a new Iot Hub, Ordinal 0 is device ID and Ordinal 1 is enqueue time.
How can this be so unstable? Is there any rule for how the columns are mapped to Ordinals? Is there an easy way to debug these mapping mismatch issues? The first time I set this up, it was with trial and error I figured out the columns. When was this change introduced?
Thank you!
{count} votes
-
PRADEEPCHEEKATLA 90,566 Reputation points2023-02-09T07:42:59.2566667+00:00 Hello @Kovács, Ferenc
We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
-
PRADEEPCHEEKATLA 90,566 Reputation points
2023-02-09T10:43:57.9933333+00:00 Hello @Kovács, Ferenc
The above description is not clear.
“For 3 or 4 years, Ordinal 0 was the enqueued time and Ordinal 1 was device ID. Now, when I rerun the exact same procedure to set up ingestion from a new Iot Hub, Ordinal 0 is device ID and Ordinal 1 is enqueue time.”What do they mean? The mapping changed? The data changed?
“The first time I set this up, it was with trial and error I figured out the columns”
What do they mean?
Their mapping is super-simple. Either a data looks differently (they set up a new IoT hub) or the mapping isn’t specified properly.It feels that perhaps you might confused about what we call Ordinals, and what is a mapping about.
Can you please share sample of data and the client activity ID?
-
Kovács, Ferenc 61 Reputation points
2023-02-14T15:55:47.45+00:00 Hello,
Thank you for having a look so quickly! I can try and explain more clearly.
We have automated pipelines set up (IaC) which are able to create new resources and data mappings in a deterministic way (hopefully):
- A part of this infrastructure setup is creating IoT Hub resources via AZ commands. I am omitting adding the code for it here, it is straight forward in powershell.
- The next step is setting up the data connections in Azure Data Explorer for ingestion. This is done with .NET libraries (see above code snippet, which creates the IotHubDataConnection in C#).
- The last step is the ingestion CSV mapping. This is done via KQL, again the snippet is attached above (.create-or-alter statement).
The data producers which feed data into the IoT Hub remained unchanged (medical devices). Each sends a different payload but of course have no business overriding any system properties of the EventData objects.
These 3 steps are part of our IaC, which was set up and working whenever we ran it for about 4 years now without any changes from our end.
On the most recent run, for an unknown reason, the enqueue time and device id columns got inverted. Payload still remained column 3 / ordinal 2. We are at a loss why, since on our part nothing has changed.
In a manual setup, I can imagine the order in which one checks the boxes in the portal matters when selecting which system property to add to the ingestion, but in our case everything is fully automated.
About your comment that "the data looks differently": note that how system properties look like is driven by Microsoft. This is the question.
-
PRADEEPCHEEKATLA 90,566 Reputation points
2023-02-16T09:18:10.17+00:00 Hello @Kovács, Ferenc
Best would be getting a sample of data and the client activity ID.
Otherwise we can only guess (You have created a proper mapping but ingest command doesn’t use it? Your data IS different for some reason? etc)
-
Kovács, Ferenc 61 Reputation points
2023-02-20T08:48:13.62+00:00 Hello @PRADEEPCHEEKATLA
How can I get a sample of data? I have only access to control the payload on the source systems, which is irrelevant and working. None of the system properties are set by the source systems, which are the cause of the problem. See here:
So how can I get a sample of the raw data for you, where the ordinals for these system props prove the breaking change? I've tried the open source ServiceBuxExplorer but it is very old.
How can I obtain a client activity ID? I know this is available right after running normal queries, but how do I get one for ingesting from IoT Hub? This is not an ingest from inline query but from IoT Hub.
Thanks!
-
Kovács, Ferenc 61 Reputation points
2023-02-20T09:35:15.9433333+00:00 I found a way to get sample data: I configured a new hub consumer group on messages/events and a route from IoT Hub to Data Lake and configured it to store all events hitting the built in event hub endpoint in JSON format. Here is a sample file which landed in the storage account Gen2:
{ "EnqueuedTimeUtc": "2023-02-08T07:32:28.7770000Z", "Properties": {}, "SystemProperties": { "connectionDeviceId": "6969696969", "connectionAuthMethod": "{\"scope\":\"device\",\"type\":\"x509Certificate\",\"issuer\":\"external\",\"acceptingIpFilterRule\":null}", "connectionDeviceGenerationId": "638107558772716682", "contentType": "application/json", "contentEncoding": "UTF-8", "enqueuedTime": "2023-02-08T07:32:28.7770000Z" }, "Body": [{ "Index": 3, "Name": "RegistrationDetails", "ApplicationTimeStamp": "2023-02-08T07:32:28.232566Z", "Type": "CLI", "SoftwareVersion": "1.0.0.0", "OperatingSystem": "Microsoft Windows NT 6.2.9200.0", "Status": 1, "UtcOffset": 2, "BridgeVersion": "1.0.0.0", "AtManufacturer": false, "V_": "1.0.0.0" }, { "Index": 4, "Name": "InstrumentInfo", "ApplicationTimeStamp": "2023-02-08T07:32:28.2340761Z", "Type": "CLI", "SoftwareVersion": "1.0.0.0", "OperatingSystem": "Microsoft Windows NT 6.2.9200.0", "CycleCount": 69, "Status": 1, "TimeZoneName": "EET", "RegisteredTime": "2023-02-08T07:32:28.2328372Z", "AtManufacturer": false, "UtcOffset": 2, "BridgeVersion": "1.0.0.0", "V_": "1.0.0.0" }, { "Index": 5, "Name": "SoftwareVersion", "ApplicationTimeStamp": "2023-02-08T07:32:28.2344044Z", "SoftwareModule": "DeviceCLI_NetCore", "Version": "6.6.6", "V_": "1.0.0.0" } ] } -
Kovács, Ferenc 61 Reputation points
2023-02-20T13:22:52.34+00:00 Is this a client activity ID?
3b0e03e4-67d5-4ddc-ac29-e87c297b116c
-
PRADEEPCHEEKATLA 90,566 Reputation points
2023-03-01T03:32:22.42+00:00 Hello @Kovács, Ferenc
I had shared the details with the internal team to get more information related to your query and will get back to you as soon as we have an update.
Sign in to comment
Sign in to answer