Something in the sqoop process apparently causes wmf_raw.mediawiki_private_actor(and therefore wmf.mediawiki_history as well) to mangle usernames that use unusual characters.
I found this looking for duplicate actor IDs for the same user:
select
actor_name,
count(actor_id) as n_actor_ids,
collect_list(actor_user) as user_ids
from wmf_raw.mediawiki_private_actor
where
snapshot = "2019-07" and
wiki_db = "enwiki"
group by actor_name
having n_actor_ids > 1| actor_name | n_actor_ids | user_ids |
|---|---|---|
| ???? | 28 | [7971454,7630487,29548314,169932,1241395,14754180,16163829,36027965,36919251,37020397,28526886,28303085,30943519,30775112,32487125,30943557,31584194,29929605,30943142,32547336,31767650,29548814,31082738,30747253,30989504,32838619,35052220,35310020] |
| ???????? | 4 | [13080937,555876,7977532,19937304] |
| ???????????? | 2 | [1481183,29076045] |
| ???????????????????? | 2 | [1956565,34551626] |
| ???????????????????????? | 2 | [31469439,30293052] |
| ???????????????????????? ???????????????????????? | 2 | [33162048,33061987] |
| ???????????????????????????? | 3 | [36177882,36489581,36633928] |
| ???????????????????????????????? | 5 | [28277776,26501398,34237464,29655875,29774585] |
| ???????????????????????????????????? | 2 | [36874166,36135233] |
| ???????????????????????????????????????????? | 3 | [36932906,33559373,34844422] |
But all of these users have different usernames (although they all use unusual characters):
select *
from enwiki.actor
where actor_user in (
7971454, 7630487, 29548314, 169932,
1241395, 14754180, 16163829, 36027965,
36919251, 37020397, 28526886, 28303085,
30943519, 30775112, 32487125, 30943557,
31584194, 29929605, 30943142, 32547336,
31767650, 29548814, 31082738, 30747253,
30989504, 32838619, 35052220, 35310020,
13080937, 555876, 7977532, 19937304,
1481183, 29076045, 1956565, 34551626,
31469439, 30293052, 33162048, 33061987,
36489581, 36633928, 28277776, 26501398,
34237464, 29655875, 29774585, 36874166,
36135233, 36932906, 33559373, 34844422
)| actor_id | actor_user | actor_name |
|---|---|---|
| 784003 | 169932 | ๐ |
| 1755400 | 555876 | ???????? |
| 2438761 | 1241395 | ๏ฟฝ |
| 2678423 | 1481183 | ???????????? |
| 3153421 | 1956565 | ???????????????????? |
| 8825347 | 7630487 | ๐ฅฃ |
| 9165658 | 7971454 | ๐กด |
| 9171734 | 7977532 | ๐ชฅ๐ชฅ |
| 14276353 | 13080937 | ๐ ป๐ชฅ |
| 15949544 | 14754180 | ๐ฅ |
| 17359126 | 16163829 | ๐ |
| 21120621 | 19937304 | ๐บ๐ธ |
| 27660058 | 26501398 | ๐ผ๐ฐ๐ป๐พ๐ฐ๐๐ฝ๐น |
| 29413874 | 28277776 | ๐๐๐๐ณ๐ช๐ฟ๐๐ |
| 29438626 | 28303085 | ๐ |
| 29657439 | 28526886 | ๐ |
| 30191839 | 29076045 | ๐จ๐จจ๐จช |
| 240508 | 29548314 | ๐ฑ |
| 30655423 | 29548814 | ๐ค |
| 30760497 | 29655875 | ๐ฎ๐๐๐๐๐๐๐ |
| 30877169 | 29774585 | ๐๐จ๐ฐ๐๐ฅ๐ฌ๐ค๐ข |
| 31029895 | 29929605 | ๐ผ |
| 31388832 | 30293052 | ๐น๐๐๐๐๐ |
| 31835096 | 30747253 | ๐ค |
| 31862445 | 30775112 | ๐ฃ |
| 32026961 | 30943142 | ๐ |
| 32027334 | 30943519 | ๐ |
| 32027372 | 30943557 | ๐ |
| 32072325 | 30989504 | ๐ |
| 32163688 | 31082738 | ๐ฉ |
| 32544016 | 31469439 | ๐ด๐น๐๐ท๐๐ |
| 32657206 | 31584194 | ๐ |
| 32837819 | 31767650 | ๐ |
| 33546876 | 32487125 | ๐ |
| 33606170 | 32547336 | ๐ |
| 33893522 | 32838619 | ๐ฅ |
| 34113533 | 33061987 | ๐ฒ๐บ๐ท๐ฏ๐ฐ๐น ๐บ๐ธ๐ป๐ป๐ด๐ |
| 34211980 | 33162048 | ๐๐ฆ๐๐๐๐ ๐๐๐๐๐ค๐ |
| 34603368 | 33559373 | ๐๐ฝ๐ผ๐๐ฒ๐พ๐๐ฒ๐ฝ๐ผ๐พ |
| 35272596 | 34237464 | ๐ฎ๐๐ป๐๐๐๐๐ |
| 35583696 | 34551626 | ๐ฝ๐๐๐๐ |
| 35873206 | 34844422 | ๐๐๐๐๐๐๐๐๐๐๐ |
| 36077826 | 35052220 | ๐ |
| 36331644 | 35310020 | ๐ฉน |
| 190083556 | 36027965 | ๐ฆ |
| 190539644 | 36135233 | ๐ฏ๐๐๐๐๐ ๐๐ก๐ |
| 192294629 | 36489581 | ๐๐๐๐๐๐๐ |
| 192988969 | 36633928 | ๐ฒ๐๐๐๐๐ธ๐ |
| 194101412 | 36874166 | ๐๐ฒ๐ท๐พ๐๐ฒ๐ท๐พ๐ผ |
| 194315971 | 36919251 | ๐ |
| 194384446 | 36932906 | ๐ค๐๐ญ๐ข๐๐๐๐๐ข๐๐ณ |
| 194803968 | 37020397 | ๐ฆ |
This propagates into mediawiki_history too. The following query returns only null values for event_user_text:
select event_user_text
from wmf.mediawiki_history
where
event_user_id in (
7971454, 7630487, 29548314, 169932,
1241395, 14754180, 16163829, 36027965,
36919251, 37020397, 28526886, 28303085,
30943519, 30775112, 32487125, 30943557,
31584194, 29929605, 30943142, 32547336,
31767650, 29548814, 31082738, 30747253,
30989504, 32838619, 35052220, 35310020,
13080937, 555876, 7977532, 19937304,
1481183, 29076045, 1956565, 34551626,
31469439, 30293052, 33162048, 33061987,
36489581, 36633928, 28277776, 26501398,
34237464, 29655875, 29774585, 36874166,
36135233, 36932906, 33559373, 34844422
) and
wiki_db = "enwiki" and
snapshot = "2019-07"
limit 20