locked
Merging data into a single row RRS feed

  • Question

  • Hi! I'm trying to merge a set of data that is contained in an array that AppInsights creates during an event.

    The incoming storage blob looks like this;

    {"event":[{"name":"PageRead","count":1}],"internal":{"data":{"id":"246335b6-a6fe-4fce-b846-fc4cdf0df7c2","documentVersion":"1.61"}},"context":{"device":{"os":"Windows","osVersion":"Windows 8.1","type":"PC","browser":"Chrome","browserVersion":"Chrome 46.0","screenResolution":{"value":"1920X1080"},"locale":"unknown","id":"browser"},"application":{},"location":{"continent":"Europe","country":"Sweden","clientip":"xxx.xxx.xxx.xxx","province":"","city":""},"data":{"isSynthetic":false,"eventTime":"2015-11-11T10:26:53.75Z","samplingRate":100.0},"user":{"isAuthenticated":false,"anonId":"09425677-173C-4402-803A-A189CFBCE054","anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{"id":"2E08416A-7F11-4081-BBC3-AF14C7007CE5","parentId":"2E08416A-7F11-4081-BBC3-AF14C7007CE5"},"cloud":{},"serverDevice":{},"custom":{"dimensions":[{"User":"xxxxxxx"},{"Thread":"Policy för trådlöst gästnät"},{"Environment":"PROD"}],"metrics":[]},"session":{"id":"2D3BCE07-0F06-4623-BBAD-35EAAB93834A","isFirst":false}}}

    In context.custom.dimensions it inserts an entry for each field rather than a single object to represent the custom event. I've tried with various ways of trying to isolate the values and make a merged/single-row insert to the SQL output but to no avail. The "furthest" I've gotten is that it complains about the INTO-clause in the editor.

    Version 1

    WITH mSubSet AS (
        SELECT flatDimension.ArrayValue.[Thread] AS [Thread],
    	flatDimension.ArrayValue.[Section] AS [Section],
    	flatDimension.ArrayValue.[User] AS [User],
    	flatDimension.ArrayValue.[Environment] AS [Environment],
    	KFAI1.context.data.eventTime as [EventDate],
            flatEvent.ArrayValue.name as [EventType],
            KFAI1.internal.data.id as [EventId]       
        FROM [KompetensForumAppInsights] KFAI1
        CROSS APPLY GetElements(KFAI1.[event]) as flatEvent
        CROSS APPLY GetElements(KFAI1.context.custom.dimensions) as flatDimension
    )
    
    SELECT s2.Thread, s3.Section, s4.User, s5.Environment, s1.EventDate, s1.EventType, s1.EventId 
    FROM mSubSet s1
    INNER JOIN mSubSet s2 ON s1.EventId=s2.EventId AND s2.[Thread] IS NOT NULL
    INNER JOIN mSubSet s3 ON s1.EventId=s3.EventId AND s3.[Section] IS NOT NULL
    INNER JOIN mSubSet s4 ON s1.EventId=s4.EventId AND s4.[User] IS NOT NULL
    INNER JOIN mSubSet s5 ON s1.EventId=s5.EventId AND s5.[Environment] IS NOT NULL
    INTO [KompetensForumAzureSQL]

    and Version 2

    WITH mThread AS (
      SELECT flatDimension1.ArrayValue.[Thread] AS [Thread]
      FROM [KompetensForumAppInsights] KFAI1
      CROSS APPLY GetElements(KFAI1.context.custom.dimensions) as flatDimension1
      WHERE flatDimension1.ArrayValue.[Thread] IS NOT NULL
    ),
    mSection AS (
      SELECT flatDimension2.ArrayValue.[Section] AS [Section] 
      FROM [KompetensForumAppInsights] KFAI2
      CROSS APPLY GetElements(KFAI2.context.custom.dimensions) as flatDimension2
      WHERE flatDimension2.ArrayValue.[Section] IS NOT NULL
    ),
    mUser AS (
      SELECT flatDimension3.ArrayValue.[User] AS [User] 
      FROM [KompetensForumAppInsights] KFAI3
      CROSS APPLY GetElements(KFAI3.context.custom.dimensions) as flatDimension3
      WHERE flatDimension3.ArrayValue.[User] IS NOT NULL
    ),
    mEnv AS (
      SELECT flatDimension4.ArrayValue.[Environment] AS [Environment] 
      FROM [KompetensForumAppInsights] KFAI4
      CROSS APPLY GetElements(KFAI4.context.custom.dimensions) as flatDimension3
      WHERE flatDimension4.ArrayValue.[Section] IS NOT NULL
    )
    
    SELECT 	mThread.[Thread] AS [Thread], 
    	mSection.[Section] AS [Section],
    	mUser.[User] AS [User],
    	mEnv.[Environment] AS [Environment],
    	KFAI5.context.data.eventTime as [EventDate],
            flatEvent.ArrayValue.name as [EventType],
            KFAI5.internal.data.id as [EventId]       
    FROM [KompetensForumAppInsights] KFAI5
    CROSS APPLY GetElements(KFAI5.[event]) as flatEvent
    INTO [KompetensForumAzureSQL]

    Neither of these will work. If I just run with this

        SELECT flatDimension.ArrayValue.[Thread] AS [Thread],
    	flatDimension.ArrayValue.[Section] AS [Section],
    	flatDimension.ArrayValue.[User] AS [User],
    	flatDimension.ArrayValue.[Environment] AS [Environment],
    	KFAI1.context.data.eventTime as [EventDate],
            flatEvent.ArrayValue.name as [EventType],
            KFAI1.internal.data.id as [EventId]       
        FROM [KompetensForumAppInsights] KFAI1
        CROSS APPLY GetElements(KFAI1.[event]) as flatEvent
        CROSS APPLY GetElements(KFAI1.context.custom.dimensions) as flatDimension
    I get 3 rows due to user,thread and environment existing in separate entries in the array. If Thread is null, Section isn't.

    Tuesday, December 1, 2015 9:02 AM

Answers

  • I've had to drop the Guid/uniqueidentifier column in SQL until Azure Stream Analytics supports it. I could make an intermediate table and have a trigger convert the incoming string to a uniqueidentifier but that's a bit of a hassle since I'm confident I won't be getting duplicate entries anymore anyway.

    And also I'm running with the assumption of array-positions for the properties for now until a more dynamic method rears it's head.

    Tuesday, December 1, 2015 2:53 PM

All replies

  • Doing a dangerous assumption, that the user,thread and environment variables will be in the same arrayindex everytime, I can get onwards, sort of, with this query;

     SELECT CAST(GetArrayElement(KFAI.context.custom.dimensions,0) AS NVARCHAR(MAX)) as [User],
            CAST(GetArrayElement(KFAI.context.custom.dimensions,1) AS NVARCHAR(MAX)) as [Thread],
            CAST(GetArrayElement(KFAI.context.custom.dimensions,2) AS NVARCHAR(MAX)) as [Environment],
            KFAI.context.data.eventTime as [EventDate],
            flatEvent.ArrayValue.name as [EventType],
            KFAI.internal.data.id as [EventId]
    INTO [KompetensForumAzureSQL]               
    FROM    [KompetensForumAppInsights] KFAI
    CROSS APPLY GetElements(KFAI.[event]) as flatEvent

    As you can see, I'm in the process of casting those records because I saw an error in the management log that it was unable to insert into the sql-table because it was unable to convert from record to system.string. 

    And trying to CAST said elements to NVARCHAR(MAX) the interface says it cannot cast element of type 'any' to 'nvarchar(max)'

    There's always something at every turn....

    Tuesday, December 1, 2015 12:20 PM
  • I've had to drop the Guid/uniqueidentifier column in SQL until Azure Stream Analytics supports it. I could make an intermediate table and have a trigger convert the incoming string to a uniqueidentifier but that's a bit of a hassle since I'm confident I won't be getting duplicate entries anymore anyway.

    And also I'm running with the assumption of array-positions for the properties for now until a more dynamic method rears it's head.

    Tuesday, December 1, 2015 2:53 PM