Amazon Redshiftで新規追加したスキーマを検索パスに含める

Amazon Redshiftでは、初期構築時にはpublicスキーマ及びその他のシステムに関するスキーマ等が用意されています。また、テーブル構築時にスキーマーの指定を行わない場合はデフォルトでpublicスキーマにテーブルが作成されます。

...という仕様ではありますが、構築するシステムの状況によっては当然新たなスキーマを構築し、publicスキーマ等と併せて設計・運用を進める事にもなって行くでしょう。このエントリではその際のちょっとした一手間メモについて触れておきたいと思います。

新規追加したスキーマ情報を参照するには、『search_path パラメータ』への情報追記が必要

• PG_TABLE_DEF - Amazon Redshift

はい！このエントリでお伝えしたい点はこれだけです。

まず最初に、任意のスキーマを作成します。ここでは本番環境をpublicスキーマと想定し、それに対する検証用環境、または一時的なデータ生成に用いるテーブルなどを集約するスキーマをstagingスキーマと定義する事にして、このstagingスキーマを作成する事にします。

• CREATE SCHEMA - Amazon Redshift

CREATE SCHEMA staging;

次いで、このstagingスキーマに必要なテーブルを作成します。スキーマ名『staging』をテーブル名の前に付与してテーブルを作成。

CREATE TABLE staging.sample_table (
  user_id INT NOT NULL,
  user_name VARCHAR(100) NOT NULL,
  ...,
  ...,
  ...,
  )
distkey(...)
sortkey(...);

この時点で冒頭言及している『search_path』の内容を確認しています。publicスキーマは定義されていますが(当然の事ながら)stagingスキーマは記載がありません。

# show search_path;
      search_path       
------------------------
 $user, public
(1 行)

これがどういう影響を及ぼすかというと、例えば以下エントリでまとめている中の『テーブル一覧を表示する』SQLを実行する場合、下記例ではpublicスキーマを指定していますが、これを仮にスキーマ名指定を外したとしてもstagingスキーマは検索に引っかからないのです。(テーブル：PG_TABLE_DEFの情報にstagingスキーマの内容が含まれない)

• Amazon Redshiftで良く使いそうな便利系SQLをまとめてみた ｜ Developers.IO

SELECT DISTINCT pg_table_def.tablename
FROM pg_table_def
WHERE schemaname = 'public' AND tablename NOT LIKE'%_pkey'
ORDER BY tablename;

ではどうするか。検索パス(search_path)にスキーマ名を含めれば良いだけの話です。ログインセッション内でのみ有効な形で構わないのであれば、以下の様にset文で該当するスキーマを指定すれば以降のコマンドで見れるようになります。

# set search_path to '$user', 'public', 'staging';
SET
# SELECT schemaname, COUNT(schemaname) from pg_table_def GROUP BY schemaname;
 schemaname | count 
------------+-------
 public     |   123
 pg_catalog |  2345
 staging    |    12
 (3 行)

ただ、これだとDBログイン時に毎回指定しなきゃならないし、面倒臭いですよね。そこでもう1つの方法として、Redshiftのパラメータグループにこの内容を指定する欄があるのでこちらに上記の内容を追記し・反映させます。Redshiftクラスタに紐付いているパラメータグループを編集、search_pathの内容に以下の様にスキーマ名を含め、保存→クラスタ再起動の手順を踏むことで、デフォルトで追記したスキーマ名も検索対象に含まれるようになります。

環境や状況に於いては『スキーマを追加する』という作業はそう多くないかも知れませんが、テーブル状況を確認する上では欠かせない要素の1つであり、見落としがちなポイントではあるのかなと思い今回エントリとして書き起こしてみた次第でした。私からは以上です。