Page MenuHomePhabricator
Create Task
Maniphest T416481

Adapt Sqoop for imagelinks schema changes
Closed, ResolvedPublic
Actions

Description

Similar to T397923 for categorylinks, the imagelinks MediaWiki table is being normalized in T299953 and we should adapt to it.

In addition to Sqoop, it's possible that Commons Impact Metrics also depends on this table directly.

I think T415786 is the ticket that tracks the actual migration in the production database.

We need to do the following:

  • Add il_target_id to Sqoop's imagelinks table definition in python/refinery/sqoop.py
  • Drop il_to column from Sqoop's imagelinks table definition by setting value to null
  • Update the CREATE HQL script of wmf_raw.mediawiki_imagelinks with the new columns added
  • Run alter table statement on table wmf_raw.mediawiki_imagelinks in Hive to the new column (ALTER TABLE ADD COLUMN...)
  • Update commons_impact_metrics hql script
  • Update CIM airflow dag

Details

Related Changes in Gerrit:
SubjectAuthorRepoBranchLines +/-
Adapt imagelinks pipeline and consumers for imagelink normalizationSnwachukwuanalytics/refinerymaster+34 -13
Customize query in gerrit
Related Changes in GitLab:
TitleReferenceAuthorSource BranchDest Branch
Pass mediawiki_private_linktarget_table to commons impact metrics dagrepos/data-engineering/airflow-dags!2026ebysansimagelinks_CIMmain
Customize query in GitLab

Related Objects
Search...

Event Timeline

GGoncalves-WMF created this task.Feb 4 2026, 2:32 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 4 2026, 2:32 PM
Ahoelzl edited projects, added: Data-Engineering (Q3 FY25/26 January 1st - March 31th); removed: Data-Engineering.Feb 5 2026, 6:04 PM
Ahoelzl assigned this task to Snwachukwu.Feb 9 2026, 11:06 PM
Zabe added a parent task: T299953: Normalize imagelinks table.Feb 10 2026, 12:51 AM
Snwachukwu updated the task description. (Show Details)Feb 11 2026, 8:30 PM
Snwachukwu updated the task description. (Show Details)
Snwachukwu updated the task description. (Show Details)Feb 12 2026, 2:19 PM
Snwachukwu updated the task description. (Show Details)Feb 12 2026, 2:39 PM
Snwachukwu updated the task description. (Show Details)
Snwachukwu updated the task description. (Show Details)Feb 12 2026, 2:41 PM
gerritbot added a comment.Feb 12 2026, 7:03 PM

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

[analytics/refinery@master] Adapt imagelinks pipeline and consumers for imagelink normalization

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

gerritbot added a project: Patch-For-Review.Feb 12 2026, 7:03 PM
JMonton-WMF moved this task from Next Up to In Review on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Feb 17 2026, 4:53 PM
xcollazo subscribed.Feb 18 2026, 8:12 PM

Considering the changes for commons impact metrics (CIM), can we add a step to make sure we compare a previous CIM run with a manual CIM run with the new changes just to make sure we are good?

Snwachukwu added a comment.Feb 20 2026, 2:20 PM

@xcollazo Sounds good. Are there particular metrics you’d like us to look out for or compare when validating the previous CIM run against the manual run with the new changes?

xcollazo added a comment.Feb 20 2026, 4:13 PM
In T416481#11636241, @Snwachukwu wrote:

@xcollazo Sounds good. Are there particular metrics you’d like us to look out for or compare when validating the previous CIM run against the manual run with the new changes?

I suggest we do:

  • A row count between the output tables of both versions. The row count should be equal or very close.
  • A comparison of page_title between the output of both versions.
    • The amount of NULLs should match or be very close.
    • A manual check of a few specific page_titles. Make sure they appear on both versions.
Snwachukwu added a comment.Feb 23 2026, 7:23 PM

I did a manual CIM run just comparing the number of rows alone, only the wmf_contributors.commons_category_metrics_snapshot and wmf_contributors.commons_edits have the same row counts with their equivalent test tables. The remaining 3 tables didn't even populate after running manually. I'm currently investigation the reason.

image.png (2,330×514 px, 164 KB)

