Amazon Redshift 共有データにサクッとアクセスするビュー作成を自動化する方法

2022.07.07

データアナリティクス事業本部コンサルティングチームの石川です。Redshiftのデータ共有は、Redshiftクラスタ間でライブデータを共有できる機能です。本日は、その共有データにサクッとアクセスするビュー作成を自動化する方法についてご紹介します。

Data Sharingする方法と、その課題

Redshiftのデータ共有は、「データ共有」という器にスキーマやテーブル、ビューを追加して他のクラスタに共有します。共有されたクラスタは、「データ共有」からデータベースを作成することでデータにアクセスできるようになります。データ共有する方法は、以下のブログをご覧ください。

しかし、Data Sharingしたオブジェクト参照で用いられる<database>.<schema>.<object>の3つをドットで修飾した表記法は、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)」があります。共有先のクラスタでアナリストが利用しやすい形式する方法として、今回作成したビューをベースに、カラム名やコード体系を変換することで更に分析しやすいデータを提供することにも効果的です。

上記の検証用のテーブルやビューには、コメントを追加していませんでしたがコメントの自動生成もサポートしていますので、ご活用いただければ幸いです。