Add MW table 'cu_log' to data lake
Open, MediumPublic5 Estimated Story Points
Actions

Assigned To

Authored By

	mpopov
	May 7 2024, 3:34 PM

Description

Background/Goal

Product Analytics maintains a Superset dashboard of IP Masking (Temp Accounts) metrics for Trust & Safety Product. Most of these metrics are calculated from sqooped-up MediaWiki tables in the data lake (in wmf_raw). One table is missing – cu_log – and requires us to calculate the Admin Requests metric by querying MariaDB analytics replicas.

Currently all these metrics are calculated from a Jupyter notebook scheduled to run under @jwang's user account. We are starting to migrate the queries to Airflow (T364406) and will be able to migrate all but one which depends on cu_log.

Please add it to the list of MW tables that are sqooped up and made available as monthly snapshots in the data lake. Thank you!

KR/Hypothesis(Initiative)

Temporary accounts for unregistered users

Success metrics

How we will measure success

Example areas:

Deadlines
User satisfaction
Performance
Accessibility
Maintenance
Movement impact
Scalability
Data Quality
Integration
Compliance

In scope

Monthly snapshots of cu_log available in the data lake

Out of Scope

known boundaries

Artifacts & Resources

https://www.mediawiki.org/wiki/Extension:CheckUser/cu_log_table

Details

Subject	Repo	Branch	Lines +/-
Add create table statement file for wmf_raw.cu_log table	analytics/refinery	master	+42 -0
Add Query for cu_log table in sqoop.	analytics/refinery	master	+51 -0
Add cu_log table to sqoop job	operations/puppet	production	+1 -1

Customize query in gerrit

Related Objects

Mentioned In: T380318: Migrate admin_request_month schema to airflow
T379921: Implement a canary against MariaDB schema changes
T376752: Add cu_log_event and cu_private_event CheckUser tables to data lake
T364406: Migrate IP masking dashboard data pipeline to airflow
Mentioned Here: T364406: Migrate IP masking dashboard data pipeline to airflow

Event Timeline

mpopov triaged this task as Medium priority.May 7 2024, 3:34 PM

mpopov created this task.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 7 2024, 3:34 PM

jwang mentioned this in T364406: Migrate IP masking dashboard data pipeline to airflow.May 7 2024, 5:00 PM

mpopov updated the task description. (Show Details)May 14 2024, 4:12 PM

• lbowmaker moved this task from Backlog to Data Engineering on the Data-Platform board.May 14 2024, 4:53 PM

• lbowmaker added a project: Data-Engineering.

• lbowmaker set the point value for this task to 5.May 16 2024, 1:01 PM

• lbowmaker moved this task from Incoming (new tickets) to Candidates for next quarter on the Data-Engineering board.May 17 2024, 11:31 AM

JayCano added a project: Temporary accounts.Sep 12 2024, 12:49 PM

kostajh moved this task from Inbox to Triaged on the Temporary accounts board.Sep 13 2024, 11:00 AM

Update: @Ahoelzl is going to sync with DPE about sqoop and will follow up after the group is aligned on making more MariaDB wiki replica data available in the Data Lake.

mpopov mentioned this in T376752: Add cu_log_event and cu_private_event CheckUser tables to data lake.Oct 16 2024, 6:41 PM

Ahoelzl edited projects, added Data-Engineering (Q2 2024 October 1st - December 31th); removed Data-Engineering.Oct 17 2024, 5:41 PM

Ahoelzl assigned this task to Snwachukwu.Oct 17 2024, 6:08 PM

DPE DE started working on the table ingestion.

Ahoelzl moved this task from Next Up to In progress on the Data-Engineering (Q2 2024 October 1st - December 31th) board.Oct 18 2024, 5:45 PM

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

[analytics/refinery@master] Add Query for cu_log table in sqoop.

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

gerritbot added a project: Patch-For-Review.Wed, Oct 23, 4:50 PM

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

[operations/puppet@production] Add cu_log table to sqoop job

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

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

[analytics/refinery@master] Add create table statement file for wmf_raw.cu_log table

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

Change #1082515 merged by Snwachukwu:

[analytics/refinery@master] Add Query for cu_log table in sqoop.

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

Ahoelzl moved this task from In progress to In Review on the Data-Engineering (Q2 2024 October 1st - December 31th) board.Wed, Oct 30, 2:18 AM

Change #1084208 merged by Joal:

[analytics/refinery@master] Add create table statement file for wmf_raw.cu_log table

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

Change #1082800 merged by Brouberol:

[operations/puppet@production] Add cu_log table to sqoop job

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

Maintenance_bot removed a project: Patch-For-Review.Wed, Oct 30, 1:31 PM

joal merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/898

Add cu_log table to list of mediawiki sqoop tables to be loaded.

As requested by @jwang and @mpopov, September's data for cu_log is now available in the data lake at the wmf_raw.mediawiki_private_cu_log table. The cu_log table has been added to the list of tables to be sqooped monthly so expect to get monthly data as other tables.