MySQL道普請便り

第2回MySQLにはじめてのデータを入れてみる

MySQLをインストールしたはいいけれど、何のデータを入れようか思い付かない……ランダムデータだと練習する気にならなくて……公式のサンプルデータベースは英語で面白くない……あなたのMySQL、お漬物になっていませんか?

今回は、勉強用にMySQLに放り込むための(誰にでも手に入りそうで、意味がそれなりにわかりそうな)データをいくつか紹介します。飽くまでSQLの勉強用としてのデータですので、ストレステストに使うような類のデータではありませんので悪しからず。

デモンストレーション環境について

この回ではMySQLのインストールについては触れません。

この回で利用している環境は、CentOS 6.6にMySQL Yum Repositoryを導入し、執筆時点で最新のmysql-community-server-5.6.26-2.el6.x86_64をyumコマンドで以下の通りインストールしてあります。依存関係でmysqlコマンドなども一緒にインストールされている前提でいます。

$ sudo rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum install mysql-community-server

データの投入のためにワンライナーを使ったりLOAD DATA INFILEステートメントの不思議な構文を使ったりしていますが、それらを覚えたり気にしたりする必要はありません。⁠こんなサンプルデータはこんなクエリーの練習に使えるよ」という紹介ですので、興味があればMySQL :: MySQL 5.6 リファレンスマニュアルから詳細を探してみてください。

また、投入しているデータは「身近なものをMySQLにロードし、SQLを試す」ためのデータですので、⁠そのデータを本当にMySQLに投入するのが良いのか」などはあまり考えていません(あまり考えていませんが、テーブル設計は最後の2つを除いて一応考えてあります⁠⁠。

サンプルデータその1、ls

さて、それではまず手始めに誰でもほぼ手に入れられそうなデータ……ということで、lsコマンドの出力結果をMySQLに入れてみます。

yumコマンドでMySQLをインストールした環境なので、MySQL関連のコマンドは/usr/binに入っています。この名前とファイルサイズをMySQLに入れてみましょう。繰り返しになりますが、ワンライナーはあまり気にしないでください。

mysql> CREATE DATABASE ls;
mysql> CREATE TABLE ls.ls(name varchar(255) NOT NULL, size int unsigned NOT NULL, PRIMARY KEY(name));
$ ls -l /usr/bin/my* | awk '{print "INSERT INTO ls.ls VALUES ('\''"$NF"'\'', "$5");"}' | mysql -u root
mysql> SELECT * FROM ls.ls;

ファイル名とファイルサイズを属性に持つテーブルにデータが入りました。WHERE name LIKE '%myisam%'でMyISAM関連のユーティリティーがあるなあ、とか、ORDER BY size DESCでファイルサイズの大きな順番に並べてみるとか、簡単なWHEREとORDER BYの練習に使ってみてください。

サンプルデータその2、vmstat

続いて、誰でもほぼ手に入れられそうなデータで時系列を持つものがいいかな……ということで、vmstatコマンドの出力結果をMySQLに入れてみます。

