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();
}
}
