| 1 | #!/usr/bin/env bash |
| 2 | |
| 3 | set -e |
| 4 | |
| 5 | region=eu-west-1 |
| 6 | |
| 7 | report_date="$(date +%Y-%m-%d)" |
| 8 | |
| 9 | run_query() { |
| 10 | local name="$1" |
| 11 | local query="$2" |
| 12 | |
| 13 | res=$(aws athena start-query-execution \ |
| 14 | --region $region \ |
| 15 | --result-configuration "OutputLocation=s3://nixos-metrics/$report_date/$name/" \ |
| 16 | --query-string "$query") |
| 17 | |
| 18 | execution_id="$(printf "%s" "$res" | jq -r -e .QueryExecutionId)" |
| 19 | [[ -n $execution_id ]] |
| 20 | |
| 21 | echo "Started query $name as $execution_id." |
| 22 | |
| 23 | redirect=latest/$name.csv |
| 24 | aws s3api put-object \ |
| 25 | --bucket nixos-metrics \ |
| 26 | --key "$redirect" \ |
| 27 | --website-redirect-location "/$report_date/$name/$execution_id.csv" >/dev/null |
| 28 | |
| 29 | echo "Created redirect http://nixos-metrics.s3-website-eu-west-1.amazonaws.com/$redirect." |
| 30 | } |
| 31 | |
| 32 | if true; then |
| 33 | |
| 34 | run_query traffic-per-day \ |
| 35 | " |
| 36 | select day, host, sum(nr) as nr_requests, sum(total_bytes) as total_bytes |
| 37 | from urls |
| 38 | group by day, host |
| 39 | order by day, host |
| 40 | " |
| 41 | |
| 42 | run_query traffic-per-country \ |
| 43 | " |
| 44 | select geo_country, sum(nr) as nr_requests, sum(total_bytes) as total_bytes |
| 45 | from clients |
| 46 | group by geo_country |
| 47 | order by total_bytes desc |
| 48 | " |
| 49 | |
| 50 | run_query cache-info-requests-per-day \ |
| 51 | " |
| 52 | select day, sum(nr) as cache_info_requests |
| 53 | from nix_cache_info |
| 54 | group by day |
| 55 | order by day |
| 56 | " |
| 57 | |
| 58 | run_query cache-info-requests-per-day-not-hosted \ |
| 59 | " |
| 60 | select day, sum(nr) as cache_info_requests |
| 61 | from nix_cache_info |
| 62 | where asn not in (select asn_nr from hosting_asns) |
| 63 | group by day |
| 64 | order by day |
| 65 | " |
| 66 | |
| 67 | run_query cache-info-requests-per-day-per-ua \ |
| 68 | " |
| 69 | with tmp as |
| 70 | (select *, regexp_replace(regexp_replace(request_user_agent, '.* Nix', 'Nix'), 'pre[^ ]*', 'pre*') as cleaned_ua from nix_cache_info) |
| 71 | select day, cleaned_ua, sum(nr) as cache_info_requests |
| 72 | from tmp |
| 73 | group by day, cleaned_ua |
| 74 | order by day, cache_info_requests desc |
| 75 | " |
| 76 | |
| 77 | run_query flake-registry-requests-per-day \ |
| 78 | " |
| 79 | select day, sum(nr) as total_requests |
| 80 | from urls |
| 81 | where host = 'channels.nixos.org' and url like '%/flake-registry.json' |
| 82 | group by day |
| 83 | order by day |
| 84 | " |
| 85 | |
| 86 | run_query top-store-paths \ |
| 87 | " |
| 88 | select path, sum(nr) as total_requests |
| 89 | from urls |
| 90 | join all_paths on regexp_replace(regexp_replace(url, '.narinfo', ''), '/', '') = regexp_replace(regexp_replace(path, '/nix/store/', ''), '-.*', '') |
| 91 | where |
| 92 | host = 'cache.nixos.org' |
| 93 | and url like '%.narinfo' |
| 94 | group by path |
| 95 | having sum(nr) > 100 |
| 96 | order by total_requests desc |
| 97 | " |
| 98 | |
| 99 | run_query narinfo-queries-per-release \ |
| 100 | " |
| 101 | with tmp as |
| 102 | (select distinct path, regexp_replace(regexp_replace(regexp_replace(regexp_replace(release_name, 'pre.*', 'pre'), 'alpha.*', ''), 'beta.*', 'beta'), '\.[0-9]+\.[0-9a-f][0-9a-f][0-9a-f][0-9a-f]+$', '') as release from release_paths) |
| 103 | select release, sum(nr) as total_requests |
| 104 | from urls |
| 105 | join tmp on regexp_replace(regexp_replace(url, '.narinfo', ''), '/', '') = regexp_replace(regexp_replace(path, '/nix/store/', ''), '-.*', '') |
| 106 | where |
| 107 | host = 'cache.nixos.org' |
| 108 | and url like '%.narinfo' |
| 109 | group by release |
| 110 | order by total_requests desc |
| 111 | " |
| 112 | |
| 113 | run_query nix-installer-downloads \ |
| 114 | " |
| 115 | select day, sum(nr) |
| 116 | from urls |
| 117 | where |
| 118 | host = 'releases.nixos.org' |
| 119 | and regexp_like(url, '^/nix/nix-[^/]+/install$') |
| 120 | group by day |
| 121 | order by day |
| 122 | " |
| 123 | |
| 124 | run_query nix-installer-architectures \ |
| 125 | " |
| 126 | select arch, sum(nr) as count from |
| 127 | (select url, nr, regexp_replace(regexp_replace(url, '/nix/nix-[^/]+/nix-[^-]+-(rc[^-]*-)?', ''), '.tar.xz', '') as arch |
| 128 | from urls |
| 129 | where |
| 130 | host = 'releases.nixos.org' |
| 131 | and regexp_like(url, '^/nix/nix-[^/]+/nix-[^-]+-.*tar.xz$')) |
| 132 | group by arch |
| 133 | order by count desc |
| 134 | " |
| 135 | |
| 136 | fi |