Экспорт пользовательских измерений событий в SQL из Application Insights с помощью Stream Analytics

Чуть более динамичное решение — настроить временную таблицу:

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)

а затем выполнять соединения на основе уникального ключа

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 

Довольно раздражающим моментом является требование для datediff, потому что соединения предназначены для объединения двух потоков данных, но в этом случае вы соединяетесь только по уникальному ключу. Поэтому я установил большое значение 5 дней. Это действительно защищает только от того, что пользовательские параметры не упорядочены по сравнению с другим решением.

Большинство руководств в Интернете используют CROSS APPLY или OUTER APPLY, однако это не то, что вам нужно, потому что каждое свойство будет помещено в другую строку. Чтобы преодолеть это, используйте функции: GetRecordPropertyValue и GetArrayElement, как показано ниже. Это объединит свойства в одну строку.

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 Page
INTO
  [outputstream]
FROM
  [inputstream] MySource

Какая схема у вас есть в SQL? Вам нужна одна строка в SQL со всеми измерениями в виде столбцов?

Сегодня это может быть невозможно. Однако после 30 июля в Azure Stream Analytics будет больше функций массива/записи.

Тогда вы сможете сделать что-то вроде этого:

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 PageSection
FROM input

Если вы хотите иметь отдельные строки для каждого измерения, вы можете использовать оператор CROSS APPLY.

Очень удобный способ сделать это, предложенный Алексом Райзманом, состоит в том, чтобы выполнить некоторые агрегации для полей, которые вы хотите сгладить, группируя по оставшемуся списку выбора, предполагая, что

• вы знаете набор возможных объектов в измерениях, и
• у вас нет повторяющихся объектов в этом массиве, и

• есть что-то, что вы можете однозначно идентифицировать ваши начальные строки (например, id )

  SELECT
    CategoryAction= MIN(CAST(GetRecordPropertyValue(d.arrayvalue, 'CategoryAction') AS
    NVARCHAR(MAX))),
    SessionId= min(CAST(GetRecordPropertyValue(d.arrayvalue, 'SessionId') AS
    NVARCHAR(MAX))),
    WebsiteVersion= MIN(CAST(GetRecordPropertyValue(d.arrayvalue, 'WebsiteVersion') AS
    NVARCHAR(MAX))),
    PageSection= MIN(CAST(GetRecordPropertyValue(d.arrayvalue, 'PageSection') AS
    NVARCHAR(MAX))),
    Category= MIN(CAST(GetRecordPropertyValue(d.arrayvalue, 'Category') AS
    NVARCHAR(MAX))),    
    Page= MIN(CAST(GetRecordPropertyValue(d.arrayvalue, 'Page') AS NVARCHAR(MAX))) 
  INTO  
    [outputstream] 
  FROM [inputstream] MySource 
  CROSS APPLY GetArrayElements(MySource.[context].[custom].[dimensions]) d 
  GROUP BY System.Timestamp, MySource.id
  

Мы также группируем по System.Timestamp, чтобы создать одно временное окно, как ожидается от Stream Analytics, для выполнения операций на основе наборов, таких как подсчет или агрегирование.

Хотя вопрос старый. Но именно так достигается одна строка для пользовательских размеров. Это может стать уродливым по мере увеличения количества настраиваемых параметров.

    SELECT
    A.internal.data.id,        
    eventFlat.ArrayValue.name as eventName,
    A.context.operation.name as operation,
    A.context.data.eventTime,
    a1.company,
    a2.userId,
    a3.feature,        
    A.context.device,    
    A.context.location         
FROM [YourInputAlias] A   
OUTER APPLY GetArrayElements(A.event) eventFlat  
LEFT JOIN (
        SELECT 
        A1.internal.data.id as id,   
        customDimensionsFlat.ArrayValue.company
      FROM [YourInputAlias] A1  
      OUTER APPLY GetArrayElements(A1.context.custom.dimensions) customDimensionsFlat   
      where  customDimensionsFlat.ArrayValue.company IS NOT NULL
      ) a1 ON a.internal.data.id = a1.id AND datediff(day, a, a1) between 0 and 5
LEFT JOIN (
        SELECT 
        A2.internal.data.id as id,   
        customDimensionsFlat.ArrayValue.userid     
      FROM [YourInputAlias] A2  
      OUTER APPLY GetArrayElements(A2.context.custom.dimensions) customDimensionsFlat    
      where  customDimensionsFlat.ArrayValue.userid  IS NOT NULL
      ) a2 ON a.internal.data.id = a2.id AND datediff(day, a, a2) between 0 and 5
LEFT JOIN (
        SELECT 
        A3.internal.data.id as id,   
        customDimensionsFlat.ArrayValue.feature     
      FROM [YourInputAlias] A3
      OUTER APPLY GetArrayElements(A3.context.custom.dimensions) customDimensionsFlat    
      where  customDimensionsFlat.ArrayValue.feature  IS NOT NULL
      ) a3 ON a.internal.data.id = a3.id AND datediff(day, a, a3) between 0 and 5