MySQL Import- und Exportoperationen

Einführung

In diesem Lab lernen Sie die wesentlichen Fähigkeiten zum Importieren und Exportieren von Daten in einer MySQL-Datenbank. Sie üben das Laden von Daten aus einer CSV-Datei (Comma-Separated Values) in eine Tabelle mithilfe des Befehls LOAD DATA INFILE, einer schnellen und effizienten Methode für die Massen-Datenaufnahme.

Sie lernen auch den umgekehrten Prozess: das Exportieren von Daten aus einer Tabelle in eine neue CSV-Datei. Darüber hinaus behandelt das Lab grundlegende Datenvalidierungsprüfungen nach einem Import, um die Datenqualität sicherzustellen. Am Ende dieses Labs werden Sie versiert darin sein, Daten in und aus MySQL zu verschieben.

Datenbank und Tabelle vorbereiten

Bevor Sie Daten importieren können, benötigen Sie ein Ziel dafür. Dies beinhaltet die Erstellung einer Datenbank, die Ihre Daten aufnehmen soll, und einer Tabelle mit einer Struktur, die mit den Daten übereinstimmt, die Sie importieren möchten.

Öffnen Sie zunächst das Terminal auf Ihrem Desktop.

Verbinden Sie sich als Benutzer root mit dem MySQL-Server. In dieser Laborumgebung können Sie sudo verwenden, um sich ohne Passwort zu verbinden.

sudo mysql -u root

Sobald Sie verbunden sind, sehen Sie die MySQL-Eingabeaufforderung (mysql>), die anzeigt, dass Sie nun direkt mit dem Datenbankserver interagieren.

Erstellen Sie als Nächstes eine neue Datenbank namens company. Die Klausel IF NOT EXISTS ist eine gute Praxis, die einen Fehler verhindert, falls die Datenbank bereits erstellt wurde.

CREATE DATABASE IF NOT EXISTS company;

Wechseln Sie nun zu Ihrer neu erstellten Datenbank, damit alle nachfolgenden Befehle für diese gelten.

USE company;

Erstellen Sie schließlich eine Tabelle namens employees, um die Mitarbeiterdaten zu speichern. Die Tabellenstruktur muss mit den Spalten in der CSV-Datei übereinstimmen, die Sie später importieren werden.

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

• INT PRIMARY KEY: Definiert die Spalte id als Ganzzahl und als Primärschlüssel, was bedeutet, dass jeder Wert eindeutig sein muss.
• VARCHAR(50): Definiert eine Spalte, die eine Zeichenkette variabler Länge mit bis zu 50 Zeichen speichern kann.

Sie können überprüfen, ob die Tabelle erfolgreich erstellt wurde, indem Sie Folgendes ausführen:

SHOW TABLES;

Sie sollten die Tabelle employees in der Ausgabe sehen.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

Lassen Sie die MySQL-Shell geöffnet, da Sie sie im nächsten Schritt weiterhin verwenden werden.

Daten aus einer CSV-Datei importieren

Nachdem die Datenbank und die Tabelle vorbereitet sind, können Sie nun Daten aus einer externen Datei importieren. Die Anweisung LOAD DATA INFILE ist eine sehr effiziente Methode, um Daten aus einer Textdatei in eine Tabelle zu laden.

Das Setup-Skript für dieses Lab hat bereits eine Datei namens employees.csv im Verzeichnis /tmp erstellt. Vor dem Import ist es gute Praxis, den Inhalt der Datei zu überprüfen.

Wichtig: Sie müssen ein neues Terminal-Tab für diesen Befehl öffnen, da Ihr aktuelles Terminal die MySQL-Shell ausführt. Klicken Sie auf das +-Symbol im Terminalfenster, um ein neues Tab zu öffnen. Führen Sie im neuen Terminal Folgendes aus:

cat /tmp/employees.csv

Die Ausgabe zeigt vier Zeilen mit kommagetrennten Daten:

1,John,Doe,john.doe@example.com,Sales
2,Jane,Smith,jane.smith@example.com,Marketing
3,Peter,Jones,peter.jones@example.com,Engineering
4,Mary,Brown,mary.brown@example.com,HR

