1444243 - Add Treeherder database as dataset to atmo

Status: RESOLVED FIXED

(Data Platform and Tools Graveyard :: Telemetry Analysis Service (ATMO), enhancement)

Description

[Sebastian Hengst [:aryx] (needinfo me if it's about an intermittent or backout)]

I am running into the limitations of stmo with one of my queries, both regarding run time and complexity. It's using the treeherder data source and for a goal.

RaFromBRC mentioned it might be time to consider using ATMO to deploy a spark notebook or python script.

Thank you in advance.

Comment 1

[Kyle Lahnakoski [:ekyle]]

Yes, I would love to help with this! There is a replica of the Treeherder database. There is a job that converts the Treeherder jobs to JSON [1] and stores them in S3 [2]. These documents are in ActiveData [3], and you have some (primitive) access via STMO [4].

[1] Code to generate JSON: https://github.com/klahnakoski/MySQL-to-S3
[2a] Example JSON in S3: https://s3-us-west-2.amazonaws.com/active-data-treeherder-jobs/1176.90.json.gz
[2b] Same, but normalized: https://s3-us-west-2.amazonaws.com/active-data-treeherder-normalized/1176.90.json.gz
[3] Example in ActiveData:  https://activedata.allizom.org/tools/query.html#query_id=IBtfSDPN
[4] Example in STMO: https://sql.telemetry.mozilla.org/queries/52203/source 

Note that STMO depends on a limited SQL Parser (https://github.com/mozilla/moz-sql-parser). If you want to use SQL, then I would like to work with you the generate more sophisticated test cases.

Comment 2

[Kyle Lahnakoski [:ekyle]]

:aryx 

So, what direction are you going to go?

Comment 3

[Sebastian Hengst [:aryx] (needinfo me if it's about an intermittent or backout)]

Attachment: classification time - flowchart - v3.png

Hi Kyle, sorry for the late reply. Was on PTO and busy with other work last week. Thank you for the links.

The data rules are attached in the flowchart image.

With STMO this was possible: https://sql.telemetry.mozilla.org/queries/51941 It's missing the time delta calculations near the bottom of the flow chart (adding more complex stuff lets it time out).

A partial implementation as ActiveData query is this:
{
	"from":"treeherder",
	"where":{"and":[
		{"eq":{"run.state":"completed"}},
		{"in":{"repo.branch.name":[
			"autoland",
			"mozilla-inbound",
			"mozilla-central",
			"mozilla-beta"
		]}},
		{"in":{"run.tier":[1,2]}},
		{"in":{"run.result":["busted","exception","testfailed"]}},
		{"neq":{"failure.notes.failure_classification":"autoclassified intermittent"}},
		{"not":{"find":{"job.type.name":"test-verify"}}},
		{"gt":["action.end_time",{"date":"today-1month"}]}
	]},
	"format":"list"
}

The 4 hours limit has not been implemented, it would use "action.start_time", "repo.date" and the 4 hours. Has this to be done with |sub|? https://github.com/mozilla/ActiveData/blob/dev/docs/jx_expressions.md How has the whole |lt| condition to look like?

The retrigger rule near the bottom looks hard to implement.

Eventually, there shall be a dashboard (showing a numeric time delta). MDC shall ingest the output to allow viewing the status over a longer timespan.

Comment 4

[Kyle Lahnakoski [:ekyle]]

I too apologize for not getting back sooner. I will look over your query and see what I can do to mimic it in ActiveData.  ActiveData has the historical record; so, once you know what to pull, we can partitions the results over time, and other dimensions if you wish.

I *think* you are looking for {"lt":[{"sub":["action.start_time", "repo.date"]}, 14400]} for the four hour limit filter.

Comment 5

[Kyle Lahnakoski [:ekyle]]

Also, ActiveData has a "percentile" aggregate, so your query will be a bit simpler.

Comment 6

[Kyle Lahnakoski [:ekyle]]

Here is what I think you are looking for. Of course, we must still review the where clause, to be sure it is picking up (or excluding) what we expect it to

Looking at the result, it seems my work on the ActiveData upgrade has let the ETL pipeline get a bit behind. I will be fixing that for sure. 

{
	"from":"treeherder",
	"select":[{
		"name":"time to classify (hours)",
		"value":{"div":[{"sub":["failure.notes.created","action.end_time"]},3600]},
		"aggregate":"percentile",
		"percentile":0.9
	}],
	"edges":[{
		"name":"date",
		"value":"action.end_time",
		"domain":{
			"type":"time",
			"min":"today-month",
			"max":"today",
			"interval":"day"
		}
	}],
	"where":{"and":[
		{"eq":{"run.state":"completed"}},
		{"in":{"repo.branch.name":[
			"autoland",
			"mozilla-inbound",
			"mozilla-central",
			"mozilla-beta"
		]}},
		{"ne":{"failure.classification":"not classified"}},
		{"ne":{"failure.classify":"not classified"}},
		{"in":{"run.tier":[1,2]}},
		{"in":{"run.result":["busted","exception","testfailed"]}},
		{"neq":{"failure.notes.failure_classification":"autoclassified intermittent"}},
		{"not":{"find":{"job.type.name":"test-verify"}}},
		{"gt":["action.end_time",{"date":"today-1month"}]},
		{"lt":[{"sub":["action.start_time","repo.push.date"]},14400]}
	]},
	"format":"list",
	"limit":10000
}