Amazon Redshift 共有データにサクッとアクセスするビュー作成を自動化する方法
データアナリティクス事業本部コンサルティングチームの石川です。Redshiftのデータ共有は、Redshiftクラスタ間でライブデータを共有できる機能です。本日は、その共有データにサクッとアクセスするビュー作成を自動化する方法についてご紹介します。
Data Sharingする方法と、その課題
Redshiftのデータ共有は、「データ共有」という器にスキーマやテーブル、ビューを追加して他のクラスタに共有します。共有されたクラスタは、「データ共有」からデータベースを作成することでデータにアクセスできるようになります。データ共有する方法は、以下のブログをご覧ください。
しかし、Data Sharingしたオブジェクト参照で用いられる<database>.<schema>.<object>の３つをドットで修飾した表記法は、BIツールなどでは、データベースの指定がサポートされていないためスキーマが参照できません。BIツールからスキーマとして参照できるようにするには、更に<database>.<schema>の部分を外部スキーマとして登録し、<external_schema>.<table>の形式で再定義することで回避できます。
そのためには、再定義するためのビューを作成します。数多くの共有するテーブルやビュー、そのカラムを再定義するビューを作成、継続的にメンテナンスするには、大変な作業です。
共有データにサクッとアクセスするビュー作成を自動化する
そこで、共有元のクラスタのメタデータを利用して、ビュー作成を自動化してしまおうと考えました。
サンプルのデータベースオブジェクト
今回は、テーブルやビュー、更にコメントも自動生成、オブジェクト名を日本語に設定しています。
以下の例では、tickitスキーマにある以下のオブジェクトのビューを自動作成します。
dev=# set search_path to tickit;
SET
dev=# \d
List of relations
schema | name | type | owner
--------+----------------------------+-------+--------
tickit | category | table | tickit
tickit | date | table | tickit
tickit | event | table | tickit
tickit | listing | table | tickit
tickit | mv_tbl__イベント_mv__0 | table | rdsdb
tickit | mv_tbl__カテゴリ_mv__0 | table | rdsdb
tickit | mv_tbl__ユーザ_mv__0 | table | rdsdb
tickit | mv_tbl__リスティング_mv__0 | table | rdsdb
tickit | mv_tbl__日付_mv__0 | table | rdsdb
tickit | mv_tbl__販売_mv__0 | table | rdsdb
tickit | mv_tbl__開催_mv__0 | table | rdsdb
tickit | sales | table | tickit
tickit | users | table | tickit
tickit | venue | table | tickit
tickit | イベント | view | tickit
tickit | イベント_l | view | tickit
tickit | イベント_mv | view | tickit
tickit | カテゴリ | view | tickit
tickit | カテゴリ_l | view | tickit
tickit | カテゴリ_mv | view | tickit
tickit | ユーザ | view | tickit
tickit | ユーザ_l | view | tickit
tickit | ユーザ_mv | view | tickit
tickit | リスティング | view | tickit
tickit | リスティング_l | view | tickit
tickit | リスティング_mv | view | tickit
tickit | 日付 | view | tickit
tickit | 日付_l | view | tickit
tickit | 日付_mv | view | tickit
tickit | 販売 | view | tickit
tickit | 販売_l | view | tickit
tickit | 販売_mv | view | tickit
tickit | 開催 | view | tickit
tickit | 開催_l | view | tickit
tickit | 開催_mv | view | tickit
(35 rows)
tickitスキーマの全てのオブジェクトのビューを作成するSQL
最後の行に自動生成したいスキーマ「tickit」を指定します。自動生成されたSQLを共有先のクラスタで実行します。
SELECT ddl FROM (
SELECT
table_id
,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
,seq
,ddl
FROM
(
SELECT
table_id
,schemaname
,tablename
,seq
,ddl
FROM
(
--SET SESSION AUTHORIZATION
SELECT c.oid::bigint as table_id ,n.nspname AS schemaname, c.relname AS tablename, 0 AS seq,
'SET SESSION AUTHORIZATION ' + QUOTE_IDENT(s.usename) +';' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_user AS u ON c.relowner = u.usesysid
INNER JOIN pg_user AS s ON n.nspowner = s.usesysid
WHERE c.relkind in ('r', 'v')
--DROP VIEW
UNION SELECT
c.oid::bigint as table_id
,n.nspname AS schemaname
,c.relname AS tablename
,1 AS seq
,'DROP VIEW IF EXISTS ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ';' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
WHERE c.relkind in ('r', 'v')
--CREATE VIEW
UNION SELECT
c.oid::bigint as table_id
,n.nspname AS schemaname
,c.relname AS tablename
,2 AS seq
,'CREATE VIEW ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + '' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
WHERE c.relkind in ('r', 'v')
--OPEN PAREN COLUMN LIST
UNION SELECT c.oid::bigint as table_id,n.nspname AS schemaname, c.relname AS tablename, 5 AS seq, '(\nSELECT' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
WHERE c.relkind in ('r', 'v')
--COLUMN LIST
UNION SELECT
table_id
,schemaname
,tablename
,seq
,'\t' + col_delim + col_name AS ddl
FROM
(
SELECT
c.oid::bigint as table_id
,n.nspname AS schemaname
,c.relname AS tablename
,100000000 + a.attnum AS seq
,CASE WHEN a.attnum > 1 THEN ',' ELSE '' END AS col_delim
,QUOTE_IDENT(a.attname) AS col_name
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
LEFT OUTER JOIN pg_attrdef AS adef ON a.attrelid = adef.adrelid AND a.attnum = adef.adnum
WHERE c.relkind in ('r', 'v')
AND a.attnum > 0
ORDER BY a.attnum
)
--CLOSE PAREN COLUMN LIST
-- UNION SELECT c.oid::bigint as table_id,n.nspname AS schemaname, c.relname AS tablename, 299999999 AS seq, 'FROM {{dbname}}.' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + '\n) WITH NO SCHEMA BINDING;' AS ddl
UNION SELECT c.oid::bigint as table_id,n.nspname AS schemaname, c.relname AS tablename, 299999999 AS seq, 'FROM ' + QUOTE_IDENT(s.usename) + '_ds.' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + '\n) WITH NO SCHEMA BINDING;' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_user AS s ON n.nspowner = s.usesysid
WHERE c.relkind in ('r', 'v')
--COMMENT
UNION SELECT c.oid::bigint AS table_id,
n.nspname AS schemaname,
c.relname AS tablename,
600250000 AS seq,
('COMMENT ON '::text + nvl2(cl.column_name, 'column '::text, 'table '::text) + quote_ident(n.nspname::text) + '.'::text + quote_ident(c.relname::text) + nvl2(cl.column_name, '.'::text + cl.column_name::text, ''::text) + ' IS \''::text + des.description + '\'; '::text)::character VARYING AS ddl
FROM pg_description des
JOIN pg_class c ON c.oid = des.objoid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN information_schema."columns" cl
ON cl.ordinal_position::integer = des.objsubid AND cl.table_name::NAME = c.relname
WHERE c.relkind in ('r', 'v')
--RESET SESSION AUTHORIZATION
UNION SELECT c.oid::bigint as table_id ,n.nspname AS schemaname, c.relname AS tablename, 600600000 AS seq,
'RESET SESSION AUTHORIZATION;' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_user AS u ON c.relowner = u.usesysid
WHERE c.relkind in ('r', 'v')
-- END NEW LINE
UNION SELECT c.oid::bigint as table_id ,n.nspname AS schemaname, c.relname AS tablename, 999999999 AS seq,
'' AS ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_user AS u ON c.relowner = u.usesysid
WHERE c.relkind in ('r', 'v')
)
ORDER BY table_id, schemaname, tablename, seq
)
)
WHERE schemaname = 'tickit' -- shared schema;
自動生成されたビューの例
テーブル
SET SESSION AUTHORIZATION tickit;
DROP VIEW IF EXISTS tickit.users;
CREATE VIEW tickit.users
( +
SELECT
userid
,username
,firstname
,lastname
,city
,state
,email
,phone
,likesports
,liketheatre
,likeconcerts
,likejazz
,likeclassical
,likeopera
,likerock
,likevegas
,likebroadway
,likemusicals
FROM tickit_ds.tickit.users +
) WITH NO SCHEMA BINDING;
RESET SESSION AUTHORIZATION;
ビュー
SET SESSION AUTHORIZATION tickit;
DROP VIEW IF EXISTS tickit."ユーザ";
CREATE VIEW tickit."ユーザ"
( +
SELECT
"ユーザid"
,"ユーザ名"
,"名字"
,"名前"
,"市区町村"
,"都道府県"
,"メールアドレス"
,"電話番号"
,"スポーツ好き"
,"映画好き"
,"コンサート好き"
,"ジャズ好き"
,"クラシック好き"
,"オペラ好き"
,"ロック好き"
,"ベガス好き"
,"ブロードウェイ好き"
,"ミュージカル好き"
FROM tickit_ds.tickit."ユーザ" +
) WITH NO SCHEMA BINDING;
RESET SESSION AUTHORIZATION;
最後に
サイロ化した貴重なデータを企業の意思決定支援に活かすには、データをDWHに物理的に「集める」ということが大前提でしたが、Redshiftは、Data Sharingを用いることでデータを物理的に集めることなく、必要なデータのみをクラスタ間で論理的に集めることできる画期的な機能です。
DWHの父、ビル・インモンさんが提唱するDWHの4つ特性の中に、「統合化（integrated）」があります。共有先のクラスタでアナリストが利用しやすい形式する方法として、今回作成したビューをベースに、カラム名やコード体系を変換することで更に分析しやすいデータを提供することにも効果的です。
上記の検証用のテーブルやビューには、コメントを追加していませんでしたがコメントの自動生成もサポートしていますので、ご活用いただければ幸いです。
![[アップデート] EBSスナップショットとAMIのゴミ箱保持ルールの適用対象をタグで制御できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-bac3d29aa65f45576f73094798087ee5/039ad6f8a7d8f18da47986d21c447f48/amazon-ec2)
