azure stream analytics to cosmos db

2
votes

I have a trouble saving telemetry that are coming from Azure IoT hub to Cosmos DB. I have the following setup:

  • IoT Hub - for events aggregation
  • Azure Stream Analytics - for event stream processing
  • Cosmos DB with Table API. Here I created 1 table.

The sample message from IoT Hub: {"id":33,"deviceId":"test2","cloudTagId":"cloudTag1","value":24.79770721657087} The query in stream analytics which processes the events: SELECT concat(deviceId, cloudtagId) as telemetryid, value as temperature, id, deviceId, 'asd' as '$pk', deviceId as PartitionKey INTO [TableApiCosmosDb] From [devicesMessages] the proble is following every time the job tries to save the output to CosmosDB I get an error An error occurred while preparing data for DocumentDB. The output record does not contain the column '$pk' to use as the partition key property by DocumentDB

Note: I've added $pk column and PartitionKey when trying to solve the problem.

EDIT Here, is the output configuration:

enter image description here

Does anyone know what I'm doing wrong?

3
azureazure-stream-analyticsazure-iot-hubazure-cosmosdb
How did you configure the output to DocumentDB, could you provided the screenshot for the Output details of your documentdb output? Have you check this tutorial about documentdb output? – Bruce Chen
@Bruce-MSFT, yes, I've read that topic. Sure, I will update my question with screenshot – Ihor Korotenko
@Bruce-MSFT, check the output configuration image I've added. – Ihor Korotenko
I found your Database is set to TelemetryDB, but as you mentioned that your output is configured as Cosmos DB with Table API. AFAIK, for cosmos DB with table API, the database should be set as TablesDB. – Bruce Chen
@Bruce-MSFT, yes, you're right? Initially it was TablesDB, I forgot that changed that then. Tried again with the TablesDB - the result is the same as in the question. – Ihor Korotenko

3 Answers

1
votes

Unfortunately the Table API from CosmosDB is not supported yet as output sink for ASA.

If want to use Table as output, you can use the one under Storage Account. Sorry for the inconvenience.

We will add the Cosmos DB Table API in the future.

Thanks! JS - Azure Stream Analytics team

1
votes

I had this problem also. Although it isn't clear in the UI only the SQL API for CosmosDB is currently supported. I switched over to that and everything worked fantastically.

0
votes

Try with 

SELECT 
    concat(deviceId, cloudtagId) as telemetryid, value as temperature, id, deviceId, 'asd' as 'pk', deviceId as PartitionKey
INTO
    [TableApiCosmosDb]
From
    [devicesMessages]

The Special char is the problem.

While create the output with partition as 'id' and while insert query 'deviceId' as PartitionKey, because of that it is not partition correctly.

Example:

SELECT
        id as PartitionKey, SUM(CAST(temperature AS float)) AS temperaturesum ,AVG(CAST(temperature AS float)) AS temperatureavg
INTO streamout
FROM
    Streaminput TIMESTAMP by Time
GROUP BY
     id ,
    TumblingWindow(second, 60)