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=>