Classer donnée TXT dans base Mysql

**MRJBGO** · 06/11/2011, 10h32

Bonjour à tous !!

Je fais appelle à vous pour réaliser un projet que j'ai !

Je vous explique, je veux créé des tableaux de statistiques à partir de fichier qui se trouve sur une url fixes

http://wiki.fr.grepolis.com/wiki/Donn%C3%A9es_mondes

Comme par exemple : http://fr1.grepolis.com/data/players.txt.gz

Je voudrais en fait que toute les heures, ce fichier (pour commencer) soit téléchargé, décompressé et classé dans une base Mysql de façon à pourvoir insérer les données dans un tableau

J'espère avoir été clair

et que quelqu'un puisse m'aiguiller

**rawsrc** · 06/11/2011, 11h21

Bonjour,

Pour effectuer une action à intervalles réguliers, il faut programmer ton système d'exploitation pour qu'il déclenche l'éxécution automatiquement : LINUX -> Cron Job - WINDOWS -> Scheduled Tasks.

**MRJBGO** · 06/11/2011, 13h35

Bonjour,

Pour effectuer une action à intervalles réguliers, il faut programmer ton système d'exploitation pour qu'il déclenche l'éxécution automatiquement : LINUX -> Cron Job - WINDOWS -> Scheduled Tasks.

Je pensais mettre sa sur un serveur externe avec base mysql ... n'y -t-il pas un script ou quelque chose pour qu'il fasse l'action automatiquement ?

**rawsrc** · 06/11/2011, 13h50

Envoyé par MRJBGO

n'y -t-il pas un script ou quelque chose pour qu'il fasse l'action automatiquement ?

Non y'a rien, ta tâche récurrente doit juste appeler ton script php de traitement à intervalles réguliers. Tu trouveras des tonnes d'exemples sur le web.

**MRJBGO** · 06/11/2011, 14h26

D'accord ! Très bien ! Maintenant ... vif du sujet ... la façon de créer ce script ^^

**Celira** · 07/11/2011, 13h43

Un script PHP pour tâche récurrente, c'est comme un script PHP classique, à ceci près qu'au lieu de le lancer via un navigateur, c'est la tâche planifiée qui va le lancer.
Donc il faut que tu écrives un script PHP qui fait ce qui tu veux que ta tâche fasse.

Tu pourras le tester en lançant en ligne de commande ou simplement en l'appelant dans ton navigateur comme n'importe quel autre script php

**ticad** · 07/11/2011, 16h27

Tu peux faire un script de remplissage de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
//================================
//   Recherche du fichier
//================================
 
$Fnm = 'fichier.txt'; 
if (file_exists($Fnm))	
{ 	
	$tableau = file($Fnm); 	
}
else 	
{	
	echo 'fichier introuvable';	
}
//================================
//	Connexion à la base
//================================
$db = mysql_connect('hostname', 'user', 'mdp '); 
mysql_select_db('database',$db); 
 
//================================
//  Traitement du fichier
//================================
 
//Ici par exemple les données sont séparées par des points virgules.
 
 while (list($cle,$val) = each($tableau))
	 {
		 @list($var1,$var2)=explode(";", $val);  //on sépare chaque donnée
 
		mysql_query("INSERT INTO tatable(VAR1,VAR2) VALUES (\"$var1\",\"$var2\")"); //Insertion dans la base.
 
	 }
 
 
mysql_close();

Et ensuite via un .bat tu appelles ton script

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
cd "C:\wamp\bin\php\php5.3.0\"
php.exe "C:\wamp\www\tonscript.php"

**KstorTroy** · 08/11/2011, 02h29

Je travaille actuellement sur une map Grepo, c'est ton jour de chance:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
include('connection.php'); 
ini_set("memory_limit", "12000M"); // augmentation memoire
set_time_limit(0);  // illimite dans le  temps exé du script
$req = mysql_query("TRUNCATE TABLE towns");
$datafile = file('towns.txt'); // absortion des données
if(!is_array($datafile)) die("File could not be opened");
 
foreach($datafile as $datum){
 
	list($id, $player_id, $name, $island_x, $island_y, $number_on_island, $point) = explode(',', $datum);
	include ('connection.php');
	$sql = "INSERT INTO towns (id, player_id, name, island_x, island_y, number_on_island, point) VALUES('$id', '$player_id', '$name', '$island_x', '$island_y','$number_on_island','$point')";
	mysql_query($sql) or die('la base island n\'a pas ete mise a jour '.mysql_error());
 
 
}echo 'La table towns a ete mise à jour:';