デモンストレーション環境がCentOS 6.6なので、他のディストリビューションやバージョンでは少し違うかも知れませんが、テーブルはこんなものを用意します(カラム名は出力結果と合わせるのではなく、何となく意味が分かるようにつけてあります⁠⁠。

mysql> CREATE DATABASE vmstat;
mysql> CREATE TABLE vmstat.vmstat (
    ->   dt datetime NOT NULL,
    ->   running int unsigned NOT NULL,
    ->   blocking int unsigned NOT NULL,
    ->   swapped int unsigned NOT NULL,
    ->   free int unsigned NOT NULL,
    ->   buffer int unsigned NOT NULL,
    ->   cache int unsigned NOT NULL,
    ->   swap_in int unsigned NOT NULL,
    ->   swap_out int unsigned NOT NULL,
    ->   block_in int unsigned NOT NULL,
    ->   block_out int unsigned NOT NULL,
    ->   interapt int unsigned NOT NULL,
    ->   context_switch int unsigned NOT NULL,
    ->   cpu_user tinyint unsigned NOT NULL,
    ->   cpu_system tinyint unsigned NOT NULL,
    ->   cpu_idle tinyint unsigned NOT NULL,
    ->   cpu_iowait tinyint unsigned NOT NULL,
    ->   cpu_steal tinyint unsigned NOT NULL,
    ->   PRIMARY KEY(dt)
    -> );

ではデータを取ってみましょう。vmstatコマンドの出力結果を更に長くなったワンライナーでゴニョゴニョしています(本当は、こんなに長くなるくらいならPerlで書くのが筆者の好みですが⁠⁠。

$ vmstat -n 1 10 | awk '$1 !~ /^[a-z]/{print "INSERT INTO vmstat.vmstat VALUES ('\''"strftime("%Y/%m/%d %H:%M:%S", systime())"'\'', "$1", "$2", "$3", "$4", "$5", "$6", "$7", "$8", "$9", "$10", "$11", "$12", "$13", "$14", "$15", "$16", "$17");"}' | mysql -u root

10秒待つとプロンプトが返ってきますCtrl+Cで止めてしまうとデータが投入される前にプロセスが終了してしまうので、待ってあげてください⁠⁠。早速中身を確認してみましょう。

$ mysql -u root
mysql> use vmstat
mysql> SELECT * FROM vmstat LIMIT 1\G
*************************** 1. row ***************************
            dt: 2015-09-07 13:51:21
       running: 2
      blocking: 0
       swapped: 520252
          free: 90520
        buffer: 29632
         cache: 139960
       swap_in: 0
      swap_out: 0
      block_in: 18
     block_out: 41
      interapt: 16
context_switch: 12
      cpu_user: 0
    cpu_system: 0
      cpu_idle: 100
    cpu_iowait: 0
     cpu_steal: 0
1 row in set (0.00 sec)

dtカラムに記録された年月日時分秒がプライマリーキーで、出力結果のそれぞれの値がそれぞれのカラムに格納されています。

折角なので、他のターミナルでI/O負荷をかけるコマンドを叩いて、それを同じワンライナーでMySQLに記録してみました。GROUP BYを使って分単位で読み込みと書き込みの量を見てみます。

mysql> SELECT DATE_FORMAT(dt, '%H:%i') AS time, SUM(block_in), SUM(block_out) FROM vmstat GROUP BY time;
+-------+---------------+----------------+
| time  | SUM(block_in) | SUM(block_out) |
+-------+---------------+----------------+
| 13:51 |            18 |             89 |
| 13:54 |          4550 |            153 |
| 13:55 |         13758 |        6441349 |
| 13:56 |         16464 |       15020068 |
+-------+---------------+----------------+
4 rows in set (0.01 sec)

ちゃんと取得できているようです。

vmstatコマンドの出力結果は(標準的なLinux環境であれば)マシンが稼働している間中取得できますので、データの数を増やしていけば単一テーブルでSQL(主にGROUP BYを使用した集約関数でしょうか)の練習をするには充分身近ではないかと思います。

サンプルデータその3、KEN_ALL.CSV

みなさんはKEN_ALL.CSVというファイルをご存知でしょうか。日本郵便株式会社が公開している、CSV形式の日本全国の郵便番号が記録されたファイルです。好き嫌いはあるかも知れませんが、今度はこのファイルをMySQLのテーブルに読み込ませてみたいと思います。

KEN_ALL.CSVはこちらのページからダウンロードできます。⁠都道府県一覧」「全国一括」というものがそれです。

図1 ダウンロードページ
図1 ダウンロードページ
$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
$ unzip ken_all.zip
$ iconv -f Shift_JIS -t UTF-8 KEN_ALL.CSV > /tmp/KEN_ALL_UTF8.CSV

このファイルは非常に残念ながら文字コードがShift_JISであり、半角カナも使われている素敵仕様ですので、iconvコマンドを使用して文字コードをUTF-8に変更したものを作っておきます。半角カナの有無に関わらず、日本語が含まれている場合は文字コードはUTF-8を推奨します(MySQL上では文字コード名がutf8になります、また、utf8の拡張であるutf8mb4のほうが更にお勧めです⁠⁠。

mysql> CREATE DATABASE zipcode CHARACTER SET utf8mb4;
mysql> CREATE TABLE zipcode.zipcode(
    ->   code varchar(12) NOT NULL,
    ->   old_zipcode varchar(5) NOT NULL,
    ->   zip_code varchar(7) NOT NULL,
    ->   prefecture_kana varchar(255) NOT NULL,
    ->   city_kana varchar(255) NOT NULL,
    ->   town_kana varchar(255) NOT NULL,
    ->   prefecture varchar(128) NOT NULL,
    ->   city varchar(128) NOT NULL,
    ->   town varchar(128) NOT NULL
    -> ) DEFAULT CHARACTER SET= utf8mb4;

データの意味は郵便番号データの説明 - 日本郵便に記載されています。とりあえず、10カラム目以降は見ていて面白いサンプルデータとは言えないので省略してしまいます。

mysql> SET character_set_server= utf8mb4;
mysql> SET NAMES utf8mb4;
mysql> use zipcode
mysql> LOAD DATA INFILE '/tmp/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy);
Query OK, 123823 rows affected (1.54 sec)
Records: 123823  Deleted: 0  Skipped: 0  Warnings: 0

2つのSETステートメントは、サーバサイドの文字コードとクライアントサイドの文字コードを合わせています。my.cnfなどで全てきれいに統一されていればいいのですが、ここではyumコマンドでインストールしたまま何の編集もしていないため、データベースとテーブルをutf8mb4で作成したにも関わらず、これらの設定を通さないとUTF-8の文字が化けてしまいます。クライアント側、サーバ側、データベース、テーブル、4つの文字コードは原則同じものに揃えましょう。

テーブルのカラム数とCSVファイルのカラム数が合わなくなったため、LOAD DATA INFILEステートメントの中でカラム数の差異を吸収しています(CSVにだけ存在する10~15カラム目は@dummyというユーザー変数に格納され、テーブルにはロードされません。この記述をしない場合、カラムの数が合わないという内容のワーニングまたはエラーが表示されます⁠⁠。これもこんな構文を憶える必要は全くないと思いますので、そういうもんだと思ってデータをロードしてあげてください。

このCSVファイルに触ったことのある方はご存知かと思いますが、このデータは全く正規化されておらず、テーブル分割の練習をするのにとても丁度良いサンプルになります。

mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode ORDER BY zip_code LIMIT 3;
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| zip_code | prefecture | prefecture_kana       | city            | city_kana                   | town                                 | town_kana                                     |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| 0010000  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 以下に掲載がない場合                 | イカニケイサイガナイバアイ                               |
| 0010010  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 北十条西(1~4丁目)               | キタ10ジョウニシ(1-4チョウメ)                           |
| 0010011  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 北十一条西(1~4丁目)             | キタ11ジョウニシ(1-4チョウメ)                           |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+

mysql> CREATE TABLE zipcode.prefecture_kana(
    ->   prefecture varchar(128) NOT NULL,
    ->   prefecture_kana varchar(255) NOT NULL,
    ->   PRIMARY KEY(prefecture)
    -> ) DEFAULT CHARACTER SET= utf8mb4;

mysql> INSERT INTO zipcode.prefecture_kana SELECT DISTINCT prefecture, prefecture_kana FROM zipcode;

mysql> ALTER TABLE zipcode DROP COLUMN prefecture_kana, ADD CONSTRAINT FOREIGN KEY fkey_prefecture (prefecture) REFERENCES prefecture_kana(prefecture);

mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode NATURAL JOIN zipcode.prefecture_kana ORDER BY zip_code LIMIT 3;
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| zip_code | prefecture | prefecture_kana       | city            | city_kana                   | town                                 | town_kana                                     |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| 0010000  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 以下に掲載がない場合                 | イカニケイサイガナイバアイ                               |
| 0010010  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 北十条西(1~4丁目)               | キタ10ジョウニシ(1-4チョウメ)                           |
| 0010011  | 北海道     | ホッカイドウ               | 札幌市北区      | サッポロシキタク                   | 北十一条西(1~4丁目)             | キタ11ジョウニシ(1-4チョウメ)                           |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+

たとえばこの通り、prefectureカラムとprefecture_kanaカラムは1対1で対応していますので、簡単に外部テーブルに切り出すことができます。切り離したテーブルはJOINで結合することで同じ結果に戻すことができるので、JOINの練習ができます。

ちなみに、prefecture_kanaテーブルを切り出したのと同じように読み仮名だけを切り出していくと、town_kanaテーブルを作ろうとしたところで「旭町」⁠あさひまち、あさひちょう、の両方があります)がユニーク制約に引っかかって、ぐぬぬぬぬプライマリーキーは漢字と読み両方にしないとダメか……citycity_kanaはたまたま衝突するものがなかっただけでプライマリーキーを変更しておくべきか……など、テーブル設計の練習のようなことができたりもします。

更に郵便番号を1番多く持っている都道府県はどこか、第20位はどの都道府県か(MySQLにRANK関数はありませんので、なにがしかの方法で上手くやる必要がありますが、SQLだけで全て終わらせる必要はありません⁠⁠。あるいは「サ」で始まる市町村を1番多く持つ都道府県が持つ郵便番号のうち最小のものはどれか……などなど、JOINGROUP BYやサブクエリーを駆使した問い合わせ、また、それらに上手く効くインデックスを考える……など、⁠データが多少身近なものなので)楽しみ方は色々あります。

更なるサンプルデータを求めて

3つのサンプルデータを紹介してきましたが、いずれもMySQLの操作に慣れてくると飽きが生じます(vmstat以外は生成され続ける類のものではありませんし、vmstatも単一のテーブルでしかないのであまり長い間楽しめるかというと今一つです⁠⁠。

MySQLの勉強のためのサンプルデータと考えた場合、⁠そのマシンだけで生成できること(マシンを起動しているだけで手に入るもの⁠⁠時系列的に)継続的に取り続けられること」⁠他の情報とのリレーションシップを持つもの」がお勧めです。1つ目は、外部要因が関係してくると手間がかかるからです(逆に言うと、それを取得するのに必要な手間が充分小さければ、それも適していると思います⁠⁠。2つ目は、データが積み重なることで状況が変わってくることを体験するためです。100行のテーブルならどんなクエリーでもある程度の時間で返ってきますが、10億行のテーブルでは1バイトのソートが致命的な遅さをもたらすこともあります。3つ目は、やはりリレーショナルデータベースの醍醐味はJOINサブクエリー、GROUP BYを使って縦横無尽にデータを抽出することですので、⁠この時間にCPUがスパイクしている、他のリソースの様子はどうだった?」⁠MySQLのテンポラリーテーブルの作成回数とブロック単位のI/Oの回数に相関はあるのか?」など、日本語で命題を定義し、それをSQLに落としていく、という楽しみのためです。

この3つを満たすというとやはりリソース情報になるのかなと思っていますが、何かもっと面白そうな題材があったら教えてください。

おすすめ記事

記事・ニュース一覧