> select count(*) from wmf_contributors.commons_category_metrics_snapshot where year_month='2026-01';
count(1)
607339
Time taken: 11.661 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_category_metrics_snapshot where year_month='2026-01';
count(1)
607339
Time taken: 0.765 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from wmf_contributors.commons_edits WHERE dt >= to_timestamp('2026-01') AND dt < to_timestamp('2026-01') + INTERVAL 1 MONTH;
count(1)
6779392
Time taken: 13.326 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_edits WHERE dt >= to_timestamp('2026-01') AND dt < to_timestamp('2026-01') + INTERVAL 1 MONTH;
count(1)
6779392
Snwachukwu added a comment.Feb 23 2026, 7:45 PM

Seems like this may have to do with the fact that all usage_map values in the intermidiate table category_and_media_with_usage_map_2026_01 is NULL value. Still trying to figure out why.

spark-sql (default)> select count(*) from ebysans.category_and_media_with_usage_map_2026_01;
count(1)
55684425
Time taken: 12.291 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from from ebysans.category_and_media_with_usage_map_2026_01 where usage_map is NULL;
from
55684425
Time taken: 11.787 seconds, Fetched 1 row(s)
spark-sql (default)>

image.png (1,860×358 px, 93 KB)

xcollazo added a comment.Feb 24 2026, 2:44 PM

@Snwachukwu can you share the parameters you used to run this test?

I suspect perhaps the previous step, compute_category_graph, maybe was not computed?

Snwachukwu added a comment.Feb 24 2026, 7:11 PM

@xcollazo Found the culprit. 😔 That new inner join produced no rows. Of course that's because the mediawiki_imagelinks table doesn't have the il_target_id yet. 🙈

spark-sql (default)> DROP TABLE IF EXISTS ebysans.imagelinks_with_title;
Response code
Time taken: 0.037 seconds
spark-sql (default)>
                   > CREATE TABLE ebysans.imagelinks_with_title
                   > USING PARQUET AS
                   > SELECT il.il_from,
                   >        il.wiki_db,
                   >        lt.lt_title
                   > FROM wmf_raw.mediawiki_imagelinks il
                   > INNER JOIN wmf_raw.mediawiki_private_linktarget lt
                   >     ON il.il_target_id = lt.lt_id
                   >     AND lt.snapshot = il.snapshot
                   >     AND lt.wiki_db = il.wiki_db
                   > WHERE il.snapshot = '2026-01'
                   >     AND il.il_from_namespace = 0
                   >     AND il.wiki_db NOT IN ('commonswiki', 'wikidatawiki')
                   >     AND lt.snapshot = '2026-01';
26/02/24 19:00:16 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.
26/02/24 19:00:55 WARN DAGScheduler: Broadcasting large task binary with size 1118.6 KiB
26/02/24 19:00:56 WARN DAGScheduler: Broadcasting large task binary with size 1160.5 KiB
Response code
Time taken: 98.699 seconds
spark-sql (default)>
                   >
                   > SELECT COUNT(*) AS cnt FROM ebysans.imagelinks_with_title;
cnt
0
Time taken: 5.905 seconds, Fetched 1 row(s)
spark-sql (default)>
Snwachukwu added a comment.Feb 24 2026, 7:19 PM
This comment was removed by Snwachukwu.
Snwachukwu added a comment.EditedFeb 24 2026, 7:23 PM

Okay so after using the imagelink table gotten from the manual sqoop run to run a manual CIM. here are the row counts of all the tables below. You would see that all tables are poluated. But there is a diff in row counts for common_pageviews_per_category_monthly, commons_pageviews_per_media_file_monthly, and commons_media_file_metrics_snapshot

spark-sql (default)> select count(*) from wmf_contributors.commons_media_file_metrics_snapshot where year_month='2026-01';
count(1)
5816473
Time taken: 0.737 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_media_file_metrics_snapshot where year_month='2026-01';
count(1)
5822645
Time taken: 0.422 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from wmf_contributors.commons_pageviews_per_category_monthly where year_month='2026-01';
count(1)
78818419
Time taken: 1.457 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_pageviews_per_category_monthly where year_month='2026-01'
                   > ;
