Discussion :

[nrpfc]

Bonjours,

j'ai bien suivis vos conseil et avec l'aide du forum sur les MCD et les schéma, j'ai reorganisé ma base de donnée.

Donc je me retrouve avec une belle base bien pensée (image 1), j'ai remplis toute les tables en dehors de "relever_mesure".

JE vous explique vite fait mon probleme, novice sur MySQL j'ai pour projet de stage de monter une base de donnée a partir de fichier *.csv (extrait d'un de ces fichiers joint "janvier_2012.zip").

Il faut que je recupere les infos pour les reinjecter dans la ma table "relever_mesure", pour le moment j'arrive a isoler une partie de la table, j'ai un premier soucis quand je veux la reinjecter dans la table "relever_mesure" c'est que je n'ai pas d'id_calendrier dans mes fichiers csv mais une datetime

le second soucis et que j'ai un fichier *.csv qui arrive tout les mois avec toute ces données et il faudrait donc que je crée une procedure pour faciliter la remonté des données car apres mon stage ce sera surement aux chercheurs de remonter eux meme les données.

Mes fichiers csv ont 84 colonnes : station, date_heure_locale et de 0 a 82 ( les 83 capteurs).

Ces informations doivent remontés dans une table « relever_mesure » qui se compose de 4 colonnes : id_calendrier_calendrier, id_station_station, id_capteur_capteur et valeur.

id_station_station correspond à la colonne station
id_capteur_capteur correspond au numéro de colonne capteur (de 0 a 82)
valeur correspond à la colonne capteur
id_calendrier_calendrier correspond à date_heure_locale (mais il va falloir une jonction ou quelques choses dans le genre)

Les fichiers csv que je dois remonter arrivent une fois par mois et font donc 84 colonnes sur 90 000 lignes maximum (en moyenne 84 000).

merci de m'aider a prendre la bonne voix pour écrire ma requête

Si quelqu'un pouvait m'orienter dans la bonne direction ce serait parfait
Merci d'avance

[nrpfc]

alors voila le début de mon raisonnement:

avec cette formule j’extrais les valeurs, la date_heure et la station pour le capteur 0

Code :

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

SELECT `id_station`,`date_heure`,`0` FROM `import`;

après si je veux l’insérer dans ma table « relever_mesure »

Code :

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

1
2
3
INSERT INTO `relever_mesure`(`valeur`, `id_calendrier_calendrier`, `id_station_Stations`, `id_capteur_capteur`) 
SELECT `0`,`id_station`,`date_heure` 
FROM `import`

bon la j'ai un probleme deja les colonnes n'ont pas le meme nom.
la colonne "0" deviens la colonne "valeur",
la colonne "id_station" deviens la colonne "id_station_Stations",
la colonne "id_capteur_capteur" n'existe pas mais dans cette exemple doit avoir pour valeur 0,
et enfin la colonne "date_heure" deviens la colonne "id_calendrier_calendrier" qui n'est pas sur le meme format

donc j'en ai deduit ça a l'air de marcher

Code :

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

1
2
3
INSERT INTO `relever_mesure`(`valeur`, `id_calendrier_calendrier`, `id_station_Stations`,`id_capteur_capteur`) 
select `import`.`0` as `valeur`, `calendrier`.`id_calendrier`, `import`.`code_station`, '0' from `import`  
JOIN `calendrier` on `import`.`date_heure` = `calendrier`.`date_heure_locale`

donc nouveau probleme je ne veux pas que les valeurs (null) soit copier dans ma nouvelle table puisque si j'ai bien compris ce n'est pas utile et ça va la surcharger ...

la je bloque ...

je veux bien qu'on m'éclaire un peu si possible

[CinePhil]

Choses à vérifier :
1) La colonne `calendrier`.`date_heure_locale` est bien de type DATETIME ?
2) La colonne `import`.`date_heure` est-elle aussi de type DATETIME ?
3) Cette dernière contient bien les bonnes valeurs que l'on retrouve dans calendrier ?
4) Y a t-il bien un id_capteur ayant la valeur 0 dans la table des capteurs ?

Tu pourrais poster la structure exacte des tables de la BDD modélisée (résultat de SHOW CREATE TABLE nom_de_la_table avec le texte complet) STP ?

[nrpfc]

je suis bien contant de te retrouver ici

Choses à vérifier :
1) La colonne `calendrier`.`date_heure_locale` est bien de type DATETIME ?
2) La colonne `import`.`date_heure` est-elle aussi de type DATETIME ?
3) Cette dernière contient bien les bonnes valeurs que l'on retrouve dans calendrier ?
4) Y a t-il bien un id_capteur ayant la valeur 0 dans la table des capteurs ?

je repond oui a tes 4 questions et je te joins la structure des tables comme demandé

[CinePhil]

Au lieu de joindre des PDF, tu pouvais te contenter de copier/coller le résultat de la requête dans des balises CODE !

