| # Copyright 2022 gRPC authors. |
| # |
| # Licensed under the Apache License, Version 2.0 (the "License"); |
| # you may not use this file except in compliance with the License. |
| # You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| |
| QUERY = """ |
| #standardSQL |
| |
| -- See https://console.cloud.google.com/bigquery?sq=830293263384:5a8549832dfb48d9b2c04312a4ae3181 for the original query |
| |
| WITH |
| |
| runs AS ( |
| SELECT |
| RTRIM(LTRIM(REGEXP_REPLACE(test_target, r'(@poller=.+)', ''))) AS test_binary, |
| REGEXP_EXTRACT(test_target, r'poller=(\w+)') AS poll_strategy, |
| job_name, |
| test_target, |
| test_class_name, |
| CASE |
| # in case of timeout / retry / segfault the "test_case" fields will contain weird stuff |
| # e.g. "test_shard2_run0_attempt0" or "shard_2/20" |
| # when aggregating, we want to display all of these as a single category of problems |
| WHEN test_case like 'test_shard_%_run%_attempt%' THEN 'CANNOT_DETERMINE' |
| WHEN test_case like '%shard_%/%' THEN 'CANNOT_DETERMINE' |
| # when test_case looks suspiciously like test_target |
| # its value is probably meaningless and it means that the entire target has failed |
| # e.g. test_target="//test/cpp/client:destroy_grpclb_channel_with_active_connect_stress_test" and test_case="test/cpp/client/destroy_grpclb_channel_with_active_connect_stress_test.exe" |
| WHEN STRPOS(test_case, REPLACE(SUBSTR(test_target, 3), ":", "/")) != 0 THEN 'CANNOT_DETERMINE' |
| ELSE test_case |
| END AS test_case, |
| result, |
| build_id, |
| timestamp |
| FROM |
| `grpc-testing.jenkins_test_results.rbe_test_results` |
| WHERE |
| DATETIME_DIFF(CURRENT_DATETIME(), |
| dateTIME(timestamp), |
| HOUR) < {lookback_hours} |
| ), |
| |
| results_counts_per_build AS ( |
| SELECT |
| test_binary, |
| #test_target, # aggregate data over all pollers |
| test_class_name, |
| test_case, |
| SUM(SAFE_CAST(result != 'PASSED' |
| AND result != 'SKIPPED' AS INT64)) AS runs_failed, |
| SUM(SAFE_CAST(result != 'SKIPPED' AS INT64)) AS runs_total, |
| job_name, |
| build_id |
| FROM |
| runs |
| GROUP BY |
| test_binary, |
| test_class_name, |
| test_case, |
| job_name, |
| build_id), |
| |
| builds_with_missing_cannot_determine_testcase_entry AS ( |
| SELECT |
| test_binary, |
| job_name, |
| build_id, |
| FROM |
| results_counts_per_build |
| GROUP BY |
| test_binary, |
| job_name, |
| build_id |
| HAVING COUNTIF(test_case = 'CANNOT_DETERMINE') = 0 |
| ), |
| |
| # for each test target and build, generate a fake entry with "CANNOT_DETERMINE" test_case |
| # if not already present. |
| # this is because in many builds, there will be no "CANNOT_DETERMINE" entry |
| # and we want to avoid skewing the statistics |
| results_counts_per_build_with_fake_cannot_determine_test_case_entries AS ( |
| (SELECT * FROM results_counts_per_build) |
| UNION ALL |
| (SELECT |
| test_binary, |
| '' AS test_class_name, # when test_case is 'CANNOT_DETERMINE', test class is empty string |
| 'CANNOT_DETERMINE' AS test_case, # see table "runs" |
| 0 AS runs_failed, |
| 1 AS runs_total, |
| job_name, |
| build_id |
| FROM |
| builds_with_missing_cannot_determine_testcase_entry) |
| ), |
| |
| results_counts AS ( |
| SELECT |
| test_binary, |
| test_class_name, |
| test_case, |
| job_name, |
| SUM(runs_failed) AS runs_failed, |
| SUM(runs_total) AS runs_total, |
| SUM(SAFE_CAST(runs_failed > 0 AS INT64)) AS builds_failed, |
| COUNT(build_id) AS builds_total, |
| STRING_AGG(CASE |
| WHEN runs_failed > 0 THEN 'X' |
| ELSE '_' END, '' |
| ORDER BY |
| build_id ASC) AS build_failure_pattern, |
| FORMAT("%T", ARRAY_AGG(build_id |
| ORDER BY |
| build_id ASC)) AS builds |
| FROM |
| #results_counts_per_build |
| results_counts_per_build_with_fake_cannot_determine_test_case_entries |
| GROUP BY |
| test_binary, |
| test_class_name, |
| test_case, |
| job_name |
| HAVING |
| runs_failed > 0) |
| |
| SELECT |
| ROUND(100*builds_failed / builds_total, 2) AS pct_builds_failed, |
| ROUND(100*runs_failed / runs_total, 2) AS pct_runs_failed, |
| test_binary, |
| test_class_name, |
| test_case, |
| job_name, |
| build_failure_pattern |
| |
| FROM |
| results_counts |
| ORDER BY |
| pct_builds_failed DESC |
| """ |