locked
No output events to Azure SQL or Power BI on processing query RRS feed

  • Question

  • Hi

    I have an ASA streaming job configured to read Application Insights metrics from a Blob storage container, query the data that I need, and output it to an Azure SQL DB and Power BI. The processing part of the input seems to be going well, I can verify this by testing the query for a given input stream window. However, no output events are triggered at all. 

    When I create an Azure SQL DB table that doesn't match the specs of the supposed outputted data, I get warnings such as "Encountered error trying to write 6 event(s): Column 'createdby' does not allow nulls." which leads to me to believe that it does try to output data. I see nothing appearing in SQL or in Power BI though.

    My SQL query to create the table:

    CREATE TABLE [dbo].[ApiUsages]
    (
      CreatedOn DATETIME NULL,
      CreatedBy NVARCHAR(MAX) NULL,
      ContractModelId INT NULL,
      Operation NVARCHAR(MAX) NULL,
      TransactionType NVARCHAR(6) NULL,
      INDEX IX_ContractModelId NONCLUSTERED (ContractModelId),
      INDEX IX_CreatedOn NONCLUSTERED (CreatedOn)
    )
    

    My ASA query:
    WITH elements
    AS
    (
    SELECT
        msg.Internal.Data.Id,
        coll.ArrayValue
    FROM 
        AIInput msg
    CROSS APPLY GetArrayElements(msg.Context.Custom.Dimensions) AS coll
    ),
    properties
    AS
    (
    SELECT 
        el.Id,
        prop.*
    FROM elements el
    CROSS APPLY GetRecordProperties(el.ArrayValue) AS prop
    ),
    records
    AS
    (
    SELECT
        msg.Internal.Data.Id,
        msg.Context.Data.EventTime AS CreatedOn,
        p1.PropertyValue AS CreatedBy,
        p2.PropertyValue AS Operation,
        p3.PropertyValue AS TransactionType,
        p4.PropertyValue AS ContractModelId
    FROM
        AIInput as msg
        LEFT JOIN properties p1 ON p1.Id = msg.Internal.Data.Id AND DATEDIFF(millisecond, msg, p1) = 0 AND p1.PropertyName = 'Identity'
        LEFT JOIN properties p2 ON p2.Id = msg.Internal.Data.Id AND DATEDIFF(millisecond, msg, p2) = 0 AND p2.PropertyName = 'OperationName'
        LEFT JOIN properties p3 ON p3.Id = msg.Internal.Data.Id AND DATEDIFF(millisecond, msg, p3) = 0 AND p3.PropertyName = 'Method'
        LEFT JOIN properties p4 ON p4.Id = msg.Internal.Data.Id AND DATEDIFF(millisecond, msg, p4) = 0 AND p4.PropertyName = 'ContractModelId'
    )
    
    SELECT	
        CreatedOn,
        CreatedBy,
        ContractModelId,
        Operation,
        TransactionType
    INTO ReportingDb
    FROM records
    WHERE Operation IS NOT NULL
    
    SELECT	
        CreatedOn,
        CreatedBy,
        ContractModelId,
        Operation,
        TransactionType
    INTO PowerBI
    FROM records
    WHERE Operation IS NOT NULL

    The resulting json from the ASA query (formatted for readability):
    [
    	{"createdon":"2017-03-06T08:43:22.5070000Z","createdby":"DummyUser","contractmodelid":"6","operation":"DummyOperation","transactiontype":"POST"},
    	{"createdon":"2017-03-06T08:43:23.1870000Z","createdby":"DummyUser","contractmodelid":"6","operation":"DummyOperation","transactiontype":"POST"},
    	{"createdon":"2017-03-06T08:43:24.0840000Z","createdby":"DummyUser","contractmodelid":"6","operation":"DummyOperation","transactiontype":"POST"}
    ]

    Does someone have an idea what the reason could be that output events aren't triggered for this?

    Br Kris

    Monday, March 6, 2017 9:31 AM

All replies

  • Also including a metric chart for the past hour. The data conversion and runtime errors are a result of me messing with the SQL DB.

    Monday, March 6, 2017 9:42 AM
  • Update.

    Removing the "WHERE Operation IS NOT NULL" line from both my SELECT INTO queries now ensures that data is outputted. However, unlike the data result I received from the web-based Test panel, now all my fields except for "CreatedOn" are empty.

    I presume that the LEFT JOINS from my INPUT on the CTE are not yielding any desired results. Could the DATEDIFF be the root cause of this? And if so, is there a big difference between live query processing and when testing the query?

    Br Kris

    Monday, March 6, 2017 10:01 AM
  • Update 2.

    After updating the datepart on the LEFT JOINS' DATEDIFF's from "millisecond" to "hour" I'm getting the results I expected from the beginning.

    This leaves me with the question as to why the test run yielded results while a live run does not?

    Br Kris

    • Marked as answer by Kris.C Monday, March 6, 2017 10:43 AM
    • Unmarked as answer by Kris.C Monday, March 6, 2017 2:15 PM
    Monday, March 6, 2017 10:43 AM
  • Update 3.

    I was celebrating too soon. About 2 hours ago the output events stopped working again. No idea why as it worked pretty well before. When doing a test run of the query the results show correctly.

    This is rather troublesome since we intend to use this data for reporting purposes.

    Update 4.

    I stopped and started the job and suddenly I get all my data from the past 2 hours outputted to my data sinks. I'm under the impression that ASA's functionality isn't really consistent?

    Br Kris



    • Edited by Kris.C Monday, March 6, 2017 2:35 PM
    Monday, March 6, 2017 2:15 PM
  • Hi Kris,

    Did you get any errors in the Activity Log before the job stopped processing? Can it be there is no data to be processed in the last two hours?

    If you got any errors in the Activity Log you can turn on diagnostics logs (https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-job-diagnostic-logs) and configure them to be stored in a Azure storage account where you might get more details about what is going on.

    If you can share the subscription id, the job name and the time (with a time zone or UTC) when it stopped processing I can take a look at our service logs to check for problems.

    Thanks


    This posting is provided "AS IS" with no warranties, and confers no rights


    Tuesday, March 7, 2017 7:04 PM
  • Hi Silviu

    No, the activity log is clear of any error messages or the likes. What I did right now is update the query so it looks for a DATEDIFF between 0 and 3 minutes. This seems to ensure that messages are outputted as I would expect.

    I'll keep monitoring the functionality over the next few days and will update this thread with my findings next week.

    Br Kris

    Thursday, March 9, 2017 12:12 AM