仕事で数億のレコードをテーブルにロードする機会がありました。初めての経験で戸惑うことも多かった為、忘備録として残します。
環境
Windows10
PostgreSQL13
気にしなければならないこと
①大量のデータをロードすると、どうしても時間がかかってしまいます。なので、その時間をいかに短縮するか。
②レコード数が多いと、何かしらの不備でレコードのロードミスが発生する可能性もある。確認が必要。
③Cドライブの容量不足にならないか確認。
####①の解決方法。
少しでもロードを早くする為に、インデックスをつける際にはデータをロードした後に追加すると良い。
ここでいうインデックスは、プリマリーキーも含まれる。
###②の解決方法
ロードが終わった際に、総レコード数の件数（SELECT COUNT)と、NULLがないかのコマンドを使用する。
###③の解決方法。
テーブルスペースを使用して、他の場所にデータをロードする。
参照： https://qiita.com/taxi-btn/items/32a890f08d0944a09d17
データベースに接続
postgres-# \c db201225
データベース"db201225"にユーザ"postgres"として接続しました。
db201225-#
テーブルの作成
CREATE TABLE testtable (
red bigint not null,
blue varchar(10000) not null,
yellow varchar(10000) not null,
green varchar(10000) not null,
pink numeric not null,
black integer not null,
white varchar(10000) not null
);
ここでは、テーブルとカラムを作成するが、インデックスはつけない。上記で書いたように、先にインデックスをつけてしまうとロード時間が長くなってします。PKも同様。
あとは、not nullも後付けの方が良いのかなあなんて思っています。万が一レコードにNULLが発生したらエラーになってしまうので。
データをロードする
COPY testtable FROM 'D:\color.csv' with csv header;
ここでは、Dドライブに入っているcolor.csvのレコードをロードするコマンドを書いています。
インデックスをつける
CREATE INDEX ON testtable (red);
CREATE INDEX ON testtable (blue);
CREATE INDEX ON testtable (yellow);
インデックスの種類を指定しないのであれば、デフォルトでB-treeになる。
選ぶ基準が難しいので、B-treeでいいんじゃなかと思ってしまいます。わかりやすい記事などあれば教えて欲しいです。
レコードの件数を数える
SELECT COUNT(*) FROM testtable;
- ←このワイルドカードで全てを表します。
NULLがないか確認する。
SELECT COUNT(red)FROM testtable;
testtableテーブルのredカラムのカウント数を数えることで、レコード数と見比べて数が間違っていないか調べることができる。
エラーが出ていないかログで確認する為、ログファイルの設定（confファイルをいじるのは自己責任で！）
※順番が前後してしまいますが、ロード前に修正してください。
設定ファイル C:\Program Files\PostgreSQL\13\data 直下にあるpostgresql.confファイルを使用。
ファイルが開かなければ、7-zipとsublimeの併用で開く。
①修正１：
420行目あたりにある、REPORTING AND LOGGING項目で
log_destination = 'stderr'にする。
②修正２：
420行目あたりにある、
logging_collector = onにする。（ファイル出力の設定）
③修正３：
558行目あたりにある、
log_statement = "all"にする。（どのSQL文をログに記録するかを制御する）。
ログの確認方法
C:\Program Files\PostgreSQL\13\data\log\対象のファイルをクリック。
中にログが記載されている。
文字ばけしている可能性有。
もし文字化けしていたら、
postgresql.conf内で、以下のように変更をする。
【変更前】
lc_messages = 'Japanese_Japan.932'
【変更後】
lc_messages = 'en_US'
(Ubuntuの場合は'en_US.UTF8'のようです）
※変更はPostgreSQLを再起動すると反映されます。
logを確認することで、正常にロードができているか確認することができる。