MySQLをインストールしたはいいけれど、
今回は、
デモンストレーション環境について
この回ではMySQLのインストールについては触れません。
この回で利用している環境は、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ステートメントの不思議な構文を使ったりしていますが、
また、
サンプルデータその1、ls
さて、lsコマンドの出力結果をMySQLに入れてみます。
yumコマンドで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でファイルサイズの大きな順番に並べてみるとか、
サンプルデータその2、vmstat
続いて、vmstatコマンドの出力結果をMySQLに入れてみます。
デモンストレーション環境がCentOS 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コマンドの出力結果を更に長くなったワンライナーでゴニョゴニョしています
$ 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カラムに記録された年月日時分秒がプライマリーキーで、
折角なので、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コマンドの出力結果はGROUP BYを使用した集約関数でしょうか）
サンプルデータその3、KEN_ALL.CSV
みなさんはKEN_
KEN_
$ 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_iconvコマンドを使用して文字コードをUTF-8に変更したものを作っておきます。半角カナの有無に関わらず、
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;
データの意味は郵便番号データの説明 - 日本郵便に記載されています。とりあえず、
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ステートメントは、yumコマンドでインストールしたまま何の編集もしていないため、
テーブルのカラム数とCSVファイルのカラム数が合わなくなったため、LOAD DATA INFILEステートメントの中でカラム数の差異を吸収しています@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 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十条西（１～４丁目） | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | | 0010011 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十一条西（１～４丁目） | ｷﾀ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 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十条西（１～４丁目） | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | | 0010011 | 北海道 | ﾎｯｶｲﾄﾞｳ | 札幌市北区 | ｻｯﾎﾟﾛｼｷﾀｸ | 北十一条西（１～４丁目） | ｷﾀ11ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ) | +----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
たとえばこの通り、prefectureカラムとprefecture_カラムは1対1で対応していますので、JOINで結合することで同じ結果に戻すことができるので、JOINの練習ができます。
ちなみに、prefecture_テーブルを切り出したのと同じように読み仮名だけを切り出していくと、town_テーブルを作ろうとしたところでcityとcity_はたまたま衝突するものがなかっただけでプライマリーキーを変更しておくべきか……など、
更に郵便番号を1番多く持っている都道府県はどこか、JOINとGROUP BYやサブクエリーを駆使した問い合わせ、
更なるサンプルデータを求めて
3つのサンプルデータを紹介してきましたが、
MySQLの勉強のためのサンプルデータと考えた場合、JOIN、GROUP BYを使って縦横無尽にデータを抽出することですので、
この3つを満たすというとやはりリソース情報になるのかなと思っていますが、