Page MenuHomePhabricator
Create Task
Maniphest T404735

Fix CommonsCategoryGraphBuilder to reflect latest changes to categorylinks table
Closed, ResolvedPublic
Actions

Description

Column categorylinks.cl_to has been deprecated and set to NULL recently (see T397923) but org.wikimedia.analytics.refinery.job.CommonsCategoryGraphBuilder depends on that column to construct a SQL query:

SELECT
    cl.cl_from,
    pg.page_id AS cl_to,
    cl.cl_type
FROM ${params.categorylinksTable} cl
    INNER JOIN ${params.pageTable} pg
    ON (cl.cl_to = pg.page_title)
WHERE
    cl.wiki_db = 'commonswiki' AND
    cl.snapshot = '${params.mediawikiSnapshot}' AND
    pg.wiki_db = 'commonswiki' AND
    pg.snapshot = '${params.mediawikiSnapshot}' AND
    pg.page_namespace = 14

This issue prevented us from delivering the Commons Impact Metrics job results for August 2025. Please fix the query in org.wikimedia.analytics.refinery.job.CommonsCategoryGraphBuilder and re-run the August 2025 job.

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
Update changelog.md for v0.3.2analytics/refinery/sourcemaster+8 -0
Additional constraints to the CommonsCategoryGraphBuilder queryanalytics/refinery/sourcemaster+2 -0
Update CommonsCategoryGraphBuilder to avoid column cl_toanalytics/refinery/sourcemaster+8 -1
Customize query in gerrit
Related Changes in GitLab:
TitleReferenceAuthorSource BranchDest Branch
Support the new CommonsCategoryGraphBuilder coderepos/data-engineering/airflow-dags!1682amastilovicfeature/T404735-Fix-CommonsCategoryGraphBuildermain
Customize query in GitLab

Related Objects

Event Timeline

amastilovic created this task.Sep 16 2025, 4:01 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 16 2025, 4:01 PM
GFontenelle_WMF subscribed.Sep 16 2025, 8:16 PM
gerritbot added a comment.Sep 17 2025, 2:12 AM

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

[analytics/refinery/source@master] Update CommonsCategoryGraphBuilder to avoid column cl_to

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

gerritbot added a project: Patch-For-Review.Sep 17 2025, 2:12 AM
CodeReviewBot added a comment.Sep 17 2025, 2:32 AM

amastilovic opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1682

Draft: Support the new CommonsCategoryGraphBuilder code

amastilovic added a comment.EditedSep 17 2025, 2:34 AM

The new SQL query is:

SELECT
    cl.cl_from,
    pg.page_id AS cl_to,
    cl.cl_type
FROM ${params.categorylinksTable} cl
    INNER JOIN ${params.linktargetTable} lt
    INNER JOIN ${params.pageTable} pg
    ON (cl.cl_target_id = lt.lt_id AND lt.lt_title = pg.page_title)
WHERE
    cl.wiki_db = 'commonswiki' AND
    cl.snapshot = '${params.mediawikiSnapshot}' AND
    lt.snapshot = '${params.mediawikiSnapshot}' AND
    pg.wiki_db = 'commonswiki' AND
    pg.snapshot = '${params.mediawikiSnapshot}' AND
    pg.page_namespace = 14

Manual execution of the query for the 2025-08 snapshot does give (what looks like correct) data back:

image.png (209×303 px, 17 KB)

gerritbot added a comment.Sep 17 2025, 2:07 PM

Change #1188914 merged by jenkins-bot:

[analytics/refinery/source@master] Update CommonsCategoryGraphBuilder to avoid column cl_to

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

CodeReviewBot added a comment.Sep 17 2025, 3:35 PM

amastilovic merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1682

Support the new CommonsCategoryGraphBuilder code

Maintenance_bot removed a project: Patch-For-Review.Sep 17 2025, 4:30 PM
gerritbot added a comment.Sep 17 2025, 8:31 PM

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

[analytics/refinery/source@master] Additional constraints to the CommonsCategoryGraphBuilder query

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

gerritbot added a project: Patch-For-Review.Sep 17 2025, 8:31 PM
gerritbot added a comment.Sep 17 2025, 8:45 PM

Change #1189323 merged by jenkins-bot:

[analytics/refinery/source@master] Additional constraints to the CommonsCategoryGraphBuilder query

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

Maintenance_bot removed a project: Patch-For-Review.Sep 17 2025, 9:30 PM
amastilovic added a comment.Sep 18 2025, 2:36 AM

The 2025-08 backfill run of the DAG has completed successfully, and judging by data sizes on HDFS I'd say it falls in line with what we've seen in the previous months. @GFontenelle_WMF if you have some basic validation checks to run on this data, now would be a good time. Thank you!

gerritbot added a comment.Sep 18 2025, 10:07 PM

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

[analytics/refinery/source@master] Update changelog.md for v0.3.2

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

gerritbot added a project: Patch-For-Review.Sep 18 2025, 10:07 PM
GFontenelle_WMF added a comment.Sep 19 2025, 2:04 AM

@amastilovic: I've tested it and it looks like it's working normally now. Thanks so much!

gerritbot added a comment.Sep 19 2025, 4:09 PM

Change #1189588 merged by Aleksandar Mastilovic:

[analytics/refinery/source@master] Update changelog.md for v0.3.2

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

Maintenance_bot removed a project: Patch-For-Review.Sep 19 2025, 4:31 PM
Ahoelzl moved this task from Next Up to Done on the Data-Engineering (Q1 FY25/26 July 1st - September 30th) board.Sep 22 2025, 3:15 PM
Ahoelzl closed this task as Resolved.Sep 24 2025, 9:37 PM
mforns mentioned this in T406509: Commons Impact Metrics has no data for September snapshot.Oct 6 2025, 4:51 PM
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