locked
Error: Reference data can only be used in temporal predicate RRS feed

  • Question

  • Trying to do wordcount with stopwords as reference data but i´m getting the error "Source 'stopwords' can only be used in temporal predicate using 'datediff' function. As far as i i can see in the documentation joins with reference data does not need to be temporal. The code below works fine without the JOIN clause

    WITH wordArray AS (
        SELECT
            CAST (UDF.makeWordArray(tweet) AS array) AS wordArray
        FROM [TwitterIn]
        TIMESTAMP BY DATEADD(millisecond, timestamp, '1970-01-01T00:00:00Z')
        )

    SELECT
        COUNT(*) AS counts,
        arrayElement.ArrayValue AS words,
        System.Timestamp AS Window

    INTO [toPowerBi]  

    FROM
        wordArray AS event
    CROSS APPLY GetArrayElements(event.wordArray) AS arrayElement
    LEFT JOIN StopWords ON wordArray = StopWords WHERE StopWords IS NULL
    GROUP BY
        arrayElement.ArrayValue,
        TumblingWindow(hour, 1)

    Thursday, February 22, 2018 1:41 PM

Answers

  • The 'wordArray' is ambiguous in your query. It is used once as a name of the step and then as a field name.

    If in 'ON wordArray = StopWords' the wordArray and StopWords are names of streams then this is not allowed. You must use field names. But if you mean the wordArray field you project, then you should qualify it since it is used in a join - 'wordArray.wordArray'.

    Here is a rewrite that should work:

    WITH wordArrayStep AS (
        SELECT 
            CAST ( UDF.makeWordArray(tweet) AS array) AS wordArray
        FROM [TwitterIn] 
        TIMESTAMP BY DATEADD(millisecond, timestamp, '1970-01-01T00:00:00Z')
        )
    
    SELECT 
        COUNT(*) AS counts, 
        arrayElement.ArrayValue AS words,
        System.Timestamp AS Window
    
    INTO [toPowerBi]  
    
    FROM
        wordArrayStep AS event
    CROSS APPLY GetArrayElements(event.wordArray) AS arrayElement
    LEFT JOIN StopWords ON event.wordArray = StopWords.words WHERE StopWords.words IS NULL
    GROUP BY
        arrayElement.ArrayValue,
        TumblingWindow(hour, 1)
    
    I renamed first step to be clearer with naming, and I may not fully understand your intent, but I hope you can take it from here.


    Azure Stream Analytics.

    • Marked as answer by k_palm Friday, February 23, 2018 3:30 PM
    Thursday, February 22, 2018 4:54 PM

All replies

  • The 'wordArray' is ambiguous in your query. It is used once as a name of the step and then as a field name.

    If in 'ON wordArray = StopWords' the wordArray and StopWords are names of streams then this is not allowed. You must use field names. But if you mean the wordArray field you project, then you should qualify it since it is used in a join - 'wordArray.wordArray'.

    Here is a rewrite that should work:

    WITH wordArrayStep AS (
        SELECT 
            CAST ( UDF.makeWordArray(tweet) AS array) AS wordArray
        FROM [TwitterIn] 
        TIMESTAMP BY DATEADD(millisecond, timestamp, '1970-01-01T00:00:00Z')
        )
    
    SELECT 
        COUNT(*) AS counts, 
        arrayElement.ArrayValue AS words,
        System.Timestamp AS Window
    
    INTO [toPowerBi]  
    
    FROM
        wordArrayStep AS event
    CROSS APPLY GetArrayElements(event.wordArray) AS arrayElement
    LEFT JOIN StopWords ON event.wordArray = StopWords.words WHERE StopWords.words IS NULL
    GROUP BY
        arrayElement.ArrayValue,
        TumblingWindow(hour, 1)
    
    I renamed first step to be clearer with naming, and I may not fully understand your intent, but I hope you can take it from here.


    Azure Stream Analytics.

    • Marked as answer by k_palm Friday, February 23, 2018 3:30 PM
    Thursday, February 22, 2018 4:54 PM
  • Thank you, that fixed this issue and the Query runs as it should.

    (Now i just have to fix the join so i does what i want i to do)

    Friday, February 23, 2018 3:30 PM