Azure Stream analytics job / Sql input issue

Question

Hello,

I try to create visualization with real time data in PowerBi using Azure Stream analytics job and getting data from azure sql database. I created input and output, connections passed tests correctly, now i try to match input with output with the simplest sql statement. The issue is that the name of the input doesn't want to accept my created input, check the image below. I can write everything after From and there is no syntax error but when I write "smartlab" or "sqlinput" it doesn't want to match the names...

Thanks in advance for help

Answer 1

Hello @Gizinski, Artur (EXT - PL/Bydgoszcz) ,
Thanks for the ask and using Microsoft Q&A platform .

As we understand the ask here is to query SQL as input . Please correct me if thats not accurate .

Azure stream analytics ( ASA) supports the below four inputs type/data streams .

1. Azure Event Hubs
2. Azure IoT Hub
3. Azure Blob storage
4. Azure Data Lake Storage Gen2

SQL is supported as a reference type only and the reference type can only be used with above four data input only . Please read about theis more here .
The below documents does talk about how we can use reference type .
https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-add-inputs#reference-data-input

Azure SQL Database
Azure SQL Database reference data is retrieved by your Stream Analytics job and is stored as a snapshot in memory for processing. The snapshot of your reference data is also stored in a container in a storage account that you specify in the configuration settings. The container is auto-created when the job starts. If the job is stopped or enters a failed state, the auto-created containers are deleted when the job is restarted.
If you select the query and run it the message is more clear . ( as shown below)

Test query

Failed to compile the selected query: Query must refer to at least one data stream input....

I suggest you to explloer if you move read the records from a blob / ADS gen2 . You always use to write from SQL to BLOB using Azure data factory and other Azure service . Please read more on this here

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

-------------------------------------------------------------------------------------------------------------------------

10. 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
11. Want a reminder to come back and check responses? Here is how to subscribe to a notification
12. 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

Hello,

Thank you for your answer. I was following your suggestion and I used azure data factory to copy data from Azure SQL to blob storage (to CSV file) and I used it as an input for Azure Stream Job. I successfully run the job, dataset in PowerBi workspace was created but it looks like it sent the data once or twice and stopped doing it even though job was still running and every time I checked a query the output was there with latest data. When I check dataset in PowerBi workspace, it seems like refresh date is outdated although if I go to Edit Api it seems like data are up to date.
Creating new tiles in dashboard using this dataset shows nothing. The query is the simplest "Select * INTO output FROM input" At the beginning I thought that maybe I had to use Update instead of Select but that not the case as update is not supported. I am not sure what else I should do to make it work.