【Shell】【PostgreSQL】シェルでSQL結果を受け取る

■ はじめに

 Amazon EMR で シェル経由で
Amazon Redshift に対して SQL を実行し、
そのシェルが実行結果を受け取り、
結果によって、エラーにするか / 正常終了にするか
ってことを実装したい。

 そこで、まずは、Amazon Redshift の元となっている
PostgreSQL をシェルで実行して、SQLの実行結果を受け取ることを
やってみる。

目次

【1】検証環境
【2】前提条件
【3】関連する psql の オプション 
【4】サンプル
 例1:SQL結果を取得して表示
 例2:SELECT COUNT(*) の結果から処理を切り替える
 例3:SQL結果一覧(複数項目)を取得して表示
 例4:ファイルを使わずにSQLを実行し結果を表示
【5】使用上の注意
 1)-v 使用時の注意
 2)空白文字の扱い
【6】実験
 例1:値が返却されない場合
 例2:エラーハンドリング

【1】検証環境

* OS : Windows10
* シェル実行環境 : Git Bash
* DB : PostgreSQL v12.1

【2】前提条件

* psql の パスワードを省略する設定が必要。
 =>そうしないと、結果が返ってこないので、
  フリーズしたみたいになる。
 => 詳細は、以下の関連記事を参照のこと。

psqlでパスワードを省略する
https://dk521123.hatenablog.com/entry/2020/03/06/000000

【3】関連する psql の オプション

サンプルで使用されている psql の オプション について記す

https://www.postgresql.jp/document/9.4/html/app-psql.html

# 略式 オプション 説明
1 -t --tuples-only 列名と結果の行数フッタなどの表示を無効
2 -A --no-align 位置揃えなしの出力モード
3 -q --quiet psqlがメッセージ出力なしで処理
4 -X --no-psqlrc 起動用ファイル(psql全体の設定など)を読み込まない
5 -F [separator] --field-separator=[separator] [separator]を位置揃えを行わない出力における区切り文字として使用
6 -c [command] --command=[command] [command(SQL文)]を実行する(例4を参照)
7 -v [assignment] --set=assignment --variable=assignment [assignment]を引数として実行する(使用上の注意や例2などを参照)

【4】サンプル

検証用データ

--  テーブル
CREATE TABLE IF NOT EXISTS "users" (
  "user_id" INTEGER NOT NULL,
  "user_name" VARCHAR(50) NOT NULL,
  "created_at" TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP',
  PRIMARY KEY ("user_id")
);

-- デモデータ
INSERT INTO "users" ("user_id", "user_name", "created_at") VALUES
  (1, 'Mike', '2019-12-19 21:50:21.3885'),
  (2, 'Tom', '2019-12-19 21:50:21.3885'),
  (3, 'Sam', '2019-12-19 21:50:21.3885'),
  (4, 'Kevin', '2019-12-19 21:50:27.483248'),
  (5, 'Smith', '2019-12-19 21:50:27.484514'),
  (6, 'Ken', '2019-12-19 21:50:27.484514'),
  (10, 'Sam', '2020-08-26 22:39:55.175736'),
  (12, 'Naomi', '2020-08-26 22:39:55.175736'),
  (20, 'Tim', '2021-08-02 16:39:55.175736'),
  (30, 'Kevin', '2021-08-02 16:40:55.175736');

例1:SQL結果を取得して表示

 まず手始めに、
SQLをシェルで実行し、結果を表示することをやってみる。
以下を参考。

https://stackoverflow.com/questions/28451598/how-to-return-a-value-from-psql-to-bash-and-use-it

sample_ex1.sql

SELECT
  COUNT(*) AS user_count
FROM
  users
;

sample_ex1.sh

#!/bin/bash

result=$(psql -qtAX -h localhost -p 5432 -d sample_db -U postgres -f sample_ex1.sql)
echo $result

