Als Data Scientist haben wir mit Daten zu tun (wer hätte das gedacht) und in ziemlich vielen Fällen liegen diese Daten in einer relationalen Datenbank. Und die Abfragesprache dafür ist SQL. Also muss ein Data Scientist mindestens die Grundzüge von SQL beherrschen.
Aber zum Glück ist das SQL 1×1 gar nicht schwer, denn die am meisten verwendeten Befehle sind nicht viele und logisch aufgebaut. Natürlich steckt hinter relationalen Datenbanken und SQL wesentlich mehr, wenn es um Datenbank-Design oder komplizierte Berechnungen geht. Da gibt es ganze Regalmeter voll Fachwissen. Aber das soll Dich vorerst gar nicht kümmern. Mit dieser SQL Anleitung für Anfänger bekommst Du eine praktische Einführung und kannst direkt mit der Beispiel-Datenbank loslegen.
Inhalt
Was ist SQL?
SQL steht für „Structured Query Language“, wurde in den 1970ern entwickelt und ist laut Wikipedia eine Datenbanksprache zur Definition von Datenstrukturen in relationalen Datenbanken sowie zum Bearbeiten (Einfügen, Verändern, Löschen) und Abfragen von darauf basierenden Datenbeständen. Es gibt also drei Aufgabengebiete:
- Definition von Datenstrukturen, sprich das Design und Anlegen einer Datenbank mit ihren Tabellen, Sichten (Views), Indizes, Trigger usw.
- Das Bearbeiten von Daten
- Die Abfrage von Daten (query), also Anzeige/Export einer Tabelle
Auf Punkt 3 gehen wir in diesem Tutorial ein, denn als Data Scientist wollen wir ja Daten in unsere R- oder Python-Skripte importieren, um dann darauf Analysen zu machen.
Es gibt zwar gewisse SQL-Standards (ANSI), jedoch gibt es viele Dialekte, je nachdem welches Datenbank-System (MSSQL, PostgreSQL, DB, MySQL, …) im Einsatz ist. Insofern muss man bei der Suche nach Anleitungen im Internet immer schauen, für welchen Dialekt das Beispiel geschrieben ist.
Zugriff auf eine SQL Datenbank
Auf einem Server läuft ein Datenbankmanagement-System (DBMS), z.B. PostgreSQL, MS-SQL oder MySQL. Das sind ziemlich komplexe Softwaresysteme, die die Datenbanken verwalten. Zum Beispiel habe ich eine MySQL-Datenbank bei meinem Webhost, die alle Daten der databraineo-Website enthält. Auf dieses DBMS kann ich nun per SQL zugreifen.
Man kann sich auch lokal auf dem PC ein Datenbankmanagement-System wie PostgreSQL installieren, allerdings ist dieses dann in der Regel auch nur vom lokalen PC zugreifbar.
Wir machen es uns einfach und benutzen SQLite. Das ist eine relationale Datenbank in einer einzigen Datei. Es gibt zwar ein paar Einschränkungen, dafür braucht man aber keine weitere Server-Software und kann sofort loslegen. Auch auf Mobiltelefonen oder in Webbrowsern kommt zum Beispiel SQLite zum Einsatz.
Die SQL IDE DBeaver
Ein sehr nützliches Tool, um SQL-Datenbanken zu verwalten, ist DBeaver. In der Community Edtion ist es kostenlos und unterstützt viele SQL-DBMS, also probiert es aus. In DBeaver können wir z.B. Verbindungen zu verschiedenen Datenbanken aufbauen, sehen, welche Tabellen es gibt, SQL-Code schreiben, Daten importieren oder exportieren.
Zugriff per ODBC
ODBC (open database connectivity) ist ein Standard für eine Datenbankschnittstelle. Man installiert den Treiber für das anzubindende DBMS. Fast jede Programmiersprache hat eine Bibliothek, mit dem man dann die Verbindung herstellen und SQL-Abfragen machen kann.
Für R gibt es beispielsweise die Packages odbc oder Rodbc. Für Python gibt es ebenfalls viele Möglichkeiten, z.B. pyodbc oder turbodbc. Im Python-Wiki gibt es eine Seite mit den ODBC-Libraries.
Aufbau einer SQL-Query
Wenn ihr DBeaver installiert habt, dann könnt ihr direkt loslegen und eigene Queries ausprobieren. In den folgenden Beispielen benutzen wir die Chinook-Datenbank, welche als Beispiel-Datenbank bei DBeaver dabei ist. Ansonsten kann man sich die sqlite-Datei aus dem Github-Repository herunterladen.
Nun wollen wir unsere erste Abfrage machen. Dazu wählst Du die Datenbank „DBeaver Sample Database“ aus, klickst rechts und wählst SQL Editor (Shortcut F3). Dadurch öffnet sich ein Textfenster, in das wir direkt die SQL-Befehle tippen können.
Mit Strg + Eingabe bzw. dem Play-Button links (siehe Screenshot) wird die Zeile bzw. der markierte Bereich ausgeführt und unten kommt das Ergebnis.
Mit diesem Befehl wird die gesamte Tabelle Artist zurückgegeben.
Im Datenbanknavigator auf der linken Seite sehen wir übrigens, welche Tabelle es gibt und welche Spalten diese haben. Artist hat zwei Spalten, nämlich ArtistId und Name.
Der SELECT-Befehl
Jede Abfrage, welche Daten aus der Datenbank holt, fängt mit SELECT an. Dann folgen Spaltennamen oder Ausdrücke, gefolgt von FROM Tabellenname und einem Semikolon.
Statt dem * im obigen Beispiel könnten wir auch die Spaltennamen aufzählen, also
Um den Spaltennamen in der Ausgabe zu ändern, benutzen wir das Schlüsselwort AS
Filtern mittels WHERE-Bedingung
Meist sind Tabellen ja ziemlich groß, so dass wir die Daten filtern. Das passiert mit einer WHERE-Bedingung, die nach dem FROM kommt.
substr steht für substring, wir pulen damit das erste Zeichen aus dem Namen raus. D.h. die Query gibt alle Künstler zurück, die mit A anfangen.
Eine sehr praktische Alternative zum Vergleichsoperator = ist LIKE. Auch LIKE vergleicht einfach nur zwei Strings. Das tolle daran sind aber die zwei Wildcards % und _. Dabei steht % für beliebig viele Zeichen und _ für ein beliebiges Zeichen.
Mehrere Bedingungen können mit AND und OR kombiniert werden und eine Bedingung kann mit NOT negiert werden.
In dieser Query haben wir noch eine zweite Bedingung ergänzt: Der zweite Buchstabe darf kein c sein.
Sortieren mit ORDER BY
Um die Ausgabe-Tabelle zu sortieren, benutzen wir am Ende den Zusatz ORDER BY. Soll nach mehreren Spalten sortiert werden, trennt man diese mit Komma. Mit dem Keyword DESC wird die Reihenfolge umgedreht.
In dem Beispiel wird also zuerst absteigend nach Composer sortiert, im Anschluss aufsteigend nach Name.
Eindeutige Ergebnisse mit dem Schlüsselwort DISTINCT
Häufig gibt es mehrere Einträge mit dem gleichen Wert in einer Spalte. Interessiert uns nur eindeutige Werte, ist das Schlüsselwort DISTINCT nützlich, welches direkt nach SELECT verwendet wird.
Bedingungen mittels CASE WHEN
Wollen wir Bedingungen einbauen, dann benutzen wir in SQL eine CASE-WHEN-Struktur. In vielen anderen Sprachen entspricht das einer IF-ELSE-Bedingung.
Schauen wir uns die CASE WHEN Bedingung an. Nach dem CASE WHEN kommt eine (oder mehrere Bedingungen mit AND, OR, NOT). Trifft diese zu, wird der Teil nach dem THEN zurückgegeben. Ein ELSE-Teil ist optional. Falls dieser nicht vorhanden ist, wird NULL zurückgegeben, ansonsten eben der Wert aus dem ELSE-Teil. Abgeschlossen wird CASE WHEN mit END.
Noch eine Sache: Wir haben Index in eckige Klammern gepackt. Das liegt daran, dass Index ein SQL-Schlüsselwort ist. Mit den eckigen Klammern sagen wir, dass es hier nur als Spaltenname verwendet werden soll. Das ist zwar kein so guter Stil, aber manchmal nötig, wenn man gewisse Spaltennamen im Export benötigt. Häufiger Kandidat ist auch Alter, welches auch ein SQL-Schlüsselwort ist. Auch Leerzeichen sind mit den eckigen Klammern in den Spaltennamen möglich.
Mehrere Bedingungen lassen sich einfach untereinander schreiben. Das END kommt erst am Ende von allen Bedingungen.
Aggregation mit GROUP BY
Bisher haben wir nur Teile einer Tabelle angezeigt. Nun kommen wir zu einem der wichtigsten Hilfsmittel für Data Scientisten, nämlich die Aggregation von Zeilen. Wir fassen mehrere Zeilen zusammen, z.B. als Summe, Durchschnitt, Minimum oder Anzahl.
In diesem Beispiel wird also der Mittelwert über die Länge aller Tracks in Minuten berechnet. Jetzt wollen wir aber nicht alle Zeilen zusammenfassen, sondern mehrere Kennzahlen für jeden Composer berechnen. Das geht mit dem Zusatz GROUP BY.
Wollen wir bezüglich der Aggregate filtern, geht das nicht mit WHERE, denn die Filterung passiert vor der Gruppierung. Für die Filterung nach der Gruppierung benutzt man HAVING.
Die Query oben filtert die Tabelle Track zuerst auf die Tracks, deren Namen mit einem Vokal anfangen. Dann berechnet sie für jeden Vokal separat die Anzahl und durchschnittliche Länge der Tracks. Danach wird nochmal gefiltert, nämlich auf mehr als 50 Tracks (dadurch fällt der Vokal u heraus).
Eine Abfrage über mehrere Tabellen mittels JOIN
In den seltensten Fällen genügt die Abfrage auf eine Tabelle, denn eine Datenbank ist relational aufgebaut, hat also Beziehungen zwischen den Tabellen. Wir sehen das gut in unsere Beispiel-Datenbank. In der Tabelle Track ist zwar eine AlbumId vorhanden, aber kein Albumname. Dazu müssen wir in der Tabelle Album nachschauen. Und das geht folgendermaßen:
Es gibt vier Arten von JOIN:
- INNER JOIN: Behält nur die Zeilen, die in beiden Tabellen vorhanden sind
- LEFT JOIN: Behält alle Zeilen aus der ersten Tabelle
- RIGHT JOIN: Behält alle Zeilen aus der zweiten Tabelle
- FULL OUTER JOIN: Behält alle Zeilen aus beiden Tabellen
Ich habe für euch die vier JOIN-Typen auch als PDF-Cheatsheet dargestellt.
Man kann auch mehrere Tabellen miteinander verbinden. Hier habe ich zudem Aliases a, b, c, verwendet, um etwas Schreibarbeit zu sparen.
Verschachtelte Abfragen
Man kann Abfragen ineinander verschachteln, also statt einer Tabelle nach FROM oder JOIN eine ganze SELECT-Query. Wir nehmen die vorherige Abfrage und bilden nun den Mittelwert der Bestellungen
Ausblick
So, jetzt kennt ihr die wichtigsten Befehle für Datenbankabfragen. Natürlich braucht man manchmal noch ein bisschen mehr, z.B. wenn man eine Datums- oder Zeitstempel-Spalte umwandeln muss. Zudem kommt irgendwann die Performance-Optimierung ins Spiel, wenn die Queries zu lange dauern.
Aber mit den obigen SQL-Statements kommt ihr schon ziemlich weit.
Ein großes Thema ist das Design von Datenbanken, also welche Tabellen mit welchen Spalten angelegt werden. Das fängt dann mit Normalformen an und geht mit Triggern und Indizes weiter. Das ist eine ganze Welt für sich. Einige Grundkenntnisse hier helfen natürlich weiter, aber es hängt auch davon ab, ob man diese Sachen als Data Scientist selber machen muss oder IT-Support bekommt.
Daten einfügen und aktualisieren hingegen ist nicht sonderlich schwer, das machen wir in einem anderen Blogpost. Und dann wollen wir das alles ja nicht mit einem extra SQL-Skript, sondern SQL direkt aus R oder Python ausführen. Aber auch das ist mit den richtigen Packages ziemlich einfach.
Happy SQLing,
Euer Holger
