github.com/munnerz/test-infra@v0.0.0-20190108210205-ce3d181dc989/metrics/configs/flakes-daily-config.yaml (about)

     1  metric: flakes-daily
     2  description: Calculates flakiness for each job for each day.
     3  query: |
     4    #standardSQL
     5    select
     6      job,
     7      consistent_builds,
     8      builds,
     9      round(consistent_builds/builds, 3) build_consistency,
    10      consistent_commits,
    11      commits,
    12      round(consistent_commits/commits, 3) commit_consistency,
    13      flakes,
    14      UNIX_SECONDS(TIMESTAMP(day)) day
    15    from (
    16      select /* for each (job, day) */
    17        job, /* name of job */
    18        sum(if(flaked=1,passed,runs)) consistent_builds,
    19        count(distinct commit)-sum(flaked) consistent_commits,
    20        sum(runs) builds,
    21        count(distinct commit) commits,
    22        sum(flaked) flakes, /* number of times it flaked */
    23        day
    24      from (
    25        select
    26          job,
    27          commit,
    28          if(passed = runs or passed = 0, 0, 1) flaked, /* consistent: always pass or always fail */
    29          passed,
    30          safe_cast(runs as int64) runs,
    31          day
    32        from (
    33          select /* for each (job, day, commit) */
    34            job,
    35            commit,
    36            sum(if(result='SUCCESS',1,0)) passed,
    37            count(result) runs, /* count the number of times we ran a job on this commit for this PR */
    38            day
    39          from (
    40            SELECT
    41              job,
    42              if(substr(job, 0, 3) = 'pr:',
    43                regexp_extract(
    44                  (
    45                    select i.value
    46                    from t.metadata i
    47                    where i.key = 'repos'
    48                  ),
    49                  r'[^,]+,\d+:([a-f0-9]+)"'
    50                ),
    51                version
    52              ) commit,  /* repo commit for PR or version for CI */
    53              result,  /* SUCCESS if the build passed */
    54              date_trunc(date(started), DAY) day
    55            FROM `k8s-gubernator.build.all` as t
    56            where
    57              /* Select results that have occurred on or after the day of <LAST_DATA_TIME> (those results were
    58                 for the day before), but don't include results from today, only return results for complete days. */
    59              datetime(started) >= datetime_trunc(parse_datetime("%s", cast(<LAST_DATA_TIME> as string)), DAY)
    60              and datetime(started) < datetime_trunc(current_datetime(), DAY) /* Drop results from partial day */
    61              and (
    62                (substr(job, 0, 3) = 'ci-' and version != 'unknown') or
    63                exists(
    64                  select as struct
    65                    i
    66                  from t.metadata i
    67                  where i.key = 'repos' and
    68                  array_length(split(replace(i.value,', ', ''), ',')) = 2 /*serial pr jobs only (# of PR refs +1 == 2)*/
    69                )
    70              )
    71          )
    72          group by job, day, commit
    73        )
    74      )
    75      group by job, day /* summarize job across all commits/builds and group results by day for backfilling influxdb */
    76    )
    77    order by day desc, flakes desc, build_consistency, commit_consistency, job /* flakiest jobs first */
    78  
    79  jqfilter: |
    80    ([(.[] | .day|tonumber)] | max) as $newestday |
    81    [(.[] | select((.job | contains("pr:")) and ((.day|tonumber)==$newestday)) | {(.job): {
    82      flakes: (.flakes|tonumber),
    83      commit_consistency: (.commit_consistency|tonumber),
    84      commits: (.commits|tonumber),
    85      consistent_commits: (.consistent_commits|tonumber),
    86      build_consistency: (.build_consistency|tonumber),
    87      build: (.builds|tonumber),
    88      consistent_builds: (.consistent_builds|tonumber)
    89    }})] | add
    90  
    91  measurements:
    92    backfill: flakes_daily
    93    jq: |
    94      [(.[] | {
    95        measurement: "flakes_daily",
    96        time: (.day|tonumber),
    97        tags: {
    98          job: (.job),
    99          day: (.day|tonumber)
   100        },
   101        fields: {
   102          flakes: (.flakes|tonumber),
   103          commit_consistency: (.commit_consistency|tonumber),
   104          commits: (.commits|tonumber),
   105          consistent_commits: (.consistent_commits|tonumber),
   106          build_consistency: (.build_consistency|tonumber),
   107          builds: (.builds|tonumber),
   108          consistent_builds: (.consistent_builds|tonumber)
   109        }
   110      })]