History Werte nach SQL übernehmen

sissiwup · 10 Dec 2015, 20:09

Hallo,

leider ein wenig Handarbeit, aber es ist machbar:

Exporte die Adapter und die Geräte aus der aktuellen Datenbank, also es werden nur die Daten übernommen, für die ihr im sql auch die history aktiviert habt.

sqlite:

cd /opt/iobroker/iobroker-data/sqlite/
sqlite3
.open sqlite.db
.mode csv
.output datapoints.csv
select * from datapoints;
.output sources.csv
select * from sources;

mysql:

SELECT * INTO OUTFILE '/tmp/datapoints.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM iobroker.datapoints WHERE 1;

SELECT * INTO OUTFILE '/tmp/sources.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM iobroker.sources s WHERE 1;

im Javaprogramm die Pfade anpassen:

package de.wh.iobroker;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;

public class convert {
  private static int counter = 0;
  public static void listDir(File dir, ArrayList <file>fs) {

    File[] files = dir.listFiles();
    if (files != null) {
      for (File file : files) {
        // System.out.print(file.getAbsolutePath());
        if (file.isDirectory()) {
          // System.out.print(" (Ordner)\n");
          listDir(file, fs); // ruft sich selbst mit dem
          // Unterverzeichnis als Parameter auf
        } else {
          // System.out.print(" (Datei)\n");
          fs.add(file);
        }
      }
    }
  }

  public static void parseFile(File f, HashMap <string, integer="">sources,
      HashMap <string, int[]="">datapoints, BufferedWriter ts_number,
      BufferedWriter ts_bool, BufferedWriter ts_string) {

    String adapter = f.getName().replace("history.", "").replace(".json", "");
    if (!datapoints.containsKey(adapter)) {
      // System.out.println("Geraet nicht gefunden: " + adapter);
      return;
    }
    int[] l = datapoints.get(adapter); // System.out.println(adapter);
    int type = l[1];
    int id = l[0];
    try {
      JSONParser parser = new JSONParser();

      Object obj = parser.parse(new FileReader(f.getAbsolutePath()));

      JSONArray jsonArray = (JSONArray) obj;
      // System.out.println(jsonArray.toJSONString());

      @SuppressWarnings("unchecked")
      Iterator <jsonobject>iterator = jsonArray.iterator();
      String val = "";
      String ts = "";
      String ack = "";
      String from = "";
      // String lc = "";
      int sourcenummer = -1;
      while (iterator.hasNext()) {
        // System.out.println(iterator.next().toJSONString());
        JSONObject entry = iterator.next();
        val = (entry.containsKey("val") ? entry.get("val").toString() : "");
        val = (type == 2 ? ((val.compareToIgnoreCase("true") == 0) ? "1" : "0")
            : val);
        ts = (entry.containsKey("ts") ? entry.get("ts").toString() : "");
        ack = (entry.containsKey("ack") ? entry.get("ack").toString() : "");
        ack = ((ack.compareToIgnoreCase("true") == 0) ? "1" : "0");
        // lc = (entry.containsKey("lc") ? entry.get("lc").toString() : "");
        from = (entry.containsKey("from") ? entry.get("from").toString() : "");
        if (!sources.containsKey(from)) {
          System.out.println("Source nicht gefunden:" + from);
          return;
        }
        sourcenummer = sources.get(from);
        counter++;
        String ms = "000" + counter;
        ts = ts + ms.substring(ms.length() - 3, ms.length());
        System.out.println(" id:" + id + " ts:" + ts + " val:"
            + val.substring(0, (val.length() > 30 ? 30 : val.length()))
            + " ack:" + ack + " _from:" + sourcenummer + " q:0" + " (type:"
            + type + ")");
        switch (type) {
        case 0:
          ts_number.write(id + "," + ts + "," + val + "," + ack + ","
              + sourcenummer + ",0\n");
          break;
        case 1:
          ts_string.write(id + "," + ts + ",\"" + val + "\"," + ack + ","
              + sourcenummer + ",0\n");
          break;
        case 2:

          ts_bool.write(id + "," + ts + "," + val + "," + ack + ","
              + sourcenummer + ",0\n");
          break;

        default:
          break;
        }
      }

    } catch (Exception e) {
      e.printStackTrace();
    }

  }

