PostgreSQLでCSVファイルをレコードに取り込む(COPY編)

COPY 句

ファイル ⇔ テーブル間のデータを高速にコピーする際に使用する。

ファイルの内容を無条件でインポートする際は高速で楽なのだが、あまり複雑なことができず、以下のケースで困る。

1. 任意の条件によるインポート対象の絞り込み
2. キー重複

COPYコマンドを使用する場合は、上述のようなファイル・データが扱われないよう調整するのが最初の手になる。

1. については、テンポラリテーブルを作成 → INSERT SELECT コマンドの発行(＋テンポラリテーブルの破棄)
2.も同時に対応する場合はON CONFLICTコマンドを追加してやればよい。

2. のみの場合について、
レプリケーション環境でなければpg_bulkloadを使えばよい。
DBなので微妙なライン。。
レプリケーション環境である場合は、file_fdwを使用して外部テーブル化し、INSERT INTO SELECT で ON CONFLICTコマンドを発行する。

• CSVファイルで受け取ったデータを、一括でテーブルに登録する
• テーブルから全レコードのデータをファイル出力する

といったユースケースにおいて、プログラム＋INSERT、SELECT句を使用するよりも簡単かつ高速に実現できるコマンドだ。

テーブル ⇒ ファイル

一番シンプルなパターンだと、

 COPY [テーブル名] TO 出力先ファイルパス

で、テーブルの全カラム全レコードがタブ区切りで出力される

ファイル ⇒ テーブル

同じく、まずは一番シンプルに

 COPY [テーブル名] FROM 入力元ファイルパス

で、ファイルの全データがインポートされる。

ファイルのデータ順とテーブルのカラム順が一致していなかったり、特定のカラムだけ取り込みを行いたい場合は、テーブル名の後ろにカラム名を指定すればできます。

注意点

ファイルパスはPostgreSQLが参照可能なディレクトリである必要があります。
DBとクライアントとなるアプリがそれぞれ別サーバに配置されている場合、指定されるファイルパスをDBサーバが参照可能なディレクトリとする必要があります。
上記構成の場合、DBサーバとアプリサーバで共通で参照可能なディレクトリを共有しておく必要があります。

＠ConfigurationPropertiesの実装メモ

• .propertiesからの設定値を読み出す際に使用する。
• prefix で、読み出し範囲の絞り込みが可能。
• setterが必要。
• ＠ConfigurationPropertiesを設定したクラスを継承しても、親の設定値は注入されない。
• getter/setterの名称は、get/set[プロパティ名の先頭のみ大文字、以下はプロパティ名と同じ]となる。
• camel記法の場合は、アンダーバーの後ろの文字を大文字にすることでも読み込んでくれたはず。

@Transactionalの使い方の注意点メモ

技術的？メモ(spring)｜kochabloさんのﾌﾞﾛｸﾞ

• DIして直接@Transactionalのメソッドを呼ぶこと
• @Transactionalのメソッドより、RuntimeExceptionがthrowされることでロールバック実施となる
• 同一クラス内の@Transactionalメソッドを呼び出すような実装では、ロールバック実施されない
• 一応自分でRuntimeExceptionをthrowすることでも、自発的にロールバックすることは可能

Spring bootでGZIPリクエスト／レスポンスに対応する

Spring bootでGZipを扱う際は、リクエストへの対応かレスポンスへの対応かで対応方法が異なります。

* やりたいこと	* 対応方法
GZIPリクエストに対応する	Filter(GZipFilter)の実装
GZIPでレスポンスを返す	設定の変更

GZIPでレスポンスを返す

Appendix A. Common application properties

server.compression.enabled=false # Whether response compression is enabled.

の設定をtrueにしてやれば、とりあえずGZIP形式でのレスポンスとなる。
有効無効の設定がアプリ全体での切り替えになるので、I/F次第では注意が必要。

GZIPリクエストに対応する

Filterインターフェースを実装して、リクエストのServletInputStreamからGZIPInputStreamを生成する。
Content-Encodingを確認して、対応要否の判断は必要かと思います。
実装例なりGitHubなりすばらしい先人がすでにいるので、ソースについては割愛。
自分も実装時に参考にさせていただきました。

GZIPレスポンスもFilterでの実装で対応可能なようですが、GZIPリクエストを有効にできる設定は現状ないようです。

時間帯重複チェック

期間A(開始日時、終了日時)と、期間B(開始日時、終了日時)の期間が重複しているかのチェック。

期間跨ぎが不可能な条件での重複チェック

年の指定により、開始日時＞終了日時となるケースが発生しないケースでの重複チェック

期間A.終了日時 > 期間B.開始日時 AND 期間A.開始日時 < 期間B.終了日時

期間跨ぎが可能な条件での重複チェック

年を指定しないことにより、開始日時＜終了日時となるケースが発生しないケースでの重複チェック
比較対象となる期間が期間を跨ぐ（開始日時＞終了日時）かによって、判定条件が異なる。

期間A／期間Bともに、期間を跨がない場合

期間A.終了日時 > 期間B.開始日時 AND 期間A.開始日時 < 期間B.終了日時

期間A／期間Bのいずれか一方が、期間を跨ぐ場合

期間A.終了日時 > 期間B.開始日時 OR 期間A.開始日時 < 期間B.終了日時

期間A／期間Bともに、期間を跨ぐ場合

常に重複

となります。

Super CSVを使ってStringをCSVオブジェクトに変換する

CSVの解析は、ちゃんとやろうとすると意外と大変だったりします。
カンマでsplitするだけ、と思っていてはダメで、ダブルクォーテーションによる区切りとそのエスケープまでしないといけません。面倒です。
なので、ライブラリを使用するのが望ましいです。

Super CSV – Welcome

CSVの読み書きをする際に使用しました。
StringからCSVオブジェクトへ変換したかったのですが、例を見かけなかったので整理。
#StringReaderに気づけば、わざわざ悩む内容ではない。。？

CsvListReader csvListReader = new (new StringReader("csv, string, hogehoge"), preferences);

PostgreSQLでHEX文字列を符号ありの数値に変換する

HEX文字列から数値への変換処理は、PostgreSQLのSQLでもできます。

4Byte(8文字)、8Byte(16文字)

4Byte、8Byteの変換は、下記Stack Overflowで回答がつけられています。
そちらを参照のこと。
stackoverflow.com

2Byte(4文字)

自分がやりたかったのは2ByteのHEX文字列変換だったので、上記回答を参考に、以下のようにすることでできました。

SELECT (((('x' || lpad(hex, 4, '0'))::bit(16))::bit(32)::int4)>>16)::int2::numeric(16,4) as short_val
FROM   (
   VALUES ('1'::text)
         ,('f')
         ,('100')
         ,('7fff')
         ,('8000')
         ,('dead')
         ,('ffff')
   ) AS t(hex);
 ||<

結果

>||
   short_val
------------
          1
         15
        256
      32767
     -32768
         -1