Page MenuHomePhabricator
Create Task
Maniphest T406000

Adapt mediawiki_history to the removal of mediawiki revision.rev_sha1
Closed, ResolvedPublic
Actions

Description

The work from T389026: Rethink rev_sha1 field will remove revision.rev_sha1 from MariaDB.
This field is currently used to compute identity-reverts in the mediawiki_history dataset.
The value can still be computed using content-slots-sha1 available in the content table (to be accessed from the revision table with a join to the slots table and then the content table).

As written in this comment, an implementation of the sha1 value using content-slots-sha1 is easy.
An analysis of the code shows that the slot-order is made on the slot-role (alphabetical) and that slots to be considered are non-derived slots..

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
Update mediawiki_history job for rev_sha1 DB removalanalytics/refinery/sourcemaster+382 -211
Add appropriate NULL checks to computeForTuplesanalytics/refinery/sourcemaster+32 -3
Fix mediawiki-history UDF NPEanalytics/refinery/sourcemaster+20 -5
Update sqoop for mediawiki_historyoperations/puppetproduction+2 -2
Nullify sha1 from revision and archive in sqoopanalytics/refinerymaster+2 -2
Improve mediawiki_history previous patchanalytics/refinery/sourcemaster+140 -48
Fix mediawiki_history bug from previous patchanalytics/refinery/sourcemaster+28 -9
Fix mediawiki_history bug from previous patchanalytics/refinery/sourcemaster+6 -2
Customize query in gerrit
Related Changes in GitLab:
TitleReferenceAuthorSource BranchDest Branch
Update mediawiki_history_denormalize jarrepos/data-engineering/airflow-dags!1782joalupdate_mediawiki_history_denorm_jarmain
Update mediawiki_history_denormalize jar versionrepos/data-engineering/airflow-dags!1753joalupdate_mediawiki_denormalize_jarmain
Update mediawiki_history_denormalize dependenciesrepos/data-engineering/airflow-dags!1750joalupdate_mediawiki_history_denormalizemain
Customize query in GitLab

Related Objects
Search...

Event Timeline

JAllemandou created this task.Sep 30 2025, 6:47 AM
JAllemandou updated the task description. (Show Details)Sep 30 2025, 8:25 AM
JAllemandou updated the task description. (Show Details)Sep 30 2025, 8:31 AM
JAllemandou added a comment.Sep 30 2025, 8:58 AM

Proposal for this task:

Questions:

  • Do we have slots / content for archived revisions? I assume yes, but I have never checked.
  • Is it ok to write the UDF as a Spark only UDF instead of a Hive UF (compatible with spark)? We don't use Hive execution engine anymore and the code is more complicated for Hive...
JAllemandou added a comment.Sep 30 2025, 8:59 AM

For reference: example of base-36 sha1 creation with scala/spark:

math.BigInt(spark.sql("SELECT sha1(CONCAT('1jkqj7lxs8l999wu3jzlmzafwh6e2h4', 't15553qh44ewu6rrgv4xbrkpoutrvj3'))").collect.map(r => r.getString(0)).head, 16).toString(36)
JAllemandou added a subscriber: mforns.Sep 30 2025, 9:39 AM

@Milimetric and @mforns , may I ask you to review the plan and question in the comment above please?

Milimetric added a comment.Sep 30 2025, 3:07 PM
In T406000#11228093, @JAllemandou wrote:

Proposal for this task:

sounds mostly good, no notes except for the below:

  • Update mediawiki_history scala job to compute sha1 from content for both archive and revision
    • Join archive/revision with slots, slots_role and content to get, for each revision, an array of struct(slot_role, content_sha1) (non-derived content/slots only)
    • Write a spark UDF getting the array of struct as parameter and returning the computed sha1
      • Sort the array of struct by slot_role
      • Reduce the array on content sha1 values as described here

