Using Stream Analytics to transfer data from IoT Hub to Cosmos DB gives partition error

Question

I want to transfer data from my IoT Hub to a Cosmos DB and a storage table using Stream Analytics. The storage table goes fine. However, my stream analytics gives me the following data conversion error in the activity log:

"The output record does not contain the column 'deviceId' (case-sensitive) to use as the partition key property. By default, Azure Stream Analytics retries writing the event indefinitely until the write succeeds. Consider choosing Drop Output Error Policy to skip such errors, so an error wouldn't block the job progress.. Error encountered after writing [0] batches."

deviceId is my partition key in my cosmos db. I can see that the data is comming into stream analytics correctly. Here is a sample of the input:

[{"deviceId":1,"dateStamp":"2019-03-27T18:55:43.3546682Z","temperature":6.510664596692969,"EventProcessedUtcTime":"2019-03-27T18:58:41.6172586Z","PartitionId":1,"EventEnqueuedUtcTime":"2019-03-27T18:55:43.3450000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"Simulator","ConnectionDeviceGenerationId":"636891216524279053","EnqueuedTime":"2019-03-27T18:55:43.3370000Z","StreamId":null}},
{"deviceId":1,"dateStamp":"2019-03-27T18:56:43.3809346Z","temperature":5.5680961758215428,"EventProcessedUtcTime":"2019-03-27T18:58:41.6172586Z","PartitionId":1,"EventEnqueuedUtcTime":"2019-03-27T18:56:43.3640000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"Simulator","ConnectionDeviceGenerationId":"636891216524279053","EnqueuedTime":"2019-03-27T18:56:43.3690000Z","StreamId":null}},
{"deviceId":1,"dateStamp":"2019-03-27T18:57:43.4122929Z","temperature":5.07182001605249,"EventProcessedUtcTime":"2019-03-27T18:58:41.6172586Z","PartitionId":1,"EventEnqueuedUtcTime":"2019-03-27T18:57:43.4050000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"Simulator","ConnectionDeviceGenerationId":"636891216524279053","EnqueuedTime":"2019-03-27T18:57:43.4010000Z","StreamId":null}}]

My SQL API query is the following, the ColdStorageSmartFridge is the storage table and the HotStorageSmartFridge is the cosmosdb:

SELECT
    deviceId,
    dateStamp as time,
    temperature
INTO
    [ColdStorageSmartFridge]
FROM
    [IoTHubSmartFridge]

SELECT 
    deviceId,
    dateStamp,
    temperature    
INTO
    [HotStorageSmartFridge]
FROM
    [IoTHubSmartFridge]

I've worked on this for a whole afternoon and could not get it working. What am I missing?

Jean-Sébastien Jean-Sébastien · Accepted Answer · 2019-03-27T21:01:18

It seems your partition key may be case sensitive. Previously Azure Stream Analytics lower-cased the different fields. While this was not the expected behavior, we didn't want to introduce a breaking change in the service so a fix was released under "compatibility level 1.1".

Can you try to change the job compat level to 1.1 and let me know if it fixes your problem.

We will change the default compat level in the near future.

Using Stream Analytics to transfer data from IoT Hub to Cosmos DB gives partition error

1 Answers