locked
Multiple source inputs to a unique output RRS feed

  • Question

  • Hello,

    I'm trying to combine data in Azure Stream Analytics from 2 sources into a single output:
    - One comes from event hub and is a real time streaming data
    - The other is from Azure storage which contains an "fixed" data in a JSON or Excel or CVS format.
    I'm trying to combine them with a query but it is not really a SQL query. From what I understood, it is not possible to directly query from to 2 sources like in normal SQL so I tried to use a JOIN but I get syntax mistakes.

    My query (without the 2nd input and which works) is in the format : 

    SELECT
    input.xxx, 
            input.xxx,
            input.xxx
    INTO output
    FROM input
    WHERE 
       condition

    From this query, I can as an ouput in PowerBI online a dataset.

    What I would like to have is, if possible, 2 datasets in output with my other data from the Azure storage.

    But it is also okay to reference the 2 datasets in this query but JOIN doesn't work in this format :

    SELECT
    input.xxx, 
            input.xxx,
            input.xxx,
            input2.xxx,
            input2.xxx
    INTO output
    FROM input

    JOIN input2

    WHERE 
       condition

    I can't see why it doesn't work. If you have any idea on how to make this work out, it would be great.

    Thank you in advance,

    Rob

    EDIT : I've already looked at these links that say that is possible to do so but it does not work when I try to write the query... https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-use-reference-data https://msdn.microsoft.com/library/en-us/Dn949258.aspx


    • Edited by RobKith Thursday, January 5, 2017 3:01 PM
    Thursday, January 5, 2017 9:41 AM

All replies

  • You're missing the ON criteria in your JOIN.
    Thursday, January 5, 2017 4:41 PM
  • Thank you ! Actually it was a bit silly !

    Now I face another problem. So I joined the reference data (fixed data) with the streaming data (one of the table of the streaming data is referenced in the fixed data). The query takes data from the streaming input and the reference input. However, in Power BI, although I can see the field of the reference data but there is nothing in the table... In other words, the tables appear but they are empty.

    Where can this problem come from ?

    I joined a CVS file for reference data.

    Friday, January 6, 2017 4:51 PM
  • Sorry, I was out for a few days and am just sorting through my long lists of this. Have you checked for any issues like case sensitivity or padding that may be causing the lookup to fail.
    Tuesday, January 31, 2017 2:44 AM