Le script est lourd et peut comporter jusqu'à 120.000 lignes alors j'ai ajouté

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ini_set("memory_limit", "12000M"); // augmentation memoire
set_time_limit(0);  // illimite dans le  temps exé du script

la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

$req = mysql_query("TRUNCATE TABLE towns");

est dangereuse car elle efface la table pour pouvoir la mettre à jour. Soit sur que le script s'exécute jusqu'à la fin si tu le lances.

Petit conseil maintenant:
Je te déconseille de travailler avec Mysql car des tables avec 120 000 entrées et des requêtes avec jointures, ca va être lent très lent !
Alors si tu travaille sur un site de stat je te conseilles de garder les fichiers .txt et de travailler dessus. Comme ça en plus tu peux garder un historique et créer des graphiques d'évolution sans devoir créer 400 tables. et pour mettre à jour tu as juste 10 fichiers à dl car l'incorporation des fichiers dans la base prend près de 2h à full 100% serveur (dual core 2.4).
Pour exemple une requête qui verifie qu'une ile est habitée en comparant la table towns et islands m'a pris la matinée et elle s'est jamais terminée alors que ça a pris 2min en conservant les .txt

Si tu veux que le script s'exécute à intervalle régulier, RDV dans le menu 'Cron' de ton hébergeur

Tu peux me contacter IG si tu veux ! Meme pseudo sur Upsilon !

**rawsrc** · 08/11/2011, 10h10

Envoyé par KstorTroy

Petit conseil maintenant:
Je te déconseille de travailler avec Mysql car des tables avec 120 000 entrées et des requêtes avec jointures, ca va être lent très lent !
Alors si tu travaille sur un site de stat je te conseilles de garder les fichiers .txt et de travailler dessus.

Tu es sûr de toi ? Parce que 120 000 entrées pour MySql c'est de la franche rigolade. A moins que tu t'y sois pris comme un pied pour monter tes tables, je ne vois pas comment tu peux faire ramer MySql avec si peu de données.
J'ai utilisé MySql avec des tables de plusieures dizaines de millions d'enregistrements, jointures et tout ce qui va avec et je peux t'assurer que c'est indolore.

Ensuite tu gagnerais en vitesse si tu n'incluais pas le fichier connection.php à chaque boucle dans le foreach.
Et puis ton histoire qui t'évite de devoir créer 400 tables pour des graphiques, je suis dubitatif. C'est clair qu'avec un système organisé comme cela semble être le cas, tu m'étonnes qua ça rame.

**Celira** · 08/11/2011, 13h20

Envoyé par rawsrc

Ensuite tu gagnerais en vitesse si tu n'incluais pas le fichier connection.php à chaque boucle dans le foreach..

