はじめに
この実験では、MySQL データベースへのデータのインポートとエクスポートの基本的なスキルを習得します。CSV（Comma-Separated Values）ファイルからテーブルへデータをロードするために、高速かつ効率的なバルクデータ挿入方法である LOAD DATA INFILE コマンドを使用する練習を行います。
また、その逆のプロセス、つまりテーブルから新しい CSV ファイルへデータをエクスポートする方法も学びます。さらに、この実験ではインポート後の基本的なデータ検証チェックを行い、データの品質を確保する方法についても説明します。この実験を終える頃には、MySQL との間でデータを効率的にやり取りできるようになっているでしょう。
データベースとテーブルの準備
データをインポートする前に、そのデータを格納するための場所が必要です。これには、データを格納するデータベースと、インポートしようとしているデータと一致する構造を持つテーブルを作成することが含まれます。
まず、デスクトップからターミナルを開きます。
root ユーザーとして MySQL サーバーに接続します。この実験環境では、パスワードなしで接続するために sudo を使用できます。
sudo mysql -u root
接続すると、MySQL プロンプト (mysql>) が表示され、データベースサーバーと直接対話していることを示します。
次に、company という名前の新しいデータベースを作成します。IF NOT EXISTS 句は、データベースが既に作成されている場合にエラーを防ぐための良い習慣です。
CREATE DATABASE IF NOT EXISTS company;
次に、新しく作成したデータベースに切り替えて、それ以降のすべてのコマンドがそのデータベースに適用されるようにします。
USE company;
最後に、従業員データを格納するための employees という名前のテーブルを作成します。テーブル構造は、後でインポートする CSV ファイルの列と一致している必要があります。
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:id列を整数型および主キーとして定義します。これは、各値が一意でなければならないことを意味します。VARCHAR(50): 最大 50 文字の可変長文字列を格納できる列を定義します。
以下のコマンドを実行して、テーブルが正常に作成されたことを確認できます。
SHOW TABLES;
出力に employees テーブルが表示されるはずです。
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
+-------------------+
1 row in set (0.00 sec)
次のステップで使用するため、MySQL シェルを開いたままにしておきます。
CSV ファイルからのデータインポート
データベースとテーブルの準備ができたので、外部ファイルからデータをインポートできます。LOAD DATA INFILE ステートメントは、テキストファイルからテーブルへデータを一括ロードするための非常に効率的な方法です。
この実験のセットアップスクリプトは、既に /tmp ディレクトリに employees.csv という名前のファイルを作成しています。インポートする前に、ファイルのコンテンツを確認することをお勧めします。
重要: 現在のターミナルは MySQL シェルを実行しているため、このコマンドには新しいターミナルタブを開く必要があります。ターミナルウィンドウの + アイコンをクリックして、新しいタブを開きます。新しいターミナルで、以下を実行します。
cat /tmp/employees.csv
出力には、カンマ区切りの 4 行のデータが表示されます。
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
次に、MySQL シェル (mysql>) が表示されている元のターミナルタブに戻ります。LOAD DATA INFILE コマンドを使用してファイルをインポートします。
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
このコマンドの内訳を見てみましょう。
LOAD DATA INFILE '/tmp/employees.csv': ソースファイルへの絶対パスを指定します。INTO TABLE employees: データのエクスポート先テーブルを指定します。FIELDS TERMINATED BY ',': 各行のデータフィールド（列）がカンマで区切られていることを MySQL に伝えます。LINES TERMINATED BY '\n': ファイルの各改行が新しい行を表すことを MySQL に伝えます。
コマンドが実行されると、MySQL はインポートされた行数を報告します。インポートが成功したことを確認するには、テーブルをクエリしてそのコンテンツを表示します。
SELECT * FROM employees;
出力には、CSV ファイルの 4 つのレコードが表示され、これらは現在 employees テーブルに格納されています。
+----+------------+-----------+---------------------------+-------------+
| 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)
クエリ結果を CSV ファイルにエクスポート
データのインポートと同様に、エクスポートも重要です。レポートを作成したり、他のシステムとデータを共有したり、スプレッドシートプログラムで分析を実行したりする必要がある場合があります。SELECT ... INTO OUTFILE ステートメントを使用すると、任意のクエリの結果を直接ファイルに保存できます。
まず、MySQL シェルでテーブルにさらに 2 人の従業員を追加しましょう。
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');
次に、employees テーブル全体を employees_export.csv という名前の新しいファイルにエクスポートします。まず、正しいデータベースにいることを確認してください。
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 ...: エクスポートするデータを指定する標準的なクエリです。INTO OUTFILE '/tmp/employees_export.csv': 出力ファイルのフルパスを指定します。セキュリティのため、MySQL はこのファイルが既に存在しないことを要求します。FIELDS TERMINATED BY ',': フィールドをカンマで区切ります。ENCLOSED BY '"': 各フィールド値をダブルクォートで囲みます。これは一般的な CSV フォーマットです。LINES TERMINATED BY '\n': 各行を改行文字で終了します。
コマンドを実行した後、別のターミナルタブに切り替えるか（または新しいタブを開く）、新しく作成されたファイルのコンテンツを表示します。
cat /tmp/employees_export.csv
テーブルの 6 行すべてが、CSV ファイルとしてフォーマットされて表示されます。
"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"
インポートデータの検証
データをインポートした後、品質と整合性を確保するために検証することが非常に重要です。実際のデータはしばしば乱雑で、エラー、欠損値、または不正なフォーマットを含んでいます。このステップでは、簡単な SQL クエリを使用して一般的な問題を見つける方法を示します。
セットアップスクリプトは employees_validation.csv を作成しました。これには、無効なメールアドレスと欠損している部署の値が含まれています。まず、MySQL シェルで employees テーブルをクリアします。
TRUNCATE TABLE employees;
次に、検証ファイルをインポートします。
LOAD DATA INFILE '/tmp/employees_validation.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
「ダーティ」データをロードしたら、いくつかの検証チェックを実行しましょう。
1. 無効なメールフォーマットの検索
有効なメールアドレスの非常に基本的なチェックは、@ シンボルと . シンボルが含まれているかどうかを確認することです。NOT LIKE を使用して、このチェックに失敗した行を見つけることができます。
SELECT * FROM employees WHERE email NOT LIKE '%@%.%';
このクエリは、メールアドレスが invalid_email である行を見つけます。これは、必要なシンボルが欠けているためです。
+----+------------+-----------+---------------+------------+
| id | first_name | last_name | email | department |
+----+------------+-----------+---------------+------------+
| 3 | Invalid | Email | invalid_email | Sales |
+----+------------+-----------+---------------+------------+
1 row in set (0.00 sec)
2. 欠損している部署の検索
空文字列 '' をチェックすることで、値が欠損している行を見つけることができます。
SELECT * FROM employees WHERE department = '';
このクエリは、CSV ファイルで部署が空白のままだった行を見つけます。
+----+------------+------------+--------------------------------+------------+
| id | first_name | last_name | email | department |
+----+------------+------------+--------------------------------+------------+
| 4 | Missing | Department | missing.department@example.com | |
+----+------------+------------+--------------------------------+------------+
1 row in set (0.00 sec)
これらの簡単なクエリは、最初のデータ品質チェックのための強力なツールです。問題のある行を特定した後、UPDATE ステートメントで修正するか、DELETE で削除するかを決定できます。
これで実験は完了です。MySQL シェルを終了できます。
exit
まとめ
この実験では、MySQL データベースへのデータの移動とデータベースからのデータのエクスポートに関する基本的な操作を学びました。まず、新しいデータベースとテーブルを使用して適切なデータベース環境をセットアップしました。次に、LOAD DATA INFILE コマンドを使用して CSV ファイルから効率的にデータをインポートしました。
次に、レポート作成やデータ共有で一般的なタスクである SELECT ... INTO OUTFILE ステートメントを使用して、テーブルから新しい CSV ファイルにデータをエクスポートする練習をしました。最後に、インポート後のフォーマットエラーや欠損値を確認するために、SQL クエリを使用した基本的なデータ検証の方法を学びました。これらのスキルは、MySQL を扱うすべての開発者または管理者にとって不可欠です。
