Combine static and real time data in Azure Stream Analytics

Question

I am looking into combining data (stored in Azure SQL) and real-time stream data (coming via IoT Hub) in Stream Analytics. One way I found is to use blob storage to copy the SQL Azure data and use it as Input type "Reference Data" and in Stream Analytics query editor JOIN with the streaming data which works fine. However, I am looking into whether it is possible to use JavaScript UDF function capability in stream analytics to get data from SQL Azure and combine with streaming IoT data? I also don't know which one is the suggested approach to combining these type of data together?

Thanks

Answer 1

UDFs in streaming analytics won't allow you to call out to external services like SQL. They're used for things like basic data manipulation, regex, Math, etc. If your SQL data is slow moving in nature, the approach you've outlined here of using something like Data Factory to move SQL information into Blob storage and then use it as a Reference data inside your Stream Analytics query is the correct way (and only way currently) to solve your problem.

If it's fast moving data in SQL you'd want to investigate hooking into the SQL database changes and then publishing them on to Event Hubs. You could then pull this into your query as a second Data Stream input type and do the appropriate joins in your query.