今回は MariaDB のテーブルに CSV ファイル経由でデータを読み込む方法について。 ちょくちょくやり方を調べている気がするのでメモしておく。
使った環境は次のとおり。
$ sw_vers ProductName: macOS ProductVersion: 12.1 BuildVersion: 21C52 $ uname -rm 21.2.0 arm64 $ mysql --version mysql Ver 15.1 Distrib 10.6.4-MariaDB, for osx10.17 (arm64) using readline 5.1
もくじ
下準備
あらかじめ Homebrew を使って MariaDB をインストールする。 サンプルデータをダウンロードするために wget も入れておく。
$ brew install mariadb wget
インストールできたら MariaDB のサービスを開始する。
本来は mysql_secure_installation を実行して作業した方が望ましいけど、今回は省略する。
$ brew services start mariadb $ brew services list Name Status User File mariadb started amedama ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
サンプルデータとして、Seaborn が利用している taxis データセットを使う。 これはタクシーの利用情報に関するデータセットになっている。
$ wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/taxis.csv
あらかじめ example という名前でデータベースを作っておく。
$ mysql -vvv -e "CREATE DATABASE IF NOT EXISTS example" -------------- CREATE DATABASE IF NOT EXISTS example -------------- Query OK, 1 row affected (0.000 sec) Bye
そして、taxis データセットに適合する形でテーブルを用意する。
$ cat << 'EOF' | mysql -vvv -D example CREATE TABLE IF NOT EXISTS taxis ( pickup DATETIME, dropoff DATETIME, passengers INT, distance FLOAT, fare FLOAT, tip FLOAT, tolls FLOAT, total FLOAT, color TEXT, payment TEXT, pickup_zone TEXT, dropoff_zone TEXT, pickup_borough TEXT, dropoff_borough TEXT ); EOF -------------- CREATE TABLE IF NOT EXISTS taxis ( pickup DATETIME, dropoff DATETIME, passengers INT, distance FLOAT, fare FLOAT, tip FLOAT, tolls FLOAT, total FLOAT, color TEXT, payment TEXT, pickup_zone TEXT, dropoff_zone TEXT, pickup_borough TEXT, dropoff_borough TEXT ) -------------- Query OK, 0 rows affected (0.097 sec) Bye
CSV ファイルからデータを読み込む
さて、ここからが今回の本題になる。
CSV ファイルからデータを読み込むときは LOAD DATA LOCAL INFILE でファイルパスを指定する。
また、フィールドの区切り文字などを同時に指定している。
そして、サンプルファイルは最初にヘッダの行が含まれるので IGNORE 1 LINES で最初の 1 行を読み飛ばしている。
$ cat << 'EOF' | mysql -vvv -D example LOAD DATA LOCAL INFILE "taxis.csv" INTO TABLE taxis FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; EOF -------------- LOAD DATA LOCAL INFILE "taxis.csv" INTO TABLE taxis FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES -------------- Query OK, 6433 rows affected (0.054 sec) Records: 6433 Deleted: 0 Skipped: 0 Warnings: 0 Bye
確認すると、ちゃんとテーブルにデータが読み込まれていることがわかる。
$ mysql -vvv -D example -e "SELECT * FROM taxis LIMIT 5" -------------- SELECT * FROM taxis LIMIT 5 -------------- +---------------------+---------------------+------------+----------+------+------+-------+-------+--------+-------------+-----------------------+-----------------------+----------------+-----------------+ | pickup | dropoff | passengers | distance | fare | tip | tolls | total | color | payment | pickup_zone | dropoff_zone | pickup_borough | dropoff_borough | +---------------------+---------------------+------------+----------+------+------+-------+-------+--------+-------------+-----------------------+-----------------------+----------------+-----------------+ | 2019-03-23 20:21:09 | 2019-03-23 20:27:24 | 1 | 1.6 | 7 | 2.15 | 0 | 12.95 | yellow | credit card | Lenox Hill West | UN/Turtle Bay South | Manhattan | Manhattan | | 2019-03-04 16:11:55 | 2019-03-04 16:19:00 | 1 | 0.79 | 5 | 0 | 0 | 9.3 | yellow | cash | Upper West Side South | Upper West Side South | Manhattan | Manhattan | | 2019-03-27 17:53:01 | 2019-03-27 18:00:25 | 1 | 1.37 | 7.5 | 2.36 | 0 | 14.16 | yellow | credit card | Alphabet City | West Village | Manhattan | Manhattan | | 2019-03-10 01:23:59 | 2019-03-10 01:49:51 | 1 | 7.7 | 27 | 6.15 | 0 | 36.95 | yellow | credit card | Hudson Sq | Yorkville West | Manhattan | Manhattan | | 2019-03-30 13:27:42 | 2019-03-30 13:37:14 | 3 | 2.16 | 9 | 1.1 | 0 | 13.4 | yellow | credit card | Midtown East | Yorkville West | Manhattan | Manhattan | +---------------------+---------------------+------------+----------+------+------+-------+-------+--------+-------------+-----------------------+-----------------------+----------------+-----------------+ 5 rows in set (0.001 sec) Bye
いじょう。
