forked from dora-team/fourkeys
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdeployments.sql
More file actions
74 lines (72 loc) · 2.71 KB
/
Copy pathdeployments.sql
File metadata and controls
74 lines (72 loc) · 2.71 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# Deployments Table
WITH deploys_cloudbuild_github_gitlab AS (# Cloud Build, Github, Gitlab pipelines
SELECT
source,
id as deploy_id,
time_created,
CASE WHEN source = "cloud_build" then JSON_EXTRACT_SCALAR(metadata, '$.substitutions.COMMIT_SHA')
WHEN source like "github%" then JSON_EXTRACT_SCALAR(metadata, '$.deployment.sha')
WHEN source like "gitlab%" then JSON_EXTRACT_SCALAR(metadata, '$.commit.id') end as main_commit,
CASE WHEN source LIKE "github%" THEN ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY(metadata, '$.deployment.additional_sha')) AS string_element)
ELSE ARRAY<string>[] end as additional_commits
FROM four_keys.events_raw
WHERE ((source = "cloud_build"
AND JSON_EXTRACT_SCALAR(metadata, '$.status') = "SUCCESS")
OR (source LIKE "github%" and event_type = "deployment_status" and JSON_EXTRACT_SCALAR(metadata, '$.deployment_status.state') = "success")
OR (source LIKE "gitlab%" and event_type = "pipeline" and JSON_EXTRACT_SCALAR(metadata, '$.object_attributes.status') = "success"))
),
deploys_tekton AS (# Tekton Pipelines
SELECT
source,
id as deploy_id,
time_created,
IF(JSON_EXTRACT_SCALAR(param, '$.name') = "gitrevision", JSON_EXTRACT_SCALAR(param, '$.value'), Null) as main_commit,
ARRAY<string>[] AS additional_commits
FROM (
SELECT
id,
TIMESTAMP_TRUNC(time_created, second) as time_created,
source,
four_keys.json2array(JSON_EXTRACT(metadata, '$.data.pipelineRun.spec.params')) params
FROM four_keys.events_raw
WHERE event_type = "dev.tekton.event.pipelinerun.successful.v1"
AND metadata like "%gitrevision%") e, e.params as param
),
deploys AS (
SELECT * FROM
deploys_cloudbuild_github_gitlab
UNION ALL
SELECT * FROM deploys_tekton
),
changes_raw AS (
SELECT
id,
metadata as change_metadata
FROM four_keys.events_raw
),
deployment_changes as (
SELECT
source,
deploy_id,
deploys.time_created time_created,
change_metadata,
four_keys.json2array(JSON_EXTRACT(change_metadata, '$.commits')) as array_commits,
main_commit
FROM deploys
JOIN
changes_raw on (
changes_raw.id = deploys.main_commit
or changes_raw.id in unnest(deploys.additional_commits)
)
)
SELECT
source,
deploy_id,
time_created,
main_commit,
ARRAY_AGG(DISTINCT JSON_EXTRACT_SCALAR(array_commits, '$.id')) changes,
FROM deployment_changes
CROSS JOIN deployment_changes.array_commits
GROUP BY 1,2,3,4;