Revisita aos dumps da Wikipédia

October 6th, 2009 ntavares Posted in pt_PT, wikipedia |

Ouvir com webReader

Desta vez em português, decidi dar [alguma] continuidade ao que comecei há uns dias com a importação dos dumps da Wikipédia. Graças à dica do Rei-artur foi fácil extrair a lista de robôs, para excluir das estatísticas.

PLAIN TEXT
CODE:
  1. [myself@speedy ~]# wget 'http://pt.wikipedia.org/w/api.php?action=query&list=allusers&aufrom=A&augroup=bot&aulimit=500&format=txt' -q -O - > bots.tmp
  2.  
  3. [myself@speedy ~]# cat bots.tmp | grep '\[name\]' | sed 's,^.*\[name\] => ,,' > /tmp/bots.txt

Aproveitei e repesquei os user_id para simplificar as pesquisas sem fazer alterações na tabela user.

PLAIN TEXT
MySQL:
  1. mysql> CREATE TABLE user_bots ( bot_name VARCHAR(25) );
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. mysql> LOAD DATA INFILE '/tmp/bots.txt' INTO table user_bots;
  5. Query OK, 136 rows affected (0.00 sec)
  6. Records: 136  Deleted: 0  Skipped: 0  WARNINGS: 0
  7.  
  8.  
  9. mysql> ALTER TABLE user_bots add COLUMN bot_user_id INT;
  10. Query OK, 136 rows affected (0.01 sec)
  11. Records: 136  Duplicates: 0  WARNINGS: 0
  12.  
  13. mysql> ALTER TABLE user add index idx_t ( user_name );
  14. Query OK, 119134 rows affected (2.63 sec)
  15. Records: 119134  Duplicates: 0  WARNINGS: 0
  16.  
  17. mysql> UPDATE user_bots ub JOIN user u on user_name = bot_name SET ub.bot_user_id = u.user_id;
  18. Query OK, 134 rows affected (0.00 sec)
  19. Rows matched: 134  Changed: 134  WARNINGS: 0
  20.  
  21. mysql> ALTER TABLE user_bots add PRIMARY KEY (bot_user_id);
  22. Query OK, 136 rows affected, 1 warning (0.00 sec)
  23. Records: 136  Duplicates: 0  WARNINGS: 1
  24.  
  25. mysql> SHOW WARNINGS;
  26. +---------+------+---------------------------------------------------+
  27. | Level   | Code | Message                                           |
  28. +---------+------+---------------------------------------------------+
  29. | Warning | 1265 | Data truncated for COLUMN 'bot_user_id' at row 71 |
  30. +---------+------+---------------------------------------------------+
  31. 1 row in SET (0.00 sec)
  32.  
  33. mysql> UPDATE user_bots SET bot_user_id = -1 WHERE bot_user_id = 0;
  34. Query OK, 1 row affected (0.00 sec)
  35. Rows matched: 1  Changed: 1  WARNINGS: 0

Não tinha reparado que havia um utilizador/robô com o nome "MediaWiki default" mas, bem, depois de criar a Primary Key ficou com o bot_user_id=0 e, para evitar que coincidisse com o agregado para anonymous, dei-lhe o bot_user_id=-1.

Então agora já estamos prontos a completar a query onde ficámos no último dia (número de edições em artigos distintos em cada namespace por utilizador):

PLAIN TEXT
MySQL:
  1. mysql> EXPLAIN SELECT epn.user_name,epn.page_namespace,epn.edits
  2.     -> FROM edits_per_namespace epn
  3.     -> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
  4.     -> WHERE ub.bot_user_id IS NULL
  5.     -> AND epn.user_id <> 0
  6.     -> ORDER BY edits desc limit 20;
  7. +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
  8. | id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows   | Extra                                |
  9. +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
  10. |  1 | SIMPLE      | epn   | ALL    | NULL          | NULL    | NULL    | NULL                 | 187624 | USING WHERE; USING filesort          |
  11. |  1 | SIMPLE      | ub    | eq_ref | PRIMARY       | PRIMARY | 4       | ntavares.epn.user_id |      1 | USING WHERE; USING index; NOT EXISTS |
  12. +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
  13. 2 rows in SET (0.00 sec)
  14.  
  15. mysql> SELECT epn.user_name,epn.page_namespace,epn.edits
  16.     -> FROM edits_per_namespace epn
  17.     -> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
  18.     -> WHERE ub.bot_user_id IS NULL
  19.     -> AND epn.user_id <> 0
  20.     -> ORDER BY edits desc limit 10;
  21. +----------------+----------------+-------+
  22. | user_name      | page_namespace | edits |
  23. +----------------+----------------+-------+
  24. | EMP,Nice poa   |              0 | 58138 |
  25. | Dantadd        |              0 | 44767 |
  26. | João Carvalho  |              3 | 44533 |
  27. | OS2Warp        |              0 | 43396 |
  28. | Yanguas,Sonlui |              0 | 37020 |
  29. | Lijealso       |              0 | 34157 |
  30. | Rei-artur      |              0 | 33863 |
  31. | Tumnus         |              3 | 33213 |
  32. | Nuno Tavares   |              0 | 31910 |
  33. | Bisbis         |              0 | 29886 |
  34. +----------------+----------------+-------+
  35. 10 rows in SET (0.76 sec)

Os resultados completos estão aqui.

Já agora, para finalizar, a tão afamada lista de wikipedistas por número de edições:

PLAIN TEXT
MySQL:
  1. mysql> CREATE TABLE edits_per_user SELECT rev_user,count(1) as counter FROM revision GROUP BY rev_user;
  2. Query OK, 119134 rows affected (12.61 sec)
  3. Records: 119134  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> SELECT u.user_name,epu.counter
  6.     -> FROM edits_per_user epu
  7.     -> LEFT JOIN user_bots ub on ub.bot_user_id = epu.rev_user
  8.     -> JOIN user u on epu.rev_user = u.user_id
  9.     -> WHERE ub.bot_user_id IS NULL ORDER BY counter desc limit 10;
  10. +----------------+---------+
  11. | user_name      | counter |
  12. +----------------+---------+
  13. | anonymous      | 3119758 |
  14. | EMP,Nice poa   |  176338 |
  15. | OS2Warp        |  163751 |
  16. | Dantadd        |  105657 |
  17. | Lijealso       |   90025 |
  18. | Yanguas,Sonlui |   89152 |
  19. | Rei-artur      |   83662 |
  20. | Mschlindwein   |   75680 |
  21. | Bisbis         |   75361 |
  22. | Nuno Tavares   |   73141 |
  23. +----------------+---------+
  24. 10 rows in SET (0.05 sec)

Os resultados completos estão aqui.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

One Response to “Revisita aos dumps da Wikipédia”

  1. GPS Humano » Blog Archive » Actualização das páginas órfãs Says:
    October 18th, 2009 at 4:45 am

    [...] que o dump utilizado anteriormente era insuficiente, pelo que se descarregou a tabela pagelinks, desta vez do dump de [...]

Leave a Reply

« Revisita aos dados estruturados
Actualização das páginas órfãs »