# psql Options
# -t only tuple
# -A output not unaligned
# -q quiet
# -X Don't run .psqlrc file

実行結果

$ ./sample_ex1.sh
1

例2:SELECT COUNT(*) の結果から処理を切り替える

 SQL文「SELECT COUNT(*)」の結果を受け取り、
データが1件以上あれば、シェルを正常終了(exit 0)。
データがなければ、シェルをエラー(exit 1)にする
(上述「■ はじめに」で書いた 今回 やりたかったこと)

sample_ex2.sql

SELECT
  COUNT(*) AS user_count
FROM
  users
WHERE
  user_name=:target_name
;

sample_ex2.sh

#!/bin/bash

# {1} : target user name (e.g. 'Mike')
TARGET_USER_NAME=${1}

echo "TARGET_USER_NAME = ${TARGET_USER_NAME}"

user_count=$(psql -qtAX -h localhost -p 5432 -d sample_db -U postgres -f sample_ex2.sql -v target_name="'${TARGET_USER_NAME}'")

echo "user_count = ${user_count}"

# ${user_count} > 0
if [ ${user_count} -gt 0 ]; then
  echo "OK"
  exit 0
else
  echo "ERROR..."
  exit 1
fi

実行結果

$ ./sample_ex2.sh Mike
TARGET_USER_NAME = Mike
user_count = 1
OK

$ ./sample_ex2.sh XXX
TARGET_USER_NAME = XXX
user_count = 0
ERROR...

例3:SQL結果一覧(複数項目)を取得して表示

調査していて以下のサイトが気になったので、試してみた。
複数項目の複数データのSQL結果を取得して、表示する。

http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-sql_21.html
sample_ex3.sql

SELECT
  user_id,
  user_name,
  created_at
FROM
  users
;

sample_ex3.sh

#!/bin/bash

psql \
  -h localhost  \
  -p 5432 \
  -d sample_db \
  -U postgres \
  --no-align \
  -t \
  --field-separator '|' \
  -q \
  -f "sample_ex3.sql" \
| while IFS='|' read user_id user_name created_at ; do

  echo "*****************"
  echo "user_id = ${user_id}"
  echo "user_name = ${user_name}"
  echo "created_at = ${created_at}"

done

実行結果

$ ./sample_ex3.sh
*****************
user_id = 1
user_name = Mike
created_at = 2019-12-19 21:50:21.3885
*****************
user_id = 2
user_name = Tom
created_at = 2019-12-19 21:50:21.3885
*****************
user_id = 3
user_name = Sam
created_at = 2019-12-19 21:50:21.3885
*****************
user_id = 4
user_name = Kevin
created_at = 2019-12-19 21:50:27.483248
*****************
user_id = 5
user_name = Smith
created_at = 2019-12-19 21:50:27.484514
*****************
user_id = 6
user_name = Ken
created_at = 2019-12-19 21:50:27.484514
*****************
user_id = 10
user_name = Sam
created_at = 2020-08-26 22:39:55.175736
*****************
user_id = 12
user_name = Naomi
created_at = 2020-08-26 22:39:55.175736
*****************
user_id = 20
user_name = Tim
created_at = 2021-08-02 16:39:55.175736
*****************
user_id = 30
user_name = Kevin
created_at = 2021-08-02 16:40:55.175736

例4:ファイルを使わずにSQLを実行し結果を表示

* 例3をベースに作成
 => メインの変更点は「--command "${sql}"」のみ
 => もちろん「--command "SELECT * FROM users ..."」としてもOK

sample_ex4.sh

#!/bin/bash

sql=`cat << EOS
  SELECT
    user_id,
    user_name,
    created_at
  FROM
    users
  WHERE
    user_name = 'Sam'
  ;
EOS
`

psql \
  -h localhost  \
  -p 5432 \
  -d sample_db \
  -U postgres \
  --no-align \
  -t \
  --field-separator $'\t' \
  -q \
  --command "${sql}" \
