新型コロナウイルス感染症（COVID-19）のオープンデータをPostgreSQLに格納する作業メモ


MacOS上の仮想環境（Virtual Box）のRHEL8環境に構築したPostgreSQL12にCOPYコマンドを使ってCSVデータをインポートした際の作業メモ


# 前提環境
- ホストOS：macOS Catalina 10.15.1
- 仮想化ソフト：VirtualBox Version 6.1.0 r135406
- ゲストOS：Red Hat Enterprise Linux release 8.0 (Ootpa)
- ゲストOSカーネル：4.18.0-80.el8.x86_64
- PostgreSQLバージョン：12.1

```console:バージョン情報
[pgadm01@rhel8 ~]$ uname -a 
Linux rhel8 4.18.0-80.el8.x86_64 #1 SMP Wed Mar 13 12:02:46 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[pgadm01@rhel8 ~]$ 
[pgadm01@rhel8 ~]$ psql -d mydb 
psql (12.1)
"help"でヘルプを表示します。

mydb=> 
mydb=> SELECT version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 行)

mydb=> 
```

上記環境の構築手順は、以下リンク先参照
https://qiita.com/1429takahiro/items/3d9c7591e1508d9ad1df

# 1. 新型コロナウイルス感染症（COVID-19）のオープンデータの取得

https://stopcovid19.metro.tokyo.lg.jp/

陽性患者の属性 - > オープンデータを入手 - > ダウンロード


# 2. 投入対象のCSVファイルをローカル環境(Mac)から、仮想環境(VirtualBox)のRHEL8環境に転送

- ダウンロードファイルの存在確認&ファイルの転送

```console:ダウンロードファイルの存在確認&ファイルの転送
[root@localhost Downloads]# ls -ltr /Users/takahiro/Downloads/130001_tokyo_covid19_patients.csv
-rw-r--r--@ 1 takahiro  staff  231660 Apr 23 22:05 /Users/takahiro/Downloads/130001_tokyo_covid19_patients.csv
[root@localhost Downloads]# 
[root@localhost Downloads]# scp /Users/takahiro/Downloads/130001_tokyo_covid19_patients.csv login00@192.168.56.100:/home/login00/
130001_tokyo_covid19_patients.csv                                                     100%  226KB  18.3MB/s   00:00    
[root@localhost Downloads]# 
```

- 仮想環境へのログイン&ファイルの存在確認

```console:仮想環境へのログイン&ファイルの存在確認
[root@localhost Downloads]# ssh login00@192.168.56.100
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Thu Apr 23 22:09:38 2020 from 192.168.56.1
[login00@rhel8 ~]$
[login00@rhel8 ~]$ ls -ltr ./130001_tokyo_covid19_patients.csv 
-rw-r--r--. 1 login00 login00 231660  4月 23 22:10 ./130001_tokyo_covid19_patients.csv
[login00@rhel8 ~]$ 
```

# 3. PostgreSQLにテーブルを作成

- テーブルは以下の通り作成

テーブル名：tokyo_covid19_patients

| 項目名 | 列名 | データ型 | 属性 |
|:--|:--|:--|:--|
| No | no | integer | 主キー | 
| 全国地方公共団体コード | g_code | integer |
| 都道府県名 | prefecture | varchar(10) |
| 市区町村名 | city | varchar(10) |
| 公表_年月日 | a_date | date |
| 曜日 | a_day | varchar(3) |
| 発症_年月日 | d_date | date |
| 患者_居住地 | p_residence | varchar(10) |
| 患者_年代 | p_age | varchar(5) |
| 患者_性別 | p_sex | varchar(5) |
| 患者_属性 | p_attr | varchar(10) |
| 患者_状態 | p_status | varchar(10) |
| 患者_症状 | p_symptom | varchar(10) |
| 患者_渡航歴の有無フラグ | p_travel_flag | integer |
| 備考 | note | varchar(30) |
| 退院済フラグ | p_discharge_flag | integer |

- psqlにてPostgreSQLに接続後テーブルの作成

```console:テーブル作成
mydb=> CREATE TABLE tokyo_covid19_patients
mydb-> ( no integer PRIMARY KEY,
mydb(> g_code integer,
mydb(> prefecture varchar(10),
mydb(> city varchar(10),
mydb(> a_date date,
mydb(> a_day varchar(3),
mydb(> d_date date,
mydb(> p_residence varchar(10),
mydb(> p_age varchar(5),
mydb(> p_sex varchar(5),
mydb(> p_attr varchar(10),
mydb(> p_status varchar(10),
mydb(> p_symptom varchar(10),
mydb(> p_travel_flag integer,
mydb(> note varchar(30),
mydb(> p_discharge_flag integer);
CREATE TABLE
mydb=> 
mydb=> \d
 pgadm01  | tokyo_covid19_patients | テーブル   | pgadm01

mydb=>
```

# 4. CSVファイルのデータを作成したテーブルにCOPYコマンドで投入

- Integer型で定義していた”p_discharge_flag”列に、""（空文字）が入っていた為エラー

```console:COPYコマンド(失敗)
mydb=> \COPY tokyo_covid19_patients FROM '/home/pgadm01/130001_tokyo_covid19_patients.csv' WITH CSV HEADER
ERROR:  "integer"型の入力構文が不正です: ""
CONTEXT:  tokyo_covid19_patientsのCOPY、行 5、列 p_discharge_flag: ""
mydb=> 
```

- テーブル定義の変更(p_discharge_flag列をInteger型からvarchar型に変更)

