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