PostgreSQL のテーブルに CSV でデータを読み込む

今回は PostgreSQL のテーブルに CSV ファイル経由でデータを読み込む方法について。ちょくちょくやり方を調べている気がするのでメモしておく。

使った環境は次のとおり。

$ sw_vers
ProductName:    macOS
ProductVersion: 12.1
BuildVersion:   21C52
$ uname -rm
21.2.0 arm64
$ psql --version           
psql (PostgreSQL) 14.1

下準備

まずは PostgreSQL をインストールする。ついでに、CSV をダウンロードするために wget も入れる。

$ brew install postgresql wget

PostgreSQL のサービスを開始する。

$ brew services start postgresql
$ brew services list
Name       Status  User    File
postgresql started amedama ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

今回は Seaborn に同梱されている Taxis データセットを使う。あらかじめ CSV ファイルをダウンロードしておく。

$ wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/taxis.csv -P /tmp
$ head -n 5 /tmp/taxis.csv
pickup,dropoff,passengers,distance,fare,tip,tolls,total,color,payment,pickup_zone,dropoff_zone,pickup_borough,dropoff_borough
2019-03-23 20:21:09,2019-03-23 20:27:24,1,1.6,7.0,2.15,0.0,12.95,yellow,credit card,Lenox Hill West,UN/Turtle Bay South,Manhattan,Manhattan
2019-03-04 16:11:55,2019-03-04 16:19:00,1,0.79,5.0,0.0,0.0,9.3,yellow,cash,Upper West Side South,Upper West Side South,Manhattan,Manhattan
2019-03-27 17:53:01,2019-03-27 18:00:25,1,1.37,7.5,2.36,0.0,14.16,yellow,credit card,Alphabet City,West Village,Manhattan,Manhattan
2019-03-10 01:23:59,2019-03-10 01:49:51,1,7.7,27.0,6.15,0.0,36.95,yellow,credit card,Hudson Sq,Yorkville West,Manhattan,Manhattan

続いて、上記のデータに適合するテーブルを用意する。データベースは、デフォルトで postgres が作られている。

$ cat << 'EOF' | psql postgres
CREATE TABLE IF NOT EXISTS taxis (
  pickup TIMESTAMP,
  dropoff TIMESTAMP,
  passengers INT,
  distance FLOAT,
  fare FLOAT,
  tip FLOAT,
  tolls FLOAT,
  total FLOAT,
  color TEXT,
  payment TEXT,
  pickup_zone TEXT,
  dropoff_zone TEXT,
  pickup_borough TEXT,
  dropoff_borough TEXT
);
EOF
CREATE TABLE

CSV ファイルを読み込む

CSV ファイルのインポートには COPY を使う。これは PostgreSQL 独自の文なので、使い方はドキュメントを参照する。

www.postgresql.jp

実際に、COPY を使って taxis テーブルに /tmp/taxis.csv ファイルを読み込む。 CSV ファイルは先頭行がヘッダになっているので、読み飛ばす設定を WITH 以下に入れる。

$ cat << 'EOF' | psql postgres
COPY taxis
FROM '/tmp/taxis.csv'
WITH (
  FORMAT csv,
  HEADER true
)
EOF

以下のとおり、確認するとファイルの内容が読み込まれていることがわかる。

$ cat << 'EOF' | psql postgres                               
\pset pager off
SELECT * FROM taxis LIMIT 5
EOF
Pager usage is off.
       pickup        |       dropoff       | passengers | distance | fare | tip  | tolls | total | color  |   payment   |      pickup_zone      |     dropoff_zone      | pickup_borough | dropoff_borough 
---------------------+---------------------+------------+----------+------+------+-------+-------+--------+-------------+-----------------------+-----------------------+----------------+-----------------
 2019-03-23 20:21:09 | 2019-03-23 20:27:24 |          1 |      1.6 |    7 | 2.15 |     0 | 12.95 | yellow | credit card | Lenox Hill West       | UN/Turtle Bay South   | Manhattan      | Manhattan
 2019-03-04 16:11:55 | 2019-03-04 16:19:00 |          1 |     0.79 |    5 |    0 |     0 |   9.3 | yellow | cash        | Upper West Side South | Upper West Side South | Manhattan      | Manhattan
 2019-03-27 17:53:01 | 2019-03-27 18:00:25 |          1 |     1.37 |  7.5 | 2.36 |     0 | 14.16 | yellow | credit card | Alphabet City         | West Village          | Manhattan      | Manhattan
 2019-03-10 01:23:59 | 2019-03-10 01:49:51 |          1 |      7.7 |   27 | 6.15 |     0 | 36.95 | yellow | credit card | Hudson Sq             | Yorkville West        | Manhattan      | Manhattan
 2019-03-30 13:27:42 | 2019-03-30 13:37:14 |          3 |     2.16 |    9 |  1.1 |     0 |  13.4 | yellow | credit card | Midtown East          | Yorkville West        | Manhattan      | Manhattan
(5 rows)