diff options
Diffstat (limited to 'db/click_house/main')
4 files changed, 65 insertions, 0 deletions
diff --git a/db/click_house/main/20230707151359_create_ci_finished_builds.sql b/db/click_house/main/20230707151359_create_ci_finished_builds.sql new file mode 100644 index 00000000000..6b1b846518d --- /dev/null +++ b/db/click_house/main/20230707151359_create_ci_finished_builds.sql @@ -0,0 +1,33 @@ +-- source table for CI analytics, almost useless on it's own, but it's a basis for creating materialized views +CREATE TABLE ci_finished_builds +( + id UInt64 DEFAULT 0, + project_id UInt64 DEFAULT 0, + pipeline_id UInt64 DEFAULT 0, + status LowCardinality(String) DEFAULT '', + + --- Fields to calculate timings + created_at DateTime64(6, 'UTC') DEFAULT now(), + queued_at DateTime64(6, 'UTC') DEFAULT now(), + finished_at DateTime64(6, 'UTC') DEFAULT now(), + started_at DateTime64(6, 'UTC') DEFAULT now(), + + runner_id UInt64 DEFAULT 0, + runner_manager_system_xid String DEFAULT '', + + --- Runner fields + runner_run_untagged Boolean DEFAULT FALSE, + runner_type UInt8 DEFAULT 0, + runner_manager_version LowCardinality(String) DEFAULT '', + runner_manager_revision LowCardinality(String) DEFAULT '', + runner_manager_platform LowCardinality(String) DEFAULT '', + runner_manager_architecture LowCardinality(String) DEFAULT '', + + --- Materialized columns + duration Int64 MATERIALIZED age('second', started_at, finished_at), + queueing_duration Int64 MATERIALIZED age('second', queued_at, started_at) + --- This table is incomplete, we'll add more fields before starting the data migration +) +ENGINE = ReplacingMergeTree -- Using ReplacingMergeTree just in case we accidentally insert the same data twice +ORDER BY (status, runner_type, project_id, finished_at, id) +PARTITION BY toYear(finished_at) diff --git a/db/click_house/main/20230719101806_create_ci_finished_builds_aggregated_queueing_delay_percentiles.sql b/db/click_house/main/20230719101806_create_ci_finished_builds_aggregated_queueing_delay_percentiles.sql new file mode 100644 index 00000000000..56889ffc0d4 --- /dev/null +++ b/db/click_house/main/20230719101806_create_ci_finished_builds_aggregated_queueing_delay_percentiles.sql @@ -0,0 +1,11 @@ +CREATE TABLE ci_finished_builds_aggregated_queueing_delay_percentiles +( + status LowCardinality(String) DEFAULT '', + runner_type UInt8 DEFAULT 0, + started_at_bucket DateTime64(6, 'UTC') DEFAULT now(), + + count_builds AggregateFunction(count), + queueing_duration_quantile AggregateFunction(quantile, Int64) +) +ENGINE = AggregatingMergeTree() +ORDER BY (started_at_bucket, status, runner_type) diff --git a/db/click_house/main/20230808070520_create_events_cursor.sql b/db/click_house/main/20230808070520_create_events_cursor.sql new file mode 100644 index 00000000000..effc3c64f60 --- /dev/null +++ b/db/click_house/main/20230808070520_create_events_cursor.sql @@ -0,0 +1,9 @@ +CREATE TABLE sync_cursors +( + table_name LowCardinality(String) DEFAULT '', + primary_key_value UInt64 DEFAULT 0, + recorded_at DateTime64(6, 'UTC') DEFAULT now() +) +ENGINE = ReplacingMergeTree(recorded_at) +ORDER BY (table_name) +PRIMARY KEY (table_name) diff --git a/db/click_house/main/20230808140217_create_ci_finished_builds_aggregated_queueing_delay_percentiles_mv.sql b/db/click_house/main/20230808140217_create_ci_finished_builds_aggregated_queueing_delay_percentiles_mv.sql new file mode 100644 index 00000000000..504e2d87609 --- /dev/null +++ b/db/click_house/main/20230808140217_create_ci_finished_builds_aggregated_queueing_delay_percentiles_mv.sql @@ -0,0 +1,12 @@ +CREATE MATERIALIZED VIEW ci_finished_builds_aggregated_queueing_delay_percentiles_mv +TO ci_finished_builds_aggregated_queueing_delay_percentiles +AS +SELECT + status, + runner_type, + toStartOfInterval(started_at, INTERVAL 5 minute) AS started_at_bucket, + + countState(*) as count_builds, + quantileState(queueing_duration) AS queueing_duration_quantile +FROM ci_finished_builds +GROUP BY status, runner_type, started_at_bucket |