Amazon Redshiftに対してpsqlコマンドでSQLファイル＆バインド変数を使ってアクセスする

小ネタです。

バッチ処理でスクリプトからコマンドを実行してAmazon Redshiftにアクセスさせたい、という場合にまずパッと思い浮かぶもののうちの1つが『psqlコマンド』になるかと思いますが、psqlコマンドでは『SQLファイルを利用』する事と『バインド変数』を利用する事が出来ます。PostgreSQLでは勿論の事、(PostgreSQL8.0を準拠している)Amazon Redshiftでも同様にこの手法は活用させる事が出来ますので簡単な実例を用いてその手順をご紹介してみたいと思います。

• psql

psqlコマンドはpsql --helpを実行する事でオプションが表示されます。

-fオプションで『ファイルからコマンドを読み込んで実行後、終了』、 また-vオプションを使って『psql 変数 '名前' に '値' をセット』させる事が可能である事が分かります。

この2つのオプションを使って実際に動作確認をしてみたいと思います。

$ psql --help
psql は PostgreSQL の会話型ターミナルです。

使用方法:
  psql [オプション]... [データベース名 [ユーザ名]]

一般的なオプション：
  -c, --command=コマンド   (SQLまたは内部の)単一コマンドを一つだけ実行して終了
  -d, --dbname=DB名        接続するデータベース名を指定(デフォルト: "ec2-user")
  -f, --file=ファイル名    ファイルからコマンドを読み込んで実行後、終了
  -l(エル), --list         使用可能なデータベース一覧を表示して終了
  -v, --set=, --variable=名前=値
                           psql 変数 '名前' に '値' をセット
  -V, --version             バージョン情報を表示し、終了します
  -X, --no-psqlrc          初期化ファイル (~/.psqlrc) を読みこまない
  -1(数字の１), --single-transaction
                           単一のトランザクションとしてコマンドファイルを実行
  -?, --help                このヘルプを表示し、終了します
   :
 (以下後略)

まずは以下の形で検証用にシンプルなテーブルを用意します。

CREATE TABLE public.psqloptiontest(
  id int NOT NULL,
  username VARCHAR(30) NOT NULL,
  reg_date DATE NOT NULL,
  reg_timestamp TIMESTAMP NOT NULL
);

データ投入のINSERT文は以下の様な形となります。(※今回は値を変数渡しで連携して実際にその値がちゃんと登録されている事を確認する為にINSERT文を用いています)

# INSERT INTO public.psqloptiontest
VALUES(1,'cm-user','2017-02-08',CONVERT_TIMEZONE('Asia/Tokyo',SYSDATE));
INSERT 0 1

# SELECT * FROM public.psqloptiontest ORDER BY reg_timestamp;
 id | username |  reg_date  |       reg_timestamp        
----+----------+------------+----------------------------
  1 | cm-user  | 2017-02-08 | 2017-02-08 23:28:26.008163
(1 row)

投入に用いるSQLファイルを以下の形で用意します。トランザクション内で実行することを想定してBEGIN-COMMITを記述していたり、実行時間を記録しておきたいので\timingを冒頭に実行してたりしますがポイントは3行目のみとなります。変数部分については:変数名の形で記載しておく必要があります。

psqloptiontest.sql

\timing
BEGIN;
INSERT INTO public.psqloptiontest VALUES(:id, :username, :exec_date, CONVERT_TIMEZONE('Asia/Tokyo',SYSDATE));
COMMIT;

以下が実際のpsql実行構文です。見易さ考慮のため改行を加えていますが実際は1行で実行する形となります。

2行目でファイルパスを指定(今回はEC2上で検証を行ったため、以下のパスとしてみました)、

3行目で必要な変数を-v 変数名=値の形で列挙しています。変数の内容については数値項目はクオート等の付与なし、文字列や日付に付いては別途以下の形で囲んでおいてあげる必要がありました。

$ psql -d cmdb -h xxx.xx.xxx.xx -p 5439 -U cmuser \
-f /home/ec2-user/psqloptiontest.sql \
-v id=987 -v username="'cmpsqluser'" -v exec_date="'2017-02-08'"

以下が実行結果となります。所定の内容を引き継いでちゃんと登録出来ている事が確認出来ました。

# SELECT * FROM public.psqloptiontest ORDER BY reg_timestamp;
 id  |  username  |  reg_date  |       reg_timestamp        
-----+------------+------------+----------------------------
   1 | cm-user    | 2017-02-08 | 2017-02-08 23:28:26.008163
 987 | cmpsqluser | 2017-02-08 | 2017-02-08 23:43:40.425059
(2 rows)

まとめ

小ネタ且つ非常に基本的な内容ではありますが、これらの機能を活用する事でプロジェクトでのバッチ処理でもより便利且つスムーズな処理の実行、プログラムの作成管理が行えるかと思います。Redshiftを活用する際・バッチプログラムを作成する際にはご参考頂けますと幸いです。