Stream Analytics : Setting value for fixed variable in the query

Umair Akhtar 41 Reputation points
2022-12-27T11:35:53.263+00:00

Hi,

So I have a AZ3166 device which I am using to connect with Azure Iot Hub and to Stream analytics. Currently, device is sending Temperature, Humidity and timestamp only. However, i need to define the Device ID in Stream analytics query for example, DeviceID: RASB1 which stay constant and add into the device id column whenever there is row is added into database.

I want to know how do I define such value in stream analytics query.

I have using

SELECT
temp as Temperature,
hmdt as Humidity
INTO sqlDB
FROM hubinput

Thank you

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
220 questions
No comments
{count} votes

Accepted answer
  1. HimanshuSinha-msft 17,641 Reputation points Microsoft Employee
    2022-12-27T22:34:53.47+00:00

    Hello @Umair Akhtar ,
    Thanks for the question and using MS Q&A platform.\
    As we understand the ask here is how to add a deviceId in teh query , please do let us know if its not accurate.

    SELECT
    'RASB1 ' as DeviceId
    temp as Temperature,
    hmdt as Humidity
    INTO sqlDB
    FROM hubinput

    I did tried with some dummt data and sharing the query result on my side .

    274350-image.png

    Please do let me if you have any queries.
    Thanks
    Himanshu


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
      • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    No comments

1 additional answer

Sort by: Most helpful
  1. Umair Akhtar 41 Reputation points
    2022-12-30T07:36:39.25+00:00

    @HimanshuSinha-msft
    Would you know the issue? If I am able to receive message from iot message route to storage account decrypted. But stream analytics is unable to get any data from iot hub input for very same device data. I also tried using seperate consumer group with no success. When I try test input connection. Sometime it gives error that 'Unable to connect to 'iothubinput' using connection string. Please make sure you have configured IOT hub input correctly.'

    And sometime input test connection succeed but then give below error:
    'No data was found for preview from 'iothubinput'. Make sure the input has recently received data and the correct format of those events has been selected.'

    I am using Node-Red and sending some simulated data through that.

    Just to make sure, I tried Azure rasberry pi simulated sensor with same iot hub and stream analytics and stream analytics is able to read the data and process. But when I use Node-Red device data, data appears in direct message route in storage but not in stream analytics.

    What could be the possible solution for this?

    No comments