Amazon Redshift からテーブル作成DDLを自動生成する

Amazon Redshift 自体にはクラスターに存在するテーブルなどのオブジェクトのDDLを生成する機能はないが、以下のスクリプトやツールで生成することができる。
つまり、Oracle Database の DataPump の CONTENT=METADATA_ONLY や exp の rows=n ような機能はないが、DBMS_METADATA.GET_DDL 相当のことができるスクリプトやツールは存在する。

以下のツールでDDL文を生成できる*1。

• Amazon Redshift Utilities の v_generate_tbl_ddl.sql
• SQL Workbench/J
• Aginity Workbench for Amazon Redshift

具体的な手順例は以下の通り。

SQL Workbench/J を使う

• SQL Workbench/J で Redshift に接続する。
  • インストール・接続方法は SQL Workbench/J で Amazon Redshift に接続する - ablog 参照。
• [Tools]-[Show Database Explorer] を選択する。
• [Schema] を選択する。
• 左ペインでテーブルを選択し、[Control]+クリックでショートカットメニューを表示する。
• [Create DDL Script] を選択する。

25. SQL Workbench/J to generate DDL commands を見るとコマンドラインからでも同じことができそう。

Amazon Redshift Utilities のスクリプトを使う方法

• awslab からスクリプトを入手する

$ git clone https://github.com/awslabs/amazon-redshift-utils.git

• v_generate_tbl_ddl.sql があるディレクトリに移動する。

$ cd amazon-redshift-utils/src/AdminViews

• Redshift クラスターに接続する

$ psql "host=******.******.ap-northeast-1.redshift.amazonaws.com user=master dbname=mydb port=5439"
Password:
psql (9.6.2, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.
mydb=#

• admin スキーマを作成する。

mydb=# CREATE SCHEMA admin;
CREATE SCHEMA

• v_generate_tbl_ddl ビューを作成する。

mydb=# \i ./v_generate_tbl_ddl.sql
CREATE VIEW

• pager を OFF にする。

mydb=# \pset pager
Pager usage is off.

• DDL を生成する(pg_catalog スキーマの全てのテーブル)。

mydb=# select * from admin.v_generate_tbl_ddl where schemaname='pg_catalog' limit 30;
 schemaname |      tablename      |    seq    |                              ddl
------------+---------------------+-----------+---------------------------------------------------------------
 pg_catalog | padb_config_harvest |         0 | --DROP TABLE "pg_catalog"."padb_config_harvest";
 pg_catalog | padb_config_harvest |         2 | CREATE TABLE IF NOT EXISTS "pg_catalog"."padb_config_harvest"
 pg_catalog | padb_config_harvest |         5 | (
 pg_catalog | padb_config_harvest | 100000001 |         "name" CHAR(136) NOT NULL  ENCODE lzo
 pg_catalog | padb_config_harvest | 100000002 |         ,"harvest" INTEGER NOT NULL  ENCODE lzo
 pg_catalog | padb_config_harvest | 100000003 |         ,"archive" INTEGER NOT NULL  ENCODE lzo
 pg_catalog | padb_config_harvest | 100000004 |         ,"directory" CHAR(500) NOT NULL  ENCODE lzo
 pg_catalog | padb_config_harvest | 299999999 | )
 pg_catalog | padb_config_harvest | 300000001 | DISTSTYLE EVEN
 pg_catalog | padb_config_harvest | 600000000 | ;
 pg_catalog | pg_aggregate        |         0 | --DROP TABLE "pg_catalog"."pg_aggregate";
 pg_catalog | pg_aggregate        |         2 | CREATE TABLE IF NOT EXISTS "pg_catalog"."pg_aggregate"
 pg_catalog | pg_aggregate        |         5 | (
 pg_catalog | pg_aggregate        | 100000001 |         "aggfnoid" REGPROC NOT NULL
 pg_catalog | pg_aggregate        | 100000002 |         ,"aggtransfn" REGPROC NOT NULL
 pg_catalog | pg_aggregate        | 100000003 |         ,"aggfinalfn" REGPROC NOT NULL
 pg_catalog | pg_aggregate        | 100000004 |         ,"aggtranstype" OID NOT NULL
 pg_catalog | pg_aggregate        | 100000005 |         ,"agginitval" TEXT
 pg_catalog | pg_aggregate        | 299999999 | )
 pg_catalog | pg_aggregate        | 300000001 | DISTSTYLE EVEN
 pg_catalog | pg_aggregate        | 600000000 | ;
 pg_catalog | pg_am               |         0 | --DROP TABLE "pg_catalog"."pg_am";
 pg_catalog | pg_am               |         2 | CREATE TABLE IF NOT EXISTS "pg_catalog"."pg_am"
 pg_catalog | pg_am               |         5 | (
 pg_catalog | pg_am               | 100000001 |         "amname" NAME NOT NULL
 pg_catalog | pg_am               | 100000002 |         ,"amowner" INTEGER NOT NULL
 pg_catalog | pg_am               | 100000003 |         ,"amstrategies" SMALLINT NOT NULL
 pg_catalog | pg_am               | 100000004 |         ,"amsupport" SMALLINT NOT NULL
 pg_catalog | pg_am               | 100000005 |         ,"amorderstrategy" SMALLINT NOT NULL
 pg_catalog | pg_am               | 100000006 |         ,"amcanunique" BOOLEAN NOT NULL
(30 rows)

• 出力先ファイル名を設定する。

mydb=# \o ddl.sql

• DDL のみ生成する。

mydb=# select ddl from admin.v_generate_tbl_ddl where schemaname='pg_catalog';

• 切断する。

mydb=# \q

• 出力ファイルを確認する。

$ head -10 ddl.sql 
                                         ddl
--------------------------------------------------------------------------------------
 --DROP TABLE "pg_catalog"."padb_config_harvest";
 CREATE TABLE IF NOT EXISTS "pg_catalog"."padb_config_harvest"
 (
         "name" CHAR(136) NOT NULL  ENCODE lzo
         ,"harvest" INTEGER NOT NULL  ENCODE lzo
         ,"archive" INTEGER NOT NULL  ENCODE lzo
         ,"directory" CHAR(500) NOT NULL  ENCODE lzo
 )
$ tail -10 ddl.sql
 CREATE TABLE IF NOT EXISTS "pg_catalog"."systable_topology"
 (
         "hash" INTEGER NOT NULL  ENCODE lzo
         ,"slice" INTEGER NOT NULL  ENCODE lzo
         ,"mirror" INTEGER NOT NULL  ENCODE lzo
 )
 DISTSTYLE EVEN
 ;
(4815 rows)

Aginity Workbench for Amazon Redshift

• Windows のみで使える
• Redshift専用 Windows GUIツール『Aginity Workbench for Amazon Redshift』が便利かもしれない件 ｜ DevelopersIO
• Redshiftの管理ツール決定版か？Aginity Workbench Beta for Amazon Redshift (1) - yoshidashingo

参考

• Amazon Redshift Utilities
• Amazon Redshift便利ツール『amazon-redshift-utils』の便利SQLスクリプト紹介(AdminViews編) ｜ DevelopersIO
• postgresql - amazon redshift utility - view v_generate_tbl_ddl not returning primary key constraint in ddl after table has been populated - Stack Overflow
• Amazon RedshiftのMac OS X向けGUIツールとして『Intellij IDEA Ultimate Edition』のDatabase Toolsを使う ｜ DevelopersIO

*1:以下は一例で他にもある