Archives mensuelles : août 2012

Tip MySQL: restaurer une table à partir d’une sauvegarde complète

par

En tant qu’administrateur de base de données MySQL, vous avez surement mis en place une  politique de sauvegarde des bases de données qui permet de sauvegarder toutes les bases installées.
Pour ma part j’utilise automysqlbackup qui permet de faire une rotation des bases de données.
Or ce logiciel sauvegarde l’ensemble des bases dans un seul et même fichier.

Imaginons maintenant qu’un développeur vienne vous dire qu’il a détruit les données d’une table et qu’il faudrait restaurer uniquement cette table à  partir des données de la veille.

La première chose que vous faites et de croiser les doigts en allant regarder dans le répertoire de sauvegarde s’il y a bien un dump des bases.
Ensuite, vous essayez de localiser la table en question avec votre éditeur préféré pour faire un copier coller dans l’IHM MySQL Workbench.
Problème:  le fichier de dump fait 20 Go!
même vi a du mal à  ouvrir le fichier…
Vous devez donc vous pencher sur les capacité du programme sed dont voici une bonne introduction.

L’explication suivante s’appuie sur un exemple précis:
 restaurer la table comments qui appartient à  la base de donées facebook.

Identification des patterns

Si on regarde de plus près le type de sauvegarde qu’automysqlbackup effectue ( grâce au programme mysqldump), on remarque plusieurs choses :

  • avant  chaque création de base de données on retrouve un commentaire.Exemple : 

—
— Current Database: `facebook`
—
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `facebook` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `facebook`;

  • pour chaque table , la table est détruite ( commande DROP)

DROP TABLE IF EXISTS `comment`;

  • la table est crée, verrouillée, les données sont insérées puis enfin la table est déverrouillée : 

UNLOCK TABLES;

On peut donc en déduire une stratégie :

  1. d’abord extraire la base de données cherchée et la sauvegarder dans un fichier
  2. ensuite extraire la table  cherchée et la sauvegarder dans un fichier

Extraction de la Base  de données 

On va donc utiliser le pattern « Current Database » pour  extraire uniquement la base de données facebook:

sed -n -e ‘/Current Database: .facebook/,/Current Database/p’ mon_fichier_de_dump.sql > facebook.sql

cette commande va extraire à  partir du fichier mon_fichier_de_dump.sql tout ce qui se trouve entre le texte « Current Database: `facebook` » et le prochain texte « Current Database: »  et sauvegarder dans le fichier facebook.sql.

l’option -n avec l’option /p  permet à  sed de n’écrire que les lignes comprises entre les deux patterns.
l’option -e

Extraction de la table

Une fois que la base de données est extraite, on peut maintenant extraire la table comment :

sed -n -e ‘/DROP TABLE.*`comment`/,/UNLOCK TABLES/p’ facebook.sql > comment.sql

Restauration de la table

 Il suffit ensuite de restaurer la table comments dans votre serveur de base de données avec la commande mysql:

mysql -h myserver.mynetwork.com  -u root -pmyPassword -D facebook< facebook.sql

Evidemment, vous pourrez modifier

Tip SQL/MDX: chercher le top X pour chaque groupe

par

Une des demandes les plus récurrentes, lorsque l’on fait des tableaux de bord c’est de retrouver les « 10 meilleurs clients par zone commerciale », ou encore les « 10 plus gros contributeurs facebook par page » .

Aujourd’hui je vous propose 3 façons de résoudre cette problématique en SQL, selon le système  utilisé.
Imaginons la table suivante:

Create   TABLE Contribs (id_page int, page varchar(50), fan varchar(50) , contribs INT);

INSERT INTO Contribs VALUES (2, ‘françois Hollande’, ‘toto’ , 65);
INSERT INTO Contribs VALUES (2,’françois Hollande’, ‘titi’ , 50);
INSERT INTO Contribs VALUES  (2,’françois Hollande’, ‘tutu’ , 30);
INSERT INTO Contribs VALUES (2,’françois Hollande’, ‘tata’ , 20);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘toto’ , 5);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘titi’ , 41);
INSERT INTO Contribs VALUES   (8,’Nicolas Sarkozy’, ‘tutu’ , 35);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘tata’ , 66);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘tete’ , 67);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘toto’ , 35);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘tata’ , 15);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘titi’ , 5);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘tutu’ , 3);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘zozo’ , 2);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zizi’ , 69);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zaza’ , 100);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zuzu’ , 1230);

