k8s.io/test-infra@v0.0.0-20240520184403-27c6b4c223d8/metrics/configs/flakes-config.yaml (about)

     1  metric: flakes
     2  description: Calculates flakiness for each job for the past week and the flakiest tests for each job.
     3  query: |
     4    #standardSQL
     5    select
     6      job,
     7      build_consistency,
     8      commit_consistency,
     9      flakes,
    10      runs,
    11      commits,
    12      array(
    13        select as struct
    14          i.n name,
    15          count(i.failures) flakes
    16        from tt.tests i
    17        group by name
    18        having name not in ('Test', 'DiffResources', 'DumpClusterLogs', 'DumpFederationLogs')  /* uninteresting tests */
    19        order by flakes desc
    20        limit 10 /* top ten flakiest tests in this job */
    21      ) flakiest
    22    from (
    23      select
    24        job, /* name of job */
    25        round(sum(if(flaked=1,passed,runs))/sum(runs),3) build_consistency, /* percentage of runs that did not flake */
    26        round(1-sum(flaked)/count(distinct commit),3) commit_consistency, /* percentage of commits that did not flake */
    27        sum(flaked) flakes, /* number of times it flaked */
    28        sum(runs) runs, /* number of times the job ran */
    29        count(distinct commit) commits, /* number of commits tested */
    30        array_concat_agg(tests) tests /* array of flaking tests in this job */
    31      from (
    32        select
    33          job,
    34          commit,
    35          if(passed = runs or passed = 0, 0, 1) flaked, /* consistent: always pass or always fail */
    36          passed,
    37          safe_cast(runs as int64) runs,
    38          array(
    39            select as struct
    40              i.name n, /* test name */
    41              countif(i.failed) failures /* number of times it flaked */
    42            from tt.tests i
    43            group by n
    44            having failures > 0 and failures < tt.runs /* same consistency metric */
    45            order by failures desc
    46          ) tests
    47        from (
    48          select
    49            job,
    50            commit,
    51            sum(if(result='SUCCESS',1,0)) passed,
    52            count(result) runs,  /* count the number of times we ran a job on this commit for this PR */
    53            array_concat_agg(test) tests /* create an array of tests structs */
    54          from (
    55            SELECT
    56              job,
    57              if(substr(job, 0, 3) = 'pr:', 'pull', 'ci') kind,  /* pull or ci */
    58              ifnull(repo_commit, version) version, /* git version, empty for ci  */
    59              if(substr(job, 0, 3) = 'pr:',
    60                regexp_extract(
    61                  repo,
    62                  r'[^,]+,\d+:([a-f0-9]+)"'
    63                ),
    64                ifnull(repo_commit, version)
    65              ) commit,  /* repo commit for PR or version for CI */
    66              result,  /* SUCCESS if the build passed */
    67              test  /* repeated tuple of tests */
    68            from (
    69                  select *,
    70                         ifnull(b.repos, (select i.value from b.metadata i where i.key = 'repos')) repo from `k8s-gubernator.build.week` as b
    71            ) as t
    72            where
    73              datetime(started) > datetime_sub(current_datetime(), interval 7 DAY)
    74              and (version != 'unknown' or repo_commit is not null)
    75              and (
    76                substr(job, 0, 3) = 'ci-' or
    77                array_length(split(replace(t.repo,', ', ''), ',')) = 2 /*serial pr jobs only (# of PR refs +1 == 2)*/
    78              )
    79          )
    80          group by job, commit
    81        ) as tt
    82      ) as tt
    83      group by job /* summarize info for this job across all commits/builds */
    84    ) as tt
    85    order by flakes desc, commit_consistency, build_consistency, job /* flakiest jobs first */
    86  
    87  jqfilter: |
    88    [(.[] | {(.job): {
    89        consistency: (.commit_consistency|tonumber),
    90        flakes: (.flakes|tonumber),
    91        test_flakes: ([(.flakiest[] | {
    92          (.name): (.flakes|tonumber)}) ])| add
    93    }})] | add