この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
先日投稿した以下エントリでは、既存構築済のテーブル内容から諸々構成情報を引っこ抜いてCREATE TABLE文を作り直す、という方法について一つの方法を書き記してみていました。ですが2つ(CREATE TABLE文、コメント付与文)書いた方のうちの前者はスクリプト言語(Groovy)で書いていたので若干妥協してしまった感もあり、何とかしてSQLで書けないかな〜と思っておりました。
当エントリはその続編的エントリとなります。
目次
検証用CREATE TABLE文
まず用意したのは以下CREATE TABLE文3つ。
分散スタイル: EVEN
こちらは分散スタイル: EVENのテーブル。テーブルを構成している項目のデータ型と列圧縮タイプはひと通り網羅する感じで、あと分散キー指定は無し=EVENで構成しています。
CREATE TABLE public.testtable_a_dist_even (
id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
num_int INT encode mostly16 NOT NULL, /** mostly16 */
num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL, /** runlength */
num_double DOUBLE PRECISION, /** raw(無圧縮) */
value_boolean BOOLEAN,
value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
value_date DATE encode delta NOT NULL, /** delta */
value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
value_varchar_text32k VARCHAR(100) encode text32k NOT NULL /** text32k */
)
sortkey(id, value_char, num_int);
分散スタイル: KEY
こちらは分散キーを指定した場合のテーブル。上記テーブル構成と異なるのは分散キー指定の部分が追加されたところだけです。
CREATE TABLE public.testtable_a_dist_key (
id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
num_int INT encode mostly16 NOT NULL, /** mostly16 */
num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL, /** runlength */
num_double DOUBLE PRECISION, /** raw(無圧縮) */
value_boolean BOOLEAN,
value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
value_date DATE encode delta NOT NULL, /** delta */
value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
value_varchar_text32k VARCHAR(100) encode text32k NOT NULL /** text32k */
)
distkey(id)
sortkey(id, value_char, num_int);
分散スタイル: ALL
そして3つ目が分散スタイル: ALL。こちらも分散キー指定同様、異なるのは分散スタイルを『diststyle ALL』にしている箇所のみです。
CREATE TABLE public.testtable_a_dist_all (
id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
num_int INT encode mostly16 NOT NULL, /** mostly16 */
num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL, /** runlength */
num_double DOUBLE PRECISION, /** raw(無圧縮) */
value_boolean BOOLEAN,
value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
value_date DATE encode delta NOT NULL, /** delta */
value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
value_varchar_text32k VARCHAR(100) encode text32k NOT NULL /** text32k */
)
diststyle ALL
sortkey(id, value_char, num_int);
CREATE TABLE文を生成する(by SQL)
そして以下が対象テーブルのCREATE TABLE文を引っこ抜くSQL文です。だいぶ長くなってしまいましたが、SQL文中に含まれている<スキーマ名>及び<テーブル名>に対象テーブルの情報を記載・実行してもらえればCREATE TABLE文が生成されます。ざっくり解説しますと、ソート番号的な項目(attnum)と生成するSQL文テキスト(sqlstmt)をひたすら連結し、ソート番号で並べつつ最後にSQL文だけ取得している感じです。途中出てくる2000とかの数字は適当です。項目の並びが意図した通りになるのであればOKです。
SELECT
sqlstmt
FROM (
/** CREATE TABLE文: 作成テーブル名の宣言 */
(SELECT 0 AS attnum, 'CREATE TABLE ' || '<スキーマ名>.<テーブル名> (' AS sqlstmt)
UNION ALL
/** CREATE TABLE文: テーブル内各項目定義 */
(
SELECT
def_comment.attnum AS attnum,
' '
|| pg_table_def.column
|| ' '
|| UPPER(pg_table_def.type)
|| ' encode ' || CASE pg_table_def.encoding WHEN 'none' THEN 'raw' ELSE pg_table_def.encoding END
|| CASE pg_table_def.notnull WHEN TRUE THEN ' NOT NULL' ELSE ' ' END
|| ',' AS sqlstmt
FROM
pg_table_def,
(
SELECT
pg_stat_user_tables.schemaname AS schema,
pg_stat_user_tables.relname AS tablename,
tablecom.description AS table_comment,
pg_attribute.attname AS columnname,
colcom.description AS column_comment,
pg_attribute.attnum,
pg_attrdef.adsrc AS default
FROM
pg_stat_user_tables, pg_type, pg_attribute
LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
LEFT JOIN pg_description colcom ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
WHERE
pg_attribute.attrelid = pg_stat_user_tables.relid
AND pg_attribute.atttypid = pg_type.oid
AND pg_attribute.attnum > 0
AND pg_stat_user_tables.relname IN (
SELECT pg_class.relname
FROM
pg_namespace
JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
WHERE
pg_class.relkind = 'r'
AND pg_class.relname NOT LIKE 'pg_%'
AND pg_class.relname NOT LIKE 'sql_%')
AND pg_stat_user_tables.schemaname = '<スキーマ名>'
AND pg_stat_user_tables.relname = '<テーブル名>'
ORDER BY
pg_stat_user_tables.schemaname,
pg_stat_user_tables.relname,
pg_attribute.attnum
) def_comment
WHERE
pg_table_def.tablename = def_comment.tablename
AND pg_table_def.column = def_comment.columnname
AND pg_table_def.schemaname = '<スキーマ名>'
AND pg_table_def.tablename = '<テーブル名>'
ORDER BY
def_comment.schema,
def_comment.tablename,
def_comment.attnum
)
/** CREATE TABLE文: 各種項目定義終わり */
UNION ALL
(SELECT 1000 AS attnum, ')' AS sqlstmt)
/** CREATE TABLE文: 分散スタイル指定 */
UNION ALL
(
SELECT DISTINCT
2000 AS attnum,
CASE pg_class.reldiststyle
/** 1:KEY(分散キー指定) */WHEN 1 THEN 'distkey(' || pg_table_def.column || ')'
/** 0:EVEN */WHEN 0 THEN 'diststyle EVEN' /** OR '' */
END AS sqlstmt
FROM
pg_table_def,
pg_class
WHERE
pg_table_def.schemaname = '<スキーマ名>'
AND pg_table_def.tablename = '<テーブル名>'
AND pg_table_def.distkey = TRUE
AND pg_table_def.tablename = pg_class.relname
)
UNION ALL
(
SELECT DISTINCT attnum, sqlstmt
FROM
(SELECT
2000 AS attnum,
CASE pg_class.reldiststyle
/** 8:ALL */WHEN 8 THEN 'diststyle ALL'
END AS sqlstmt
FROM
pg_table_def,
pg_class
WHERE
pg_table_def.schemaname = '<スキーマ名>'
AND pg_table_def.tablename = '<テーブル名>'
AND pg_table_def.distkey = FALSE
AND pg_table_def.tablename = pg_class.relname)
)
/** CREATE TABLE文: ソートキー指定 */
UNION ALL
(
SELECT sortkey AS attnum, sortitemtable.sortitem AS sqlstmt FROM
(SELECT sortkey, sortitem FROM
(SELECT 20000 AS sortkey, 'sortkey(' AS sortitem)
UNION ALL
(
SELECT
20100 + pg_table_def.sortkey,
pg_table_def.column || ',' AS sortitem
FROM
pg_table_def
WHERE
pg_table_def.schemaname = '<スキーマ名>'
AND pg_table_def.tablename = '<テーブル名>'
AND pg_table_def.sortkey != 0
ORDER BY
pg_table_def.sortkey
)
UNION ALL
(SELECT 21000 AS sortkey, ')'AS sortitem)
) sortitemtable)
)
ORDER BY attnum;
実行結果
上記SQLを実行してみた結果が以下となります。
分散スタイル: EVEN
# SELECT
# sqlstmt
# FROM (
:
(中略)
:
# ORDER BY attnum;
sqlstmt
--------------------------------------------------------------------------
CREATE TABLE public.testtable_a_dist_even (
id SMALLINT encode mostly8 NOT NULL,
num_int INTEGER encode mostly16 NOT NULL,
num_biging BIGINT encode mostly32 NOT NULL,
num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL,
num_double DOUBLE PRECISION encode raw ,
value_boolean BOOLEAN encode raw ,
value_char CHARACTER(5) encode bytedict NOT NULL,
value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
value_date DATE encode delta NOT NULL,
value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
)
sortkey(
id,
value_char,
num_int,
)
(21 rows)
分散スタイル: KEY
# SELECT
# sqlstmt
# FROM (
:
(中略)
:
# ORDER BY attnum;
sqlstmt
--------------------------------------------------------------------------
CREATE TABLE public.testtable_a_dist_key (
id SMALLINT encode mostly8 NOT NULL,
num_int INTEGER encode mostly16 NOT NULL,
num_biging BIGINT encode mostly32 NOT NULL,
num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL,
num_double DOUBLE PRECISION encode raw ,
value_boolean BOOLEAN encode raw ,
value_char CHARACTER(5) encode bytedict NOT NULL,
value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
value_date DATE encode delta NOT NULL,
value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
)
distkey(id)
sortkey(
id,
value_char,
num_int,
)
(22 rows)
分散スタイル: ALL
# SELECT
# sqlstmt
# FROM (
:
(中略)
:
# ORDER BY attnum;
sqlstmt
--------------------------------------------------------------------------
CREATE TABLE public.testtable_a_dist_all (
id SMALLINT encode mostly8 NOT NULL,
num_int INTEGER encode mostly16 NOT NULL,
num_biging BIGINT encode mostly32 NOT NULL,
num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
num_real REAL encode runlength NOT NULL,
num_double DOUBLE PRECISION encode raw ,
value_boolean BOOLEAN encode raw ,
value_char CHARACTER(5) encode bytedict NOT NULL,
value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
value_date DATE encode delta NOT NULL,
value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
)
diststyle ALL
sortkey(
id,
value_char,
num_int,
)
(21 rows)
まとめ
以上、Amazon Redshiftに於けるCREATE TABLE文をSQLで頑張って生成してみたエントリでした。
でしたが!実はこのSQL、完全ではありません。御覧頂くと分かるように、『各列項目の最後となる部分』及び『ソートキーの最後の部分』のカンマが付いたままですので、このままでは実行してもエラーとなります。私のSQL脳とRedshift脳ではこの部分を解決出来るに至らず『もう、実行する前にカンマ削ればええやん(by 悪魔)』が『何としてもSQLでやり切るッ!(by 天使)』を上回ってしまいました。ですのでご利用の際にはその辺りを御留意ください。m(_ _)m (ブログタイトルが若干歯切れの悪い文末としているのはこれが理由です)
あと、思い当たる節としては以下内容辺りでしょうか。
- CREATE TABLE文で宣言した項目型とCREATE TABLE文作成SQLで出来たSQL文の項目型の表記が違っている(CHAR→CHARACTER, VARCHAR→CHARACTER VARYING、DECIMAL→NUMERIC等)。まぁこれはRedshift側で吸収してくれるので気にしないor最初からこちらの定義で宣言すべき、という事象ではありますが...
- CREATE TABLE文でソートキー指定が無かった場合のケースが漏れている
- あと、可能ならばソートキーは横に並べたかった
- プライマリーキー指定が未対応(これはそもそも情報が取れるのか、という問題もありそうですが)
という訳で、甚だ不完全な情報ではございますが、不完全部分については今後も継続してチャレンジして行く所存でございますので当エントリはこの完成度での公開を御了承頂ければと思います。m(_ )m m( _)m
こちらからは以上です。