CSV Import - Dynamisches SQL Statement

[mbolder]

Hallo zusammen,

ich möchte ein CSV - File mittels Java importieren (habe hierfür Apache commons csv benutzt) und dann in einer Datenbank speichern (Datenbank mittels postgresql.jar verbunden) soweit so gut. Allerdings hat das CSV - File leere Felder, die bei einem fixen SQL Insert Statement von Postgre abgewiesen werden. Meine Idee war nun anhand der Header/Value Paare jeweils pro Zeile zu überprüfen ob Feld leer ist und dann aus den vollen Feldern jeweils ein SQL Insert Statement zu generieren. Sprich "INSERT INTO DATENBANK (?,?,?) VALUES (?,?,?);",("Header1", "Header2", "Header4"), (Value1, Value2, Value4);. Ich verstehe das ein derartiges Vorgehen zu SQL Injektion führen kann, allerdings ist dies ein geschlossenes System, bei dem diese Gefahr ausgeschlossen werden kann.

In Python hätte ich folgendes benutzt, allerdings konnte ich in Java nicht die passenden Äquivalente finden :

Beste Grüße

Markus

with codecs.open(filename, 'rb', encoding='utf-8') as csvfile:
        reader = csv.DictReader(csvfile, delimiter='\t')
        col=[]
        val=[]
        for row in reader:
              for column, value in row.items():
                    if value != '':
                       col.append(column)
                       val.append(unicode(value, "utf8"))
                        try:
                            query1=sql.SQL("INSERT INTO DATABSE ({}) VALUES ({}) ON CONFLICT DO NOTHING").format(sql.SQL(', ').join(map(sql.Identifier, col)),sql.SQL(', ').join(sql.Placeholder() * len(col)))
                            c.execute(query1, tuple(val),)
                        except psycopg2.IntegrityError as e:
                            print e
                        col=[]
                        val=[]                          
                    conn.commit()

 

[mrBrown]

Allerdings hat das CSV - File leere Felder, die bei einem fixen SQL Insert Statement von Postgre abgewiesen werden.

Warum werden die denn abgewiesen? Lässt die Datenbank die null-Werte nicht zu? Dann sollte das bei der anderen Variante auch scheitern.


Sind die Header vorher bekannt oder sind das beliebige, es gibt aber immer eine Tabelle, die genau diesen Headern entspricht?

 

[mbolder]

Warum werden die denn abgewiesen? Lässt die Datenbank die null-Werte nicht zu? Dann sollte das bei der anderen Variante auch scheitern.