Sur ce point, ça dépend du contenu du fichier connection.php. Si tu définis des fonctions dedans, ça va faire pire que bouffer du temps et de la ressource, ça va planter pour cause de redéfinition de fonction existante !
Si connection.php se contente de faire une connexion à la base de données (genre mysql_connect), inutile de le faire n fois : une connexion à la base de données reste ouverte tant qu'elle n'est pas explicitement fermée (ou jusqu'à la fin du script si la connexion est non persistante).

**KstorTroy** · 08/11/2011, 17h44

Oui, en effet, je l'ai sortit de la boucle mais j'ai oublié de l'effacer dans la boucle ! :s Je corrige ça immédiatement !

T'as jamais oublié une ligne, un point virgule ou un dollars ???
T'es pas obligé d...

Un Update de 120.000 entrées et non pas un bête insert ou select.
Il faut updater les 120k entrées et non pas updater 1 entrée dans une table de 120k.
Rien que l'insertion prend quelques minutes.
l'update dans une boucle évidemment puisqu'elle va chercher les données dans un .txt va prendre les données une à une pour les comparer une à une dans la bdd ca doit faire un peu près 120k.120k comparaisons et réédition.

Au lieu de faire un match dans 2 chaines issues de 2 fichiers .txt.... :s
Surtout s'il veut exécuter les scripts de màj toutes les heures !

OMG 500.000 lignes à inserer, updater ou effacer toutes les heures ! (bah ouais, y'a 10 fichiers)
Sans compter les requêtes nécéssaires aux visiteurs.

Et en plus ca simplifie la mise à jour car il n'y'a qu'à telecharger 10 fichiers.

Mtnt peut être que j'me trompes mais c'est pas à cause du mysqlconnect dans la boucle car la requête update avec la jointure dépasse le temps impartit quand je l'exécute dans phpmyadmin.

Si quelqu'un à une solution pour modifier un champ sur chaque ligne des 120.000 lignes de la table 'islands' en fonction d'une valeur trouvée dans la table 'towns', et ce en moins de 30sec histoire que ce soit de la rigolade pour le serveur, je suis preneur.
http://fr1.grepolis.com/data/islands.txt.gz
http://fr1.grepolis.com/data/towns.txt.gz

**MRJBGO** · 09/11/2011, 00h00

C'est vraiment gentil ! Merci de votre aide ! Je teste ça demain ! Je suis vraiment à fond dans ce projet

KstorTroy : excellent que tu sois sur Grepo ! Je vois que tu es sur le forum de Grepolis, tu dois me connaitre ^^ Je contacterais !

**rawsrc** · 09/11/2011, 00h21

Envoyé par KstorTroy

Un Update de 120.000 entrées et non pas un bête insert ou select.
Il faut updater les 120k entrées et non pas updater 1 entrée dans une table de 120k.
Rien que l'insertion prend quelques minutes.
l'update dans une boucle évidemment puisqu'elle va chercher les données dans un .txt va prendre les données une à une pour les comparer une à une dans la bdd ca doit faire un peu près 120k.120k comparaisons et réédition.

Bon accélérer tout ça je t'invite à jeter un coup d'oeil à LOAD DATA INFILE
La 1ère phrase est

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

Ensuite une fois que tu as tes données dans une table avec les index qui vont bien, tu traites tes mises à jour de manière ensembliste. J'ai regardé tes fichiers et cela doit être assez aisément faisable.
En plus si la structure de tes fichiers ne change pas, il te suffira de purger tes tables d'import à la fin de tes traitements et cela restera réutilisable lors du prochain jeu de données à injecter. Tu peux également purger tes données dans des tables "history".
Et au pire tu crées une procédure stockée qui profite des optimisations internes du moteur MySql à l'opposé d'une séquence de requêtes SQL envoyées depuis PHP.
Et en terme de performances, tu devrais voir une sacrée différence.
@plus

**KstorTroy** · 09/11/2011, 16h01

Merci, ça à l'air magique vu comme ça. Je connaissais pas.
Je teste ça immédiatement !

Envoyé par rawsrc

En plus si la structure de tes fichiers ne change pas, il te suffira de purger tes tables d'import à la fin de tes traitements et cela restera réutilisable lors du prochain jeu de données à injecter. Tu peux également purger tes données dans des tables "history".@plus

En effet, elle ne change pas ! Mais ca à l'air vague pour l'instant

+

**MRJBGO** · 09/11/2011, 16h11

Je suis en train de tester le script du haut ! J'ai effectivement testé la fonction cron de l'hébergeur qui marche parfaitement !

Tient moi au courant sur la méthode que tu vas tester

**MRJBGO** · 09/11/2011, 17h22

J'ai créé le fichier connection.php, j'ai testé, ça me remplit ma base avec les bonnes données !!

**KstorTroy** · 11/11/2011, 22h23

Voilà le magikscript :
Great tks à rawsrc qui m'a lancé sur la route...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
include ('connection.php');
 
 
$nom_url_fichiers = array(  
					array(  'Index' => "fichier1", 
							'Nom_Table' => "players",
							'Url_Fichier' => "data_players.txt" 
					),
					array(  'Index' => "fichier2", 
							'Nom_Table' => "islands",
							'Url_Fichier' => "data_islands.txt" 
					),   
					array(  'Index' => "fichier3", 
							'Nom_Table' => "towns",
							'Url_Fichier' => "data_towns.txt" 
					),   
					array(  'Index' => "fichier4", 
							'Nom_Table' => "alliances",
							'Url_Fichier' => "data_alliances.txt"
					),   
					);
 
 
foreach( $nom_url_fichiers as $fichiers ) {	
		$urlfichier = $fichiers['Url_Fichier'];
		$nomtable = $fichiers['Nom_Table'];
		mysql_query("TRUNCATE TABLE $nomtable");
		$sql = "LOAD DATA LOCAL INFILE '$urlfichier' INTO TABLE $nomtable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n'";
		mysql_query($sql) or die('non mis à jour'.mysql_error());
		}

J'ai mis les constantes dans un array 2D car j'ai pas envie que l'index d'un tableau soit utilisé en tant que variable et encore moins s'il s'agit d'une constante.

Il y'a moyen d'optimiser la fonction cf ici mais une bonne partie des optimisations sont inutiles lorsque les données sont importées dans une table vide et le script s'exécute assez rapidement à mon goût.

Je vais mettre ce script dans une autre boucle pour màj les données des xxxx serveurs, ca devrait prendre moins de 60 secondes.
En effet, c'est BEAUCOUP plus rapide.
Que plaisir pour les yeux mais surtout quel soulagement pour le serveur de pouvoir faire CA : ~~set_time_limit(0)~~
Y'avait pas besoin de ceci: ~~ini_set("memory_limit", "12000M")~~ dans le premier script, je l'utilisais dans un autre script pour insérer dans une image les 123291 éléments qui composent la map. J'suis un sérial copy-pasteur mais j'me soigne !

Je ne purgerai pas les données pour les mettre dans des tables history car si je comptes bien, rien que pour les serveurs francais, j'aurai plus de 200 tables.
C'est grave si j'me retrouve avec des tables de 1.000.000 d'entrées ?
Sinon, j'éfface: mysql_query("TRUNCATE TABLE $nomtable")

Aucune notion de 'procédures stockées' avec mysql, je vais travailler dessus maintenant.

Toujours pas de solution pour modifier rapidement les 120.000 entrées d'une table. Je vais voir si les procédures stockées vont remédier à ça.

.

**rawsrc** · 12/11/2011, 13h50

Envoyé par KstorTroy

Toujours pas de solution pour modifier rapidement les 120.000 entrées d'une table. Je vais voir si les procédures stockées vont remédier à ça.

.

Quelles sont exactement ces modifications ?
Je ne t'en voudrai pas si tu es précis.

**KstorTroy** · 12/11/2011, 15h43

J'ai trouvé une solution alternative pour modifier les 120.000 entrées car je n'ai besoin de faire qu'une fois cette opération pour mettre au point la map.

Mais j'aimerais modifier 27.000 entrées.
Il y'a 2 colonnes 'island_x' et 'island_y' dans la table 'towns' ce n'est pas la position de la ville mais la position de l'île sur laquelle se trouve la ville.

Hors ces positions sont déjà dans la table 'islands'.
La position réelle d'une ville sur la map est trouvée grâce à la position de l'île, grâce à la position de la ville sur l'île mais aussi grâce au type d'île.

Je dois donc pour positionner une ville sur la map aller chercher 2 informations dans la table 'islands'.
En comparant les colonnes 'island_x' et 'island_y' de la table towns et les colonnes 'x_pos' et 'y_pos' de la table islands'.

En bref je dois remplacer les colonnes 'island_x' et 'island_y' par une colonne 'island_id' dans la table 'towns' pour faciliter la jointure.

Et j'ai besoin que cette opération soit performante parc'qu'elle sera récurrente.

http://forum.grepolis.net/showthread.php?t=5589

**rawsrc** · 12/11/2011, 16h52

Envoyé par KstorTroy

En bref je dois remplacer les colonnes 'island_x' et 'island_y' par une colonne 'island_id' dans la table 'towns' pour faciliter la jointure.

T'embarques pas dans island_id, aucune nécessité, tu poses des index sur chaque champs : t_town.island_x - t_towns.island_y - t_island.x_pos - t_island.y_pos et ensuite tu fais une simple jointure dans ton SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
  `t_grep_town`.`id`,
  `t_grep_town`.`player`,
  `t_grep_town`.`name`,
  `t_grep_town`.`island_x`,
  `t_grep_town`.`island_y`,
  `t_grep_town`.`number`,
  `t_grep_town`.`points`,
  `t_grep_island`.`island_type`
FROM
  `t_grep_town`
  INNER JOIN `t_grep_island` ON (`t_grep_town`.`island_x` = `t_grep_island`.`x_pos`)
  AND (`t_grep_town`.`island_y` = `t_grep_island`.`y_pos`)

Tu dois poser un index sur la paire (t_island.x_pos, t_island.y_pos) juste pour être sûr que cette paire est unique.
J'ai fait un test sur les fichiers mis à disposition sur le site et le temps de traitement de la requête ci-dessus est de 265 ms sur une petite config donc sur un serveur cela devrait être sacrément plus court (je récupère tout le jeu de données)

Classer donnée TXT dans base Mysql [MySQL]

PHP & Base de données

Discussions similaires

Partager

Partager