CsvJdbc Treiber

Einleitung

Der CsvJdbc Treiber ermöglicht das Lesen von Csv-Dateien über Jdbc.

Es können SQL-Abfragen abgesetzt werden, die die Daten aus der Datei selektieren und zurückgeben. Die Syntax setzt sich wie folgt zusammen.

  • als Tabelle wird eine Csv-Datei in der FROM-Klausel angegeben
  • die Spalten werden in der SELECT-Klausel selektiert

Die folgende Csv-Datei kann man zum Beispiel mit Hilfe des Treibers einlesen:

TrackId|Name|AlbumId|MediaTypeId|GenreId|Composer|Milliseconds|Bytes|UnitPrice
1|For Those About To Rock (We Salute You)|1|1|1|Angus Young, Malcolm Young, Brian Johnson|343719|11170334|0,99
2|Balls to the Wall|2|2|1||342562|5510424|0,99
3|Fast As a Shark|3|2|1|F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman|230619|3990994|0,99
4|Restless and Wild|3|2|1|F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman|252051|4331779|0,99
5|Princess of the Dawn|3|2|1|Deaffy & R.A. Smith-Diesel|375418|6290521|0,99
6|Put The Finger On You|1|1|1|Angus Young, Malcolm Young, Brian Johnson|205662|6713451|0,99
7|Let's Get It Up|1|1|1|Angus Young, Malcolm Young, Brian Johnson|233926|7636561|0,99

Eine SQL-Abfrage über den Treiber sieht dann so aus:

SELECT x,y,z
    FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'

Das ergibt folgende Ergebnismenge:

x y z
- - - 
TrackId Name AlbumId 
1 For Those About To Rock (We Salute You) 1 
2 Balls to the Wall 2 
3 Fast As a Shark 3 
4 Restless and Wild 3 
5 Princess of the Dawn 3 
6 Put The Finger On You 1 
7 Let's Get It Up 1

Die Spalten werden nacheinander aus der Datei selektiert. Dabei werden die in der COLUMN-Klausel vorgegebenen Spaltennamen vergeben.

Überschriften

Wenn die Csv-Datei Überschriften hat (wie in unserem Beispiel), können diese zur Identifizierung der Spaltennamen verwendet werden.

Dazu wird die HEADLINE-Klausel verwendet:

SELECT trackid,name,composer
    FROM "chinook.Track.csv" COLUMNS(trackid,name,composer) HEADLINE 1 SEPARATED BY '|'

Die Angabe der Zeilennummer mit den Überschriften (hinter HEADLINE) hat folgende Auswirkungen:

  1. Durch Angabe der HEADLINE-Klausel werden die darin enthalten Überschriften nicht in die Ergebnismenge übernommen.
  2. Die Zeile mit den Überschriften muss nicht die erste Zeile einer Csv-Datei sein (alle vorausgehenden Zeilen vor der Überschriftenzeile werden dann irgnoriert).
  3. Es werden nicht mehr alle Spalten in aufsteigender Reihenfolge benötigt, sondern es können gezielt Spalten per Namen ausgewählt werden.
trackid name composer
------- ---- -------- 
1       For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2       Balls to the Wall  
3       Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4       Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5       Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6       Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7       Let's Get It Up Angus Young, Malcolm Young, Brian Johnson

Aliase

Wie gewohnt können Spaltenaliase vergeben werden, um den Spalten andere Namen zu geben. Das ist dann hilfreich, wenn weitere SQL Operatoren auf die Spaltenwerte angewendet werden:

SELECT trackid,BIGINT(milliseconds)/1000/60 minutes,name,composer
    FROM "chinook.Track.csv" COLUMNS(trackid,name,composer,milliseconds) HEADLINE 1 SEPARATED BY '|'

Als Ergebnis erhält man:

trackid minutes name composer
------- ------- ---- -------- 
1       5       For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2       5       Balls to the Wall  
3       3       Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4       4       Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5       6       Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6       3       Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7       3       Let's Get It Up Angus Young, Malcolm Young, Brian Johnson

Pseudo-Spalten

Neben den Datenspalten aus dem Json-Dokument generiert der CsvJdbc-Treiber eine zusätzliche Pseudospalte "rownumber". Diese kann man zur Generierung fortlaufender Nummern verwenden:

SELECT rownumber,BIGINT(milliseconds)/1000/60 minutes,name,composer
    FROM "chinook.Track.csv" COLUMNS(trackid,name,composer,milliseconds) HEADLINE 1 SEPARATED BY '|' 
rownumber minutes name composer
--------- ------- ---- -------- 
1         5       For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2         5       Balls to the Wall  
3         3       Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4         4       Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5         6       Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6         3       Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7         3       Let's Get It Up Angus Young, Malcolm Young, Brian Johnson

Systemtabellen

Der CsvJdbc-Treiber unterstützt Verzeichnisse als Systemtabellen. Das erlaubt in einer Loop-Query eines datasqill Moduls nach Dateien in einem Verzeichnis zu suchen, die dann anschließend geladen werden können.

Die folgende Abfrage benutzt die Funktion "files()" als Systemtabelle und ihre Spalten "directory" und "filename".

SELECT directory, filename
    FROM "csvjdbc/src/test/resources/de/softquadrat/jdbc/csv".files()
    WHERE filename = 'chinook.xlsx' or filename = 'ExcelValidate.json'
;

Als Ergebnis werden die gesuchten Dateien zurückgeliefert:

directory filename
--------- -------- 
csvjdbc/src/test/resources/de/softquadrat/jdbc/csv CsvValidate.json 
csvjdbc/src/test/resources/de/softquadrat/jdbc/csv x.csv

Anmerkungen

Datentypen

Der CsvJdbc-Treiber liefert alle Ergebnisse als Text zurück. Eine Umwandlung in Zahlen oder Datumsfelder muss in einem nachfolgenden Arbeitsschritt erfolgen.