Stream Analytics : Setting value for fixed variable in the query

Question

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

Answer 1

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 .

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

---

• Please don't forget to click on or upvote 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

Answer 2

@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?