Sample Data

This data shows the income data for the first player in a match.

{
  "EventId": "abc",
  "EventName": "MatchEnd",
  "EventNamespace": "Development",
  "EventTimestamp": "2022-07-12T09:40:40.219514Z",
  "Payload": {
    "gameDuration": 1636
    "playerData": [
      {
        "heroId": 3,
        "incomeData": {
          "goldEarned": 58613,
          "goldEarnedAtInterval": [
            500,
            1884,
            4325,
            6605,
            8780
          ],
          "goldSpent": 58459
        }
      }
    ]
    "winningTeam": 1
  }
}

Configuring The Views

-- Create a readable/query-able View for MatchEnd
CREATE OR REPLACE VIEW MatchEnd as
select   event:EventNamespace::varchar as namespace
        ,event:EventTimestamp::timestamp as eventTime
        ,event:Payload.matchId::varchar as matchId
        ,event:Payload.gameMode::varchar as gameMode
        ,event:Payload.winningTeam::int as winningTeam
        ,event:Payload.gameDuration::int as gameDuration
        ,event:Payload.startTime::timestamp as startTime
        ,event:Payload.endTime::timestamp as endTime
        ,event:Payload.region::varchar as region
        ,event:Payload.server::varchar as server
        ,event:Payload.playerData::variant as playerData
from ANALYTICS_GAME_TELEMETRY_PROD_MATCHEND where event:EventName = 'MatchEnd';
GRANT SELECT ON VIEW IDENTIFIER('MatchEnd') TO ROLE IDENTIFIER('READ_ONLY');
-- Preview the View
select * from MatchEnd limit 10;


CREATE OR REPLACE VIEW MatchEnd_PlayerData as
select   matchId::varchar as matchId
        ,eventTime::timestamp as eventTime
        ,p.value:playerId::varchar as playerId
        ,p.value:teamId::varchar as teamId
        ,p.value:heroId::varchar as heroId
        ,p.value:incomeData::object as incomeData
from MatchEnd,
lateral flatten (input => playerData) p;
GRANT SELECT ON VIEW IDENTIFIER('MatchEnd_PlayerData') TO ROLE IDENTIFIER('READ_ONLY');
-- Preview the View
select * from MatchEnd_PlayerData limit 100;


CREATE OR REPLACE VIEW MatchEnd_PlayerData_IncomeData as
select   matchId::varchar as matchId
        ,eventTime::timestamp as eventTime
        ,playerId::varchar as playerId
        ,p.value:goldEarned::int as goldEarned
        ,p.value:goldEarnedAtInterval::array as goldEarnedAtInterval
        ,p.value:assists::int as assists
        ,p.value:deaths::int as deaths
        ,p.value:largestMultiKill::int as largestMultiKill
        ,p.value:largestKillingSpree::int as largestKillingSpree
from MatchEnd_PlayerData,
lateral flatten (input => combatData) p;
GRANT SELECT ON VIEW IDENTIFIER('MatchEnd_PlayerData_CombatData') TO ROLE IDENTIFIER('READ_ONLY');
-- Preview the View
select * from MatchEnd_PlayerData_CombatData limit 100;