Cette table correspond à la liste des fans ( champ fan) de pages page facebook avec la liste

Sql Server:

Bon là c’est tellement simple que s’en est bête… on va utiliser la fonction RANK, introduite avec la version 2005
la fonction RANK() , permet de calculer un auto-incrément lors d’une requête.
Par exemple:

select *   from  ( select page, fan, contribs, RANK() over ( Partition by page order by contribs DESC)  as Rank
from  Contribs ) as TEMP
where Rank <=10;

MySql:

Bon, là  on va utiliser une fonctionnalité que j’appellerais « procédurale »de Mysql et non ensembliste.
Je m’explique:
au sein même de la requête, on utilise l’incrémentation au fur et à  mesure que le moteur lit et ordonne les lignes qu’il trouve.
On doit d’abord ordonner les contributeurs  par page:
select page, fans, contribs from Contribs  order by page, contribs DESC;

On introduit l’incrémentation au travers d’une variable : @count.
Dès que le moteur de requête change de  page, il est nécessaire de remettre à  zéro la variable @count.
L’indication du changement de page se fera avec la variable : @oldPage

select  @oldPage := 0 , @count := 0;
SELECT
    @count := IF(id_page <=> @oldPage, @count+1, 0) as mycount,
    @oldPage := id_page,
    page,
    fan,
    contribs
FROM Contribs
ORDER BY id_page, contribs DESC;

Il suffit ensuite de filtrer :

select fan,page, contribs  from (SELECT
    @count := IF(id_page <=> @oldPage, @count+1, 0) as mycount,
    @oldPage := id_page,
    page,
    fan,
    contribs
FROM Contribs
ORDER BY id_page, contribs DESC)  t
where mycount<=10;

ANSI SQL:

On doit toujours revenir aux bases…
Sachant que le langage SQL ne sait pas compter la position dans le résultat, il faut trouver autre chose.
On peut essayer d’imaginer d’extraire pour chaque contribution ( contribs)  le sous-ensemble des contributions supérieures et inférieures.

Pour chaque contribs, on peut extraire le sous ensemble des contribs supérieures:

select C2.contribs, C1.contribs
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs

On veut maintenant n’avoir que les contribs qui n’ont que trois contribs qui lui sont supérieurs. On va compter pour chaque sous ensemble le nombre distinct de contribs  :

select min(C1.contribs)
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs
group by C2.contribs
having count(DISTINCT C1.contribs) <=3;

Avec cette requête, on récupère ainsi les trois plus hautes contribs c’est à  dire 69,100 et 1230

Enfin, on veut faire le regroupement par page :

select min(C1.contribs) , C1.page
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs and C1.id_page = C2.id_page
group by C2.contribs, C1.page
having count(DISTINCT C1.contribs) <=3;

Il ne reste plus qu’à  injecter les noms des contributeurs :

select c.*  from  Contribs c
JOIN
(select min(C1.contribs) as contribs, C1.id_page
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs and C1.id_page = C2.id_page
group by C2.contribs, C1.id_page
having count(DISTINCT C1.contribs) <=3) t
on t.id_page = c.id_page and c.contribs = t.contribs;

Et voilà:  on récupère les trois meilleurs contributeurs par page!!
C’est d’ailleurs la requête la plus élégante et celle qui fonctionnera partout.

MDX:

Alors là, le système est très simple  avec l’instruction TopCount puisqu’il permet de faire le filtre pour chaque sous-ensemble.
Par exemple, on crée un « set » des clients à qui on a le plus vendu sur 2012, puis on peut l’utiliser  en dans une requête MDX:

with set [Top10cust] as ‘TopCount({[Client].[ClientName].Members}, 10.0, ([Time].[2012], [Measures].[sales]))’
member [Measures].[Ventes] as ‘[Measures].[sales]’
select {[Measures].[Ventes]} on COLUMNS,
 {[Top10cust]}  ON ROWS
from [Commercial]