Amazon Redshift: 作成済ビューの定義を確認する

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

小ネタです。

Amazon Redshiftでは一般的なデータベースと同様に『ビュー』を作成出来ますが、ビューが参照しているテーブルの定義の変更を行う場合には(テーブルの変更が行えないので)一旦削除せざるを得ない場合があります。当然処置が済んだ後は再作成する必要があるのですが、その再作成する為の元ネタ(SQL文が)手元に無い!というような時に使える情報になるかと思います。

実践

では実践を踏まえて試してみましょう。VIEW作成の参考にするのは以下のテーブルとなります。Tableauで良く用いられているSuperstoreの[Orders]ファイルの情報をテーブルに起こしたものですね。

# \d public.orders;
                   Table "public.orders"
        Column        |          Type          | Modifiers 
----------------------+------------------------+-----------
 order_id             | integer                | not null
 order_date           | date                   | not null
 priority             | character varying(12)  | not null
 quantity             | smallint               | not null
 sales                | double precision       | 
 discount_rate        | double precision       | 
 ship_mode            | character varying(20)  | not null
 profit               | integer                | not null
 unit_price           | integer                | not null
 ad_expenses          | integer                | not null
 shipping_cost        | integer                | not null
 customer_name        | character varying(50)  | not null
 prefecture           | character varying(12)  | not null
 city                 | character varying(20)  | not null
 area                 | character varying(12)  | not null
 shop_name            | character varying(20)  | not null
 customer_segment     | character varying(30)  | not null
 product_category     | character varying(30)  | not null
 product_sub_category | character varying(100) | not null
 product_id           | character varying(10)  | not null
 product_name         | character varying(100) | not null
 product_description  | character varying(200) | not null
 product_container    | character varying(100) | not null
 base_margin          | double precision       | 
 supplier             | character varying(30)  | not null
 deliver_date         | date                   | not null
 ship_date            | date                   | not null
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)

上記テーブル定義を参考にして、CREATE VIEWコマンドでビューを作成します。

# CREATE VIEW public.orders_digest AS
  (SELECT order_id, order_date, sales, prefecture FROM public.orders);
CREATE VIEW

で、作成したVIEWの定義情報がどこにあるかと言いますと、カタログテーブルの情報としてpg_viewsテーブルの中にその情報が格納されています。以下はスキーマ名とテーブル名を指定して、その定義(definition)を表示させたものです。上記で作成したCREATE VIEW文がそのまま格納されていますね!

# SELECT
    schemaname,
    viewname,
    viewowner,
    definition
  FROM
    pg_views
  WHERE
    schemaname = 'public' AND viewname = 'orders_digest';
    
 schemaname |   viewname    | viewowner |                                       definition                                        
------------+---------------+-----------+-----------------------------------------------------------------------------------------
 public     | orders_digest | XXXXXXXX  | SELECT orders.order_id, orders.order_date, orders.sales, orders.prefecture FROM orders;
(1 row)

追記:

エントリ公開後、同僚から『もっと簡単な方法がある』とツッコミを受けましたので以下に追記します。\d+ (ビュー名)で一発で出せるようです。こちらの方が簡単ですね!

# \d+ public.orders_digest
                       View "public.orders_digest"
   Column   |         Type          | Modifiers | Storage  | Description 
------------+-----------------------+-----------+----------+-------------
 order_id   | integer               |           | plain    | 
 order_date | date                  |           | plain    | 
 sales      | double precision      |           | plain    | 
 prefecture | character varying(12) |           | extended | 
View definition:
 SELECT orders.order_id, orders.order_date, orders.sales, orders.prefecture
   FROM orders;

Amazon Redshiftに於いてもビューは便利なものですので活用するケースも多いかと思いますが、DWHとしてデータの入れ替えやテーブル構造の変更等が入る場合には、それら対象となるテーブルを参照しているビューも影響を受ける事が多いかと思われます。そんな時にこの情報を活用出来れば移行作業もスムーズに進みますね!スキーマ名やビュー名が所定の範囲に絞れているのであれば、作業として自動的に定義を抽出->一括削除->一括作成、のような仕組みも作れるかも知れませんね。こちらからは以上です。

参考情報