Wikiproyecto:Wikidemia/Cómo procesar los dumps SQL
Hay dumps que están en SQL, como por ejemplo el registro de eventos (borrados, bloqueos, uploads...). En este tutorial veremos cómo cargarlos dentro de una base de datos SQL y obtener datos. Se recomienda hacer algún tutorial de SQL. La idea de este tutorial es hacer perder el miedo al que nunca haya usado SQL, porque es más fácil de lo que uno se imagina.
En este tutorial usaremos el dump de eventos de eswikibooks http://download.wikimedia.org/eswikibooks/20090124/ y el sistema de gestión mySQL.
Ejemplo 1: Extraer la lista de los 30 más bloqueados y los más bloqueadores editar
Nos bajamos el dump, lo descomprimimos, y entramos en mySQL. Allí creamos una base de datos:
mysql> CREATE DATABASE eswikibooks;
Query OK, 1 row affected (0.17 sec)
La seleccionamos y cargamos el dump:
mysql> USE eswikibooks
Database changed
mysql> source eswikibooks-20090124-logging.sql
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 5930 rows affected (1.52 sec)
Records: 5930 Duplicates: 0 Warnings: 0
Query OK, 9052 rows affected (0.59 sec)
Records: 9052 Duplicates: 0 Warnings: 0
Query OK, 8897 rows affected (1.87 sec)
Records: 8897 Duplicates: 0 Warnings: 0
Query OK, 3795 rows affected (0.61 sec)
Records: 3795 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Ya tenemos la tabla cargada, ahora podemos acceder a ella. La tabla se llama "logging" y tiene las siguientes columnas:
mysql> SELECT * FROM `logging` WHERE 1 LIMIT 0 , 10;
+--------+----------+------------+----------------+----------+---------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------+
| log_id | log_type | log_action | log_timestamp | log_user | log_namespace | log_title | log_comment | log_params | log_deleted |
+--------+----------+------------+----------------+----------+---------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------+
| 1 | delete | delete | 20041221181355 | 12 | 10 | Vocabulario_1-7_(es) | contenido era: '=HERRAMIENTAS=[[Archivo:Plamuurmes.jpg|thumb|200px|right|PALETAS]][[Archivo:Ijzerzaag.jpg|thumb|200px|center|SERRUCHO]][[Image:Gereedschapskist.jpg|th...' | | 0 |
| 2 | delete | delete | 20041221182603 | 12 | 10 | Vocabulario/12-22 | contenido era: '[[Archivo:Socks.jpg|{{lang=es}}]]' | | 0 |
| 3 | delete | delete | 20041222130206 | 12 | 10 | Vocabulario/Day | innecesario, borrado por autor | | 0 |
| 4 | delete | delete | 20041222135758 | 12 | 10 | Vocabulario/12-21_(es) | contenido era: 'PLAYERA.' | | 0 |
| 5 | delete | delete | 20041222135922 | 12 | 10 | Vocabulario/12-21(es) | contenido era: '#REDIRECT [[Plantilla:Vocabulario/12-21 (es)]]' | | 0 |
| 6 | delete | delete | 20041222140817 | 12 | 10 | Vocabulario/2004-12 | contenido era: '#REDIRECT [[Plantilla:Vocabulario/12-21]]' | | 0 |
| 7 | delete | delete | 20041222140831 | 12 | 10 | Vocabulario/12-21 | contenido era: '#REDIRECT [[Plantilla:Vocabulario/12-21(es)]]' | | 0 |
| 8 | delete | delete | 20041222141027 | 12 | 10 | Vocabulario | contenido era: '{| style='margin: 0 0 1em 1em; float: {{{float}}}; width: {{{width}}}px; text-align: center;' id='toc'! {{Vocabulario/name/{{{lang}}}}}|-|id='toc' ...' | | 0 |
| 9 | delete | delete | 20041222141222 | 12 | 10 | Vocabulario_1-9/(es) | contenido era: '#REDIRECT [[Plantilla:Vocabulario/1-9 (es)]]' | | 0 |
| 10 | delete | delete | 20041222142231 | 12 | 10 | Vocabulario:Day | contenido era: '#REDIRECT [[Plantilla:Vocabulario/Day]]' | | 0 |
+--------+----------+------------+----------------+----------+---------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------+
La sintaxis es sencilla. Significa "seleccionar todo de la tabla logging"
Empezamos por obtener la lista de bloqueados:
mysql> SELECT `log_title` FROM `logging` WHERE `log_action` = "block";
Es decir, obtener el campo log_title de la tabla logging cuando el valor de la columna log_action es block
Mejor mostramos solo 10 resultados:
SELECT `log_title` FROM `logging` WHERE `log_action` = "block" LIMIT 0, 10;
+----------------+
| log_title |
+----------------+
| 81.214.96.35 |
| 85.54.245.246 |
| 85.54.245.246 |
| 210.118.75.189 |
| 59.20.74.64 |
| 61.84.119.237 |
| 58.231.51.74 |
| 200.86.66.100 |
| 200.55.216.130 |
| Jamrojo |
+----------------+
10 rows in set (0.00 sec)
Podemos ordenarla alfabéticamente por nombre de usuario bloqueado:
mysql> SELECT `log_title` FROM `logging` WHERE `log_action` = "block" ORDER BY `logging`.`log_title` ASC LIMIT 0, 10;
+-----------------+
| log_title |
+-----------------+
| 121.44.79.159 |
| 123.190.193.8 |
| 123.232.9.185 |
| 123.49.44.10 |
| 125.109.49.173 |
| 125.109.62.189 |
| 125.163.220.117 |
| 125.19.185.212 |
| 125.19.185.212 |
| 125.244.10.130 |
+-----------------+
10 rows in set (0.05 sec)
Podemos contar las veces que ha sido bloqueado cada usuario:
mysql> SELECT `log_title` , COUNT( * ) AS NumOccurrences
FROM `logging`
WHERE `log_action` = "block"
GROUP BY log_title LIMIT 0, 10;
+-----------------+----------------+
| log_title | NumOccurrences |
+-----------------+----------------+
| 121.44.79.159 | 1 |
| 123.190.193.8 | 1 |
| 123.232.9.185 | 1 |
| 123.49.44.10 | 1 |
| 125.109.49.173 | 1 |
| 125.109.62.189 | 1 |
| 125.163.220.117 | 1 |
| 125.19.185.212 | 2 |
| 125.244.10.130 | 1 |
| 125.244.91.2 | 1 |
+-----------------+----------------+
10 rows in set (0.02 sec)
Y finalmente podemos ordenar estos valores en orden descendiente para ver quiénes son los 30 más bloqueados:
mysql> SELECT `log_title` , COUNT( * ) AS NumOccurrences
FROM `logging`
WHERE `log_action` = "block"
GROUP BY log_title
ORDER BY `NumOccurrences` DESC
LIMIT 0 , 30
+-----------------------------------------------------------------+----------------+
| log_title | NumOccurrences |
+-----------------------------------------------------------------+----------------+
| 125.19.185.212 | 2 |
| 85.54.245.246 | 2 |
| 69.79.240.86 | 2 |
| 83.220.139.134 | 1 |
| 200.172.79.174 | 1 |
| 200.127.253.78 | 1 |
...
Parece que hay pocos bloqueados. Estos datos son un poco aburridos, veamos quiénes son los usuarios más bloqueadores.
SELECT `log_user` , COUNT( * ) AS NumOccurrences FROM `logging` WHERE `log_action` = "block" GROUP BY log_user ORDER BY `NumOccurrences` DESC;
+----------+----------------+
| log_user | NumOccurrences |
+----------+----------------+
| 5677 | 225 |
| 1651 | 20 |
| 14 | 16 |
| 12 | 9 |
| 1687 | 8 |
| 9929 | 2 |
| 186 | 2 |
| 651 | 2 |
| 2754 | 2 |
| 9856 | 1 |
+----------+----------------+
10 rows in set (0.11 sec)
Esto ya es más interesante. ¿Quién será el usuario 5677? Como tenemos el registro de creación de usuarios, lo consultamos:
mysql> SELECT log_title
FROM `logging`
WHERE log_user= 5677 AND log_type= "newusers";
+-----------+
| log_title |
+-----------+
| Drini |
+-----------+
1 row in set (0.00 sec)
Desgraciadamente, los registros antiguos no están disponibles, por eso las creaciones de usuarios con ids bajas no están registradas. Ahora podríamos intentar unir las dos consultas en una, pero desgraciadamente para el que ha escrito este tutorial le resultaría más cómodo hacer varias consultas simples como la anterior con un script que pensar en cómo sería el comando SQL que haría esto ;)