Auteur Sujet: Importer des données dans une base de données MySQL (Lu 6018 fois)

DamienC · « **le:** 17 juin 2015 à 14:13:00 »

Bonjour,

J'ai en ma possession un fichier texte contenant des données de capteurs de températures.
Je dois les insérer dans une base de données MySQL, mais je n'ai pas trouvé de solutions sur Internet...

Voici un extrait du fichier :

Code: [Sélectionner]

{"name":"foo","columns":["time","sequence_number","val","temperature"],"points":[[1427959015783,210001,21,11],[1427959009506,200001,21,11]]}{"name":"foo.bar","columns":["time","sequence_number","value"],"points":[[1427965465000,1,5],[1427965462000,1,3]]}{"name":"foo.temp.temp1","columns":["time","sequence_number","value"],"points":[[1427980734000,1,200],[1427980599000,1,200],[1427980464000,1,200],[1427980328000,1,200],[1427980193000,1,200],[1427980057000,1,200],[1427979922000,1,199],[1427979787000,1,199],[1427979651000,1,199],[1427979516000,1,199],[1427979381000,1,199],[1427979245000,1,199],[1427979110000,1,199],[1427978975000,1,199],[1427978839000,1,199],[1427978704000,1,199],[1427978569000,1,199],[1427978433000,1,199],[1427978298000,1,199],[1427978162000,1,199],[1427978027000,1,199],[1427977892000,1,199],[1427977756000,1,199],[1427977621000,1,199],[1427977486000,1,199],[1427977350000,1,199],[1427977215000,1,199],[1427977080000,1,199],[1427976944000,1,199],[1427976809000,1,199],[1427976674000,1,199],[1427976538000,1,199],[1427976403000,1,199],[1427976267000,1,198],[1427976132000,1,198],[1427975997000,1,199],[1427975861000,1,198],[1427975726000,1,198],[1427975591000,1,198],[1427975455000,1,198],[1427975320000,1,198],[1427975185000,1,198],[1427975049000,1,198],[1427974914000,1,198],[1427974779000,1,198],[1427974643000,1,198],[1427974508000,1,198],[1427974373000,1,198],[1427974237000,1,198],[1427974102000,1,198],[1427973967000,1,198],[1427973831000,1,198],[1427973696000,1,198],[1427973560000,1,198],[1427973425000,1,198],[1427973290000,1,198],[1427973154000,1,198],[1427973019000,1,197],[1427972884000,1,197],[1427972748000,1,197],[1427972613000,1,197],[1427972478000,1,197],[1427972342000,1,197],[1427972207000,1,197],[1427972072000,1,197],[1427971936000,1,197],[1427971801000,1,197],[1427971666000,1,197],[1427971530000,1,197],[1427971395000,1,196],[1427971260000,1,197],[1427971124000,1,196],[1427970989000,1,196],[1427970854000,1,196],[1427970718000,1,196],[1427970583000,1,196],[1427970448000,1,196],[1427970312000,1,196],[1427970177000,1,196],[1427970042000,1,196],[1427969906000,1,196],[1427969636000,1,196],[1427969500000,1,196],[1427969230000,1,196],[1427969094000,1,196],[1427968959000,1,196],[1427968824000,1,196],[1427968688000,1,196],[1427968553000,1,196],[1427968418000,1,196],[1427968282000,1,196],[1427968147000,1,196],[1427968012000,1,196],[1427967876000,1,196],[1427967741000,1,196],[1427967606000,1,196],[1427967470000,1,196],[1427967335000,1,196],[1427967200000,1,196],[1427967065000,1,195],[1427966929000,1,195],[1427966794000,1,195],[1427966659000,1,195],[1427966523000,1,195],[1427966388000,1,195],[1427966253000,1,194],[1427966117000,1,194],[1427965982000,1,194],[1427965847000,1,194],[1427965711000,1,194],[1427965576000,1,194],[1427965441000,1,194],[1427965305000,1,194],[1427965035000,1,194],[1427964899000,1,193],[1427964764000,1,193],[1427964629000,1,192],[1427964494000,1,191],[1427964358000,1,191],[1427964223000,1,190],[1427964088000,1,190],[1427963952000,1,189],[1427963817000,1,188],[1427963682000,1,186],[1427963546000,1,182],[1427963411000,1,179],[1427963276000,1,179],[1427963140000,1,181],[1427963005000,1,180],[1427962870000,1,181],[1427962734000,1,184],[1427962599000,1,187],[1427962464000,1,198],[1427962328000,1,198],[1427962193000,1,198],[1427962058000,1,198],[1427961922000,1,198],[1427961787000,1,198],[1427961651000,1,198],[1427961516000,1,198],[1427961381000,1,198],[1427961245000,1,198],[1427961110000,1,198],[1427960975000,1,198],[1427960839000,1,198],[1427960704000,1,198],[1427960569000,1,198],[1427960433000,1,198],[1427960298000,1,198],[1427960162000,1,198],[1427960027000,1,198],[1427959892000,1,198],[1427959756000,1,198],[1427959621000,1,198],[1427959486000,1,198],[1427959350000,1,198],[1427959215000,1,198],[1427959080000,1,198],[1427958944000,1,198],[1427958809000,1,198],[1427958673000,1,198],[1427958538000,1,199],[1427958403000,1,198],

Je le met en fichier joint également.

Si une personne ayant déjà réalisé ce type d'opération pouvait m'indiquer la marche à suivre, je lui en serai reconnaissant..

Merci par avance !

Cdt,
DamienC

Phach · « **Réponse #1 le:** 17 juin 2015 à 15:11:26 »

Ca ressemble à un extract json ?
Moi ce que je ferais c'est un script PHP avec un json_decode et puis je remplirais ma BDD avec une boucle.

DamienC · « **Réponse #2 le:** 17 juin 2015 à 15:20:02 »

Oui effectivement c'est un extract json. Merci pour l'astuce, je vais essayer

DamienC · « **Réponse #3 le:** 17 juin 2015 à 16:30:01 »

Me revoilà !

J'ai réussi à modifier les enregistrements, maintenant j'ai quelque chose de plus simple :

Citer

temp1 235 1394238542
temp1 227 1394238677
temp1 210 1394241807
temp1 211 1394241942
temp1 210 1394242078
temp1 211 1394242213
temp1 211 1394242349
temp1 211 1394242484

Je n'arrive cependant pas à importer les données dans la BDD MYSQL ;(
J'ai une table nommée "foo" avec trois colonnes : "Capteur" (qui correspond au nom du capteur, ici "temp1"), "valeur" (qui correspond à la valeur du capteur) et "timestamp" (qui correspond à la date).

Avez-vous une idée pour les importer?

Merci d'avance

Phach · « **Réponse #4 le:** 17 juin 2015 à 18:41:58 »

si tu arrive à modifier tes données pour en faire un CSV tu peux utiliser la fonction SQL LOAD DATA INFILE

avec des données comme :

Citer

"temp1";"227";"1394238677"
"temp1";"210";"1394241807"
"temp1";"211";"1394241942"
"temp1";"210";"1394242078"
"temp1";"211";"1394242213"
"temp1";"211";"1394242349"
"temp1";"211";"1394242484"

une requete du genre :

Code: [Sélectionner]

LOAD DATA INFILE ‘source.csv’
INTO TABLE temperatures
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES STARTING BY ''
TERMINATED BY '\n'
(time,sequence_number,val,temperature)

Y a des choses optionnelles. il faut adapter et voir ce qui fonctionne le mieux pour toi.
Puisqu'il faut que la structure soit cohérente ( ce qui n'ets pas le cas dans l'exemple, pas le même nombre de colonnes)

kgersen · « **Réponse #5 le:** 17 juin 2015 à 19:30:47 »

Citation de: DamienC le 17 juin 2015 à 16:30:01

Me revoilà !

J'ai réussi à modifier les enregistrements, maintenant j'ai quelque chose de plus simple :
Je n'arrive cependant pas à importer les données dans la BDD MYSQL ;(
J'ai une table nommée "foo" avec trois colonnes : "Capteur" (qui correspond au nom du capteur, ici "temp1"), "valeur" (qui correspond à la valeur du capteur) et "timestamp" (qui correspond à la date).

Avez-vous une idée pour les importer?

Merci d'avance

Ta question n'est pas précise: dans quel contexte veux tu importer ? commandes sql , script shell, php , autre?

Dans un script SQL, comme Phach l'a indiqué il y a la commande LOAD DATA INFILE mais pas besoin de transformer en CSV, tu peux indiquer que le séparateur de champ est un espace (FIELDS TERMINATED BY ' '). L'avantage de CSV est que c'est le format par défaut pour pas mal de chose donc pas besoin de préciser d'options. En plus il y a des outils de conversion de JSON vers CSV. Utiliser CSV est très recommandé donc.

Dans un script ligne de commande, il y a la commande mysqlimport qui fait la même chose (c'est une surcouche sur LOAD DATA INFILE).

Dans le cas de ton fichier avec espaces. Donnes le meme nom que la table a ton fichier de données donc foo.txt et un truc du genre:

Code: [Sélectionner]

mysqlimport --local --fields-terminated-by=' ' nomdelabase foo.txt

DamienC · « **Réponse #6 le:** 18 juin 2015 à 11:33:11 »

Salut !
J'ai réussi

Merci pour vos réponses, elles m'ont permis d'avoir ce que je voulais.

Maintenant, j'ai un autre problème à résoudre (eheh, je suis vraiment une brêle en SQL^^)

J'ai ma base de données comme ceci :

(Elle compte plus de 300 000 enregistrements...)
J'ai besoin de faire une moyenne journalière ce ces valeurs, afin d'avoir une valeur par jour, le but étant de mettre ça en graphique ensuite.

J'ai beaucoup regardé sur Internet les différentes solutions, mais je n'arrive pas à faire ce que je veux, la seule chose que j'arrive à faire c'est de calculer la moyenne globale, du coup je n'ai qu'une seule valeur pour l'année..

Avez-vous des idées?

PS : voici le Dashboard que je suis en train de réaliser, je précise que je débute en programmation^^ http://dashboard-mines-telecom.damien-cueff.fr/dashboard/dash1
(Certaines fonctionnalités sont désactivées, pour des raisons de sécurité!)

Cdt,
DamienC

Macharius · « **Réponse #7 le:** 18 juin 2015 à 14:07:26 »

J'ai pas de MySQL sous la main pour tester mais un truc du genre :

Code: [Sélectionner]

SELECT YEAR(FROM_UNIXTIME(timestamp)), MONTH(FROM_UNIXTIME(timestamp)), AVG(valeur) FROM %nomdetatable% GROUP BY YEAR(FROM_UNIXTIME(timestamp)), MONTH(FROM_UNIXTIME(timestamp));

Rajoutes éventuellement un ORDER BY pour que ton resultset soit trié et ça devrait être pas mal ! Ah si une clause WHERE pour filtrer sur ta colonne capteur si tu as les données de plusieurs capteurs

DamienC · « **Réponse #8 le:** 18 juin 2015 à 14:09:21 »

Super merci ! Je test ça de suite

Macharius · « **Réponse #9 le:** 18 juin 2015 à 14:22:00 »

J'avais même pas fait gaffe que c'était la moyenne journalière que tu voulais et c'est la mensuelle que je t'ai donné... Du coup faut que tu rajoutes la granularité nécessaire par rapport à ce que je t'ai filé

kgersen · « **Réponse #10 le:** 18 juin 2015 à 19:35:05 »

un truc du genre:

Code: [Sélectionner]

Select capteur, CAST(FROM_UNIXTIME(timestamp) as date) madate, AVG(valeur)
From Foo
Group By capteur, madate

(supprime capteur pour faire la moyenne quoti pour tout les capteurs)

live demo: http://sqlfiddle.com/#!2/8648b/2

mais ta problématique est plus adaptée a une TSD (time serie database, http://opentsdb.net/ ou https://influxdb.com/) qu'une base SQL normale.
Si tu restes en SQL, converti les 'timestamp' en date a l'import pour gagner en performance.

DamienC · « **Réponse #11 le:** 19 juin 2015 à 00:06:58 »

Hello,

Merci à tous, j'ai réussi à faire ce que je voulais. J'update le site démo demain matin !

Citer

mais ta problématique est plus adaptée a une TSD (time serie database, http://opentsdb.net/ ou https://influxdb.com/) qu'une base SQL normale.

J'ai expertisé ces solutions pendant deux semaines, et ni l'une ni l'autre ne correspond à mes besoins. Surtout le fait de ne pas pouvoir supprimer simplement des valeurs. Parce que nos capteurs font parfois des erreurs de mesures, nous devons être capable de les supprimer rapidement et simplement, voir automatiquement.

Cdt,
DamienC