`本来の対処の仕方としては正しくない模様`

```console:テーブル定義の変更
mydb=> ALTER TABLE tokyo_covid19_patients ALTER COLUMN p_discharge_flag TYPE varchar(1);
ALTER TABLE
mydb=>
```

- 患者の年齢列について、5文字以上のデータが存在していた為エラー

```console:COPYコマンド(失敗)
mydb=> \COPY tokyo_covid19_patients FROM '/home/pgadm01/130001_tokyo_covid19_patients.csv' WITH CSV HEADER;
ERROR:  値は型character varying(5)としては長すぎます
CONTEXT:  tokyo_covid19_patientsのCOPY、行 1991、列 p_age: "100歳以上"
mydb=> 
```

- テーブル定義の変更（p_age列をvarchar(5)からvarchar(10)に変更）

```console:テーブル定義の変更
mydb=> ALTER TABLE tokyo_covid19_patients ALTER COLUMN p_age TYPE varchar(10);
ALTER TABLE
mydb=> 
```
- COPYコマンドによりCSVデータのインポート成功

```console:COPYコマンド(成功)
mydb=> \COPY tokyo_covid19_patients FROM '/home/pgadm01/130001_tokyo_covid19_patients.csv' WITH CSV HEADER;
COPY 3439
mydb=>
```

- 最終的なテーブル定義は以下

| 項目名 | 列名 | データ型 | 属性 |
|:--|:--|:--|:--|
| No | no | integer | 主キー | 
| 全国地方公共団体コード | g_code | integer |
| 都道府県名 | prefecture | varchar(10) |
| 市区町村名 | city | varchar(10) |
| 公表_年月日 | a_date | date |
| 曜日 | a_day | varchar(3) |
| 発症_年月日 | d_date | date |
| 患者_居住地 | p_residence | varchar(10) |
| 患者_年代 | p_age | varchar(5) |
| 患者_性別 | p_sex | varchar(5) |
| 患者_属性 | p_attr | varchar(10) |
| 患者_状態 | p_status | varchar(10) |
| 患者_症状 | p_symptom | varchar(10) |
| 患者_渡航歴の有無フラグ | p_travel_flag | integer |
| 備考 | note | varchar(30) |
| 退院済フラグ | p_discharge_flag | varchar(1) |


# 5. COPYコマンドで投入したデータの確認

- データ件数の確認

```console:データ件数
mydb=> select count(*) from tokyo_covid19_patients;
 count 
-------
  3439
(1 行)

mydb=>
```

- 日別の感染者数を抽出

```console:日別の感染者数
mydb=> select a_date as announcement_date,count(a_date) number_of_patients from tokyo_covid19_patients group by a_date having count(a_date) > 0 order by announcement_date;
 announcement_date | number_of_patients 
-------------------+--------------------
 2020-01-24        |                  1
 2020-01-25        |                  1
 2020-01-30        |                  1
 2020-02-13        |                  1
 2020-02-14        |                  2
 2020-02-15        |                  8
 2020-02-16        |                  5
 2020-02-18        |                  3
 2020-02-19        |                  3
 2020-02-21        |                  3
 2020-02-22        |                  1
 2020-02-24        |                  3
 2020-02-26        |                  3
 2020-02-27        |                  1
 2020-02-29        |                  1
 2020-03-01        |                  2
 2020-03-03        |                  1
 2020-03-04        |                  4
 2020-03-05        |                  8
 2020-03-06        |                  6
 2020-03-07        |                  6
 2020-03-10        |                  3
 2020-03-11        |                  6
 2020-03-12        |                  2
 2020-03-13        |                  2
 2020-03-14        |                 10
 2020-03-15        |                  3
 2020-03-17        |                 12
 2020-03-18        |                  9
 2020-03-19        |                  7
 2020-03-20        |                 11
 2020-03-21        |                  7
 2020-03-22        |                  2
 2020-03-23        |                 16
 2020-03-24        |                 17
 2020-03-25        |                 41
 2020-03-26        |                 47
 2020-03-27        |                 40
 2020-03-28        |                 63
 2020-03-29        |                 68
 2020-03-30        |                 13
 2020-03-31        |                 78
 2020-04-01        |                 66
 2020-04-02        |                 97
 2020-04-03        |                 89
 2020-04-04        |                116
 2020-04-05        |                143
 2020-04-06        |                 83
 2020-04-07        |                 79
 2020-04-08        |                144
 2020-04-09        |                178
 2020-04-10        |                188
 2020-04-11        |                197
 2020-04-12        |                166
 2020-04-13        |                 91
 2020-04-14        |                161
 2020-04-15        |                126
 2020-04-16        |                148
 2020-04-17        |                201
 2020-04-18        |                181
 2020-04-19        |                107
 2020-04-20        |                102
 2020-04-21        |                123
 2020-04-22        |                132
(64 行)

mydb=>
```

- 年代別患者数の抽出

```console:年代別患者数
mydb=> select p_age as 患者年代 ,count(p_age) 患者数 from tokyo_covid19_patients group by p_age having count(p_age) > 0 order by 患者年代;
 患者年代  | 患者数 
-----------+--------
 -         |      1
 100歳以上  |      1
 10歳未満   |     42
 10代      |     47
 20代      |    586
 30代      |    653
 40代      |    609
 50代      |    571
 60代      |    367
 70代      |    320
 80代      |    173
 90代      |     67
 不明      |      2
(13 行)

mydb=> 
```