Here - maybe worth considering - shifting MW history down a level to think about slots instead of revisions. Then we wouldn't have to compute this overall sha1, we'd be just dealing with the individual slot sha1. This would maybe be a bigger change in some cases but would be a better interpretation of the MW database model. With MCR adoption what it is now, maybe this doesn't matter. But if that takes off, we'd probably have to think about slots more anyway.

Questions:

  • Do we have slots / content for archived revisions? I assume yes, but I have never checked.

Yes, just checked:

select *
  from slots
           inner join
       (select ar_rev_id
          from archive
         where ar_timestamp > 2025
         limit 10
       ) a       on ar_rev_id = slot_revision_id
  • Is it ok to write the UDF as a Spark only UDF instead of a Hive UF (compatible with spark)? We don't use Hive execution engine anymore and the code is more complicated for Hive...

definitely Spark-only, no reason to take on tech debt

JAllemandou added a comment.Oct 1 2025, 1:01 PM

shifting MW history down a level to think about slots instead of revisions. Then we wouldn't have to compute this overall sha1, we'd be just dealing with the individual slot sha1. This would maybe be a bigger change in some cases but would be a better interpretation of the MW database model. With MCR adoption what it is now, maybe this doesn't matter. But if that takes off, we'd probably have to think about slots more anyway.

I don't really manage to wrap my head around what this change would entail and how the dataset would look like after the change. Given that there is only small adoption of MCR today, I'd go for the simple way of re-implementing rev_sha1. Happy to discuss more if you think the bigger change is worth the effort @Milimetric .

Ottomata added a comment.Oct 1 2025, 5:57 PM

Hi!

Since we are making changes to MW history, could we do T365648: Add user_central_id to mediawiki_history and mediawiki_history_reduced Hive tables at the same time? We need this for T403660: WE3.3.7 Year in Review and Activity Tab Services - Global Editor Metrics, and it would be nice to include it in main MW history , rather than only in the downstream druid serving layer dataset.

Who is actively working on this?

Thank you!

Ahoelzl edited projects, added Data-Engineering (Q2 FY25/26 October 1st - December 31th); removed Data-Engineering (Q1 FY25/26 July 1st - September 30th).Oct 9 2025, 12:13 AM
xcollazo removed xcollazo as the assignee of this task.Oct 10 2025, 1:29 AM
xcollazo moved this task from Next Up to Urgent on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.
xcollazo moved this task from Urgent to Next Up on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.
gerritbot added a comment.Oct 14 2025, 12:27 PM

Change #1196049 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Update mediawiki_history job for rev_sha1 DB removal

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

gerritbot added a project: Patch-For-Review.Oct 14 2025, 12:27 PM
JAllemandou claimed this task.Oct 14 2025, 4:07 PM
JAllemandou moved this task from Next Up to In progress on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.Oct 14 2025, 4:10 PM
gerritbot added a comment.Oct 15 2025, 1:40 AM

Change #1196049 merged by jenkins-bot:

[analytics/refinery/source@master] Update mediawiki_history job for rev_sha1 DB removal

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

Maintenance_bot removed a project: Patch-For-Review.Oct 15 2025, 2:30 AM
gerritbot added a comment.Oct 15 2025, 2:34 PM

Change #1196469 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Fix mediawiki_history bug from previous patch

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

gerritbot added a project: Patch-For-Review.Oct 15 2025, 2:34 PM
gerritbot added a comment.Oct 15 2025, 3:49 PM

Change #1196469 merged by jenkins-bot:

[analytics/refinery/source@master] Fix mediawiki_history bug from previous patch

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

gerritbot added a comment.Oct 15 2025, 4:10 PM

Change #1196485 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Fix mediawiki_history bug from previous patch

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

gerritbot added a comment.Oct 15 2025, 6:05 PM

Change #1196485 merged by jenkins-bot:

[analytics/refinery/source@master] Fix mediawiki_history bug from previous patch

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

Maintenance_bot removed a project: Patch-For-Review.Oct 15 2025, 6:31 PM
gerritbot added a comment.Oct 15 2025, 7:43 PM

