■ はじめに
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