Redshift の superuser のパスワードを変更する

Redshift の superuser のパスワードはマネコンまたは AWS CLI から変更できる

Amazon Redshift コンソールを使用して superuser アカウントのパスワードをリセットする

Amazon Redshift コンソールを使用してsuperuser (または adminuser) のパスワードを変更するには、次の手順を実行します。

1. AWS マネジメントコンソールにサインインします。
2. Amazon Redshift コンソールを開きます。
3. [Clusters] (クラスター) を選択します。
4. Amazon Redshift クラスターを選択します。
5. [Actions] (アクション) を選択します。
6. ドロップダウンメニューから、[Change master user password] (マスターユーザーのパスワードの変更) を選択します。
7. 新しいユーザーパスワードを入力します。
8. [Save] (保存) を選択します。これで、管理者アカウントのパスワードが変更されました。
9. 新しいアカウントパスワードを使用して Amazon Redshift にログインし直し、更新されたパスワードを確認します。
注意: パスワードの変更は非同期であり、すぐに適用されます。Amazon Redshift を再起動する必要はありません。

AWS コマンドラインインターフェイス (AWS CLI) を使用して superuser アカウントのパスワードをリセットする

1. AWS CLI を設定します。
2. modify-cluster コマンドを実行し、必要なパスワードを入力します。

aws redshift modify-cluster –cluster-identifier <identifier-for-the cluster> –master-user-password ‘master-password’;

注意: AWS CLI コマンドの実行時にエラーが発生した場合は、AWS CLI の最新バージョンを使用していることを確認してください。
modify-cluster API の詳細については、ModifyCluster を参照してください。
3. 新しいアカウントパスワードを使用して Amazon Redshift にログインし直し、更新されたパスワードを確認します。

https://aws.amazon.com/jp/premiumsupport/knowledge-center/redshift-reset-password/:tilte

psql で Redshift から定期的に S3 に UNLOAD する

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 に出力されたファイル

find で正規表現にマッチするファイルを移動したメモ

macOS(12.3.1) で find で正規表現にマッチするファイルを移動したメモ

find -E . -type f -regex ".*(foo|bar|hoge).*" -exec mv {} ./old/ \;

Amazon Redshift の VACUUM について

実行権限

• テーブルの所有者またはスーパーユーザーのみがテーブルにバキューム処理を実行できる。

以下に、VACUUM に必要な権限を示します。

• スーパーユーザー
• VACUUM の権限を持つユーザー
• テーブルの所有者
• テーブルの共有先であるデータベース所有者

VACUUM - Amazon Redshift

モード

• FULL(デフォルト): ソート＋削除。
• SORT ONLY: ソートのみ。
• DELETE ONLY: 削除のみ。
• REINDEX: Interleaved Sort Keyのメンテナンス。
• VACUUM - Amazon Redshift

排他制御

• VACUUM開始時にテーブルへの一時的な排他アクセスが必要になる。

• ロード操作または挿入操作が既に進行中の場合、バキューム操作を開始できないことがあります。バキューム操作を開始するには、テーブルへの一時的な排他アクセスが必要になります。この排他アクセスは短時間しか必要でないため、バキューム操作により同時ロードと同時挿入が長時間ブロックされることはありません。

VACUUM - Amazon Redshift

• ユーザーは、バキューム処理中のテーブルにアクセスできる。バキューム処理中のテーブルにクエリおよび書き込み操作を実行できるが、データ操作言語 (DML) コマンドおよびバキュームを同時に実行すると両方の処理時間が長くなる可能性がある。バキューム処理中にUPDATEおよびDELETEステートメントを実行する場合は、システムのパフォーマンスが低減する場合がある。VACUUM DELETEは、更新操作と削除操作を一時的にブロックする。

• バキューム操作の実行中、クエリのパフォーマンスがある程度低下することが予想されます。バキューム操作が終了すると直ちに通常のパフォーマンスに戻ります。
• バキュームオペレーション中にも、同時実行書き込みオペレーションは進行しますが、バキューム中の書き込みオペレーションの実行は推奨されていません。バキューム操作を実行する前に、書き込み操作を終了する方がより効率的です。また、バキューム操作開始後に書き込まれたすべてのデータは、その操作でバキュームすることができません。その場合は 2 回目のバキューム操作が必要です。
• ロード操作または挿入操作が既に進行中の場合、バキューム操作を開始できないことがあります。バキューム操作を開始するには、テーブルへの一時的な排他アクセスが必要になります。この排他アクセスは短時間しか必要でないため、バキューム操作により同時ロードと同時挿入が長時間ブロックされることはありません。