Postgresql lässt empty Strings bei Feldern die z.b. ein Datum erwarten nicht zu. Bei der Python Variante werden nur die Felder gefüllt, die einen Wert haben, somit tritt das Problem nicht auf. ( if value != '': // filtert die empty strings)

Sind die Header vorher bekannt oder sind das beliebige, es gibt aber immer eine Tabelle, die genau diesen Headern entspricht?

Die Tabelle entspricht der gesamten Anzahl der Header im CSV File, sprich Header1 im CSV-File ist der Name der ersten Column in Postgres etc.

 

[mrBrown]

Postgresql lässt empty Strings bei Feldern die z.b. ein Datum erwarten nicht zu. Bei der Python Variante werden nur die Felder gefüllt, die einen Wert haben, somit tritt das Problem nicht auf. ( if value != '': // filtert die empty strings)

Dann sind die Felder nullable? Ersetz leere Strings einfach mit null.

 

[mbolder]

Dann sind die Felder nullable? Ersetz leere Strings einfach mit null.

Also wie würde ich den Empty string finden, ich vermisse die Möglichkeit über die Header / Value Paare zu schleifen :

public static void importcsv() {
BufferedReader br = null;
String csvFile = "AMA116.txt";
try {
FileReader in = new FileReader(csvFile);
Iterable<CSVRecord> records = CSVFormat.TDF.withFirstRecordAsHeader().parse(in);
for (CSVRecord record : records) {

 

[mrBrown]

Wie sieht denn dein jetziger Code aus? So wie es oben klingt, hast du ja schon irgendeine Version?

 

[mbolder]

Wie sieht denn dein jetziger Code aus? So wie es oben klingt, hast du ja schon irgendeine Version?

Die hatte ich bereits wieder verworfen, da das hard coden nicht funktioniert hatte.
Hier die vereinfachte Version, im original sind es 45 Columns mit sehr langen Bezeichnungen.....

Statement s = connection.createStatement();
s.executeUpdate('INSERT INTO DATABASE ("Column1", "Column2") VALUES (+record.get(Column1)+,+record.get(Column2)+)');

 

[mrBrown]

Zumindest das "Leerer String Problem" hätte ein toNullOrNonEmpty(record.get(Column1)) (mit entsprechend ausprogrammierter Methode) gelöst.


Wenn du den String automatisch generieren willst, brauchst du nicht Header und Value zusammen.
Aus den Headern kannst du dir ein PreparedStatement generieren lassen, dieses füllst du dann nur noch mit den Werten (und dabei wieder "" mit null ersetzen) und führst es aus.

 

[mbolder]

Zusätzlich habe ich eine dynamische Routine gefunden! Allerdings habe ich hier noch ein Problem. Die erzeugte Query muss "" um die Table names aufweisen, da die Namen mit einem "-" Zeichen verbunden sind. Bekomme die " jedoch noch nicht am Anfang des ersten sowie Ende des letzten Tables in der Query...

Der Output der Query sieht wie folgt aus.

Query: INSERT INTO DATABASE(settlement-id","settlement-start-date","settlement-end-date) VALUES (?,?,?)
Batch entry 0 INSERT INTO DATABASE(settlement-id","settlement-start-date","settlement-end-date) VALUES('10327656702','2018-06-01T14:02:43+00:00','2018-06-11T09:55:50+00:00')

package MainFrame;


import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import au.com.bytecode.opencsv.CSVReader;

/**
 *
 * @author viralpatel.net
 *
 */
public class CSVLoader {

    private static final
    String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
    private static final String TABLE_REGEX = "\\$\\{table\\}";
    private static final String KEYS_REGEX = "\\$\\{keys\\}";
    private static final String VALUES_REGEX = "\\$\\{values\\}";

    private Connection connection;
    private char seprator;

    /**
    * Public constructor to build CSVLoader object with
    * Connection details. The connection is closed on success
    * or failure.
    * @param connection
    */
    public CSVLoader(Connection connection) {
        this.connection = connection;
        //Set default separator
        this.seprator = ',';
    }
 
    /**
    * Parse CSV file using OpenCSV library and load in
    * given database table.
    * @param csvFile Input CSV file
    * @param tableName Database table name to import data
    * @param truncateBeforeLoad Truncate the table before inserting
    *             new records.
    * @throws Exception
    */
    public void loadCSV(String csvFile, String tableName,
            boolean truncateBeforeLoad) throws Exception {

        CSVReader csvReader = null;
        if(null == this.connection) {
            throw new Exception("Not a valid connection.");
        }
        try {
        
            csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. "
                    + e.getMessage());
        }

        String[] headerRow = csvReader.readNext();

        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file." +
                    "Please check the CSV file format.");
        }

        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, "\",\""));
        query = query.replaceFirst(VALUES_REGEX, questionmarks);

        System.out.println("Query: " + query);

        String[] nextLine;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);

            if(truncateBeforeLoad) {
                //delete data from table before loading csv
                con.createStatement().execute("DELETE FROM " + tableName);
            }

            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {

                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date
                                    .getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            con.commit();
        } catch (Exception e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception(
                    "Error occured while loading data from file to database."
                            + e.getMessage());
        } finally {
            if (null != ps)
                ps.close();
            if (null != con)
                con.close();

            csvReader.close();
        }
    }

    public char getSeprator() {
        return seprator;
    }

    public void setSeprator(char seprator) {
        this.seprator = seprator;
    }

}

 

[mrBrown]

Füg in headerRow einfach jedem String vorn und hinten ein Anführungszeichen hinzu, joinen musst du dann nur noch mit dem Komma.

 

[mbolder]

Füg in headerRow einfach jedem String vorn und hinten ein Anführungszeichen hinzu, joinen musst du dann nur noch mit dem Komma.