Change #1196518 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Improve mediawiki_history previous patch

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

gerritbot added a project: Patch-For-Review.Oct 15 2025, 7:43 PM
JAllemandou moved this task from In progress to In Review on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.Oct 16 2025, 7:14 AM
CodeReviewBot added a comment.Oct 16 2025, 8:48 AM

joal opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1750

Update mediawiki_history_denormalize dependencies

gerritbot added a comment.Oct 16 2025, 8:53 AM

Change #1196631 had a related patch set uploaded (by Joal; author: Joal):

[operations/puppet@production] Update sqoop for mediawiki_history

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

gerritbot added a comment.Oct 16 2025, 3:59 PM

Change #1196518 merged by jenkins-bot:

[analytics/refinery/source@master] Improve mediawiki_history previous patch

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

gerritbot added a comment.Oct 16 2025, 4:34 PM

Change #1196716 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery@master] Nullify sha1 from revision and archive in sqoop

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

gerritbot added a comment.Oct 16 2025, 4:36 PM

Change #1196716 merged by Mforns:

[analytics/refinery@master] Nullify sha1 from revision and archive in sqoop

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

CodeReviewBot added a comment.Oct 16 2025, 5:21 PM

mforns merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1750

Update mediawiki_history_denormalize dependencies

gerritbot added a comment.Oct 16 2025, 5:23 PM

Change #1196631 merged by Btullis:

[operations/puppet@production] Update sqoop for mediawiki_history

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

Maintenance_bot removed a project: Patch-For-Review.Oct 16 2025, 5:30 PM
JAllemandou moved this task from In Review to Ready to Deploy on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.Oct 16 2025, 5:34 PM
JAllemandou moved this task from Ready to Deploy to Done on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.
CodeReviewBot added a project: Patch-For-Review.Oct 17 2025, 6:34 AM

joal opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1753

Update mediawiki_history_denormalize jar version

CodeReviewBot added a comment.Oct 17 2025, 2:08 PM

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

Update mediawiki_history_denormalize jar version

Maintenance_bot removed a project: Patch-For-Review.Oct 17 2025, 2:30 PM
xcollazo changed the task status from Open to In Progress.Oct 17 2025, 3:16 PM
JAllemandou mentioned this in T406644: Stop sqooping revision.rev_sha1.Oct 20 2025, 6:50 AM
JAllemandou merged a task: T406644: Stop sqooping revision.rev_sha1.
Ahoelzl closed this task as Resolved.Oct 24 2025, 9:53 PM
gerritbot added a comment.Nov 5 2025, 4:12 PM

Change #1202191 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Fix mediawiki-history UDF NPE

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

gerritbot added a project: Patch-For-Review.Nov 5 2025, 4:12 PM
gerritbot added a comment.Nov 5 2025, 4:25 PM

Change #1202191 merged by Joal:

[analytics/refinery/source@master] Fix mediawiki-history UDF NPE

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

Maintenance_bot removed a project: Patch-For-Review.Nov 5 2025, 4:30 PM
CodeReviewBot added a project: Patch-For-Review.Nov 5 2025, 7:20 PM

joal opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1782

Update mediawiki_hsitory_denormalize jar

CodeReviewBot added a comment.Nov 5 2025, 7:39 PM

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

Update mediawiki_history_denormalize jar

Maintenance_bot removed a project: Patch-For-Review.Nov 5 2025, 8:30 PM
gerritbot added a comment.Nov 5 2025, 11:45 PM

Change #1202334 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[analytics/refinery/source@master] Add appropriate NULL checks to computeForTuples

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

gerritbot added a project: Patch-For-Review.Nov 5 2025, 11:45 PM
gerritbot added a comment.Nov 6 2025, 8:02 AM

Change #1202334 merged by jenkins-bot:

[analytics/refinery/source@master] Add appropriate NULL checks to computeForTuples

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

Maintenance_bot removed a project: Patch-For-Review.Nov 6 2025, 8:31 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits