app_dart/lib/src/service/bigquery.dart - mirrors/cocoon - Git at Google

 // Copyright 2020 The Flutter Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.

 import 'dart:async';

 import 'package:googleapis/bigquery/v2.dart';
 import 'package:http/http.dart';

 import 'access_client_provider.dart';

 /// The sql query to query the build statistic from the
 /// `flutter-dashboard.datasite.luci_prod_build_status`.
 ///
 /// The schema of the `luci_prod_build_status` table:
 /// time	            TIMESTAMP
 /// date	            DATE
 /// sha	              STRING
 /// flaky_builds	    STRING
 /// succeeded_builds	STRING
 /// branch	          STRING
 /// device_os       	STRING
 /// pool	            STRING
 /// repo	            STRING
 /// builder_name	    STRING
 /// success_count	    INTEGER
 /// failure_count	    INTEGER
 /// is_flaky	        INTEGER
 ///
 /// This returns latest [LIMIT] number of build stats for each builder.
 const String getBuilderStatisticQuery = r'''
 select builder_name,
        sum(is_flaky) as flaky_number,
        count(*) as total_number,
        string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
        string_agg(succeeded_builds, ', ') as succeeded_builds,
        array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
        array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
        sum(is_flaky)/count(*) as flaky_ratio,
        min(date) as from_date,
        max(date) as to_date
 from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_prod_build_status`)
 where date>=date_sub(current_date(), interval 30 day) and
       builder_name not like '%Drone' and
       repo='flutter' and
       branch='master' and
       pool = 'luci.flutter.prod' and
       builder_name not like '%Beta%' and
       builder_name not like '% beta %' and
       builder_name not like '%Stable%' and
       builder_name not like '% stable %' and
       builder_name not like '%Dev%' and
       builder_name not like '% dev %' and
       rank<=@LIMIT
 group by builder_name;
 ''';

 const String getStagingBuilderStatisticQuery = r'''
 select builder_name,
        sum(is_flaky) as flaky_number,
        count(*) as total_number,
        string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
        string_agg(succeeded_builds, ', ') as succeeded_builds,
        array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
        array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
        sum(is_flaky)/count(*) as flaky_ratio,
        min(date) as from_date,
        max(date) as to_date
 from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_staging_build_status`)
 where date>=date_sub(current_date(), interval 30 day) and
       builder_name not like '%Drone' and
       repo='flutter' and
       branch='master' and
       pool = 'luci.flutter.staging' and
       builder_name not like '%Beta%' and
       builder_name not like '% beta %' and
       rank<=@LIMIT
 group by builder_name;
 ''';

 // Returns builds in the past 30 days to exclude obsolete historical data.
 const String getRecordsQuery = r'''
 select sha, is_flaky, failure_count from `flutter-dashboard.datasite.luci_staging_build_status`
 where builder_name=@BUILDER_NAME and date>=date_sub(current_date(), interval 30 day)
 order by time desc
 limit @LIMIT
 ''';

 class BigqueryService {
   const BigqueryService(this.accessClientProvider);

   /// AccessClientProvider for OAuth 2.0 authenticated access client
   final AccessClientProvider accessClientProvider;

   /// Return a [TabledataResource] with an authenticated [client]
   Future<TabledataResource> defaultTabledata() async {
     final Client client = await accessClientProvider.createAccessClient(
       scopes: const <String>[BigqueryApi.bigqueryScope],
     );
     return BigqueryApi(client).tabledata;
   }

   /// Return a [JobsResource] with an authenticated [client]
   Future<JobsResource> defaultJobs() async {
     final Client client = await accessClientProvider.createAccessClient(
       scopes: const <String>[BigqueryApi.bigqueryScope],
     );
     return BigqueryApi(client).jobs;
   }

   /// Return the top [limit] number of current builder statistic.
   ///
   /// See getBuilderStatisticQuery to get the detail information about the table
   /// schema
   Future<List<BuilderStatistic>> listBuilderStatistic(
     String projectId, {
     int limit = 100,
     String bucket = 'prod',
   }) async {
     final JobsResource jobsResource = await defaultJobs();
     final QueryRequest query = QueryRequest.fromJson(<String, Object>{
       'query': bucket == 'staging' ? getStagingBuilderStatisticQuery : getBuilderStatisticQuery,
       'queryParameters': <Map<String, Object>>[
         <String, Object>{
           'name': 'LIMIT',
           'parameterType': <String, Object>{'type': 'INT64'},
           'parameterValue': <String, Object>{'value': '$limit'},
         },
       ],
       'useLegacySql': false,
     });
     final QueryResponse response = await jobsResource.query(query, projectId);
     if (!response.jobComplete!) {
       throw 'job does not complete';
     }
     final List<BuilderStatistic> result = <BuilderStatistic>[];
     for (final TableRow row in response.rows!) {
       final String builder = row.f![0].v as String;
       List<String>? flakyBuilds = (row.f![3].v as String?)?.split(', ');
       flakyBuilds?.sort();
       flakyBuilds = flakyBuilds?.reversed.toList();
       List<String>? succeededBuilds = (row.f![4].v as String?)?.split(', ');
       succeededBuilds?.sort();
       succeededBuilds = succeededBuilds?.reversed.toList();
       result.add(
         BuilderStatistic(
           name: builder,
           flakyRate: double.parse(row.f![7].v as String),
           flakyBuilds: flakyBuilds ?? const <String>[],
           succeededBuilds: succeededBuilds ?? const <String>[],
           recentCommit: row.f![5].v as String?,
           flakyBuildOfRecentCommit: row.f![6].v as String?,
           flakyNumber: int.parse(row.f![1].v as String),
           totalNumber: int.parse(row.f![2].v as String),
           fromDate: row.f![8].v as String?,
           toDate: row.f![9].v as String?,
         ),
       );
     }
     return result;
   }

   /// Return the list of current builder statistic.
   ///
   /// See getBuilderStatisticQuery to get the detail information about the table
   /// schema
   Future<List<BuilderRecord>> listRecentBuildRecordsForBuilder(
     String projectId, {
     String? builder,
     int? limit,
   }) async {
     final JobsResource jobsResource = await defaultJobs();
     final QueryRequest query = QueryRequest.fromJson(<String, Object>{
       'query': getRecordsQuery,
       'parameterMode': 'NAMED',
       'queryParameters': <Map<String, Object>>[
         <String, Object>{
           'name': 'BUILDER_NAME',
           'parameterType': <String, Object>{'type': 'STRING'},
           'parameterValue': <String, Object?>{'value': builder},
         },
         <String, Object>{
           'name': 'LIMIT',
           'parameterType': <String, Object>{'type': 'INT64'},
           'parameterValue': <String, Object>{'value': '$limit'},
         },
       ],
       'useLegacySql': false,
     });
     final QueryResponse response = await jobsResource.query(query, projectId);
     if (!response.jobComplete!) {
       throw 'job does not complete';
     }
     final List<BuilderRecord> result = <BuilderRecord>[];
     // When a test is newly marked as flaky, it is possible no execution exists.
     if (response.rows == null) {
       return result;
     }
     for (final TableRow row in response.rows!) {
       result.add(
         BuilderRecord(
           commit: row.f![0].v as String,
           isFlaky: row.f![1].v as String != '0',
           isFailed: row.f![2].v as String != '0',
         ),
       );
     }
     return result;
   }
 }

 class BuilderRecord {
   BuilderRecord({
     required this.commit,
     required this.isFlaky,
     required this.isFailed,
   });

   final String commit;
   final bool isFlaky;
   final bool isFailed;
 }

 class BuilderStatistic {
   BuilderStatistic({
     required this.name,
     required this.flakyRate,
     required this.flakyNumber,
     required this.totalNumber,
     this.flakyBuilds,
     this.succeededBuilds,
     this.recentCommit,
     this.flakyBuildOfRecentCommit,
     this.fromDate,
     this.toDate,
   });

   final String name;
   final double flakyRate;
   final List<String>? flakyBuilds;
   final List<String>? succeededBuilds;
   final String? recentCommit;
   final String? flakyBuildOfRecentCommit;
   final int flakyNumber;
   final int totalNumber;
   final String? fromDate;
   final String? toDate;
 }
	// Copyright 2020 The Flutter Authors. All rights reserved.
	// Use of this source code is governed by a BSD-style license that can be
	// found in the LICENSE file.

	import 'dart:async';

	import 'package:googleapis/bigquery/v2.dart';
	import 'package:http/http.dart';

	import 'access_client_provider.dart';

	/// The sql query to query the build statistic from the
	/// `flutter-dashboard.datasite.luci_prod_build_status`.
	///
	/// The schema of the `luci_prod_build_status` table:
	/// time TIMESTAMP
	/// date DATE
	/// sha STRING
	/// flaky_builds STRING
	/// succeeded_builds STRING
	/// branch STRING
	/// device_os STRING
	/// pool STRING
	/// repo STRING
	/// builder_name STRING
	/// success_count INTEGER
	/// failure_count INTEGER
	/// is_flaky INTEGER
	///
	/// This returns latest [LIMIT] number of build stats for each builder.
	const String getBuilderStatisticQuery = r'''
	select builder_name,
	sum(is_flaky) as flaky_number,
	count(*) as total_number,
	string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
	string_agg(succeeded_builds, ', ') as succeeded_builds,
	array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
	array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
	sum(is_flaky)/count(*) as flaky_ratio,
	min(date) as from_date,
	max(date) as to_date
	from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_prod_build_status`)
	where date>=date_sub(current_date(), interval 30 day) and
	builder_name not like '%Drone' and
	repo='flutter' and
	branch='master' and
	pool = 'luci.flutter.prod' and
	builder_name not like '%Beta%' and
	builder_name not like '% beta %' and
	builder_name not like '%Stable%' and
	builder_name not like '% stable %' and
	builder_name not like '%Dev%' and
	builder_name not like '% dev %' and
	rank<=@LIMIT
	group by builder_name;
	''';

	const String getStagingBuilderStatisticQuery = r'''
	select builder_name,
	sum(is_flaky) as flaky_number,
	count(*) as total_number,
	string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
	string_agg(succeeded_builds, ', ') as succeeded_builds,
	array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
	array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
	sum(is_flaky)/count(*) as flaky_ratio,
	min(date) as from_date,
	max(date) as to_date
	from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_staging_build_status`)
	where date>=date_sub(current_date(), interval 30 day) and
	builder_name not like '%Drone' and
	repo='flutter' and
	branch='master' and
	pool = 'luci.flutter.staging' and
	builder_name not like '%Beta%' and
	builder_name not like '% beta %' and
	rank<=@LIMIT
	group by builder_name;
	''';

	// Returns builds in the past 30 days to exclude obsolete historical data.
	const String getRecordsQuery = r'''
	select sha, is_flaky, failure_count from `flutter-dashboard.datasite.luci_staging_build_status`
	where builder_name=@BUILDER_NAME and date>=date_sub(current_date(), interval 30 day)
	order by time desc
	limit @LIMIT
	''';

	class BigqueryService {
	const BigqueryService(this.accessClientProvider);

	/// AccessClientProvider for OAuth 2.0 authenticated access client
	final AccessClientProvider accessClientProvider;

	/// Return a [TabledataResource] with an authenticated [client]
	Future<TabledataResource> defaultTabledata() async {
	final Client client = await accessClientProvider.createAccessClient(
	scopes: const <String>[BigqueryApi.bigqueryScope],
	);
	return BigqueryApi(client).tabledata;
	}

	/// Return a [JobsResource] with an authenticated [client]
	Future<JobsResource> defaultJobs() async {
	final Client client = await accessClientProvider.createAccessClient(
	scopes: const <String>[BigqueryApi.bigqueryScope],
	);
	return BigqueryApi(client).jobs;
	}

	/// Return the top [limit] number of current builder statistic.
	///
	/// See getBuilderStatisticQuery to get the detail information about the table
	/// schema
	Future<List<BuilderStatistic>> listBuilderStatistic(
	String projectId, {
	int limit = 100,
	String bucket = 'prod',
	}) async {
	final JobsResource jobsResource = await defaultJobs();
	final QueryRequest query = QueryRequest.fromJson(<String, Object>{
	'query': bucket == 'staging' ? getStagingBuilderStatisticQuery : getBuilderStatisticQuery,
	'queryParameters': <Map<String, Object>>[
	<String, Object>{
	'name': 'LIMIT',
	'parameterType': <String, Object>{'type': 'INT64'},
	'parameterValue': <String, Object>{'value': '$limit'},
	},
	],
	'useLegacySql': false,
	});
	final QueryResponse response = await jobsResource.query(query, projectId);
	if (!response.jobComplete!) {
	throw 'job does not complete';
	}
	final List<BuilderStatistic> result = <BuilderStatistic>[];
	for (final TableRow row in response.rows!) {
	final String builder = row.f![0].v as String;
	List<String>? flakyBuilds = (row.f![3].v as String?)?.split(', ');
	flakyBuilds?.sort();
	flakyBuilds = flakyBuilds?.reversed.toList();
	List<String>? succeededBuilds = (row.f![4].v as String?)?.split(', ');
	succeededBuilds?.sort();
	succeededBuilds = succeededBuilds?.reversed.toList();
	result.add(
	BuilderStatistic(
	name: builder,
	flakyRate: double.parse(row.f![7].v as String),
	flakyBuilds: flakyBuilds ?? const <String>[],
	succeededBuilds: succeededBuilds ?? const <String>[],
	recentCommit: row.f![5].v as String?,
	flakyBuildOfRecentCommit: row.f![6].v as String?,
	flakyNumber: int.parse(row.f![1].v as String),
	totalNumber: int.parse(row.f![2].v as String),
	fromDate: row.f![8].v as String?,
	toDate: row.f![9].v as String?,
	),
	);
	}
	return result;
	}

	/// Return the list of current builder statistic.
	///
	/// See getBuilderStatisticQuery to get the detail information about the table
	/// schema
	Future<List<BuilderRecord>> listRecentBuildRecordsForBuilder(
	String projectId, {
	String? builder,
	int? limit,
	}) async {
	final JobsResource jobsResource = await defaultJobs();
	final QueryRequest query = QueryRequest.fromJson(<String, Object>{
	'query': getRecordsQuery,
	'parameterMode': 'NAMED',
	'queryParameters': <Map<String, Object>>[
	<String, Object>{
	'name': 'BUILDER_NAME',
	'parameterType': <String, Object>{'type': 'STRING'},
	'parameterValue': <String, Object?>{'value': builder},
	},
	<String, Object>{
	'name': 'LIMIT',
	'parameterType': <String, Object>{'type': 'INT64'},
	'parameterValue': <String, Object>{'value': '$limit'},
	},
	],
	'useLegacySql': false,
	});
	final QueryResponse response = await jobsResource.query(query, projectId);
	if (!response.jobComplete!) {
	throw 'job does not complete';
	}
	final List<BuilderRecord> result = <BuilderRecord>[];
	// When a test is newly marked as flaky, it is possible no execution exists.
	if (response.rows == null) {
	return result;
	}
	for (final TableRow row in response.rows!) {
	result.add(
	BuilderRecord(
	commit: row.f![0].v as String,
	isFlaky: row.f![1].v as String != '0',
	isFailed: row.f![2].v as String != '0',
	),
	);
	}
	return result;
	}
	}

	class BuilderRecord {
	BuilderRecord({
	required this.commit,
	required this.isFlaky,
	required this.isFailed,
	});

	final String commit;
	final bool isFlaky;
	final bool isFailed;
	}

	class BuilderStatistic {
	BuilderStatistic({
	required this.name,
	required this.flakyRate,
	required this.flakyNumber,
	required this.totalNumber,
	this.flakyBuilds,
	this.succeededBuilds,
	this.recentCommit,
	this.flakyBuildOfRecentCommit,
	this.fromDate,
	this.toDate,
	});

	final String name;
	final double flakyRate;
	final List<String>? flakyBuilds;
	final List<String>? succeededBuilds;
	final String? recentCommit;
	final String? flakyBuildOfRecentCommit;
	final int flakyNumber;
	final int totalNumber;
	final String? fromDate;
	final String? toDate;
	}