PostgreSQLのpsqlコマンドを使ってCSVファイルのデータをテーブルにインポートしたり、Linuxコマンドの結果を受け取ってそのままデータをテーブルにインポートする方法、そして高速にデータをインポートできるpg_bulkloadコマンドを使った方法など、様々な方法を解説します。データ件数に応じた使い分けや、どのようにデータをインポートするか、様々なシーンを想定して解説します。
この記事で使用するテーブルとデータ
まずこの記事で使用する「test」テーブルを作成します。id列は自動採番するためserial型にし、name列(名前)とage列(年齢)を用意しています。
CREATE TABLE test (
id serial PRIMARY KEY,
name text,
age int
);このテーブルに対して、次のような10万件のデータを登録します。
=> INSERT INTO test (name, age)
SELECT 'username' || i::text, i
FROM generate_series(1, 100000) AS t(i);すると10万件のデータが作成されます。
=> select * from test;
+--------+----------------+--------+
| id | name | age |
+--------+----------------+--------+
| 1 | username1 | 1 |
| 2 | username2 | 2 |
| 3 | username3 | 3 |
| 4 | username4 | 4 |
| 5 | username5 | 5 |
| 6 | username6 | 6 |
| 7 | username7 | 7 |
| 8 | username8 | 8 |
| 9 | username9 | 9 |
| 10 | username10 | 10 |
(一部省略)
| 99990 | username99990 | 99990 |
| 99991 | username99991 | 99991 |
| 99992 | username99992 | 99992 |
| 99993 | username99993 | 99993 |
| 99994 | username99994 | 99994 |
| 99995 | username99995 | 99995 |
| 99996 | username99996 | 99996 |
| 99997 | username99997 | 99997 |
| 99998 | username99998 | 99998 |
| 99999 | username99999 | 99999 |
| 100000 | username100000 | 100000 |
+--------+----------------+--------+
(100000 rows)この10万件のデータを「test.csv」というCSVファイルに出力しておきます。
=> \COPY test TO 'test.csv' CSV HEADER
COPY 100000すると次のようなCSVファイルが作成されています。
$ cat test.csv
id,name,age
1,username1,1
2,username2,2
3,username3,3
4,username4,4
5,username5,5
6,username6,6
7,username7,7
8,username8,8
9,username9,9
(一部省略)
99991,username99991,99991
99992,username99992,99992
99993,username99993,99993
99994,username99994,99994
99995,username99995,99995
99996,username99996,99996
99997,username99997,99997
99998,username99998,99998
99999,username99999,99999
100000,username100000,100000テスト用のCSVファイルが作成できましたので、ここでtestテーブルの中身を削除しておきます。TRUNCATE TABLE文でtestテーブルの中身を消去しておきます。「RESTART IDENTITY」というオプションを付けることによって、自動採番に利用されるシーケンス(SEQUENCE)も初期値にリセットされます。これによりid列の自動採番が1から開始されます。
=> TRUNCATE TABLE test RESTART IDENTITY;データをテーブルにインポートする様々な方法
ここからはデータをテーブルにインポートする様々な方法を説明します。
Linuxコマンドライン上でpsqlコマンドによるインポート
Linuxのコマンドラインで実行できるpsqlコマンドを使ってCSVファイルをインポートするには、以下のようにします。データをインポートするには「\COPY」コマンドを使用することで、CSVファイル(test.csv)をtestテーブルに全件インポートすることが出来ます。CSVファイルにヘッダ行がある場合には「HEADER」を付けることで１行目のヘッダ行をインポートしないようにすることが出来ます。
$ psql -c "\COPY test FROM 'test.csv' CSV HEADER"
COPY 100000これで10万件のCSVファイルをインポートできました。
=> SELECT COUNT(*) FROM test;
+--------+
| count |
+--------+
| 100000 |
+--------+
(1 row)Linuxコマンドライン上で実行できるpsqlコマンドでデータをインポートできますので非常に手軽に使うことが出来ます。データを読み込む速度は、次の\COPYコマンドとほぼ同じ速度でデータを読み込むことが可能です。
psqlコマンド内で\COPYコマンドでデータをテーブルにインポートする
psqlコマンドの中で「\COPY」コマンドを使ってCSVファイルをインポートすることもできます。CSVファイルにヘッダ行がある場合には「HEADER」を付けることで１行目のヘッダ行をインポートしないようにすることが出来ます。
=> \COPY test FROM 'test.csv' CSV HEADER
COPY 100000これで10万件のCSVファイルをインポートできました。
=> SELECT COUNT(*) FROM test;
+--------+
| count |
+--------+
| 100000 |
+--------+
(1 row)このpsqlコマンド内で\COPYコマンドを実行する方法は、先に説明したLinuxコマンドライン上でpsqlコマンドを実行した場合と速度はほぼ同じです。
\COPYコマンドで標準入力からデータをインポートする
\COPYコマンドのデータ入力ファイル名部分に「STDIN」を指定することで標準入力からデータを登録することが出来ます。つまりキーボードでデータを入力してそのままデータをテーブルにインポートすることが可能です。
以下のように\COPYコマンドにSTDINを指定して実行します。
=> \COPY test FROM STDIN CSV HEADER;すると次の画面のようにデータを入力するモードに入ります。
=> \COPY test FROM STDIN CSV HEADER;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>>ここで次のように１行づつデータを入力します。「CSV HEADER」というデータ形式を指定していますので、最初に列名の行、そしてデータ行を１行づつカンマ区切りで入力していきます。データを入力し終えたら最後に「\.」を入力してエンターキーを押すことによって、キーボードから入力したデータをテーブルにインポートすることが出来ます。ここではデータを５件登録します。
=> \COPY test FROM STDIN CSV HEADER;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> id,name,age
>> 999995,username999995,999995
>> 999996,username999996,999996
>> 999997,username999997,999997
>> 999998,username999998,999998
>> 999999,username999999,999999
>> \.
COPY 5実際にテーブルのデータを確認してみると、問題なく５件のレコードが登録されていることを確認出来ます。
=> SELECT * FROM test;
+--------+----------------+--------+
| id | name | age |
+--------+----------------+--------+
| 999995 | username999995 | 999995 |
| 999996 | username999996 | 999996 |
| 999997 | username999997 | 999997 |
| 999998 | username999998 | 999998 |
| 999999 | username999999 | 999999 |
+--------+----------------+--------+
(5 rows)標準入力からデータをインポートする方法の使い方としては、他のコマンドの結果を基にデータをテーブルにインポートしたい場合が挙げられます。例えば次のようにcatコマンドの結果を受け取って、テーブルにデータを登録したい場合に利用できます。
$ cat test.csv | psql -c "\COPY test FROM STDIN CSV HEADER;"ダブルクォーテーションで括られたデータを\COPYコマンドでインポートする
\COPYコマンドでデータをインポートする場合、もし列データがダブルクォーテーション(“)で括られていた場合、どのようにデータをインポートできるかも見てみましょう。次のようにデータがダブルクォーテーションで括られているデータを用意します。
$ cat test.csv
id,name,age
"1","username999995","999995"
"2","username999996","999996"
"3","username999997","999997"
"4","username999998","999998"
"5","username999999","999999"\COPYコマンドでCSV形式でインポートすると、自動的にダブルクォーテーションが削除された状態でデータがインポートされることが分かります。EXCELのデータをCSVに変換した時などに利用できます。
=> \COPY test FROM 'test.csv' CSV HEADER
COPY 5
=> select * from test;
+----+----------------+--------+
| id | name | age |
+----+----------------+--------+
| 1 | username999995 | 999995 |
| 2 | username999996 | 999996 |
| 3 | username999997 | 999997 |
| 4 | username999998 | 999998 |
| 5 | username999999 | 999999 |
+----+----------------+--------+
(5 rows)データがダブルクォーテーションで括られていても問題なく簡単にインポートする場合にも、データを加工しなくてもそのままインポートすることが出来ます。
pg_bulkloadコマンドでデータをインポートする
PostgreSQLに付属しているpg_bulkloadというコマンドを使うことでデータをインポートすることもできます。これまでに紹介したツールよりも高速にデータをテーブルにインポートできるツールです。
AlmaLinux9の場合、次のようにしてpg_bulkloadパッケージをインストールします。
# yum install pg_bulkload_13続いて次のようにして拡張機能をインストールして有効化します。postgresスーパーユーザーで実行しましょう。
% psql -U postgres
=# CREATE EXTENSION pg_bulkload;
CREATE EXTENSIONPostgreSQL-13の場合、次のようなオプションが利用可能となっています。
$ pg_bulkload --help
pg_bulkload is a bulk data loading tool for PostgreSQL
Usage:
Dataload: pg_bulkload [dataload options] control_file_path
Recovery: pg_bulkload -r [-D DATADIR]
Dataload options:
-i, --input=INPUT INPUT path or function
-O, --output=OUTPUT OUTPUT path or table
-l, --logfile=LOGFILE LOGFILE path
-P, --parse-badfile=* PARSE_BADFILE path
-u, --duplicate-badfile=* DUPLICATE_BADFILE path
-o, --option="key=val" additional option
Recovery options:
-r, --recovery execute recovery
-D, --pgdata=DATADIR database directory
Connection options:
-d, --dbname=DBNAME database to connect
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
Generic options:
-e, --echo echo queries
-E, --elevel=LEVEL set output message level
--help show this help, then exit
--version output version information, then exitでは先に作成していたtest.csvファイルをpg_bulkloadコマンドでテーブルに読み込んでみます。
$ cat test.csv
id,name,age
"1","username999995","999995"
"2","username999996","999996"
"3","username999997","999997"
"4","username999998","999998"
"5","username999999","999999"pg_bulkloadコマンドは次のように指定します。「-U postgres」オプションでデータベース接続ユーザーを指定(postgresユーザー)、「-i test.csv」でtest.csvファイルをインプットファイルとし、「-O test」でデータのインポート先のテーブル名をtestテーブルに指定します。「-o “type=csv”」オプションでCSV形式であること、「-o “skip=1″」で１行目のヘッダ行を読み飛ばすという設定です。
$ pg_bulkload -U postgres -i test.csv -O test -o "type=csv" -o "skip=1"このコマンドを実行すると、以下のように「1 Rows skipped.」ということで、１行目はスキップされて読み込まれず、その次の「5 Rows successfully loaded.」と表示されて５行のデータがインポートされたことが分かります。もし行数と一致しない場合には、データを読み込めなかった何らかの理由がありますのでデータを修正しましょう。
$ pg_bulkload -U postgres -i test.csv -O test -o "type=csv" -o "skip=1"
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
5 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.コマンドラインで全てのオプションを指定しなくても、制御ファイルと呼ばれるファイルを作成しておくことでもデータをインポートすることが可能です。
ここではtest.ctlというpg_bulkloadコマンドに指示をするための制御ファイルを作成します。
$ cat test.ctl
INPUT = test.csv
OUTPUT = test
TYPE = CSV
DELIMITER = ","
SKIP = 1制御ファイルにデータをインポートするのに必要な情報を記載してありますので、pg_bulkloadコマンドではデータベース接続ユーザーの指定(-U postgres)と制御ファイル名(test.ctl)を渡してあげるだけです。これでpg_bulkloadコマンドは制御ファイルから必要な情報を読み取ってデータをテーブルにインポートしてくれます。
$ pg_bulkload -U postgres test.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
5 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.その他、pg_bulkloadコマンドには処理を高速化するためのいくつかのオプションが存在します。「WRITER」というオプションを設定することでデータの読み込みモードを変えることが可能です。WRITERで設定できるオプションは次の通りです。何も設定しない場合には、DIRECTモードが使用されます。
| DIRECT (デフォルト) | テーブルに直接データをインポートします。非常に高速だが、データベースがダウンしてしまった場合にデータのリカバリが必要となる。 |
| BUFFERED | 通常のSQLと同じようにデータをインポートします。\COPYコマンドとほぼ同じ速度でデータをインポートします。 |
| BINARY | バイナリファイルを扱う場合に使用します。ここでは使用しませんので説明は省略します。 |
| PARALLEL | 並列処理でデータをインポートします。”WRITER=DIRECT”と”MULTI_PROCESS=YES”を指定したのと同じです。並列でデータをインポートするので非常に高速です。しかしデータベースダウン時にはリカバリが必要となる。 |
WRITERのモードをDIRECTにした場合は、次のようにデータがインポートされます。
$ pg_bulkload -U postgres -i test.csv -O radiuslog.test -o "type=csv" -o "skip=1" -o "writer=direct"
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
5 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.WRITERのモードをBUFFEREDにした場合は、次のようにデータがインポートされます。
$ pg_bulkload -U postgres -i test.csv -O radiuslog.test -o "type=csv" -o "skip=1" -o "writer=buffered"
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
5 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.WRITERのモードをPARALLELにした場合は、次のようにデータがインポートされます。
$ pg_bulkload -U postgres -i test.csv -O radiuslog.test -o "type=csv" -o "skip=1" -o "writer=parallel"
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
5 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.とにかくデータを高速でインポートしたい場合には「WRITER」のモードを「PARALLEL」に設定すると良いでしょう。マシン性能にもよりますが、並列でデータがインポートされますので非常に高速です。テストデータをインポートする場合等に「PARALLEL」あるいは「DIRECT」を使った方が良いです。本番環境などでは、万が一データベースがダウンしてしまった場合に、「PARALLEL」あるいは「DIRECT」でインポートしたデータが消失する可能性がありますので、時間が掛かっても「BUFFERED」でインポートした方が良いでしょう。
まとめ
PostgreSQLのテーブルにデータをインポートする様々な方法を見てきました。とにかく早く少量のデータをインポートしたい場合には、Linuxコマンドライン上でpsqlコマンドを実行する方法や、psqlコマンド内で\COPYコマンドを実行するのが手っ取り早いです。しかし大量データを読み込みたい場合には、pg_bulkloadコマンドを使った方が圧倒的にデータを高速にインポートすることが出来ます。