VACUUM - Amazon Redshift

その他

• 一度にクラスターで実行できるVACUUMコマンドは1つだけ。

• 一度にクラスターで実行できる VACUUM コマンドは 1 つだけです。複数のバキューム動作を同時に実行しようとすると、Amazon Redshift はエラーを返します。

VACUUM - Amazon Redshift

• トランザクションブロック内でVACUUMは実行できない。

• トランザクションブロック (BEGIN ... END) 内で VACUUM を実行することはできません。

VACUUM - Amazon Redshift

• VACUUMではANALYZEはされない。

VACUUM を減らす

• 以下の条件を全て満たす場合、COPY コマンドでロード時にソートされた状態でロードされほぼ VACUUM 不要。

If you load your data in sort key order using a COPY command, you might reduce or even eliminate the need to vacuum.
COPY automatically adds new rows to the table's sorted region when all of the following are true:

• The table uses a compound sort key with only one sort column.
• The sort column is NOT NULL.
• The table is 100 percent sorted or empty.
• All the new rows are higher in sort order than the existing rows, including rows marked for deletion. In this instance, Amazon Redshift uses the first eight bytes of the sort key to determine sort order.

ソートキー順序でデータをロードする - Amazon Redshift

参考

• Amazon Redshiftの仕様を調べてみた | フューチャー技術ブログ
• VACUUM - Amazon Redshift

Redshift で DROP 文を生成する

Redshift で存在するテーブル、ビュー、スキーマの DROP 文を生成する

• テーブル

select 'drop table '||schemaname||'.'||tablename||' cascade;' from pg_tables where schemaname not in ('public', 'information_schema', 'catalog_history') and schemaname not like 'pg_%';

• ビュー

select 'drop view '||schemaname||'.'||viewname||';' from pg_views where schemaname not in ('public', 'information_schema', 'catalog_history') and schemaname not like 'pg_%';

• スキーマ

select 'drop schema '||nspname||';' from pg_catalog.pg_namespace where nspname not in ('public', 'information_schema', 'catalog_history') and nspname not like 'pg_%';

pyenv を install したけど python not found と怒られる

事象

• pyenv をインストールしたけど、python が見つからないと怒られる。

% pyenv versions
  system
* 3.10.3 (set by /Users/yoheia/.pyenv/version)
% which python   
python not found

解決策

• ~/.zshrc に eval "$(pyenv init --path)" を記述する

export PYENV_ROOT="$HOME/.pyenv"
export PATH="$PYENV_ROOT/bin:$PATH"
eval "$(pyenv init --path)"
eval "$(pyenv init -)"

参考

• pyenv をinstallした後に python not foundとなる問題

関連

• pyenv で Python のバージョンを切替えて使う - ablog

Redshift で pg_table_def に問い合わせてテーブルのソートキーを調べる

Redshift で pg_table_def に問い合わせるとテーブルのソートキーを調べることができる。
調べたいスキーマを set search_path to <スキーマ名>; で指定してやる必要がある。スキーマ一覧は psql なら \dn でリストを表示できる。

クエリ

\pset pager
set search_path to '$user', public, schema1, schema2;

select * from pg_table_def
where sortkey <> 0
and schemaname  not in ('pg_catalog')
order by schemaname, tablename, sortkey;

結果

 schemaname |   tablename    |  column  |          type          | encoding | distkey | sortkey | notnull
------------+----------------+----------+------------------------+----------+---------+---------+---------
 public     | table1 | url  | character varying(768) | none     | f       |       1 | f
 public     | table1 | device   | character varying(60)  | none     | f       |       2 | f
 public     | table1 | log_date | date                   | none     | f       |       3 | t
 public     | table1 | uid     | character varying(78)  | none     | f       |       1 | f
 public     | table2 | url  | character varying(768) | none     | f       |       1 | f
 public     | table2 | device   | character varying(60)  | none     | f       |       2 | f
 public     | table2 | log_date | date                   | none     | f       |       3 | t
(7 rows)

参考

• https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_PG_TABLE_DEF.html:tilte
• search_path - Amazon Redshift
• PostgreSQL | 作成済みのスキーマ一覧を表示する

スキーマ一覧

# \dn

[PostgreSQL] よく使うコマンドまとめ | DevelopersIO