public static List <MetricDelta <Video, long> > CompareVideoLifetimeDailyTotal() { var now = DateTime.UtcNow; using (var analytics = new DataLakeYouTubeAnalyticsContext()) using (var db = new DataLakeYouTubeDataContext()) { var dayTotal = analytics.VideoDailyMetrics.Where(x => x.ValidityStart <= now && now < x.ValidityEnd) .GroupBy(x => x.VideoId, y => y, (k, v) => new { VideoId = k, Views = v.Sum(y => y.Views), StartDate = v.Min(y => y.Date), EndDate = v.Max(y => y.Date) }).ToList(); var stats = from s in db.Statistics.Where(x => x.CaptureDate == now.Date && x.ValidityStart <= now && now < x.ValidityEnd) join dt in dayTotal on s.VideoId equals dt.VideoId join v in db.Videos.Where(x => x.ValidityStart <= now && now < x.ValidityEnd) on s.VideoId equals v.VideoId select new MetricDelta <Video, long>() { Id = v, Lifetime = s.ViewCount, Total = dt.Views, DailyStart = dt.StartDate, DailyEnd = dt.EndDate, LifetimeDate = s.CaptureDate }; return(stats.ToList()); } }
public static void Write(IEnumerable <VideoDailyMetric> dailyMetrics) { using (var dlContext = new DataLakeYouTubeAnalyticsContext()) { var now = DateTime.UtcNow; foreach (var newObj in dailyMetrics) { var storedObj = dlContext.VideoDailyMetrics.SingleOrDefault(v => v.VideoId == newObj.VideoId && v.Date == newObj.Date && v.ValidityStart <= now && now < v.ValidityEnd); newObj.ValidityEnd = DateTime.MaxValue; newObj.ValidityStart = now; var modified = compareOldAndNew(storedObj, newObj); switch (modified) { case Modified.New: dlContext.Add(newObj); break; case Modified.Updated: storedObj.ValidityEnd = newObj.ValidityStart; dlContext.Add(newObj); break; default: break; } } dlContext.SaveChanges(); } }
public static void FetchViewerPercentageMetrics(YouTubeAnalyticsService analyticsService, string channelId, YTD.Video video, Logger logger) { using (var dbContext = new DataLakeYouTubeAnalyticsContext()) { var now = DateTime.UtcNow; var lastFetchedDate = dbContext.ViewerPercentageLastDates.SingleOrDefault(x => x.VideoId == video.VideoId); var initialDate = video.PublishedAt; if (lastFetchedDate == null) { // maybe the video simply doesn't support this report, check for early termination if (!RunViewerPercentageReport(analyticsService, channelId, video.PublishedAt, now, video).Any()) { logger.Debug("Report not available for video {VideoId}", video.VideoId); return; } lastFetchedDate = new YTA.ViewerPercentageLastDate() { VideoId = video.VideoId, Date = initialDate }; dbContext.Add(lastFetchedDate); } else { initialDate = DateHelper.Max(video.PublishedAt, DateHelper.Min(lastFetchedDate.Date, now - TimeMargin)); } int replicatedDays = 0; foreach (var date in DateHelper.DaysInRange(initialDate.Date, now.Date)) { var viewerPercentages = RunViewerPercentageReport(analyticsService, channelId, video.PublishedAt.Date, date, video); lastFetchedDate.Date = date; if (!viewerPercentages.Any()) { continue; } DbWriter.Write( video.VideoId, date, viewerPercentages.Select(x => Api2DbObjectConverter.ConvertViewerPercentageRow(x)), now ); dbContext.SaveChanges(); replicatedDays++; if (replicatedDays % MaxDaysToReplicateInIteration == 0) { break; } } logger.Debug("Replicated {Days} days for video {VideoId}", replicatedDays, video.VideoId); } }
public static IEnumerable <YTA.VideoDailyMetric> FetchDailyMetrics(YouTubeAnalyticsService analyticsService, string channelId, YTD.Video video, Logger logger, bool reprocess = false) { using (var dbContext = new DataLakeYouTubeAnalyticsContext()) { var now = DateTime.UtcNow; var mostRecentRecord = dbContext.VideoDailyMetrics .Where(x => x.VideoId == video.VideoId && x.ValidityStart <= now && now < x.ValidityEnd) .OrderByDescending(x => x.Date) .FirstOrDefault(); return(FetchVideoDailyMetrics(mostRecentRecord, channelId, video, now, analyticsService, logger, reprocess) .Select(x => Api2DbObjectConverter.ConvertDailyMetricRow(video.VideoId, x))); } }
public static void Write(string videoId, DateTime date, IEnumerable <ViewerPercentage> viewerPercentages, DateTime now) { using (var dlContext = new DataLakeYouTubeAnalyticsContext()) { List <ViewerPercentage> storedObjs = null; try { storedObjs = dlContext.ViewerPercentageMetric .Where(x => x.VideoId == videoId && x.StartDate <= date && x.ValidityStart <= now && now < x.ValidityEnd) .GroupBy(x => x.StartDate) .OrderByDescending(x => x.Key) .FirstOrDefault().ToList(); } catch (NullReferenceException) { // This just means that there are no objects in the database yet. } var modified = compareOldAndNew(storedObjs, viewerPercentages); // Close date range of previous values if (modified == Modified.Updated) { storedObjs.ForEach(x => { x.EndDate = date; }); } // Invalidate all entries for 'future' dates if (modified == Modified.New || modified == Modified.Updated) { dlContext.ViewerPercentageMetric .Where(x => x.VideoId == videoId && x.StartDate >= date && x.ValidityStart <= now && now < x.ValidityEnd) .ToList() .ForEach(x => { x.ValidityEnd = now; }); } foreach (var newObj in viewerPercentages) { switch (modified) { case Modified.New: case Modified.Updated: newObj.VideoId = videoId; newObj.StartDate = date; newObj.EndDate = DateTime.MaxValue; newObj.ValidityEnd = DateTime.MaxValue; newObj.ValidityStart = now; dlContext.Add(newObj); break; default: break; } } dlContext.SaveChanges(); } }