Export Custom Event Dimensions to SQL from Application Insights using Stream Analytics

A slightly more dynamic solution is to set up a temp table:

WITH ATable AS (SELECT     temp.internal.data.id as ID    ,dimensions.ArrayValue.CategoryAction as CategoryAction    ,dimensions.ArrayValue.SessionId as SessionId     ,dimensions.ArrayValue.WebsiteVersion as WebsiteVersion     ,dimensions.ArrayValue.PageSection as PageSection     ,dimensions.ArrayValue.Category as Category     ,dimensions.ArrayValue.Page as Page  FROM [analyticseventinputs] temp CROSS APPLY GetElements(temp.[context].[custom].[dimensions]) as dimensions)

and then doing joins based on a unique key

FROM [analyticseventinputs] Input Left JOIN ATable CategoryAction on     Input.internal.data.id = CategoryAction.ID AND    CategoryAction.CategoryAction <> "" AND     DATEDIFF(day, Input, CategoryAction) BETWEEN 0 AND 5 

The rather annoying bit is the requirement for the datediff, because the joins are intended to combine 2 streams of data but in this case you are only joining on the unique key. So I set it to a large value of 5 days. This really only protects against the custom params not coming in ordered compared to the other solution.

Most tutorials online use CROSS APPLY or OUTER APPLY however this is not what you are looking for because it will put each property on a different row. To over come this use the functions: GetRecordPropertyValue and GetArrayElement as demoed below. This will flatten out the properties into a single row.

SELECT    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 0), 'CategoryAction') AS CategoryAction,    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 1), 'SessionId') AS SessionId,    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 2), 'WebsiteVersion') AS WebsiteVersion,    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 3), 'PageSection') AS PageSection,    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 4), 'Category') AS Category,    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 5), 'Page') AS PageINTO  [outputstream]FROM  [inputstream] MySource

What schema do you have in SQL? Do you want a single row in SQL with all the dimensions as columns?

This might not be possible today. However there will be more Array/Record functions in Azure Stream Analytics after July 30.

Then you will be able to do something like this:

SELECT     CASE         WHEN GetArrayLength(A.context.custom.dimensions) > 0            THEN GetRecordPropertyValue(GetArrayElement(A.context.custom.dimensions, 0), 'CategoryAction')        ELSE ''        END AS CategoryAction     CASE         WHEN GetArrayLength(A.context.custom.dimensions) > 1            THEN GetRecordPropertyValue(GetArrayElement(A.context.custom.dimensions, 1), 'WebsiteVersion')        ELSE ''        END AS WebsiteVersion     CASE         WHEN GetArrayLength(A.context.custom.dimensions) > 2            THEN GetRecordPropertyValue(GetArrayElement(A.context.custom.dimensions, 2), 'PageSection')        ELSE ''        END AS PageSectionFROM input

If you want to have separate rows per dimension then you can use CROSS APPLY operator.