Amazon Redshift: 検索パス(search_path)とSQLのスキーマ指定の関係について

小ネタです。

Amazon Redshiftクラスタ環境上に於いて、スキーマを分けて運用管理をする事は往々にしてある事と思われますが、その際に同じテーブル名でスキーマを分けて作成する、という事も状況に拠ってはやる手段であるかと思います。運用面からの必要性であったり、又は開発環境として利用するために...という感じです。

その際、それら作成したテーブルに対してSQLアクセスを行う際の『スキーマ指定』について挙動が異なるんだけどこれは一体...という声が挙がっておりましたので、調べてみました。『検索パス(search_path)』という要素が関連していたのですが、その辺りの解説を簡単な実例を踏まえてご紹介してみたいと思います。

『検索パス』の設定値によってテーブルアクセスの順序を制御

そのものズバリな回答が以下公式ページに掲載されていますね。要はそう言う事らしいです。

• search_path - Amazon Redshift

動作検証：検索パスを指定していない場合

まずはpublicスキーマにテーブルcmtesttable1を作成し、データを投入します。

# CREATE TABLE public.cmtesttable1 ( id int NOT NULL, name VARCHAR(20) NOT NULL );
CREATE TABLE
# INSERT INTO public.cmtesttable1 VALUES(1,'AAAA');
INSERT 0 1
# INSERT INTO public.cmtesttable1 VALUES(2,'BBBB');
INSERT 0 1
# INSERT INTO public.cmtesttable1 VALUES(3,'CCCC');
INSERT 0 1

次に新しくdevelopersioというスキーマを作成、同名のcmtesttable1テーブルを作成します。

# CREATE SCHEMA developersio;
CREATE SCHEMA
# CREATE TABLE developersio.cmtesttable1 ( id int NOT NULL, name VARCHAR(20) NOT NULL );
CREATE TABLE
# INSERT INTO developersio.cmtesttable1 VALUES(11,'AAAA');
INSERT 0 1
# INSERT INTO developersio.cmtesttable1 VALUES(22,'BBBB');
INSERT 0 1
# INSERT INTO developersio.cmtesttable1 VALUES(33,'CCCC');
INSERT 0 1
# INSERT INTO developersio.cmtesttable1 VALUES(44,'DDDD');
INSERT 0 1

それぞれスキーマ名を指定(public/developersio)する場合、しない場合で検索を掛けてみます。publicスキーマはクラスタ作成時点で検索パスに含まれていますので、この場合はpublicスキーマのテーブルを見に行っている事が分かります。

# SELECT * FROM public.cmtesttable1;
 id | name 
----+------
  1 | AAAA
  2 | BBBB
  3 | CCCC
(3 rows)

# SELECT * FROM developersio.cmtesttable1;
 id | name 
----+------
 11 | AAAA
 33 | CCCC
 44 | DDDD
 22 | BBBB
(4 rows)

# SELECT * FROM cmtesttable1;
 id | name 
----+------
  1 | AAAA
  2 | BBBB
  3 | CCCC
(3 rows)

続けて、publicには存在しないcmtesttable2というテーブルをdevelopersioスキーマにのみ作成します。

# CREATE TABLE developersio.cmtesttable2 ( id int NOT NULL, name VARCHAR(20) NOT NULL );
CREATE TABLE
# INSERT INTO developersio.cmtesttable2 VALUES(111,'AAAA');
INSERT 0 1
# INSERT INTO developersio.cmtesttable2 VALUES(222,'BBBB');
INSERT 0 1
# INSERT INTO developersio.cmtesttable2 VALUES(333,'CCCC');
INSERT 0 1
# INSERT INTO developersio.cmtesttable2 VALUES(444,'DDDD');
INSERT 0 1

SELECT文による動作確認を改めて実施。publicスキーマ指定でエラーとなるのは当然ですが、スキーマ未指定の場合でも同様にエラーとなっています。こちらはdevelopersioスキーマにしか該当テーブルが無い事、且つこのスキーマが検索パスに登録されていない事が要因となっています。

# SELECT * FROM public.cmtesttable2;
ERROR:  relation "public.cmtesttable2" does not exist

# SELECT * FROM developersio.cmtesttable2;
 id  | name 
-----+------
 222 | BBBB
 333 | CCCC
 111 | AAAA
 444 | DDDD
(4 rows)

# SELECT * FROM cmtesttable2;
ERROR:  relation "cmtesttable2" does not exist

動作検証：検索パスを指定した場合

これが検索パスを指定するとどうなるか。クラスタのパラメータグループにsearch_pathという要素がありますので、この内容にdevelopersioスキーマを追加・保存します。(保存後はRedshiftクラスタの再起動を行なってください)

Redshiftクラスタにログインし、SHOWコマンドで内容を確認してみます。developersioもちゃんと追加されていますね。

# SHOW search_path;
                              search_path                               
------------------------------------------------------------------------
 $user, public, (中略), developersio
(1 row)

改めてcmtesttable2テーブルに対するアクセス確認をしてみましょう。スキーマ名を付けた場合の2つのSQLは先程と同じ挙動ですが、スキーマ名が無くても表示が出来ています。これは、developersioスキーマを検索パス追加した事で

• publicスキーマに対しcmtesttable2テーブルを見に行く→無い
• developersioスキーマに対しcmtesttable2テーブルを見に行く→ある

という順序でテーブルを見つけ出せている、という流れになる訳ですね。(当然の事ながら、publicスキーマにcmtesttable2テーブルを追加してスキーマ名指定無しで実行した場合は、search_pathの登録順序により、publicスキーマの内容が優先して表示される形になります)

# SELECT * FROM public.cmtesttable2;
ERROR:  relation "public.cmtesttable2" does not exist
# SELECT * FROM developersio.cmtesttable2;
 id  | name 
-----+------
 444 | DDDD
 111 | AAAA
 222 | BBBB
 333 | CCCC
(4 rows)

# SELECT * FROM cmtesttable2;
 id  | name 
-----+------
 444 | DDDD
 111 | AAAA
 333 | CCCC
 222 | BBBB
(4 rows)

まとめ

以上、スキーマ名指定有無による挙動の違いと、『検索パス』との関連性についてのご紹介でした。仕組みを把握していればこの辺り問題ないかとは思いますが、想定外の挙動や結果を返す事を避ける為にも、Redshiftへのアクセスを行うSQLを記述する際は必ずスキーマ名を付与した状態で書いておくのが一番安全かなと思います。こちらからは以上です。