psql で Redshift から定期的に S3 に UNLOAD したメモ。
- Redshift に接続する。
export LC_ALL=C /usr/pgsql-13/bin/psql "host=redshift-cluster-2.********.ap-northeast-1.redshift.amazonaws.com port=5439 dbname=dev user=awsuser"
- 10秒間隔で tv_query_metrics への問合せ結果を S3 に UNLOAD する。
select '\'s3://redshift-unload-yoheia/stv_query_metrics/stv_query_metrics_'||to_char(getdate(), 'YYYYMMDD-HH24MISS')||'\'' as s3_path; -- S3 出力先パスを生成 \gset -- 変数にセット unload ('select * from stv_query_metrics') to :s3_path -- 生成したタイムスタンプを含むパスに出力 iam_role 'arn:aws:iam::123456789012:role/RedshiftCopyUnloadCrossAccountRole' format as csv gzip allowoverwrite; -- 指定しないと同じパスにファイルがあるとエラーになる \watch 10 -- 10秒間隔で繰り返す
実行結果
$ /usr/pgsql-13/bin/psql "host=redshift-cluster-2.*********.ap-northeast-1.redshift.amazonaws.com port=5439 dbname=dev user=awsuser" psql (13.6, server 8.0.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. dev=# select '\'s3://redshift-unload-yoheia/stv_query_metrics/stv_query_metrics_'||to_char(getdate(), 'YYYYMMDD-HH24MISS')||'\'' as s3_path; s3_path ------------------------------------------------------------------------------- 's3://redshift-unload-yoheia/stv_query_metrics/stv_query_metrics_20220501-225032' (1 row) dev=# \gset dev=# unload ('select * from stv_query_metrics') dev-# to :s3_path dev-# iam_role 'arn:aws:iam::123456789012:role/RedshiftCopyUnloadCrossAccountRole' dev-# format as csv dev-# gzip dev-# allowoverwrite; INFO: UNLOAD completed, 0 record(s) unloaded successfully. UNLOAD dev=# \watch 10 INFO: UNLOAD completed, 0 record(s) unloaded successfully. Sun May 1 22:50:32 2022 (every 10s) UNLOAD INFO: UNLOAD completed, 0 record(s) unloaded successfully. Sun May 1 22:50:42 2022 (every 10s) UNLOAD INFO: UNLOAD completed, 0 record(s) unloaded successfully. Sun May 1 22:50:53 2022 (every 10s) UNLOAD INFO: UNLOAD completed, 0 record(s) unloaded successfully. Sun May 1 22:51:03 2022 (every 10s) UNLOAD INFO: UNLOAD completed, 0 record(s) unloaded successfully. Sun May 1 22:51:14 2022 (every 10s) UNLOAD
- S3 に出力されたファイル