Wechseln Sie nun zurück zu Ihrem ursprünglichen Terminal-Tab mit der MySQL-Shell (mysql>). Verwenden Sie den Befehl LOAD DATA INFILE, um die Datei zu importieren.

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

Lassen Sie uns diesen Befehl aufschlüsseln:

• LOAD DATA INFILE '/tmp/employees.csv': Gibt den vollständigen, absoluten Pfad zur Quelldatei an.
• INTO TABLE employees: Gibt die Zieltabelle für die Daten an.
• FIELDS TERMINATED BY ',': Teilt MySQL mit, dass Kommas die Datenfelder (Spalten) in jeder Zeile trennen.
• LINES TERMINATED BY '\n': Teilt MySQL mit, dass jede neue Zeile in der Datei eine neue Zeile darstellt.

Nachdem der Befehl ausgeführt wurde, meldet MySQL die Anzahl der importierten Zeilen. Um zu überprüfen, ob der Import erfolgreich war, fragen Sie die Tabelle ab, um ihren Inhalt anzuzeigen.

SELECT * FROM employees;

Die Ausgabe sollte die vier Datensätze aus der CSV-Datei anzeigen, die nun in Ihrer employees-Tabelle gespeichert sind.

+----+------------+-----------+---------------------------+-------------+
| id | first_name | last_name | email                     | department  |
+----+------------+-----------+---------------------------+-------------+
|  1 | John       | Doe       | john.doe@example.com      | Sales       |
|  2 | Jane       | Smith     | jane.smith@example.com    | Marketing   |
|  3 | Peter      | Jones     | peter.jones@example.com   | Engineering |
|  4 | Mary       | Brown     | mary.brown@example.com    | HR          |
+----+------------+-----------+---------------------------+-------------+
4 rows in set (0.00 sec)

Abfrageergebnisse in eine CSV-Datei exportieren

Das Exportieren von Daten ist genauso wichtig wie das Importieren. Möglicherweise müssen Sie Berichte erstellen, Daten mit anderen Systemen teilen oder Analysen in einem Tabellenkalkulationsprogramm durchführen. Die Anweisung SELECT ... INTO OUTFILE ermöglicht es Ihnen, das Ergebnis einer beliebigen Abfrage direkt in einer Datei zu speichern.

Fügen Sie zunächst zwei weitere Mitarbeiter zur Tabelle in Ihrer MySQL-Shell hinzu.

INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', 'alice.johnson@example.com', 'Sales'),
(6, 'Bob', 'Williams', 'bob.williams@example.com', 'Marketing');

Exportieren Sie nun die gesamte employees-Tabelle in eine neue Datei namens employees_export.csv. Stellen Sie zunächst sicher, dass Sie sich noch in der richtigen Datenbank befinden:

SELECT id, first_name, last_name, email, department
FROM company.employees
INTO OUTFILE '/tmp/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• SELECT ...: Dies ist eine Standardabfrage, die angibt, welche Daten exportiert werden sollen.
• INTO OUTFILE '/tmp/employees_export.csv': Gibt den vollständigen Pfad für die Ausgabedatei an. Aus Sicherheitsgründen verlangt MySQL, dass diese Datei noch nicht existiert.
• FIELDS TERMINATED BY ',': Trennt Felder durch ein Komma.
• ENCLOSED BY '"': Schließt jeden Feldwert in doppelte Anführungszeichen ein, was ein gängiges CSV-Format ist.
• LINES TERMINATED BY '\n': Beendet jede Zeile mit einem Zeilenumbruchzeichen.

Nachdem Sie den Befehl ausgeführt haben, wechseln Sie zu Ihrem anderen Terminal-Tab (oder öffnen Sie einen neuen) und zeigen Sie den Inhalt der neu erstellten Datei an.

cat /tmp/employees_export.csv

Sie werden alle sechs Zeilen aus Ihrer Tabelle sehen, formatiert als CSV-Datei.