count(1)
78804872
Time taken: 9.373 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from wmf_contributors.commons_pageviews_per_media_file_monthly where year_month='2026-01';
count(1)
35433015
Time taken: 6.013 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_pageviews_per_media_file_monthly where year_month='2026-01';
count(1)
35432414
Time taken: 6.776 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from wmf_contributors.commons_edits WHERE dt >= to_timestamp('2026-01') AND dt < to_timestamp('2026-01') + INTERVAL 1 MONTH;
count(1)
6779392
Time taken: 15.135 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.commons_edits WHERE dt >= to_timestamp('2026-01') AND dt < to_timestamp('2026-01') + INTERVAL 1 MONTH;
count(1)
6779392
xcollazo added a comment.Feb 25 2026, 6:06 PM

But there is a diff in row counts

I would expect the numbers to shift a bit considering that you did a manual sqoop that would have pulled data as of yesterday, while the regular run used data as of the 1st of the month.

This LGTM. Thanks for the effort to do these tests @Snwachukwu !

xcollazo added a comment.Feb 25 2026, 6:10 PM

Can we also please report on the other two tests from T416481#11636745?

Ottomata mentioned this in T417492: Drop support for cl_to, cl_collation and il_to from wikireplicas.Feb 25 2026, 6:21 PM
Snwachukwu added a comment.Feb 25 2026, 7:30 PM

Sure @xcollazo .
NB: All test was done using snapshot=2026-01

First, I compared the count of production wmf_raw.mediawiki_imagelink and the manual sqoop run test table ebysans.mediawiki_imagelink. Their count was very similar.

spark-sql (default)> select count(*) from ebysans.mediawiki_imagelinks where snapshot='2026-01';
26/02/25 18:40:03 WARN DAGScheduler: Broadcasting large task binary with size 1021.6 KiB
count(1)
1878916264

spark-sql (default)> select count(*) from wmf_raw.mediawiki_imagelinks where snapshot='2026-01';
26/02/25 17:08:27 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.
count(1)
1868251813

Next, I checked that all rows in the ebysans.mediawiki_imagelinks has an imagelink target id. Which from the numbers below it does. So meaning, provided linktarget table is complete we should get all the page_title values when we do inner join.

spark-sql (default)> select count(il_target_id) from ebysans.mediawiki_imagelinks where snapshot='2026-01';
26/02/25 18:45:05 WARN DAGScheduler: Broadcasting large task binary with size 1059.8 KiB
count(il_target_id)
1878916264

Then I ran the join manually in that CIM hql code section to get imagelinks_with_title saved in a table.

