i'm following example walkthrough export sql application insights using stream analytics. trying export custom event dimensions (context.custom.dimensions in json example below) added nested json array in data file. how flatten dimensions array @ context.custom.dimensions export sql?
json...
{ "event": [ { "name": "50_distanceselect", "count": 1 } ], "internal": { "data": { "id": "aad2627b-60c5-48e8-aa35-197cae30a0cf", "documentversion": "1.5" } }, "context": { "device": { "os": "windows", "osversion": "windows 8.1", "type": "pc", "browser": "chrome", "browserversion": "chrome 43.0", "screenresolution": { "value": "1920x1080" }, "locale": "unknown", "id": "browser", "useragent": "mozilla/5.0 (windows nt 6.3; wow64) applewebkit/537.36 (khtml, gecko) chrome/43.0.2357.134 safari/537.36" }, "application": {}, "location": { "continent": "north america", "country": "united states", "point": { "lat": 38.0, "lon": -97.0 }, "clientip": "0.115.6.185", "province": "", "city": "" }, "data": { "issynthetic": false, "eventtime": "2015-07-15t23:43:27.595z", "samplingrate": 0.0 }, "operation": { "id": "2474ee6f-5f6f-48c3-ba43-51636928075a" }, "user": { "anonid": "ba05c4be-1c42-482f-9836-d79008e78a9d", "anonacquisitiondate": "0001-01-01t00:00:00z", "authacquisitiondate": "0001-01-01t00:00:00z", "accountacquisitiondate": "0001-01-01t00:00:00z" }, "custom": { "dimensions": [ { "categoryaction": "click" }, { "sessionid": "73ef454d-fa39-4125-b4d0-44486933533b" }, { "websiteversion": "3.0" }, { "pagesection": "filterfind" }, { "category": "eventcategory1" }, { "page": "/page-in-question" } ], "metrics": [] }, "session": { "id": "062703e5-5e15-491a-ac75-2fe54ef03623", "isfirst": false } } }
a more dynamic solution set temp table:
with atable ( select temp.internal.data.id id ,dimensions.arrayvalue.categoryaction categoryaction ,dimensions.arrayvalue.sessionid sessionid ,dimensions.arrayvalue.websiteversion websiteversion ,dimensions.arrayvalue.pagesection pagesection ,dimensions.arrayvalue.category category ,dimensions.arrayvalue.page page [analyticseventinputs] temp cross apply getelements(temp.[context].[custom].[dimensions]) dimensions) and doing joins based on unique key
from [analyticseventinputs] input left join atable categoryaction on input.internal.data.id = categoryaction.id , categoryaction.categoryaction <> "" , datediff(day, input, categoryaction) between 0 , 5 the rather annoying bit requirement datediff, because joins intended combine 2 streams of data in case joining on unique key. set large value of 5 days. protects against custom params not coming in ordered compared other solution.
Comments
Post a Comment