diff --git a/app/workers/click_house/events_sync_worker.rb b/app/workers/click_house/events_sync_worker.rb index ea9d610bd5204cdd9515fb84c1c257f89abf3901..21c10566a672cc0f82aeaf7b533f194d3128f7fd 100644 --- a/app/workers/click_house/events_sync_worker.rb +++ b/app/workers/click_house/events_sync_worker.rb @@ -99,7 +99,7 @@ def last_event_id_in_postgresql strong_memoize_attr :last_event_id_in_postgresql def enabled? - ClickHouse::Client.configuration.databases[:main].present? && Feature.enabled?(:event_sync_worker_for_click_house) + ClickHouse::Client.database_configured?(:main) && Feature.enabled?(:event_sync_worker_for_click_house) end def next_batch diff --git a/db/click_house/migrate/main/20231207173100_create_ci_used_minutes.rb b/db/click_house/migrate/main/20231207173100_create_ci_used_minutes.rb new file mode 100644 index 0000000000000000000000000000000000000000..901b0eed75638f6130fc909956dc21560b044803 --- /dev/null +++ b/db/click_house/migrate/main/20231207173100_create_ci_used_minutes.rb @@ -0,0 +1,26 @@ +# frozen_string_literal: true + +class CreateCiUsedMinutes < ClickHouse::Migration + def up + execute <<~SQL + CREATE TABLE IF NOT EXISTS ci_used_minutes + ( + project_id UInt64 DEFAULT 0, + status LowCardinality(String) DEFAULT '', + runner_type UInt8 DEFAULT 0, + finished_at_bucket DateTime64(6, 'UTC') DEFAULT now64(), + + count_builds AggregateFunction(count), + total_duration SimpleAggregateFunction(sum, Int64) + ) + ENGINE = AggregatingMergeTree() + ORDER BY (finished_at_bucket, project_id, status, runner_type) + SQL + end + + def down + execute <<~SQL + DROP TABLE ci_used_minutes + SQL + end +end diff --git a/db/click_house/migrate/main/20231207173101_create_ci_used_minutes_mv.rb b/db/click_house/migrate/main/20231207173101_create_ci_used_minutes_mv.rb new file mode 100644 index 0000000000000000000000000000000000000000..856710b660ea2168e5eced4bb5e1bc2e54e51dad --- /dev/null +++ b/db/click_house/migrate/main/20231207173101_create_ci_used_minutes_mv.rb @@ -0,0 +1,27 @@ +# frozen_string_literal: true + +class CreateCiUsedMinutesMv < ClickHouse::Migration + def up + execute <<~SQL + CREATE MATERIALIZED VIEW IF NOT EXISTS ci_used_minutes_mv + TO ci_used_minutes + AS + SELECT + project_id, + status, + runner_type, + toStartOfInterval(finished_at, INTERVAL 1 day) AS finished_at_bucket, + + countState() AS count_builds, + sumSimpleState(duration) AS total_duration + FROM ci_finished_builds + GROUP BY project_id, status, runner_type, finished_at_bucket + SQL + end + + def down + execute <<~SQL + DROP VIEW ci_used_minutes_mv + SQL + end +end diff --git a/ee/app/policies/ee/global_policy.rb b/ee/app/policies/ee/global_policy.rb index f99582c9d40643760a2efeca652972130ef778f4..534727c4acd698ad7ba78cd7a3a93ea789dd835e 100644 --- a/ee/app/policies/ee/global_policy.rb +++ b/ee/app/policies/ee/global_policy.rb @@ -29,6 +29,10 @@ module GlobalPolicy end end + condition(:clickhouse_main_database_available) do + ClickHouse::Client.database_configured?(:main) + end + condition(:instance_devops_adoption_available) do ::License.feature_available?(:instance_level_devops_adoption) end @@ -111,6 +115,10 @@ module GlobalPolicy rule { admin & runner_performance_insights_available }.enable :read_jobs_statistics + rule { admin & runner_performance_insights_available & clickhouse_main_database_available }.policy do + enable :read_runner_usage + end + rule { admin & service_accounts_available }.enable :admin_service_accounts rule { admin & custom_roles_allowed }.policy do diff --git a/ee/app/services/ci/runners/generate_usage_csv_service.rb b/ee/app/services/ci/runners/generate_usage_csv_service.rb new file mode 100644 index 0000000000000000000000000000000000000000..99c6fc13ee6b71fbcbd8237b3b5d356940635846 --- /dev/null +++ b/ee/app/services/ci/runners/generate_usage_csv_service.rb @@ -0,0 +1,114 @@ +# frozen_string_literal: true + +module Ci + module Runners + # Generates a CSV report containing the runner usage for a given period + # (based on ClickHouse's ci_used_minutes_mv view) + # + class GenerateUsageCsvService + attr_reader :project_ids, :runner_type, :from_time, :to_time + + REPORT_ENTRY_LIMIT = 500 # Max number of projects listed in report + + # @param [User] current_user The user performing the reporting + # @param [Symbol] runner_type The type of runners to report on. Defaults to nil, reporting on all runner types + # @param [DateTime] from_time The start date of the period to examine. Defaults to start of last full month + # @param [DateTime] to_time The end date of the period to examine. Defaults to end of month + def initialize(current_user:, runner_type: nil, from_time: nil, to_time: nil) + runner_type = Ci::Runner.runner_types[runner_type] if runner_type.is_a?(Symbol) + + @current_user = current_user + @runner_type = runner_type + @from_time = from_time || DateTime.current.prev_month.beginning_of_month + @to_time = to_time || @from_time.end_of_month + end + + def execute + return db_not_configured unless ClickHouse::Client.database_configured?(:main) + return insufficient_permissions unless Ability.allowed?(@current_user, :read_runner_usage) + + result = ClickHouse::Client.select(clickhouse_query, :main) + csv_builder = CsvBuilder::SingleBatch.new(replace_with_project_paths(result), header_to_value_hash) + csv_data = csv_builder.render(ExportCsv::BaseService::TARGET_FILESIZE) + + ServiceResponse.success(payload: { csv_data: csv_data, status: csv_builder.status }) + rescue StandardError => e + Gitlab::ErrorTracking.track_and_raise_for_dev_exception(e) + ServiceResponse.error(message: 'Failed to generate export') + end + + private + + def db_not_configured + ServiceResponse.error(message: 'ClickHouse database is not configured', reason: :db_not_configured) + end + + def insufficient_permissions + ServiceResponse.error(message: 'Insufficient permissions to generate export', reason: :insufficient_permissions) + end + + def header_to_value_hash + { + 'Project ID' => 'project_id', + 'Project path' => 'project_path', + 'Build count' => 'count_builds', + 'Total duration (minutes)' => 'total_duration_in_mins', + 'Total duration' => 'total_duration_human_readable' + } + end + + def clickhouse_query + raw_query = <<~SQL.squish + SELECT project_id, + countMerge(count_builds) AS count_builds, + sumSimpleState(total_duration) / 60000 AS total_duration_in_mins + FROM ci_used_minutes_mv + WHERE #{where_clause} + GROUP BY project_id + ORDER BY total_duration_in_mins DESC, project_id + LIMIT #{REPORT_ENTRY_LIMIT}; + SQL + + ClickHouse::Client::Query.new(raw_query: raw_query, placeholders: placeholders) + end + + def where_clause + <<~SQL + #{'runner_type = {runner_type: UInt8} AND' if runner_type} + finished_at_bucket >= {from_time: DateTime('UTC', 6)} AND + finished_at_bucket < {to_time: DateTime('UTC', 6)} + SQL + end + + def placeholders + placeholders = { + runner_type: runner_type, + from_time: format_datetime(@from_time), + to_time: format_datetime(@to_time) + } + + placeholders.compact + end + + def format_datetime(datetime) + datetime&.utc&.strftime('%Y-%m-%d %H:%M:%S') + end + + def replace_with_project_paths(result) + # rubocop: disable CodeReuse/ActiveRecord -- This is a ClickHouse query + ids = result.pluck('project_id') # rubocop: disable Database/AvoidUsingPluckWithoutLimit -- The limit is already implemented in the ClickHouse query + # rubocop: enable CodeReuse/ActiveRecord + return result if ids.empty? + + projects = Project.inc_routes.id_in(ids).limit(REPORT_ENTRY_LIMIT).to_h { |p| [p.id, p.full_path] } + + # Annotate rows with project paths + result.each do |row| + row['project_path'] = projects[row['project_id']] + row['total_duration_human_readable'] = + ActiveSupport::Duration.build(row['total_duration_in_mins'] * 60).inspect + end + end + end + end +end diff --git a/ee/app/services/click_house/data_ingestion/ci_finished_builds_sync_service.rb b/ee/app/services/click_house/data_ingestion/ci_finished_builds_sync_service.rb index 617c9f0cb9818c7cead2f4e361babadc247e2e34..1e9f7d43b826e04977c0e4ae0f3e14dda5fd22dc 100644 --- a/ee/app/services/click_house/data_ingestion/ci_finished_builds_sync_service.rb +++ b/ee/app/services/click_house/data_ingestion/ci_finished_builds_sync_service.rb @@ -29,7 +29,7 @@ def execute ) end - unless ClickHouse::Client.configuration.databases[:main].present? + unless ClickHouse::Client.database_configured?(:main) return ServiceResponse.error( message: 'ClickHouse database is not configured', reason: :db_not_configured, payload: service_payload) end diff --git a/ee/spec/policies/global_policy_spec.rb b/ee/spec/policies/global_policy_spec.rb index 155fe417b6ac5ea27c3c0e1e0a30af4947250280..18f9c68b3e7cc2f4b2fd9d7efabee23c589e1b47 100644 --- a/ee/spec/policies/global_policy_spec.rb +++ b/ee/spec/policies/global_policy_spec.rb @@ -410,6 +410,38 @@ end end + describe 'read_runner_usage' do + include AdminModeHelper + + where(:licensed, :is_admin, :enable_admin_mode, :clickhouse_configured, :expected) do + true | true | true | true | true + false | true | true | true | false + true | false | true | true | false + true | true | false | true | false + true | true | true | false | false + end + + with_them do + before do + stub_licensed_features(runner_performance_insights: licensed) + + enable_admin_mode!(admin) if enable_admin_mode + + allow(ClickHouse::Client).to receive(:database_configured?).with(:main).and_return(clickhouse_configured) + end + + let(:current_user) { is_admin ? admin : user } + + it 'matches expectation' do + if expected + is_expected.to be_allowed(:read_runner_usage) + else + is_expected.to be_disallowed(:read_runner_usage) + end + end + end + end + describe 'read_jobs_statistics' do context 'when feature is enabled' do before do diff --git a/ee/spec/services/ci/runners/generate_usage_csv_service_spec.rb b/ee/spec/services/ci/runners/generate_usage_csv_service_spec.rb new file mode 100644 index 0000000000000000000000000000000000000000..4af592c631037d0b6016589ed464f57697c279dd --- /dev/null +++ b/ee/spec/services/ci/runners/generate_usage_csv_service_spec.rb @@ -0,0 +1,194 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Ci::Runners::GenerateUsageCsvService, :enable_admin_mode, :click_house, :freeze_time, + feature_category: :fleet_visibility do + include ClickHouseHelpers + + let_it_be(:current_user) { create(:admin) } + let_it_be(:instance_runner) { create(:ci_runner, :instance, :with_runner_manager) } + let_it_be(:group) { create(:group) } + let_it_be(:group_runner) { create(:ci_runner, :group, groups: [group]) } + let_it_be(:builds) do + starting_time = DateTime.new(2023, 12, 31, 21, 0, 0) + + builds = Array.new(20) do |i| + project = create(:project, group: group) + create_build(instance_runner, project, starting_time + (50.minutes * i)) + end + + project = create(:project, group: group) + builds << create_build(group_runner, project, starting_time, 2.hours) + builds << create_build(instance_runner, project, starting_time, 10.minutes) + builds << create_build(instance_runner, create(:project, group: group), starting_time, 7.minutes) + builds + end + + let(:runner_type) { nil } + let(:from_time) { nil } + let(:to_time) { nil } + let(:service) do + described_class.new(current_user: current_user, runner_type: runner_type, from_time: from_time, to_time: to_time) + end + + let(:expected_header) { "Project ID,Project path,Build count,Total duration (minutes),Total duration\n" } + let(:expected_from_time) { DateTime.new(2023, 12, 1) } + let(:expected_to_time) { DateTime.new(2023, 12, 31, 23, 59, 59) } + + subject(:response) { service.execute } + + before do + stub_licensed_features(runner_performance_insights: true) + + insert_ci_builds_to_click_house(builds) + + travel_to DateTime.new(2024, 1, 10) + end + + context 'when current_user is not an admin' do + let_it_be(:current_user) { create(:user) } + + it 'returns error due to insufficient permissions' do + is_expected.to be_error + + expect(response.message).to eq('Insufficient permissions to generate export') + expect(response.reason).to eq(:insufficient_permissions) + end + end + + context 'when runner_performance_insights feature is not available' do + before do + stub_licensed_features(runner_performance_insights: false) + end + + it 'returns error due to insufficient permissions' do + is_expected.to be_error + + expect(response.message).to eq('Insufficient permissions to generate export') + expect(response.reason).to eq(:insufficient_permissions) + end + end + + context 'when no ClickHouse databases are configured' do + before do + allow(ClickHouse::Client).to receive(:database_configured?).and_return(false) + end + + it 'returns error' do + is_expected.to be_error + + expect(response.message).to eq('ClickHouse database is not configured') + expect(response.reason).to eq(:db_not_configured) + end + end + + it 'contains 23 builds in source ci_finished_builds table' do + expect(ClickHouse::Client.select('SELECT count() FROM ci_finished_builds', :main)) + .to contain_exactly({ 'count()' => 23 }) + end + + it 'exports usage data for all runners for the last complete month', :aggregate_failures do + expect_next_instance_of(CsvBuilder::SingleBatch, anything, anything) do |csv_builder| + expect(csv_builder).to receive(:render) + .with(ExportCsv::BaseService::TARGET_FILESIZE) + .and_call_original + end + + expect(response.payload[:csv_data].lines).to eq([ + expected_header, + "#{builds[21].project_id},#{builds[21].project.full_path},2,130,2 hours and 10 minutes\n", + "#{builds[0].project_id},#{builds[0].project.full_path},1,14,14 minutes\n", + "#{builds[1].project_id},#{builds[1].project.full_path},1,14,14 minutes\n", + "#{builds[2].project_id},#{builds[2].project.full_path},1,14,14 minutes\n", + "#{builds[3].project_id},#{builds[3].project.full_path},1,14,14 minutes\n", + "#{builds.last.project_id},#{builds.last.project.full_path},1,7,7 minutes\n" + ]) + + expect(response.payload[:status]).to eq({ rows_expected: 6, rows_written: 6, truncated: false }) + end + + context 'with group_type runner_type argument specified' do + let(:runner_type) { :group_type } + + it 'exports usage data for runners of specified type' do + expect(response.payload[:csv_data].lines).to eq([ + expected_header, + "#{builds[21].project_id},#{builds[21].project.full_path},1,120,2 hours\n" + ]) + + expect(response.payload[:status]).to eq({ rows_expected: 1, rows_written: 1, truncated: false }) + end + end + + context 'with project_type runner_type argument specified' do + let(:runner_type) { :project_type } + + it 'exports usage data for runners of specified type' do + expect(response.payload[:csv_data].lines).to contain_exactly(expected_header) + expect(response.payload[:status]).to eq({ rows_expected: 0, rows_written: 0, truncated: false }) + end + end + + context 'when from_time is beginning of current month' do + let(:from_time) { DateTime.new(2024, 1, 1) } + let(:expected_from_time) { from_time } + let(:expected_to_time) { from_time.end_of_month } + + it 'exports usage data for runners which finished builds before date' do + expect(response.payload[:status]).to eq({ rows_expected: 16, rows_written: 16, truncated: false }) + end + end + + context 'and from_time is next month' do + let(:from_time) { DateTime.new(2024, 2, 1) } + let(:expected_from_time) { from_time } + let(:expected_to_time) { from_time.end_of_month } + + it 'exports usage data for runners which finished builds before date' do + expect(response.payload[:status]).to eq({ rows_expected: 0, rows_written: 0, truncated: false }) + end + end + + context 'and to_time is an hour ago, almost at the end of the year' do + let(:to_time) { DateTime.new(2023, 12, 31, 23, 0, 0) } + let(:expected_from_time) { DateTime.new(2023, 11, 1) } + let(:expected_to_time) { to_time } + + before do + travel_to DateTime.new(2023, 12, 31, 23, 59, 59) + end + + it 'exports usage data for runners which finished builds after date' do + expect(response.payload[:status]).to eq({ rows_expected: 6, rows_written: 6, truncated: false }) + end + end + + context 'and to_time is end of last month' do + let(:to_time) { DateTime.new(2024, 1, 31) } + let(:expected_from_time) { DateTime.new(2024, 1, 1) } + let(:expected_to_time) { to_time } + + before do + travel_to DateTime.new(2024, 2, 10) + end + + it 'exports usage data for runners which finished builds after date' do + expect(response.payload[:status]).to eq({ rows_expected: 16, rows_written: 16, truncated: false }) + end + end + + def create_build(runner, project, created_at, duration = 14.minutes) + started_at = created_at + 6.minutes + + build(:ci_build, + :success, + created_at: created_at, + queued_at: created_at, + started_at: started_at, + finished_at: started_at + duration, + project: project, + runner: runner, + runner_manager: runner.runner_managers.first) + end +end diff --git a/ee/spec/services/click_house/data_ingestion/ci_finished_builds_sync_service_spec.rb b/ee/spec/services/click_house/data_ingestion/ci_finished_builds_sync_service_spec.rb index 93b7253658c4bba93c2d415a346f996aea699932..43541b42cc914635de458ed297490837442bb7a5 100644 --- a/ee/spec/services/click_house/data_ingestion/ci_finished_builds_sync_service_spec.rb +++ b/ee/spec/services/click_house/data_ingestion/ci_finished_builds_sync_service_spec.rb @@ -216,7 +216,7 @@ context 'when no ClickHouse databases are configured' do before do - allow(ClickHouse::Client.configuration).to receive(:databases).and_return({}) + allow(ClickHouse::Client).to receive(:database_configured?).and_return(false) end it 'skips execution' do diff --git a/ee/spec/workers/click_house/ci_finished_builds_sync_worker_spec.rb b/ee/spec/workers/click_house/ci_finished_builds_sync_worker_spec.rb index fcf6f638ecf5e189114b75cce1b63aa821021e77..52c647141be6e93b7dfae6e19246f4bf75b7dca9 100644 --- a/ee/spec/workers/click_house/ci_finished_builds_sync_worker_spec.rb +++ b/ee/spec/workers/click_house/ci_finished_builds_sync_worker_spec.rb @@ -40,7 +40,7 @@ context 'when an error is reported from service' do before do - allow(ClickHouse::Client.configuration).to receive(:databases).and_return({}) + allow(ClickHouse::Client).to receive(:database_configured?).and_return(false) end it 'skips execution' do diff --git a/gems/click_house-client/lib/click_house/client.rb b/gems/click_house-client/lib/click_house/client.rb index 1ca3653c45f915af7660ecfb398c40d535a3395f..499201a1abaf54fe7050b11164178d7969f72447 100644 --- a/gems/click_house-client/lib/click_house/client.rb +++ b/gems/click_house-client/lib/click_house/client.rb @@ -30,6 +30,10 @@ def configure DatabaseError = Class.new(Error) QueryError = Class.new(Error) + def self.database_configured?(database, configuration = self.configuration) + !!configuration.databases[database] + end + # Executes a SELECT database query def self.select(query, database, configuration = self.configuration) instrumented_execute(query, database, configuration) do |response, instrument| diff --git a/gems/csv_builder/lib/csv_builder/builder.rb b/gems/csv_builder/lib/csv_builder/builder.rb index 99b63153ab2d0fd1ea61dea895d9863c95e0cd41..ff7e51cf7cea018583bdedd31578fd57195ce510 100644 --- a/gems/csv_builder/lib/csv_builder/builder.rb +++ b/gems/csv_builder/lib/csv_builder/builder.rb @@ -78,7 +78,9 @@ def attributes def row(object) attributes.map do |attribute| - if attribute.respond_to?(:call) + if object.is_a?(Hash) + excel_sanitize(object[attribute]) + elsif attribute.respond_to?(:call) excel_sanitize(attribute.call(object)) else excel_sanitize(object.public_send(attribute)) # rubocop:disable GitlabSecurity/PublicSend diff --git a/spec/workers/click_house/events_sync_worker_spec.rb b/spec/workers/click_house/events_sync_worker_spec.rb index da74e5e376dcb58c2755cc36d60020c9eb1ea8a5..9662f26115a768fd00041e1ad40bafcd27f45d8b 100644 --- a/spec/workers/click_house/events_sync_worker_spec.rb +++ b/spec/workers/click_house/events_sync_worker_spec.rb @@ -151,7 +151,7 @@ context 'when clickhouse is not configured' do before do - allow(ClickHouse::Client.configuration).to receive(:databases).and_return({}) + allow(ClickHouse::Client).to receive(:database_configured?).and_return(false) end it 'skips execution' do @@ -165,7 +165,7 @@ context 'when exclusive lease error happens' do it 'skips execution' do stub_feature_flags(event_sync_worker_for_click_house: true) - allow(ClickHouse::Client.configuration).to receive(:databases).and_return({ main: :some_db }) + allow(ClickHouse::Client).to receive(:database_configured?).with(:main).and_return(true) expect(worker).to receive(:in_lock).and_raise(Gitlab::ExclusiveLeaseHelpers::FailedToObtainLockError) expect(worker).to receive(:log_extra_metadata_on_done).with(:result, { status: :skipped })