imagelinks_with_title AS (
    SELECT il.il_from,
           il.wiki_db,
           lt.lt_title
    FROM ${mediawiki_imagelinks_table} il
             INNER JOIN ${mediawiki_private_linktarget_table} lt
                 ON il.il_target_id = lt.lt_id
                 AND lt.snapshot = il.snapshot
                 AND lt.wiki_db = il.wiki_db
    WHERE il.snapshot = '${snapshot}'
      AND il.il_from_namespace = 0
      AND il.wiki_db NOT IN ('commonswiki', 'wikidatawiki')
      AND lt.snapshot = '${snapshot}'

then the ran count of rows and count of page_title

spark-sql (default)> select count(lt_title) from ebysans.imagelinks_with_title_test;
count(lt_title)
420272524
Time taken: 17.267 seconds, Fetched 1 row(s)
spark-sql (default)> select count(*) from ebysans.imagelinks_with_title_test;
count(1)
420272524
Time taken: 7.491 seconds, Fetched 1 row(s)

compared to the count of target_id in the manually sqooped imagelink table after filtering based on wiki_db and namespace i think the difference is not bad.

spark-sql (default)> select count(il_target_id) from ebysans.mediawiki_imagelinks where snapshot='2026-01' and wiki_db NOT IN ('commonswiki', 'wikidatawiki') and il_from_namespace = 0;;
26/02/25 18:52:17 WARN DAGScheduler: Broadcasting large task binary with size 1110.8 KiB
count(il_target_id)
420827165
Snwachukwu added a comment.EditedFeb 25 2026, 7:40 PM

With regards to

A manual check of a few specific page_titles. Make sure they appear on both versions.

I'm not sure at what point you think would be best to check,

xcollazo added a comment.Feb 25 2026, 7:48 PM

I'm not sure at what point you think would be best to check,

Perhaps the point where we were loosing rows before would be best?

Snwachukwu added a subscriber: Zabe.Feb 26 2026, 2:58 PM

We are loosing 554641 rows at the point where where we do the join to linktarget table. Not all il_target_id have a corresponding page_title in the linktarget table. About 366519 target_id don't have corresponding page_title in wmf_raw.mediawiki_private_linktarget table.

Time taken: 0.562 seconds
spark-sql (default)>
                   > CREATE TABLE ebysans.imagelinks_with_title_left_join
                   > USING PARQUET AS
                   > SELECT
                   >     il.il_from,
                   >     il.wiki_db,
                   >     lt.lt_title,
                   >     il.il_target_id
                   > FROM ebysans.mediawiki_imagelinks il
                   > LEFT JOIN wmf_raw.mediawiki_private_linktarget lt
                   >   ON il.il_target_id = lt.lt_id
                   >  AND lt.snapshot = il.snapshot
                   >  AND lt.wiki_db = il.wiki_db
                   > WHERE il.snapshot = '2026-01'
                   >   AND il.il_from_namespace = 0
                   >   AND il.wiki_db NOT IN ('commonswiki', 'wikidatawiki');
26/02/26 03:53:30 WARN DAGScheduler: Broadcasting large task binary with size 1194.1 KiB
26/02/26 03:53:30 WARN DAGScheduler: Broadcasting large task binary with size 1120.0 KiB
Response code
Time taken: 135.941 seconds
spark-sql (default)> select count(*) from ebysans.imagelinks_with_title_left_join;
count(1)
420827165
Time taken: 93.206 seconds, Fetched 1 row(s)
spark-sql (default)> SELECT count(il_target_id)
                   > FROM ebysans.imagelinks_with_title_left_join
                   > WHERE lt_title IS NULL;
count(il_target_id)
554641

Just confirming the ow numbers by comparing ebysans.mediawiki_imagelinks directly with wmf_raw.mediawiki_private_linktarget

CREATE TABLE ebysans.missing_imagelinks_target_ids_2026_01
                   > USING PARQUET AS
                   > SELECT DISTINCT
                   >     mil.il_target_id,
                   >     mil.wiki_db,
                   >     mil.il_from,
                   >     mil.il_from_namespace
                   > FROM ebysans.mediawiki_imagelinks mil
                   > LEFT ANTI JOIN wmf_raw.mediawiki_private_linktarget lt
                   >   ON mil.il_target_id = lt.lt_id
                   >  AND lt.snapshot = mil.snapshot
                   >  AND lt.wiki_db = mil.wiki_db
                   > WHERE mil.snapshot = '2026-01'
                   >   AND mil.il_from_namespace = 0
                   >   AND mil.wiki_db NOT IN ('commonswiki', 'wikidatawiki');
26/02/26 14:24:41 WARN DAGScheduler: Broadcasting large task binary with size 1171.5 KiB
26/02/26 14:24:41 WARN DAGScheduler: Broadcasting large task binary with size 1096.6 KiB
Response code
Time taken: 95.678 seconds
spark-sql (default)> SELECT COUNT(*) FROM ebysans.missing_imagelinks_target_ids_2026_01;
count(1)
554641

distinct count of missing target_id

spark-sql (default)> SELECT count(distinct il_target_id)
                   > FROM ebysans.imagelinks_with_title_left_join
                   > WHERE lt_title IS NULL;
count(DISTINCT il_target_id)
366519

How should we proceed given that the il_to column is expected to be deprecated soon? My expectation is that linktarget table should be refreshed with updated titles by the next Sqoop run—@Zabe, could you please help confirm the expected timing?

Additionally, @xcollazo, could you help assess whether the resulting impact on CIM metrics is acceptable in the short term, or if this warrants prioritizing a fix before the next run?

CodeReviewBot added a comment.Feb 26 2026, 6:07 PM

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

Pass mediawiki_private_linktarget_table to commons impact metrics dag

xcollazo added a comment.Feb 26 2026, 6:16 PM

554641 / 420827165 * 100 = 0.13 %

Thus I am not worried about the impact to Commons Impact Metrics.

It would be good to understand why we have this behavior, but I do not think it should block moving forward with the changes. We do make sure to cover this when we do INNER JOIN.

Zabe added a comment.EditedFeb 27 2026, 7:57 PM
In T416481#11654804, @Snwachukwu wrote:

[...]

How should we proceed given that the il_to column is expected to be deprecated soon? My expectation is that linktarget table should be refreshed with updated titles by the next Sqoop run—@Zabe, could you please help confirm the expected timing?

The population of il_target_id on commonswiki was completed on February 16.

I also made sure there are no missing target ids on prod:

wikiadmin2023@10.192.0.7(commonswiki)> select count(distinct il_target_id) from imagelinks left join linktarget on il_target_id = lt_id where lt_title is null;
+------------------------------+
| count(distinct il_target_id) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (8 min 0.339 sec)

wikiadmin2023@10.192.0.7(commonswiki)>

So if the data is not fully populated in the last Sqoop run it should be available in the next one. I can of course wait with dropping il_to until that happened.

Snwachukwu moved this task from In Review to Ready to Deploy on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mar 2 2026, 5:06 PM
gerritbot added a comment.Mar 3 2026, 4:15 PM

Change #1239200 merged by Snwachukwu:

[analytics/refinery@master] Adapt imagelinks pipeline and consumers for imagelink normalization

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

Snwachukwu added a comment.Mar 5 2026, 2:18 PM

Thank you @Zabe for the explanation. Indeed I used stale data from last sqoop run.

Stashbot added a comment.Mar 5 2026, 6:47 PM

Mentioned in SAL (#wikimedia-analytics) [2026-03-05T18:47:34Z] <dr0ptp4kt> Deploying change 1239200 for refinery ( T416481 )

Stashbot added a comment.Mar 5 2026, 6:47 PM

Mentioned in SAL (#wikimedia-operations) [2026-03-05T18:47:42Z] <dr0ptp4kt> Deploying change 1239200 for refinery ( T416481 )

Stashbot added a comment.Mar 5 2026, 7:04 PM

Mentioned in SAL (#wikimedia-analytics) [2026-03-05T19:04:32Z] <dr0ptp4kt> Deploying change 1239200 for refinery ( T416481 ) using scap, then deployed onto hdfs

Stashbot added a comment.Mar 5 2026, 7:04 PM

Mentioned in SAL (#wikimedia-operations) [2026-03-05T19:04:36Z] <dr0ptp4kt> Deploying change 1239200 for refinery ( T416481 ) using scap, then deployed onto hdfs

CodeReviewBot added a comment.Mar 5 2026, 7:08 PM

dr0ptp4kt merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2026

Pass mediawiki_private_linktarget_table to commons impact metrics dag

Maintenance_bot removed a project: Patch-For-Review.Mar 5 2026, 7:31 PM
Ottomata moved this task from Ready to Deploy to Done on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mar 9 2026, 3:35 PM
Ottomata subscribed.Mar 11 2026, 4:54 PM

T419635: Drop il_to column from imagelinks table in wmf production just showed up in Data-Engineering inbox. Just verifying that the sqoop work is coordinated with the mariadb work.

Ottomata mentioned this in T419635: Drop il_to column from imagelinks table in wmf production.Mar 11 2026, 4:55 PM
Snwachukwu updated the task description. (Show Details)Mar 11 2026, 4:57 PM

The sqoop work is done.

Ahoelzl edited projects, added: Data-Engineering (Q4 FS25/26 April 1st - June 30st); removed: Data-Engineering (Q3 FY25/26 January 1st - March 31th).Apr 3 2026, 10:26 PM
Ahoelzl moved this task from Next Up to Done on the Data-Engineering (Q4 FS25/26 April 1st - June 30st) board.
Ahoelzl closed this task as Resolved.Apr 3 2026, 10:30 PM
Ahoelzl edited projects, added: Data-Engineering (Q3 FY25/26 January 1st - March 31th); removed: Data-Engineering (Q4 FS25/26 April 1st - June 30st).Apr 3 2026, 10:31 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