LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@1429takahiro

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

Posted at

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
バージョン情報
[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）のオープンデータの取得

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

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

  • ダウンロードファイルの存在確認&ファイルの転送
ダウンロードファイルの存在確認&ファイルの転送
[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]#
  • 仮想環境へのログイン&ファイルの存在確認
仮想環境へのログイン&ファイルの存在確認
[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に接続後テーブルの作成
テーブル作成
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”列に、""（空文字）が入っていた為エラー
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型に変更)

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

テーブル定義の変更
mydb=> ALTER TABLE tokyo_covid19_patients ALTER COLUMN p_discharge_flag TYPE varchar(1);
ALTER TABLE
mydb=>
  • 患者の年齢列について、5文字以上のデータが存在していた為エラー
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)に変更）
テーブル定義の変更
mydb=> ALTER TABLE tokyo_covid19_patients ALTER COLUMN p_age TYPE varchar(10);
ALTER TABLE
mydb=>
  • COPYコマンドによりCSVデータのインポート成功
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コマンドで投入したデータの確認

  • データ件数の確認
データ件数
mydb=> select count(*) from tokyo_covid19_patients;
 count 
-------
  3439
(1 行)

mydb=>
  • 日別の感染者数を抽出
日別の感染者数
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=>
  • 年代別患者数の抽出
年代別患者数
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=>
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?