Ajoute un index UNIQUE sur la colonne calendrier.date_heure_locale.
Ajoute un index simple sur la colonne import.id_station et un autre sur la colonne import.date_heure.

[nrpfc]

desolé vieille habitude a moi "pourquoi faire simple quand on peut faire compliqué" que j'essaye de changer

Code :

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

1
2
3
4
5
CREATE TABLE `calendrier` (
 `id_calendrier` int(11) NOT NULL AUTO_INCREMENT,
 `date_heure_locale` datetime DEFAULT NULL,
 PRIMARY KEY (`id_calendrier`)
) ENGINE=InnoDB AUTO_INCREMENT=908063 DEFAULT CHARSET=utf8

Code :

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

CREATE TABLE `capteur` (  `id_capteur` int(11) NOT NULL,  `libelle` varchar(19) DEFAULT NULL,  `unite` varchar(6) DEFAULT NULL,  `courbe_ou_etat` int(11) DEFAULT NULL,  `chaine_protocole` varchar(3) DEFAULT NULL,  `chaine_protocole_ftp` varchar(3) DEFAULT NULL,  `nb_carac_protocole` int(11) DEFAULT NULL,  `gain_defaut` decimal(2,1) DEFAULT NULL,  `offset_defaut` int(11) DEFAULT NULL,  `valeur_proto_hs` int(11) DEFAULT NULL,  `valeur_base_hs` decimal(5,1) DEFAULT NULL,  `valeur_com_hs` int(11) DEFAULT NULL,  `ordre_etat_reseau` int(11) DEFAULT NULL,  `unite_wav` varchar(12) DEFAULT NULL,  `fichier_wav` varchar(12) DEFAULT NULL,  `couleur` int(11) DEFAULT NULL,  PRIMARY KEY (`id_capteur`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE TABLE `import` (
 `id_station` int(2) NOT NULL,
 `date_heure` datetime NOT NULL,
 `0` varchar(6) DEFAULT NULL,
 `1` varchar(6) DEFAULT NULL,
 `2` varchar(6) DEFAULT NULL,
 `3` varchar(6) DEFAULT NULL,
 `4` varchar(6) DEFAULT NULL,
 `5` varchar(6) DEFAULT NULL,
 `6` varchar(6) DEFAULT NULL,
 `7` varchar(6) DEFAULT NULL,
 `8` varchar(6) DEFAULT NULL,
 `9` varchar(6) DEFAULT NULL,
 `10` varchar(6) DEFAULT NULL,
 `11` varchar(6) DEFAULT NULL,
 `12` varchar(6) DEFAULT NULL,
 `13` varchar(6) DEFAULT NULL,
 `14` varchar(6) DEFAULT NULL,
 `15` varchar(6) DEFAULT NULL,
 `16` varchar(6) DEFAULT NULL,
 `17` varchar(6) DEFAULT NULL,
 `18` varchar(6) DEFAULT NULL,
 `19` varchar(6) DEFAULT NULL,
 `20` varchar(6) DEFAULT NULL,
 `21` varchar(6) DEFAULT NULL,
 `22` varchar(6) DEFAULT NULL,
 `23` varchar(6) DEFAULT NULL,
 `24` varchar(6) DEFAULT NULL,
 `25` varchar(6) DEFAULT NULL,
 `26` varchar(6) DEFAULT NULL,
 `27` varchar(6) DEFAULT NULL,
 `28` varchar(6) DEFAULT NULL,
 `29` varchar(6) DEFAULT NULL,
 `30` varchar(6) DEFAULT NULL,
 `31` varchar(6) DEFAULT NULL,
 `32` varchar(6) DEFAULT NULL,
 `33` varchar(6) DEFAULT NULL,
 `34` varchar(6) DEFAULT NULL,
 `35` varchar(6) DEFAULT NULL,
 `36` varchar(6) DEFAULT NULL,
 `37` varchar(6) DEFAULT NULL,
 `38` varchar(6) DEFAULT NULL,
 `39` varchar(6) DEFAULT NULL,
 `40` varchar(6) DEFAULT NULL,
 `41` varchar(6) DEFAULT NULL,
 `42` varchar(6) DEFAULT NULL,
 `43` varchar(6) DEFAULT NULL,
 `44` varchar(6) DEFAULT NULL,
 `45` varchar(6) DEFAULT NULL,
 `46` varchar(6) DEFAULT NULL,
 `47` varchar(6) DEFAULT NULL,
 `48` varchar(6) DEFAULT NULL,
 `49` varchar(6) DEFAULT NULL,
 `50` varchar(6) DEFAULT NULL,
 `51` varchar(6) DEFAULT NULL,
 `52` varchar(6) DEFAULT NULL,
 `53` varchar(6) DEFAULT NULL,
 `54` varchar(6) DEFAULT NULL,
 `55` varchar(6) DEFAULT NULL,
 `56` varchar(6) DEFAULT NULL,
 `57` varchar(6) DEFAULT NULL,
 `58` varchar(6) DEFAULT NULL,
 `59` varchar(6) DEFAULT NULL,
 `60` varchar(6) DEFAULT NULL,
 `61` varchar(6) DEFAULT NULL,
 `62` varchar(6) DEFAULT NULL,
 `63` varchar(6) DEFAULT NULL,
 `64` varchar(6) DEFAULT NULL,
 `65` varchar(6) DEFAULT NULL,
 `66` varchar(6) DEFAULT NULL,
 `67` varchar(6) DEFAULT NULL,
 `68` varchar(6) DEFAULT NULL,
 `69` varchar(6) DEFAULT NULL,
 `70` varchar(6) DEFAULT NULL,
 `71` varchar(6) DEFAULT NULL,
 `72` varchar(6) DEFAULT NULL,
 `73` varchar(6) DEFAULT NULL,
 `74` varchar(6) DEFAULT NULL,
 `75` varchar(6) DEFAULT NULL,
 `76` varchar(6) DEFAULT NULL,
 `77` varchar(6) DEFAULT NULL,
 `78` varchar(6) DEFAULT NULL,
 `79` varchar(6) DEFAULT NULL,
 `80` varchar(6) DEFAULT NULL,
 `81` varchar(6) DEFAULT NULL,
 `82` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code :

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

1
2
3
4
5
6
CREATE TABLE `stations` (
 `id_station` int(11) NOT NULL,
 `departement` varchar(10) NOT NULL,
 `localisation` varchar(30) NOT NULL,
 PRIMARY KEY (`id_station`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code :

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

CREATE TABLE `relever_mesure` (  `valeur` varchar(25) DEFAULT NULL,  `id_calendrier_calendrier` int(11) NOT NULL,  `id_station_Stations` int(11) NOT NULL,  `id_capteur_capteur` int(11) NOT NULL,  PRIMARY KEY (`id_calendrier_calendrier`,`id_station_Stations`,`id_capteur_capteur`),  KEY `FK_relever_mesure_id_station_Stations` (`id_station_Stations`),  KEY `FK_relever_mesure_id_capteur_capteur` (`id_capteur_capteur`),  CONSTRAINT `FK_relever_mesure_id_capteur_capteur` FOREIGN KEY (`id_capteur_capteur`) REFERENCES `capteur` (`id_capteur`),  CONSTRAINT `FK_relever_mesure_id_calendrier_calendrier` FOREIGN KEY (`id_calendrier_calendrier`) REFERENCES `calendrier` (`id_calendrier`),  CONSTRAINT `FK_relever_mesure_id_station_Stations` FOREIGN KEY (`id_station_Stations`) REFERENCES `stations` (`id_station`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

[CinePhil]

Les fichiers csv que je dois remonter arrivent une fois par mois et font donc 84 colonnes sur 90 000 lignes maximum (en moyenne 84 000).

3 minutes pour 90 000 lignes, ça me semble énorme !
C'est un escargot ton serveur ?

[nrpfc]

sur un petit plus que ça mon fichier import contient les 3 premiers mois de l'année 2012 soit plus de 250 000 lignes.
Mon serveur n'est pas tout jeune (DELL PowerEdge 2900) mais bon j'ai travaillé sur pire que ça a l'AFPA

[CinePhil]

Tu pourrais fournir le résultat de ceci ?

Code :

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

1
2
3
4
5
6
7
EXPLAIN SELECT i.`0` AS valeur,
	c.id_calendrier,
	i.code_station,
	0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`0` IS NOT NULL

Combien y a t-il de stations ?
Tu peux fournir le contenu des tables station et capteur ?

[nrpfc]

je te fait ça ce soir des que je suis rentré, de toute façon je vais continuer a bosser sur latable ce week end histoire de mieux comprendre certaines fonction de MySQL.

Je te souhaite un bon week-end au cas ou on ne se croiserais pas sur le forum

[Fred_34]

Ta procédure pourrait ressembler à ceci :

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
DELIMITER |
 
DROP PROCEDURE IF EXISTS import_donnees|
CREATE PROCEDURE import_donnees( nbCapteur INTEGER)
 
BEGIN
 
DECLARE iter INTEGER DEFAULT 0; 
 
iterwhile: WHILE iter <= nbCapteur DO 
 
  SET @myInsert:= CONCAT('
INSERT INTO relever_mesure(valeur, id_calendrier_calendrier, id_station_Stations, id_capteur_capteur)
SELECT i.`',iter,'`,
       c.id_calendrier,
       i.id_station,
       ',iter,' 
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`',iter,'` IS NOT NULL'); 
 
 
  prepare stmnt FROM @myInsert; 
  EXECUTE stmnt; 
  DEALLOCATE PREPARE stmnt;
 
 
   SET iter = iter + 1; 
END WHILE iterwhile;
END |
DELIMITER ;
CALL import_donnees(82);