e-Statの統計データをデータベース（MariaDB）に取り込む
データセットをダウンロード
政府統計のポータルサイトe-StatからCSV形式のデータをダウンロードし、それをMaridDBに取り込んで利用してみることにした。
- e-Statにアクセスし、総合案内メニューの「統計データを探す」-「データベース」-「分野から探す」-[人口・世帯」を選んだ。
- 「政府統計一覧」の中から「人口推計」を選んだ。
- 「人口推計」の一覧から例えば最新のものを選んだ。この時点では「2019年10月1日現在人口推計」だった。
- 「データセット一覧」の中から「都道府県別人口の割合－総人口」の「DB」を選んだ。
- 「ダウンロード」を押した。
- 「ダウンロード設定」の「ファイル形式」において「CSV形式(列指向形式)」にチェックを入れた。そして「ダウンロード」を押して更に「ダウンロード」を押し、CSVファイルをローカルディスク上にJapans_population_by_preference_and_gender.csvという名前で保存した。
ダウンロードしたCSVファイルの中身
e-StatからダウンロードしたCSVファイルの文字コードはShift-JIS（正式名称はMicrosoft Windows Codepage:932、略してCP932）だった。Leafpadでは問題なく表示できた。Mausepadでは開く際に文字コードの選択を求められたのでShift-JISを選んでOKボタンを押して開いた。
ダウンロードしたCSVファイルの内容は次のようになっていた。最初の3行のみを引用。
"tab_code","表章項目","cat01_code","男女別","cat02_code","人口","area_code","全国・都道府県","time_code","時間軸（年）","unit","value" "001","人口","000","男女計","001","総人口","00000","全国","2015000001","2015年","千人","127095" "001","人口","000","男女計","001","総人口","00000","全国","2016000001","2016年","千人","126933"出典：政府統計の総合窓口(e-Stat)（https://www.e-stat.go.jp/）から「人口推計 / 2019年10月1日現在」の「都道府県，男女別人口－総人口，日本人人口」より
最初の1行目は項目名。データベースのテーブルの列名（レコード名）に相当する。
各データは"（二重引用符）によって挟まれ、,（カンマ）によって区切られていた。
取り込み先のテーブルを作成
SQLスクリプトを記述したファイルを作成し、ダウンロードしたデータセットに基づいてこれらの列名を持つテーブルを定義してみた。そのスクリプト・ファイル名はcreate_table_popu_jp.sqlとし、/home/fooディレクトリに保存した。
# popu_jpテーブルを作成するSQLスクリプト # データベースを選ぶ USE mydatabase; # テーブルpopu_jpを作成 CREATE TABLE popu_jp ( tab_code CHAR(3), 表章項目 CHAR(2), cat01_code CHAR(3), 男女別 VARCHAR(3), cat02_code CHAR(3), 人口 VARCHAR(5), area_code CHAR(5), 全国都道府県 VARCHAR(4) NOT NULL, time_code VARCHAR(10), 時間軸（年） VARCHAR(5) NOT NULL, unit CHAR(2), value MEDIUMINT NOT NULL, # 複合主キーを定義 PRIMARY KEY(男女別,人口,全国都道府県,時間軸（年）) ); # 作成したテーブルを表示 SHOW TABLES;
このSQLスクリプトでは、USE構文で作業対象のデータベースを選び、CREATE TABLE構文で新しいテーブルとしてpopu_jpを定義し、テーブル一覧をSHOW TABLES構文で表示するようにした。
各列名はe-StatからダウンロードしたCSVファイルの1行目にほぼ対応しているが、短縮したものもある。データ型はその各列のデータの性質とその最大字数に対応するように設定した。
主キーとして「男女別」と「人口」と「全国・都道府県」と「時間軸（年）」との組み合わせを指定。これら4つの列（カラム）を組み合わせれば各々の行（レコード）を重複なく区別できるはず。複数の列（カラム）を組み合わせて定義した主キーのことを複合主キーと呼ぶらしい。
#に続く行はコメント。
「時間軸（年）」の年を挟む括弧は半角ではなく全角であることに要注意。半角にするとSQLの文法エラー(You have an error in your SQL syntax)になってしまう。
CSV形式のデータセットをMariaDBに取り込む
MariaDBサーバー（mysqlサービス）が停止している場合にはそれを稼働させる必要がある。その際にはオペレーティング・システムの管理者権限が必要。
systemctlコマンドの引数にis-active mysqlを指定して稼働中かどうか確かめ、稼働していなければstart mysqlを引数として指定して稼働させる。稼働中ならactiveと表示され、稼働中でなければinactiveと表示される。
$ su password: # systemct is-active mysql inactive # systemctl start mysql # exit
そうしておいてからmysqlシェルを起動する。ここでは以前に作成しておいたユーザーであるtestuserの権限で起動した。その際にパスワードも設定しておいたので-pが必要だった。mysqlシェル上では、以前に作成しておいたデータベースであるmydatabaseをUSE構文を用いて使用対象に指定。
$ mysql -u testuser -p Enter password: （中略） MariaDB [(none)]> USE mydatabase; （中略） Database changed MariaDB [mydatabase]>
sourceコマンドを用いてcreate_table_popu_jp.sqlスクリプトを実行した。このSQLスクリプトによってpopu_jpというテーブルが作成される。
MariaDB [mydatabase]> source /home/foo/create_table_popu_jp.sql Database changed Query OK, 0 rows affected (0.173 sec) +----------------------+ | Tables_in_mydatabase | +----------------------+ | persons | | popu_jp | +----------------------+ 2 rows in set (0.000 sec)
popu_jpというテーブルが新たに作成されたことが分かる。
myqlシェルが起動していない状態であっても、Linuxのコマンドライン上で入力リダイレクションを用いてSQLを実行することができる。
$ mysql < '/home/foo/create_table_popu_jp.sql' \ > -u testuser -p Enter password:
CSV形式のデータセットをテーブルへ読み込む
ダウンロードしておいたCSV形式のデータセットをデータベースのテーブルへ読み込むためのSQLスクリプトを作成した。そのファイル名はimport_csvfile_popu_jp.sqlとし、/home/fooディレクトリに保存した。
# CSV形式のデータ集合を読み込むSQLスクリプト # 作業対象とするデータベースを選ぶ USE mydatabase; # 読み込むCSVファイル名とそのパスを指定 LOAD DATA INFILE '/home/foo/Japans_population_by_prefecture_and_gender.csv' # 読み込み先となるテーブルを指定 INTO TABLE popu_jp # 各データがカンマで終わり二重引用符で囲まれている FIELDS TERMINATED BY ',' ENCLOSED BY '"' # 1行目を無視 IGNORE 1 LINES;
USE構文で使用対象とするデータベースを選ぶ。
データをファイルから読み込むにはLOAD DATA INFILE構文を用いる。読み込むCSVファイルの名前とそれが置かれているディスク上の場所を一重引用符で挟んで指定する。
INTO TABLE句には対象となるテーブル名を指定する。
FIELDS句には、データが何によって区切られているか(TERMINATED BY)、何によって挟まれているか(ENCLOSED BY)などを指定する。
ダウンロードしたCSVファイルの各データはカンマで区切られているのでTERMINATED BY ','と指定し、二重引用符で全てのデータが挟まれているのでENCLOSED BY '"'と指定した。全てのデータが二重引用符で挟まれているわけでない場合はENCLOSEDの前にOPTIONALLYが必要となる。
IGNORE句にはデータを読み込む際に無視する対象を指定する。ここでは1行目を示す1 LINESを指定した。なぜなら、ダウンロードしたCSVファイルの1行目にはデータではなくデータの項目名が記述されているので。
行頭に#（シャープ）があるのはコメント行。
mysqlシェルでこのSQLスクリプトを実行。
MariaDB [mydatabase]> source import_csvfile_popu_jp.sql Database changed Query OK, 1440 rows affected (0.541 sec) Records: 1440 Deleted: 0 Skipped: 0 Warnings: 0
うまくいけばエラーメッセージが表示されない。
SELECT構文でデータを選択して表示してみる。ここでは全ての行（レコード）を選択せずにWHERE句を指定して条件を付けた。「東京都」でかつ「2017年」に該当する行（レコード）だけが選択されて表示されるようにした。
MariaDB [mydatabase]> SELECT * FROM popu_jp
-> WHERE 全国都道府県 = '東京都'
-> AND 時間軸（年） = '2017年';
+----------+--------------+------------+-----------+------------+-----------------+-----------+--------------------+------------+--------------------+--------+-------+
| tab_code | 表章項目 | cat01_code | 男女別 | cat02_code | 人口 | area_code | 全国都道府県 | time_code | 時間軸（年） | unit | value |
+----------+--------------+------------+-----------+------------+-----------------+-----------+--------------------+------------+--------------------+--------+-------+
| 001 | 人口 | 002 | 女 | 002 | 日本人人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 6735 |
| 001 | 人口 | 002 | 女 | 001 | 総人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 6964 |
| 001 | 人口 | 001 | 男 | 002 | 日本人人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 6538 |
| 001 | 人口 | 001 | 男 | 001 | 総人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 6760 |
| 001 | 人口 | 000 | 男女計 | 002 | 日本人人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 13273 |
| 001 | 人口 | 000 | 男女計 | 001 | 総人口 | 13000 | 東京都 | 2017000001 | 2017年 | 千人 | 13724 |
+----------+--------------+------------+-----------+------------+-----------------+-----------+--------------------+------------+--------------------+--------+-------+
6 rows in set (0.002 sec)
文字コードのエラーが生じた場合
MariaDB [mydatabase]> source import_csvfile_popu_jp.sql Database changed ERROR 1366 (22007) at line 5 in file: 'import_csvfile_popu_jp.sql': Incorrect string value: '\x90l\x8C\xFB' for column `mydatabase`.`popu_jp`.`表章項目` at row 1
このようなエラーメッセージが表示されることがある。エラーはSQLスクリプトの5行目、CSVファイルを読み込むところで生じたらしい。エラーメッセージの意味は「誤った文字列の値だ」というようなもの。「表章項目」という列の日本語データが文字化けしてしまっているのが見て取れる。
この文字化けを解決する手っ取り早い手段の1つは、Shift_JISになっているCSVファイルの文字コードをUTF-8へ変換すること。オペレーティング・システムがLinuxディストロであるなら、例えばiconvコマンドを使ってそれを行うことができる。
iconvコマンドの書式は次のとおり。
iconv -f 変換前文字コード -t 変換後文字コード \ 変換元ファイル名 -o 変換後ファイル名
Japans_population_by_prefecture_and_gender.csvをJ_popu_sjis.csvというファイル名に変えて次のようにiconvコマンドを実行した。行末にある\はコマンドの改行後の継続を意味する。
$ iconv -f shift_jis -t utf-8 \ > /home/foo/J_popu_sjis.csv \ > -o /home/foo/Japans_population_by_prefecture_and_gender.csv
別の方法として例えばleafpadというエディタにCSVファイルを読み込み、新しい名前を付けて保存する際に文字コードとしてUTF-8を選ぶことでも変換することができる。
データ型に関するエラーが生じた場合
MariaDB [mydatabase]> source import_csvfile_popu_jp.sql Database changed ERROR 1406 (22001) at line 5 in file: 'import_csvfile_popu_jp.sql': Data too long for column '人口' at row 241
この場合にもSQLスクリプトの5行目でエラーが生じている。このエラーメッセージは「人口」という列の241行目のデータの長さがデータ型で定義された最長のサイズを超えていることを示している。
この問題を解決するためにはALTER TABLE構文とMODIFY句を用いてデータ型を変更する必要がある。その書式は次のようになる。SQL構文では文末に;（セミコロン）が必要なことを忘れずに。
ALTER TABLE テーブル名 MODIFY 列名 データ型(最長サイズ);
具体的には例えば次のように用いる。
MariaDB [mydatabase]> ALTER TABLE popu_jp
-> MODIFY 人口 VARCHAR(5);
Query OK, 0 rows affected (0.040 sec)
Records: 0 Duplicates: 0 Warnings: 0
テーブルの定義を変更する処理はデータが挿入された後では制約されることに要注意。
主キーに関するエラーが生じた場合
MariaDB [mydatabase]> source /home/foo/import_csvfile_popu_jp.sql Database changed ERROR 1062 (23000) at line 5 in file: '/home/foo/import_csvfile_popu_jp.sql': Duplicate entry '全国-2015年' for key 'PRIMARY'
この場合もまたSQLスクリプトの5行目でエラーが生じたことを示している。主キーが不適切に定義されていたために主キーのデータに重複が起こり、したがって主キーの一意性を保てず、処理が中断されてしまったらしい。
このエラーを解決するためには主キーの定義を変更する必要がある。
DROP PRIMARY KEY構文によって主キーを削除することができ、ADD PRIMARY KEY構文によって主キーを追加することができる。
DROP PRIMARY KEY構文を用いて既存の主キーをまず削除する。
MariaDB [mydatabase]> ALTER TABLE popu_jp
-> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.635 sec)
Records: 0 Duplicates: 0 Warnings: 0
次に、ADD PRIMARY KEY構文を用いて新たに主キーを定義し直す。
MariaDB [mydatabase]> ALTER TABLE popu_jp
-> ADD PRIMARY KEY (男女別,人口,全国都道府県,時間軸（年）);
Query OK, 0 rows affected (0.484 sec)
Records: 0 Duplicates: 0 Warnings: 0
コメント
コメントを投稿