"1","John","Doe","john.doe@example.com","Sales"
"2","Jane","Smith","jane.smith@example.com","Marketing"
"3","Peter","Jones","peter.jones@example.com","Engineering"
"4","Mary","Brown","mary.brown@example.com","HR"
"5","Alice","Johnson","alice.johnson@example.com","Sales"
"6","Bob","Williams","bob.williams@example.com","Marketing"

Importierte Daten validieren

Nach dem Import von Daten ist es entscheidend, diese zu validieren, um Qualität und Integrität sicherzustellen. Reale Daten sind oft unsauber und enthalten Fehler, fehlende Werte oder falsche Formate. Dieser Schritt zeigt Ihnen, wie Sie einfache SQL-Abfragen verwenden können, um häufige Probleme zu finden.

Das Setup-Skript hat employees_validation.csv erstellt, das eine ungültige E-Mail-Adresse und einen fehlenden Abteilungsdatensatz enthält. Leeren Sie zunächst die employees-Tabelle in Ihrer MySQL-Shell.

TRUNCATE TABLE employees;

Importieren Sie nun die Validierungsdatei.

LOAD DATA INFILE '/tmp/employees_validation.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Nachdem die "schmutzigen" Daten geladen wurden, führen wir einige Validierungsprüfungen durch.

1. Ungültige E-Mail-Formate finden

Eine sehr grundlegende Prüfung auf eine gültige E-Mail-Adresse besteht darin, zu sehen, ob sie ein @-Symbol und ein .-Symbol enthält. Wir können NOT LIKE verwenden, um Zeilen zu finden, die diese Prüfung nicht bestehen.

SELECT * FROM employees WHERE email NOT LIKE '%@%.%';

Diese Abfrage findet die Zeile, in der die E-Mail invalid_email lautet, da ihr die erforderlichen Symbole fehlen.

+----+------------+-----------+---------------+------------+
| id | first_name | last_name | email         | department |
+----+------------+-----------+---------------+------------+
|  3 | Invalid    | Email     | invalid_email | Sales      |
+----+------------+-----------+---------------+------------+
1 row in set (0.00 sec)

2. Fehlende Abteilungen finden

Sie können Zeilen finden, in denen ein Wert fehlt, indem Sie auf eine leere Zeichenkette '' prüfen.

SELECT * FROM employees WHERE department = '';

Diese Abfrage findet die Zeile, in der die Abteilung in der CSV-Datei leer gelassen wurde.

+----+------------+------------+--------------------------------+------------+
| id | first_name | last_name  | email                          | department |
+----+------------+------------+--------------------------------+------------+
|  4 | Missing    | Department | missing.department@example.com |            |
+----+------------+------------+--------------------------------+------------+
1 row in set (0.00 sec)

Diese einfachen Abfragen sind leistungsstarke Werkzeuge für eine erste Datenqualitätsprüfung. Nachdem Sie problematische Zeilen identifiziert haben, können Sie entscheiden, ob Sie diese mit UPDATE-Anweisungen korrigieren oder mit DELETE entfernen möchten.

Sie haben das Lab nun abgeschlossen. Sie können die MySQL-Shell verlassen.

exit

Zusammenfassung

In diesem Lab haben Sie die grundlegenden Operationen für das Verschieben von Daten in und aus einer MySQL-Datenbank gelernt. Sie haben mit der Einrichtung einer geeigneten Datenbankumgebung mit einer neuen Datenbank und Tabelle begonnen. Anschließend haben Sie den Befehl LOAD DATA INFILE verwendet, um Daten effizient aus einer CSV-Datei zu importieren.

Als Nächstes haben Sie das Exportieren von Daten aus einer Tabelle in eine neue CSV-Datei mit der Anweisung SELECT ... INTO OUTFILE geübt, eine gängige Aufgabe für Berichterstattung und Datenaustausch. Schließlich haben Sie gelernt, wie Sie grundlegende Datenvalidierungen mit SQL-Abfragen durchführen, um nach einem Import auf Formatierungsfehler und fehlende Werte zu prüfen. Diese Fähigkeiten sind für jeden Entwickler oder Administrator, der mit MySQL arbeitet, unerlässlich.