| while IFS=$'\t' read user_id user_name created_at ; do

  echo "*****************"
  echo "user_id = ${user_id}"
  echo "user_name = ${user_name}"
  echo "created_at = ${created_at}"

done

出力結果

$ ./sample_ex4.sh
*****************
user_id = 3
user_name = Sam
created_at = 2019-12-19 21:50:21.3885
*****************
user_id = 10
user_name = Sam
created_at = 2020-08-26 22:39:55.175736

使用上の注意

1)-v 使用時の注意

https://dk521123.hatenablog.com/entry/2021/08/01/000000

でも記載したが、
「データ側が数字以外の場合、囲い文字を意識する」

 以下の例「sample.sql」の該当箇所を
「':name' AS user_name,」ってやるとそのまま文字列 ':name' として扱う。
エラーにならずに、そのままデータとして ':name' が INSERT されてしまうので
やっかいなので注意。(ってことを何回もやってしまったのでメモっておく)

#!/bin/bash

psql \
  -h localhost  \
  -p 5432 \
  -d sample_db \
  -U postgres \
  --no-align \
  -t \
  --field-separator '|' \
  -q \
  -f "sample.sql" \
  -v name="'Levin'" # 囲み文字「'」まで渡す

sample.sql

INSERT INTO "users" ("user_id", "user_name", "created_at")
SELECT
  99 AS user_id,
  -- ★注目★ここは、囲み文字しないこと!
  :name AS user_name,
  created_at AS created_at
FROM
  users
WHERE user_name='Tim'
;

2)空白文字の扱い

* 項目に空白文字がある場合は、位置ずれを起こす可能性がある
 => 対象方法としては、半角SPなど何らかの文字を入れておく等がある

SELECT
  -- 位置ずれが発生する
  '' AS v1
  -- 位置ずれ対応例(置換)
  translate(item, '', ' ')
;

【6】実験

例1:値が返却されない場合

* 結論から言うと、何も来ないから、
 処理によっては、チェックする必要がある
 => チェックの仕方については、以下の関連記事を参照のこと。

https://dk521123.hatenablog.com/entry/2015/05/01/000043
サンプル

#!/bin/bash

# {1} : target user name (e.g. 'Mike')
TARGET_USER_NAME=${1}

echo "TARGET_USER_NAME = ${TARGET_USER_NAME}"

result=$(psql -qtAX -h localhost -p 5432 -d sample_db -U postgres -f experiment.sql -v target_name="'${TARGET_USER_NAME}'")

echo "result = ${result}"

if [ -z "$result" ]; then
  echo "Empty..."
else
  echo "Not Empty!!!"
fi

出力結果

TARGET_USER_NAME = Mike
result = 1
Not Empty!!!

$ ./sample.sh xxx
TARGET_USER_NAME = xxx
result =
Empty...

例2:エラーハンドリング

* 以下の関連記事を参照のこと。

シェルでSQL結果を受け取る ~ エラーハンドリング編 ~
https://dk521123.hatenablog.com/entry/2021/08/20/100814

関連記事

シェル ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2014/10/23/005406
シェルでSQL結果を受け取る ~ エラーハンドリング編 ~
https://dk521123.hatenablog.com/entry/2021/08/20/100814
psql コマンドでファイル実行した際に引数を渡すには
https://dk521123.hatenablog.com/entry/2021/08/01/000000
psqlでパスワードを省略する
https://dk521123.hatenablog.com/entry/2020/03/06/000000
シェル ~ 基本編・条件分岐 if / case ~
https://dk521123.hatenablog.com/entry/2015/05/01/000043
シェルスクリプトあれこれ
https://dk521123.hatenablog.com/entry/2018/03/03/210642
ヒアドキュメント ~ 複数行の テキストをファイル出力する ~
https://dk521123.hatenablog.com/entry/2016/05/13/231535