public ReportDataCollector(
ILogger <ReportDataCollector> logger,
string procedureName,
Func <Task <SqlConnection> > openGallerySqlConnectionAsync,
int commandTimeoutSeconds,
ApplicationInsightsHelper applicationInsightsHelper)
{
_logger = logger;
_procedureName = procedureName;
_openGallerySqlConnectionAsync = openGallerySqlConnectionAsync;
_commandTimeoutSeconds = commandTimeoutSeconds;
_applicationInsightsHelper = applicationInsightsHelper ?? throw new ArgumentNullException(nameof(applicationInsightsHelper));
}
private static async Task WithRetry(Func <Task> action, ILogger logger, ApplicationInsightsHelper applicationInsightsHelper)
{
int attempts = 10;
while (attempts-- > 0)
{
Exception caught;
try
{
await action();
break;
}
catch (Exception ex)
{
if (attempts == 1)
{
throw;
}
else
{
caught = ex;
}
}
SqlConnection.ClearAllPools();
logger.LogError("SQL Invocation failed, retrying. {RemainingAttempts} attempts remaining. Exception: {Exception}", attempts, caught);
if (caught is SqlException sqlException && sqlException.InnerException is Win32Exception win32Exception)
{
logger.LogError("SqlException with inner Win32Exception, native error code: {NativeErrorCode}", win32Exception.NativeErrorCode);
// Native error code reference:
// https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-erref/18d8fbe8-a967-4f1c-ae50-99ca8e491d2d
if (win32Exception.NativeErrorCode == 0x00000102) // WAIT_TIMEOUT
{
applicationInsightsHelper.TrackMetric("Stats.SqlQueryTimeout", 1);
}
}
await Task.Delay(20 * 1000);
}
}
public static async Task <IReadOnlyCollection <DirtyPackageId> > GetDirtyPackageIds(
ILogger logger,
Func <Task <SqlConnection> > openGallerySqlConnectionAsync,
DateTime reportGenerationTime,
int commandTimeout,
ApplicationInsightsHelper applicationInsightsHelper)
{
logger.LogInformation("Getting list of dirty packages IDs.");
IReadOnlyCollection <DirtyPackageId> packageIds = new List <DirtyPackageId>();
// Get the data
await WithRetry(
async() => packageIds = await GetDirtyPackageIdsFromWarehouse(openGallerySqlConnectionAsync, reportGenerationTime, commandTimeout),
logger,
applicationInsightsHelper);
logger.LogInformation("Found {DirtyPackagesCount} dirty packages to update.", packageIds.Count);
return(packageIds);
}
private async Task GenerateStandardReport(
string reportName,
DateTime reportGenerationTime,
CloudBlobContainer destinationContainer,
ILogger <ReportBuilder> reportBuilderLogger,
ILogger <ReportDataCollector> reportCollectorLogger)
{
var stopwatch = Stopwatch.StartNew();
var reportBuilder = new ReportBuilder(reportBuilderLogger, reportName);
var reportDataCollector = new ReportDataCollector(reportCollectorLogger, _storedProcedures[reportName], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds);
await ProcessReport(LoggerFactory, destinationContainer, reportBuilder, reportDataCollector, reportGenerationTime);
stopwatch.Stop();
var reportMetricName = reportName + " report";
ApplicationInsightsHelper.TrackMetric(reportMetricName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(reportMetricName);
}
public override void Init(IServiceContainer serviceContainer, IDictionary <string, string> jobArgsDictionary)
{
base.Init(serviceContainer, jobArgsDictionary);
var configuration = _serviceProvider.GetRequiredService <IOptionsSnapshot <CreateAzureCdnWarehouseReportsConfiguration> >().Value;
_sqlCommandTimeoutSeconds = configuration.CommandTimeOut ?? DefaultSqlCommandTimeoutSeconds;
_perPackageReportDegreeOfParallelism = configuration.PerPackageReportDegreeOfParallelism ?? DefaultPerPackageReportDegreeOfParallelism;
_cloudStorageAccount = ValidateAzureCloudStorageAccount(
configuration.AzureCdnCloudStorageAccount,
nameof(configuration.AzureCdnCloudStorageAccount));
_statisticsContainerName = ValidateAzureContainerName(
configuration.AzureCdnCloudStorageContainerName,
nameof(configuration.AzureCdnCloudStorageContainerName));
_dataStorageAccount = ValidateAzureCloudStorageAccount(
configuration.DataStorageAccount,
nameof(configuration.DataStorageAccount));
_reportNameConfig = ValidateReportName(
configuration.ReportName,
nameof(configuration.ReportName));
var containerNames = configuration.DataContainerName
.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var containerName in containerNames)
{
ValidateAzureContainerName(containerName, nameof(configuration.DataContainerName));
}
_dataContainerNames = containerNames;
_applicationInsightsHelper = new ApplicationInsightsHelper(ApplicationInsightsConfiguration.TelemetryConfiguration);
}
public override async Task <bool> Run()
{
try
{
var reportGenerationTime = DateTime.UtcNow;
var destinationContainer = _cloudStorageAccount.CreateCloudBlobClient().GetContainerReference(_statisticsContainerName);
_logger.LogDebug("Generating reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}", _statisticsDatabase.DataSource, _statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
if (string.IsNullOrEmpty(_reportName))
{
// generate all reports
var reportGenerators = new Dictionary <ReportBuilder, ReportDataCollector>
{
{ new ReportBuilder(ReportNames.NuGetClientVersion), new ReportDataCollector(_storedProcedures[ReportNames.NuGetClientVersion], _statisticsDatabase) },
{ new ReportBuilder(ReportNames.Last6Weeks), new ReportDataCollector(_storedProcedures[ReportNames.Last6Weeks], _statisticsDatabase) },
{ new ReportBuilder(ReportNames.RecentPopularity), new ReportDataCollector(_storedProcedures[ReportNames.RecentPopularity], _statisticsDatabase) },
{ new ReportBuilder(ReportNames.RecentPopularityDetail), new ReportDataCollector(_storedProcedures[ReportNames.RecentPopularityDetail], _statisticsDatabase) }
};
foreach (var reportGenerator in reportGenerators)
{
await ProcessReport(destinationContainer, reportGenerator.Key, reportGenerator.Value, reportGenerationTime);
ApplicationInsightsHelper.TrackReportProcessed(reportGenerator.Key.ReportName + " report");
}
await RebuildPackageReports(destinationContainer, reportGenerationTime);
await CleanInactiveRecentPopularityDetailByPackageReports(destinationContainer, reportGenerationTime);
}
else
{
// generate only the specific report
var reportBuilder = new ReportBuilder(_reportName);
var reportDataCollector = new ReportDataCollector(_storedProcedures[_reportName], _statisticsDatabase);
await ProcessReport(destinationContainer, reportBuilder, reportDataCollector, reportGenerationTime);
}
_logger.LogInformation("Generated reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}", _statisticsDatabase.DataSource, _statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
// totals reports
var stopwatch = Stopwatch.StartNew();
// build downloads.v1.json
var targets = new List <StorageContainerTarget>();
targets.Add(new StorageContainerTarget(_cloudStorageAccount, _statisticsContainerName));
foreach (var dataContainerName in _dataContainerNames)
{
targets.Add(new StorageContainerTarget(_dataStorageAccount, dataContainerName));
}
var downloadCountReport = new DownloadCountReport(targets, _statisticsDatabase, _galleryDatabase);
await downloadCountReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(DownloadCountReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(DownloadCountReport.ReportName);
stopwatch.Restart();
// build stats-totals.json
var galleryTotalsReport = new GalleryTotalsReport(_cloudStorageAccount, _statisticsContainerName, _statisticsDatabase, _galleryDatabase);
await galleryTotalsReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(GalleryTotalsReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(GalleryTotalsReport.ReportName);
// build tools.v1.json
var toolsReport = new DownloadsPerToolVersionReport(_cloudStorageAccount, _statisticsContainerName, _statisticsDatabase, _galleryDatabase);
await toolsReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(DownloadsPerToolVersionReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(DownloadsPerToolVersionReport.ReportName);
stopwatch.Restart();
return(true);
}
catch (Exception exception)
{
_logger.LogError("Job run failed! {Exception}", exception);
return(false);
}
}
private async Task RebuildPackageReports(CloudBlobContainer destinationContainer, DateTime reportGenerationTime)
{
var dirtyPackageIds = await ReportDataCollector.GetDirtyPackageIds(_statisticsDatabase, reportGenerationTime);
if (!dirtyPackageIds.Any())
{
return;
}
// first process the top 100 packages
var top100 = dirtyPackageIds.Take(100);
var reportDataCollector = new ReportDataCollector(_storedProceduresPerPackageId[ReportNames.RecentPopularityDetailByPackageId], _statisticsDatabase);
var top100Task = Parallel.ForEach(top100, new ParallelOptions {
MaxDegreeOfParallelism = 4
}, dirtyPackageId =>
{
var packageId = dirtyPackageId.PackageId.ToLowerInvariant();
var reportBuilder = new RecentPopularityDetailByPackageReportBuilder(ReportNames.RecentPopularityDetailByPackageId, "recentpopularity/" + _recentPopularityDetailByPackageReportBaseName + packageId);
ProcessReport(destinationContainer, reportBuilder, reportDataCollector, reportGenerationTime, Tuple.Create("@PackageId", 128, dirtyPackageId.PackageId)).Wait();
ApplicationInsightsHelper.TrackReportProcessed(reportBuilder.ReportName + " report", packageId);
});
// once top 100 is processed, continue with the rest
if (top100Task.IsCompleted)
{
var excludingTop100 = dirtyPackageIds.Skip(100);
top100Task = Parallel.ForEach(excludingTop100, new ParallelOptions {
MaxDegreeOfParallelism = 8
},
dirtyPackageId =>
{
// generate all reports
var reportGenerators = new Dictionary <ReportBuilder, ReportDataCollector>
{
{
new RecentPopularityDetailByPackageReportBuilder(
ReportNames.RecentPopularityDetailByPackageId,
"recentpopularity/" + _recentPopularityDetailByPackageReportBaseName +
dirtyPackageId.PackageId.ToLowerInvariant()),
new ReportDataCollector(
_storedProceduresPerPackageId[ReportNames.RecentPopularityDetailByPackageId],
_statisticsDatabase)
}
};
foreach (var reportGenerator in reportGenerators)
{
ProcessReport(destinationContainer, reportGenerator.Key, reportGenerator.Value,
reportGenerationTime, Tuple.Create("@PackageId", 128, dirtyPackageId.PackageId)).Wait();
ApplicationInsightsHelper.TrackReportProcessed(reportGenerator.Key.ReportName + " report",
dirtyPackageId.PackageId.ToLowerInvariant());
}
});
if (top100Task.IsCompleted)
{
var runToCursor = dirtyPackageIds.First().RunToCuror;
await ReportDataCollector.UpdateDirtyPackageIdCursor(_statisticsDatabase, runToCursor);
}
}
}
public override async Task Run()
{
var statisticsDatabase = GetDatabaseRegistration <StatisticsDbConfiguration>();
var reportGenerationTime = DateTime.UtcNow;
var destinationContainer = _cloudStorageAccount.CreateCloudBlobClient().GetContainerReference(_statisticsContainerName);
Logger.LogDebug("Generating reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}",
statisticsDatabase.DataSource, statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
var reportBuilderLogger = LoggerFactory.CreateLogger <ReportBuilder>();
var reportCollectorLogger = LoggerFactory.CreateLogger <ReportDataCollector>();
if (string.IsNullOrEmpty(_reportNameConfig))
{
// generate all reports
foreach (var reportName in ReportNames.StandardReports)
{
await GenerateStandardReport(reportName, reportGenerationTime, destinationContainer, reportBuilderLogger, reportCollectorLogger);
}
await RebuildPackageReports(destinationContainer, reportGenerationTime);
await CleanInactiveRecentPopularityDetailByPackageReports(destinationContainer, reportGenerationTime);
}
else if (ReportNames.StandardReports.Contains(_reportNameConfig))
{
// generate only the specific standard report
await GenerateStandardReport(_reportNameConfig, reportGenerationTime, destinationContainer, reportBuilderLogger, reportCollectorLogger);
}
else if (ShouldGenerateReport(ReportNames.RecentPopularityDetailByPackageId, _reportNameConfig))
{
await RebuildPackageReports(destinationContainer, reportGenerationTime);
await CleanInactiveRecentPopularityDetailByPackageReports(destinationContainer, reportGenerationTime);
}
Logger.LogInformation("Generated reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}",
statisticsDatabase.DataSource, statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
// totals reports
Stopwatch stopwatch;
// build downloads.v1.json
if (ShouldGenerateReport(ReportNames.DownloadCount, _reportNameConfig))
{
stopwatch = Stopwatch.StartNew();
var targets = new List <StorageContainerTarget>();
targets.Add(new StorageContainerTarget(_cloudStorageAccount, _statisticsContainerName));
foreach (var dataContainerName in _dataContainerNames)
{
targets.Add(new StorageContainerTarget(_dataStorageAccount, dataContainerName));
}
var downloadCountReport = new DownloadCountReport(
LoggerFactory.CreateLogger <DownloadCountReport>(),
targets,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>,
_sqlCommandTimeoutSeconds);
await downloadCountReport.Run();
stopwatch.Stop();
var reportMetricName = ReportNames.DownloadCount + ReportNames.Extension;
ApplicationInsightsHelper.TrackMetric(reportMetricName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(reportMetricName);
}
// build stats-totals.json
if (ShouldGenerateReport(ReportNames.GalleryTotals, _reportNameConfig))
{
stopwatch = Stopwatch.StartNew();
var galleryTotalsReport = new GalleryTotalsReport(
LoggerFactory.CreateLogger <GalleryTotalsReport>(),
_cloudStorageAccount,
_statisticsContainerName,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>,
commandTimeoutSeconds: _sqlCommandTimeoutSeconds);
await galleryTotalsReport.Run();
stopwatch.Stop();
var reportMetricName = ReportNames.GalleryTotals + ReportNames.Extension;
ApplicationInsightsHelper.TrackMetric(reportMetricName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(reportMetricName);
}
// build tools.v1.json
if (ShouldGenerateReport(ReportNames.DownloadsPerToolVersion, _reportNameConfig))
{
stopwatch = Stopwatch.StartNew();
var toolsReport = new DownloadsPerToolVersionReport(
LoggerFactory.CreateLogger <DownloadsPerToolVersionReport>(),
_cloudStorageAccount,
_statisticsContainerName,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>,
_sqlCommandTimeoutSeconds);
await toolsReport.Run();
stopwatch.Stop();
var reportMetricName = ReportNames.DownloadsPerToolVersion + ReportNames.Extension;
ApplicationInsightsHelper.TrackMetric(reportMetricName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(reportMetricName);
}
}
public override async Task Run()
{
var statisticsDatabase = GetDatabaseRegistration <StatisticsDbConfiguration>();
var reportGenerationTime = DateTime.UtcNow;
var destinationContainer = _cloudStorageAccount.CreateCloudBlobClient().GetContainerReference(_statisticsContainerName);
Logger.LogDebug("Generating reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}",
statisticsDatabase.DataSource, statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
var reportBuilderLogger = LoggerFactory.CreateLogger <ReportBuilder>();
var reportCollectorLogger = LoggerFactory.CreateLogger <ReportDataCollector>();
if (string.IsNullOrEmpty(_reportName))
{
// generate all reports
var reportGenerators = new Dictionary <ReportBuilder, ReportDataCollector>
{
{
new ReportBuilder(reportBuilderLogger, ReportNames.NuGetClientVersion),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.NuGetClientVersion], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
},
{
new ReportBuilder(reportBuilderLogger, ReportNames.Last6Weeks),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.Last6Weeks], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
},
{
new ReportBuilder(reportBuilderLogger, ReportNames.RecentCommunityPopularity),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.RecentCommunityPopularity], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
},
{
new ReportBuilder(reportBuilderLogger, ReportNames.RecentCommunityPopularityDetail),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.RecentCommunityPopularityDetail], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
},
{
new ReportBuilder(reportBuilderLogger, ReportNames.RecentPopularity),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.RecentPopularity], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
},
{
new ReportBuilder(reportBuilderLogger, ReportNames.RecentPopularityDetail),
new ReportDataCollector(reportCollectorLogger, _storedProcedures[ReportNames.RecentPopularityDetail], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds)
}
};
foreach (var reportGenerator in reportGenerators)
{
await ProcessReport(LoggerFactory, destinationContainer, reportGenerator.Key, reportGenerator.Value, reportGenerationTime);
ApplicationInsightsHelper.TrackReportProcessed(reportGenerator.Key.ReportName + " report");
}
await RebuildPackageReports(destinationContainer, reportGenerationTime);
await CleanInactiveRecentPopularityDetailByPackageReports(destinationContainer, reportGenerationTime);
}
else
{
// generate only the specific report
var reportBuilder = new ReportBuilder(reportBuilderLogger, _reportName);
var reportDataCollector = new ReportDataCollector(reportCollectorLogger, _storedProcedures[_reportName], OpenSqlConnectionAsync <StatisticsDbConfiguration>, _sqlCommandTimeoutSeconds);
await ProcessReport(LoggerFactory, destinationContainer, reportBuilder, reportDataCollector, reportGenerationTime);
}
Logger.LogInformation("Generated reports from {DataSource}/{InitialCatalog} and saving to {AccountName}/{Container}",
statisticsDatabase.DataSource, statisticsDatabase.InitialCatalog, _cloudStorageAccount.Credentials.AccountName, destinationContainer.Name);
// totals reports
var stopwatch = Stopwatch.StartNew();
// build downloads.v1.json
var targets = new List <StorageContainerTarget>();
targets.Add(new StorageContainerTarget(_cloudStorageAccount, _statisticsContainerName));
foreach (var dataContainerName in _dataContainerNames)
{
targets.Add(new StorageContainerTarget(_dataStorageAccount, dataContainerName));
}
var downloadCountReport = new DownloadCountReport(
LoggerFactory.CreateLogger <DownloadCountReport>(),
targets,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>);
await downloadCountReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(DownloadCountReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(DownloadCountReport.ReportName);
stopwatch.Restart();
// build stats-totals.json
var galleryTotalsReport = new GalleryTotalsReport(
LoggerFactory.CreateLogger <GalleryTotalsReport>(),
_cloudStorageAccount,
_statisticsContainerName,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>);
await galleryTotalsReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(GalleryTotalsReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(GalleryTotalsReport.ReportName);
// build tools.v1.json
var toolsReport = new DownloadsPerToolVersionReport(
LoggerFactory.CreateLogger <DownloadsPerToolVersionReport>(),
_cloudStorageAccount,
_statisticsContainerName,
OpenSqlConnectionAsync <StatisticsDbConfiguration>,
OpenSqlConnectionAsync <GalleryDbConfiguration>);
await toolsReport.Run();
stopwatch.Stop();
ApplicationInsightsHelper.TrackMetric(DownloadsPerToolVersionReport.ReportName + " Generation Time (ms)", stopwatch.ElapsedMilliseconds);
ApplicationInsightsHelper.TrackReportProcessed(DownloadsPerToolVersionReport.ReportName);
stopwatch.Restart();
}
