TL;DR
東京都 新型コロナウイルス陽性患者発表詳細 - 東京都_新型コロナウイルス陽性患者発表詳細 - 東京都オープンデータカタログサイト のCSVをLOAD DATA LOCAL INFILEでMySQLに突っ込む
この記事中に出てくるCSV由来のデータは 東京都オープンデータカタログサイトによりCC BY 4.0によりライセンスされています。
取り敢えずまずはCSVをダウンロードして、突っ込むテーブルを考える。
$ wget https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv
$ head 130001_tokyo_covid19_patients.csv
No,全国地方公共団体コード,都道府県名,市区町村名,公表_年月日,発症_年月日,確定_年月日,患者_居住地,患者_年代,患者_性別,患者_職業,患者_状態,患者_症状,患者_渡航歴の有無フラグ,患者_接触歴の有無フラグ,備考,退院済フラグ
1,130001,東京都,,2020-01-24,,,湖北省武漢市,40代,男性,,,,,,,1
2,130001,東京都,,2020-01-25,,,湖北省武漢市,30代,女性,,,,,,,1
3,130001,東京都,,2020-01-30,,,湖南省長沙市,30代,女性,,,,,,,1
4,130001,東京都,,2020-02-13,,,都内,70代,男性,,,,,,,1
5,130001,東京都,,2020-02-14,,,都内,50代,女性,,,,,,,1
6,130001,東京都,,2020-02-14,,,都内,70代,男性,,,,,,,1
7,130001,東京都,,2020-02-15,,,都内,80代,男性,,,,,,,1
8,130001,東京都,,2020-02-15,,,都内,50代,女性,,,,,,,1
9,130001,東京都,,2020-02-15,,,都内,50代,男性,,,,,,,1
No が連番っぽくPRIMARY KEYなのかな、130001と東京都は自明っぽいので取り込まなくて良い気がする、NULLABLEなものも特に扱いたくないので日付類は 公表_年月日 だけを使おう。居住地は取り回ししづらそうだしカーディナリティーが低くて面白くなさそうなので取り敢えず今回はパス、 患者_年代 と 患者_性別 はINT型、ENUM型に変えた上で取り込もうかな。
というわけでテーブル定義はこんな感じにすることにした。
mysql> SHOW CREATE TABLE covid19_tokyo\G
*************************** 1. row ***************************
Table: covid19_tokyo
Create Table: CREATE TABLE `covid19_tokyo` (
`no` int unsigned NOT NULL,
`published` date NOT NULL,
`age` int unsigned NOT NULL,
`gender` enum('M','F') NOT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.04 sec)
LOAD DATA LOCAL INFILEの書式は取り敢えずこんな感じにして、後でちょこちょこ直していこう。
CSVでカラムは , で区切られているので FIELDS TERMINATED BY ',' 、カラムの中の値はクォートされていなかったので ENCLOSED BY .. の指定は要らない。
括弧書きで羅列しているのは、CSVファイル内での位置とMySQL側のカラムを対応させるための記述。テーブル側のカラム名だけでなく、 @変数 にも代入できる。必ずしもCSVファイルとテーブル定義のカラムの順番が合っている必要はないし、CSV側に余剰なフィールドがあれば @dummy にでも突っ込んで読み捨てさせることができる。
ここを (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) と指定することで、
No -> noカラム
全国地方公共団体コード -> @dummyユーザー変数
都道府県名 -> @dummyユーザー変数
市区町村名 -> @dummyユーザー変数
公表_年月日 -> publishedカラム
発症_年月日 -> @dummyユーザー変数
確定_年月日 -> @dummyユーザー変数
患者_居住地 -> @dummyユーザー変数
患者_年代 -> @ageユーザー変数
患者_性別 -> @genderユーザー変数
患者_職業 -> @dummyユーザー変数
患者_状態 -> 未指定で読み飛ばされる
患者_症状 -> 未指定で読み飛ばされる
患者_渡航歴の有無フラグ -> 未指定で読み飛ばされる
患者_接触歴の有無フラグ -> 未指定で読み飛ばされる
備考 -> 未指定で読み飛ばされる
退院済フラグ -> 未指定で読み飛ばされる
という意味になる。
さあレッツゴー。
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
で、早速ERROR 3948の洗礼を食らう。これね。
mysql80 9> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
mysql80 9> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)
mysql80 9> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
サーバー側の local_infile をONにしてクライアント側はOFFのままにして叩いてみると
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
エラー番号が変わった。サーバーサイドの local_infile が問題なのかクライアントサイドの local_infile が問題なのかわかりやすくなってた。
というわけで mysql コマンドラインクライアント側の --local-infile をつけてもう一度。
$ mysql -h127.0.0.1 d1 -A --local-infile
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
Query OK, 160233 rows affected, 65535 warnings (2.44 sec)
Records: 160248 Deleted: 0 Skipped: 15 Warnings: 480761
ドバっとワーニングと、15件の登録できなかったレコード( Skipped: 15 )があるっぽい。
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'No' for column 'no' at row 1 |
| Warning | 1265 | Data truncated for column 'published' at row 1 |
| Warning | 1366 | Incorrect integer value: '患者_年代' for column 'age' at row 1 |
..
| Warning | 1262 | Row 340 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'age' at row 341 |
| Warning | 1265 | Data truncated for column 'gender' at row 341 |
+---------+------+-----------------------------------------------------------------------------+
1024 rows in set (0.04 sec)
SHOW WARNINGS 用に確保されているバッファは1024行ぶんしか入らないことが判明。
とはいえまあ全部潰していけばいいかな…。
取り敢えず row 1 はヘッダ行をスキップさせるのを忘れたせいっぽいので IGNORE 1 ROWS を足してやる。
mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.03 sec)
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
Query OK, 160232 rows affected, 65535 warnings (1.89 sec)
Records: 160247 Deleted: 0 Skipped: 15 Warnings: 480756
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'age' at row 1 |
| Warning | 1265 | Data truncated for column 'gender' at row 1 |
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'age' at row 2 |
| Warning | 1265 | Data truncated for column 'gender' at row 2 |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns |
..
ageカラムとgenderカラムはINTとENUMにそれぞれ変換を噛ますつもりがまだ噛ませていないだけなので、変換部分( SET )を書き足してやって再チャレンジ。
ageの nn台 の部分から数字の部分だけを正規表現で引っ張り出してやる ( REGEXP_SUBSTR ) のと、 CASE で男性女性をMFの一文字に変換。
mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160232 rows affected, 65535 warnings (2.46 sec)
Records: 160247 Deleted: 0 Skipped: 15 Warnings: 160283
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns |
| Warning | 1262 | Row 3 was truncated; it contained more data than there were input columns |
..
ん－、まだ truncated って言ってるなと思ったけど、よく見たら it contained more data than there were input columns だから「リストに何も指定せずに読み飛ばさせる」ってしたやつがこのワーニングになっているっぽい。
しかし Skipped: 15 だしこの状態じゃ他のワーニングが混ざってないかどうかを判定できないのでperformance_schemaに頼ることにする。
mysql80 14> use performance_schema
Database changed
mysql80 14> TRUNCATE events_errors_summary_by_thread_by_error;
Query OK, 0 rows affected (0.03 sec)
過去のワーニングやエラーが混じってもしょうがないので、 events_errors_summary_by_thread_by_error テーブルを一度クリアする。
mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.03 sec)
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160232 rows affected, 65535 warnings (2.18 sec)
Records: 160247 Deleted: 0 Skipped: 15 Warnings: 160283
これでまたワーニングを発生させてから
mysql80 14> SELECT * FROM events_errors_summary_by_thread_by_error WHERE first_seen > 0;
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| THREAD_ID | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| 47 | 1048 | ER_BAD_NULL_ERROR | 23000 | 21 | 0 | 2021-05-31 18:43:50 | 2021-05-31 18:43:51 |
| 47 | 1062 | ER_DUP_ENTRY | 23000 | 15 | 0 | 2021-05-31 18:43:51 | 2021-05-31 18:43:51 |
| 47 | 1262 | ER_WARN_TOO_MANY_RECORDS | 01000 | 160247 | 0 | 2021-05-31 18:43:50 | 2021-05-31 18:43:52 |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
3 rows in set (0.03 sec)
こうじゃ。
1262: ER_WARN_TOO_MANY_RECORDS はあきらめたから良いとして、 ER_BAD_NULL_ERROR と ER_DUP_ENTRY は何じゃい…。
取り敢えずキー重複エラー ( ER_DUP_ENTRY )はPRIMARY KEY制約に引っかかってるはずなのでいったんPRIMARY KEYを外して突っ込んでみる。
mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE covid19_tokyo DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160247 rows affected, 65535 warnings (1.84 sec)
Records: 160247 Deleted: 0 Skipped: 0 Warnings: 160268
mysql> SELECT no, COUNT(*) FROM covid19_tokyo GROUP BY no HAVING COUNT(*) > 1;
+--------+----------+
| no | COUNT(*) |
+--------+----------+
| 100045 | 2 |
| 100046 | 2 |
| 100047 | 2 |
| 100048 | 2 |
| 100049 | 2 |
| 100050 | 2 |
| 100051 | 2 |
| 100052 | 2 |
| 100053 | 2 |
| 100054 | 2 |
| 100055 | 2 |
| 100056 | 2 |
| 100057 | 2 |
| 100058 | 2 |
| 100059 | 2 |
+--------+----------+
15 rows in set (0.25 sec)
:(；ﾞﾟ’ωﾟ’): no 、お前、ユニークじゃないのかYO!!!
$ grep -n '^100045,' 130001_tokyo_covid19_patients.csv
1739:100045,130001,東京都,,2020-04-10,,,都外,20代,男性,,,,,,,1
100930:100045,130001,東京都,,2021-02-01,2021-01-24,2021-01-31,都内,90代,女性,無職,,,,1,,1
全然ユニークじゃなかった。思い込みって怖い。
しかしこれがないとなると、このテーブル上のレコードを一意に識別できそうなものはないな…（そもそも、同じ人間が2回罹った場合におそらく別の行として計上されるであろうことから一意識別もへったくれもない気がする）
NOT NULL制約違反の方も取り敢えず NOT NULL を外して突っ込んでから考えてみることにする。
mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)
mysql> desc covid19_tokyo;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| no | int unsigned | NO | | NULL | |
| published | date | NO | | NULL | |
| age | int unsigned | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE covid19_tokyo MODIFY published date, MODIFY age int unsigned, MODIFY gender enum('M', 'F');
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160247 rows affected, 65535 warnings (2.25 sec)
Records: 160247 Deleted: 0 Skipped: 0 Warnings: 160247
取り敢えず、ワーニングは当然出なくなった。
mysql80 14> SELECT * FROM events_errors_summary_by_thread_by_error WHERE first_seen > 0;
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| THREAD_ID | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| 47 | 1262 | ER_WARN_TOO_MANY_RECORDS | 01000 | 160247 | 0 | 2021-05-31 18:52:04 | 2021-05-31 18:52:06 |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.03 sec)
肝心のNULLだった中身はというと
mysql> SELECT * FROM covid19_tokyo WHERE no IS NULL OR published IS NULL OR age IS NULL OR gender IS NULL;
+--------+------------+------+--------+
| no | published | age | gender |
+--------+------------+------+--------+
| 1023 | 2020-04-05 | 20 | NULL |
| 2071 | 2020-04-12 | 40 | NULL |
| 2140 | 2020-04-13 | 20 | NULL |
| 2187 | 2020-04-14 | NULL | M |
| 2640 | 2020-04-17 | 60 | NULL |
| 2769 | 2020-04-17 | NULL | F |
| 2781 | 2020-04-17 | NULL | M |
| 2919 | 2020-04-18 | 10 | NULL |
| 2924 | 2020-04-18 | 50 | NULL |
| 3601 | 2020-04-24 | NULL | NULL |
| 3695 | 2020-04-24 | NULL | F |
| 3807 | 2020-04-25 | NULL | M |
| 4339 | 2020-05-02 | NULL | F |
| 4425 | 2020-05-02 | NULL | F |
| 9878 | 2020-07-22 | 20 | NULL |
| 12459 | 2020-07-31 | 50 | NULL |
| 30977 | 2020-10-31 | 40 | NULL |
| 56424 | 2020-12-27 | NULL | F |
| 58555 | 2020-12-30 | NULL | M |
| 114613 | 2021-03-15 | NULL | M |
+--------+------------+------+--------+
20 rows in set (0.18 sec)
ん－ホントに入ってない…。（しかも - と ― が揺れてたりするところにもんにょりするな…）
$ grep '^1023,' 130001_tokyo_covid19_patients.csv
1023,130001,東京都,,2020-04-05,,,都内,20代,-,,,,,,,1
]$ grep '^114613,' 130001_tokyo_covid19_patients.csv
114613,130001,東京都,,2021-03-15,,2021-03-06,―,-,男性,－,,,,,,1
取り敢えずここまで。
0 件のコメント :
コメントを投稿