[Maintenance] Migrate ReportUpdater browser queries to Airflow
Open, Needs TriagePublic8 Estimated Story Points
Actions

Assigned To

Authored By

	lbowmaker
	Jan 8 2024, 7:45 PM

Description

Migrate these queries to Airflow.

https://gerrit.wikimedia.org/r/plugins/gitiles/analytics/reportupdater-queries/+/refs/heads/master/browser/

Data is used to populate:

https://analytics.wikimedia.org/dashboards/browsers

Done is:

Queries are executed via Airflow
Report updater job is turned off

Details

Subject	Repo	Branch	Lines +/-
Add DataPivoter job	analytics/refinery/source	master	+444 -0
Add spark sql version of browser mobile and desktop querries. 1. Modify browser all_site queries. 2. Add browser desktop and mobile queries.	analytics/refinery	master	+71 -119
Add Reportupdater Browser All Sites Queries.	analytics/refinery	master	+321 -0

Customize query in gerrit

Title	Reference	Author	Source Branch	Dest Branch
Update analytics browser_metrics dag	repos/data-engineering/airflow-dags!666	joal	update_browser_report_dag	main
Update browser metric airflow job to use DataPivoter	repos/data-engineering/airflow-dags!622	ebysans	reportupdater_browser	main
Add dag for browser All Sites ReportUpdater.	repos/data-engineering/airflow-dags!615	ebysans	reportupdater_browser	main

Customize query in GitLab

Related Objects
Search...

Status	Assigned	Task
Resolved	None	T307500 Airflow Hackathon (May 2022)
Open	mforns	T307540 [Airflow Migration] Migrate reportupdater jobs
Open	amastilovic	T354552 [Maintenance] Migrate ReportUpdater browser queries to Airflow

Event Timeline

lbowmaker created this task.Jan 8 2024, 7:45 PM

Ahoelzl renamed this task from Migrate browser queries to Airflow to [Maintenance] Migrate ReportUpdater browser queries to Airflow.Jan 9 2024, 5:43 PM

Snwachukwu claimed this task.Jan 10 2024, 2:06 PM

Snwachukwu moved this task from Next Up to In progress on the Data-Engineering (Sprint 7) board.Jan 10 2024, 7:54 PM

The suggested approach for this will be to use spark to run the queries after which result will be saved in the cluster. However, spark saves files in folder and we don't want to have different folders for each querry result. We want to put all the output files (report) in one location with is already rsynced to report server. Thus we would use our hdfsarchive operator to move the generated output from the spark output path to the final destination.
To start, we would migrate the queries in the browser folder first.

lbowmaker set the point value for this task to 8.Jan 17 2024, 12:53 PM

lbowmaker edited projects, added Data-Engineering (Sprint 8); removed Data-Engineering (Sprint 7).Jan 30 2024, 12:21 PM

lbowmaker moved this task from Next Up to In progress on the Data-Engineering (Sprint 8) board.

Change 995271 had a related patch set uploaded (by Snwachukwu; author: Snwachukwu):

[analytics/refinery/source@master] Add Dynamic Pivot job for reportupdater reports

https://gerrit.wikimedia.org/r/995271

gerritbot added a project: Patch-For-Review.Feb 2 2024, 5:33 PM

Change 995740 had a related patch set uploaded (by Snwachukwu; author: Snwachukwu):

[analytics/refinery@master] Add Reportupdater Browser All Sites Queries.

https://gerrit.wikimedia.org/r/995740

Snwachukwu moved this task from In progress to In Review on the Data-Engineering (Sprint 8) board.Feb 4 2024, 12:14 AM

We added the following:

A spark-scala job to perform dynamic pivot because some reports need to be pivoted.
A UDF to get the start of a week given a date. This will be used in the querries since we are getting the aggregate from the very beginning each time we generate reports.

lbowmaker edited projects, added Data-Engineering (Sprint 9); removed Data-Engineering (Sprint 8).Feb 16 2024, 4:16 PM

lbowmaker moved this task from Next Up to In Review on the Data-Engineering (Sprint 9) board.

ebysans opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/615

Add dag for browser All Sites ReportUpdater.

ebysans merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/615

Add dag for browser All Sites ReportUpdater.

Change 995740 merged by Snwachukwu:

[analytics/refinery@master] Add Reportupdater Browser All Sites Queries.

https://gerrit.wikimedia.org/r/995740

Change 1005129 had a related patch set uploaded (by Snwachukwu; author: Snwachukwu):

[analytics/refinery@master] Add spark sql version of browser mobile and desktop querries. 1. Modify browser all_site queries. 2. Add browser desktop and mobile queries.

https://gerrit.wikimedia.org/r/1005129

Change 1005129 merged by Snwachukwu:

[analytics/refinery@master] Add spark sql version of browser mobile and desktop querries. 1. Modify browser all_site queries. 2. Add browser desktop and mobile queries.

https://gerrit.wikimedia.org/r/1005129

ebysans opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/622

Update browser metric airflow job to use DataPivoter

Change 995271 merged by jenkins-bot:

[analytics/refinery/source@master] Add DataPivoter job

https://gerrit.wikimedia.org/r/995271

ebysans merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/622

Update browser metric airflow job to use DataPivoter

Snwachukwu moved this task from In Review to Ready to Deploy on the Data-Engineering (Sprint 9) board.Mar 8 2024, 5:22 PM

Maintenance_bot removed a project: Patch-For-Review.Mar 8 2024, 5:30 PM

Ahoelzl reassigned this task from Snwachukwu to JAllemandou.Mar 21 2024, 2:03 AM

Ahoelzl added a subscriber: Snwachukwu.