Stream Analytics from App Insights BLOB failing to run : GetArrayElement "Expression is not supported"

Question

Context: I'm basically trying to get data out of Application Insights into Power BI. This needs to include long-term trend analysis, so I have (as directed) tried to setup Continuous Export and using Stream Analytics to export this to a Power BI dataset.

My first (extremely basic) test worked, but now I have tried to up the complexity slightly by extracting some custom dimensions (one called "SP_Page_Topic") and I keep getting the following error:

"The streaming job failed: Stream Analytics job has validation errors: Query compilation error: Expression is not supported: 'GetArrayElement(context.custom.dimensions, 0).SP_Page_Topic'..",

This works PERFECTLY fine from the Query Builder "test" function and I can see the preview of the output and it looks all correct and good.

But when the Stream Analytics job runs, I just get this error over and over.

Note - this is my query in Stream Analytics:

WITH Step1 AS (
SELECT
      context.operation.name as PageUrl,
      context.data.eventTime as EventTime,
      context.device.type as DeviceType,
      context.device.osVersion as OSVersion,
      context.device.browser as Browser,
      context.device.browserVersion as BrowserVersion,
      context.location.continent as Region,
      context.location.country as Country,
      GetArrayElement(context.custom.dimensions, 0).SP_Page_Topic as Topic
FROM
[AllInput]
)

SELECT * INTO [PageViewOutput]
FROM Step1

Sasha Alperovich Sasha Alperovich · Accepted Answer · 2019-09-27T21:44:49

You'll have to add one more step to avoid referring the property of a GetArrayElement outcome.

WITH Step1 AS (
SELECT
    context.operation.name as PageUrl,
    context.data.eventTime as EventTime,
    context.device.type as DeviceType,
    context.device.osVersion as OSVersion,
    context.device.browser as Browser,
    context.device.browserVersion as BrowserVersion,
    context.location.continent as Region,
    context.location.country as Country,
    GetArrayElement(context.custom.dimensions, 0) as Page
FROM
[AllInput]
),
Step2 AS (
SELECT
    PageUrl,
    EventTime,
    DeviceType,
    OSVersion,
    Browser,
    BrowserVersion,
    Region,
    Country,
    Page.SP_Page_Topic as Topic
FROM Step1
)

SELECT * INTO [PageViewOutput]
FROM Step2

Stream Analytics from App Insights BLOB failing to run : GetArrayElement "Expression is not supported"

1 Answers