2

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?

Ihor Korotenko
  • 846
  • 1
  • 13
  • 29
  • 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](https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-documentdb-output) about documentdb output? – Bruce Chen Jun 09 '17 at 09:37
  • @Bruce-MSFT, yes, I've read that topic. Sure, I will update my question with screenshot – Ihor Korotenko Jun 12 '17 at 08:46
  • @Bruce-MSFT, check the output configuration image I've added. – Ihor Korotenko Jun 12 '17 at 08:49
  • 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 Jun 13 '17 at 08:30
  • @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 Jun 13 '17 at 14:21
  • What happens if you remove the `$` from `$pk` in all relevant locations? – Pete M Jun 16 '17 at 15:17
  • @PeteM , What do you mean by that? – Ihor Korotenko Jun 19 '17 at 15:09
  • You have a literal dollar sign in the name of a field. I'm curious what happens if you stick to standard alphanumeric characters and rename `$pk` to simply `pk`. It seems to be having a problem resolving the property name and it would be good to rule out reserved characters in your property names as a potential issue. I don't know if $pk should work or not... – Pete M Jun 19 '17 at 19:48
  • @PeteM, as you can see from the question body the error message says that it expects `$pk` to be present in the output records. However, I've tried `pk` as well and it doesn't work either. – Ihor Korotenko Jun 20 '17 at 08:27

3 Answers3

1

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

Jean-Sébastien
  • 737
  • 3
  • 7
  • Ah, thanks for the answer. I think you should update the docs on the microsoft site to include this, because it is quite unclear for now that you're not supporting that. – Ihor Korotenko Jun 23 '17 at 10:36
  • Thanks, I asked the team to look at the doc after seeing your initial message! – Jean-Sébastien Jun 23 '17 at 20:21
  • Looks like the Mongo DB API is also not supported, am I right? I was having a similar issue (_id property not being generated). I switched over to SQL API and it worked perfectly. – luisgepeto Feb 07 '18 at 22:12
  • Mongo DB is not yet supported yet. We'll add more Cosmos DB APIs in the future. Sorry for the inconvenience. – Jean-Sébastien Feb 08 '18 at 18:43
1

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.

stimms
  • 42,945
  • 30
  • 96
  • 149
0

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)
Manimaran Samuthirapandi
  • 579
  • 5
  • 7