Per-package daily download history via the REST API

[coatless]

stat_download returns rolling windows (cnt_1d/7d/30d/total) but not a series, so to build a daily history each consumer has to poll once a day and store the result. I do that for https://github.com/r-observatory/autoobs-downloads.

The daily data is already collected, though. agg_download_pkg holds one row per (period, dt, metapkg_id, folder_id, country):

MirrorCache/lib/MirrorCache/resources/migrations/Pg.sql

Lines 450 to 458 in fb90e38

	create table if not exists agg_download_pkg (
	period stat_period_t NOT NULL,
	dt timestamp NOT NULL,
	metapkg_id bigint NOT NULL,
	folder_id bigint NOT NULL,
	country varchar(2),
	cnt bigint,
	primary key(period, dt, metapkg_id, folder_id, country)
	);

StatAggPkg already writes period='day' rows on a schedule:

MirrorCache/lib/MirrorCache/Task/StatAggPkg.pm

Lines 38 to 39 in fb90e38

	if ($minion->lock('stat_agg_pkg_day', 2*60*60)) {
	_agg($app, $job, 'day');

Cleanup keeps them for 6 months:

MirrorCache/lib/MirrorCache/Task/Cleanup.pm

Lines 178 to 183 in fb90e38

	where period = 'day' and dt < (current_timestamp - interval '6 month')
	LIMIT 100000
	)
	END_SQL
	} else {
	$sql = "delete from agg_download_pkg where period = 'day' and dt < date_sub(current_timestamp, interval 6 month) LIMIT 100000";

And stat_download already reads period='day' (last 30 days) before collapsing it into windows:

MirrorCache/lib/MirrorCache/WebAPI/Controller/Rest/Metapkg.pm

Lines 176 to 199 in fb90e38

	sub stat_download {
	my $self = shift;
	my $id = $self->param('id');
	my $sql;
	$sql = <<'END_SQL';
	select
	extract(epoch from ( select min(dt) from agg_download_pkg where metapkg_id = ? and period = 'day' ))::int as first_seen,
	coalesce( (select sum(cnt) as cnt from agg_download_pkg where metapkg_id = ? and period = 'total' and dt = (select max(dt) from agg_download_pkg where period = 'total')), 0 ) as cnt_total,
	coalesce( (select sum(cnt) as cnt from agg_download_pkg where metapkg_id = ? and period = 'hour' and dt > (select max(dt) from agg_download_pkg where period = 'total')), 0 ) as cnt_today,
	sum(cnt) as cnt_30d,
	coalesce( sum(case when dt > now() - interval '7 day' then cnt else 0 end), 0 ) as cnt_7d,
	coalesce( sum(case when dt > now() - interval '1 day' then cnt else 0 end), 0) as cnt_1d
	from agg_download_pkg
	where metapkg_id = ? and period = 'day' and dt > now() - interval '30 day'
	END_SQL
	unless ($self->schema->pg) {
	$sql =~ s/::int//g;
	$sql =~ s/interval '(\d+) day'/interval $1 day/g;
	$sql =~ s/extract\(epoch from/unix_timestamp(/g;
	}
	my $res = $self->schema->storage->dbh->selectall_arrayref($sql, {Columns => {}}, $id, $id, $id, $id);
	return $self->render(json => { data => $res });
	}

Could you expose those rows directly, e.g. GET /rest/package/<id>/stat_download_daily returning [{date, count}, ...]? It would be a near-copy of stat_download:

select to_char(dt,'YYYY-MM-DD') as date, sum(cnt) as count
from agg_download_pkg
where metapkg_id = ? and period = 'day'
group by dt order by dt;

plus one route line next to the existing stat_download route:

MirrorCache/lib/MirrorCache/WebAPI.pm

Line 194 in fb90e38

$rest_r->get('/package/#id/stat_download')->to('metapkg#stat_download');

That saves every downstream from running its own snapshotter, and gives up to 6 months of backfill.

[andrii-suse]

I may look into this, but are you aware that the stats nowadays are very inaccurate because most of downloads are going through cdn.opensuse.org and are not accounted in the MirrorCache DB?

[coatless]

Hi @andrii-suse, thanks for the quick response.

In short, that's news to me. So, even with the current counts we're listing to we'll essentially be under counting? That's less than desired. Though, it does at least allow us to shed some light on usage (albeit with asterisk).

Do you know how much of an under count we're getting? Is it like a 10% gets counted correctly and 90% is lost due to the CDN configuration?

[andrii-suse]

It is really hard to tell - most of recent zypper installations should go to cdn directly, so it can be 90% or 50% or 99% that are lost depending on distribution that pulls and variations in config.