Jetzt funktioniert es !! Danke für die Hilfe.

package MainFrame;


import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import au.com.bytecode.opencsv.CSVReader;


public class CSVLoader {

    private static final
        String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
    private static final String TABLE_REGEX = "\\$\\{table\\}";
    private static final String KEYS_REGEX = "\\$\\{keys\\}";
    private static final String VALUES_REGEX = "\\$\\{values\\}";

    private Connection connection;
    private char seprator;

    /**
    * Public constructor to build CSVLoader object with
    * Connection details. The connection is closed on success
    * or failure.
    * @param connection
    */
    public CSVLoader(Connection connection) {
        this.connection = connection;
        //Set default separator
        this.seprator = ',';
    }
  
    /**
    * Parse CSV file using OpenCSV library and load in
    * given database table.
    * @param csvFile Input CSV file
    * @param tableName Database table name to import data
    * @param truncateBeforeLoad Truncate the table before inserting
    *             new records.
    * @throws Exception
    */
    public void loadCSV(String csvFile, String tableName,
            boolean truncateBeforeLoad) throws Exception {

        CSVReader csvReader = null;
        if(null == this.connection) {
            throw new Exception("Not a valid connection.");
        }
        try {
          
            csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. "
                    + e.getMessage());
        }

        String[] headerRow = csvReader.readNext();
              
              

        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file." +
                    "Please check the CSV file format.");
        }

        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query.replaceFirst(KEYS_REGEX, "\""+StringUtils.join(headerRow, "\",\"")+"\"");
        query = query.replaceFirst(VALUES_REGEX, questionmarks);

        System.out.println("Query: " + query);

        String[] nextLine;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);

            if(truncateBeforeLoad) {
                //delete data from table before loading csv
                con.createStatement().execute("DELETE FROM " + tableName);
            }

            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {

                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date
                                    .getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            con.commit();
        } catch (Exception e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception(
                    "Error occured while loading data from file to database."
                            + e.getMessage());
        } finally {
            if (null != ps)
                ps.close();
            if (null != con)
                con.close();

            csvReader.close();
        }
    }

    public char getSeprator() {
        return seprator;
    }

    public void setSeprator(char seprator) {
        this.seprator = seprator;
    }

}

 

[mrBrown]

Code bitte in Code-Tags:
[code=java]
...dein code...
[/code]

 

[mrBrown]

Und das Problem mit leeren Strings hast du jetzt wo und wie behoben?

 

[mbolder]

Und das Problem mit leeren Strings hast du jetzt wo und wie behoben?

Hatte die leeren Felder wie gehofft gefiltert, dann allerdings festgestellt das ein Import als VarChar leere Felder akzeptiert und deutlich schneller ist inklusive des Recast zu Int, Float etc.

 

[mrBrown]

Bevor ich dich falsch verstehe: Vorher gab es alle Möglichen Datentypen in der DB (integer, date, etc ...), und weil das Probleme gab, ist jetzt einfach alles nur noch VarChar?

 

[mbolder]

Bevor ich dich falsch verstehe: Vorher gab es alle Möglichen Datentypen in der DB (integer, date, etc ...), und weil das Probleme gab, ist jetzt einfach alles nur noch VarChar?

Nein, nach tieferer Analyse trat das Problem nur bei drei Feldern auf, zwei integer und ein float Wert. (Alle anderen Felder werden vorher angepasst (siehe Date) oder werden als empty String durch Postgres akzeptiert.) Von diesen Feldern werden nur zwei weiter verwendet (für gelegentliche Berechnungen). Bei 45 Feldern benötigt die Überprüfung ob leer oder nicht in meinem Program deutlich länger, im Vergleich zum nachträglichen recast bei Berechnung mit dem benötigten Float bzw. Integer Feld auf dem Server. Mittlerweile handelt es sich um 251 CSV-Dateien mit zusammen ca. 125 GB, bei deren Verarbeitung dieser "hässliche" Trick eine deutliche Zeitersparnis einbringt. Von der ersten Python Version mit ca. 22 Minuten, über das Java-Equivalent ca. 20 Minuten hin zur "VarChar" Variante ca.13 Minuten (inkl. Recast).