  public static void main(String[] args) throws IOException {

    // Datapoints
    HashMap <string, int[]="">datapoints = new HashMap<string, int[]="">();
    Path dateiname = Paths.get("/users/wh/sql/datapoints.csv");
    List <string>dps = Files.readAllLines(dateiname, Charset.forName("UTF-8"));
    for (String string : dps) {
      // System.out.println(string);
      int id = Integer.parseInt(string.split(",")[0]);
      String txt = string.split(",")[1].replace("\"", "");
      int type = Integer.parseInt(string.split(",")[2]);
      int[] l = { id, type };
      datapoints.put(txt, l);
    }
    // Sources
    HashMap <string, integer="">sources = new HashMap<string, integer="">();
    dateiname = Paths.get("/users/wh/sql/sources.csv");
    List <string>sourcesf = Files.readAllLines(dateiname,
        Charset.forName("UTF-8"));
    for (String string : sourcesf) {
      // System.out.println(string);
      int id = Integer.parseInt(string.split(",")[0]);
      String txt = string.split(",")[1].replace("\"", "");
      sources.put(txt, id);
    }
    // Histotydateien
    File f = new File("/users/wh/sql/history");
    ArrayList <file>files = new ArrayList<file>();
    listDir(f, files);

    try (BufferedWriter ts_number = Files.newBufferedWriter(
        Paths.get("/users/wh/sql/ts_number.csv"), Charset.forName("UTF-8"));
        BufferedWriter ts_bool = Files.newBufferedWriter(
            Paths.get("/users/wh/sql/ts_bool.csv"), Charset.forName("UTF-8"));
        BufferedWriter ts_string = Files.newBufferedWriter(
            Paths.get("/users/wh/sql/ts_string.csv"), Charset.forName("UTF-8"));) {

      int count = 0; // für alle auf 0
      for (File fs : files) {
        parseFile(fs, sources, datapoints, ts_number, ts_bool, ts_string);
        count--;
        if (count == 0) {
          return;
        }
      }
    }
  }
}</file></file></string></string,></string,></string></string,></string,></jsonobject></string,></string,></file>

so sollte der output aussehen:

id:119 ts:1448861207602 val:21.1 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448861085603 val:21 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448860939604 val:20.8 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448860762605 val:20.6 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448860585606 val:20.5 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448860315607 val:20.4 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448858630608 val:20.3 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448857032609 val:20.4 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448854827610 val:20.5 ack:1 _from:2 q:0 (type:0)
 id:119 ts:1448853167611 val:20.6 ack:1 _from:2 q:0 (type:0)

es werden drei Dateien erzeugt:

ts_number.csv

ts_bool.csv

ts_string.csv

Diese dann im sql importieren(hier nur mysql als Beispiel):

create TABLE iobroker.old_ts_bool like iobroker.ts_bool;
create TABLE iobroker.old_ts_number like iobroker.ts_number;
create TABLE iobroker.old_ts_string like iobroker.ts_string;

LOAD DATA INFILE '/tmp/ts_bool.csv' 
INTO TABLE iobroker.old_ts_bool 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/ts_number.csv' 
INTO TABLE iobroker.old_ts_number 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/ts_string.csv' 
INTO TABLE iobroker.old_ts_string 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

insert INTO iobroker.ts_bool SELECT * FROM iobroker.old_ts_bool ;
insert INTO iobroker.ts_number  SELECT * FROM iobroker.old_ts_number ;
insert INTO iobroker.ts_string  SELECT * FROM iobroker.old_ts_string ;

delete FROM iobroker.old_ts_bool  ;
delete FROM iobroker.old_ts_number  ;
delete FROM iobroker.old_ts_string  ;

Viel Erfolg

PS: Ich habe mir die Daten vom pi auf den Rechner gezogen und Java dort ausgeführt.

Durch die vielen Zwischendateien, kann man alle Schritte gut nachvollziehen und ggf.

mit Excel bestimmte Werte, die man nicht haben wollte, rauswerfen.

jans_ios · 28 Sept 2017, 09:34

Hallo Sissi,

kannst Du Deine Anleitung noch ein wenig mehr konkretisieren? Wie z.B. hast Du den Java-Aufruf auf Deinem PC genau gemacht?

Ich stehe auch gerade vor der Herausforderung, die SQLite-DB vom Pi in eine MySQL-DB auf meiner Synology Diskstation zu überführen. Ich würde halt gerne alle bestehenden Daten übernehmen…

Danke!

Gruß, Jan

piForscher · 30 Apr 2018, 21:04

Hallo,

vielen Dank für diese guten Ideen, das hat mir geholfen!

Ich hatte meine Verbrauchswerte manuell in einer App gespeichert und wollte die Werte jetzt mal in iobroker übertragen,

also konkret Werte mit einem alten Zeitstempel (csv) in die MySQL-Datenbank schreiben.

Mein manueller Weg war so:

1. csv per Excel-Funktionen so umbauen, dass die Daten so aussehen, wie sie der Standard-SQL-Import möchte:

id   , timestamp     ,val       , ack, from, _q
"307","1396051200000","117282.0","0","1","0"
"307","1398038400000","117509.0","0","1","0"

2. Im phpMyAdmin in die Tabelle ts_number importieren

Ist nicht elegant und hat ein bisschen gedauert, aber dann hat es doch gut geklappt.

Und weil das nur einmalig war, brauchte ich auch keine automatischere Lösung.

piForscher

NEWS

History Werte nach SQL übernehmen

Support us

904
Online

31.3k
Users

78.9k
Topics

1.3m
Posts

NEWS

History Werte nach SQL übernehmen

Support us

904Online

31.3kUsers

78.9kTopics

1.3mPosts

904
Online

31.3k
Users

78.9k
Topics

1.3m
Posts