この記事について
Redshift上に格納している複数のテーブルについて、まとめてドキュメント化する必要があり、こういうの↓がCSVで欲しかったのでそのためのクエリをまとめた。
ほぼ参考記事のクエリを改変しただけです(@foursue 様ありがとうございます)がメモまで
| table_id | schema_name | table_name | column_number | column_name | column_datatype |
|---|---|---|---|---|---|
| 1 | user | demographic | 1 | uuid | VARCHAR |
| 1 | user | demographic | 2 | age | INT |
| 1 | user | demographic | 3 | gender | VARCHAR |
| 1 | user | demographic | 4 | country | VARCHAR |
| 2 | order | order_detail | 1 | order_id | BIGINT |
| 2 | order | order_detail | 2 | timestamp | TIMESTAMP |
| 2 | order | order_detail | 3 | uuid | VARCHAR |
| 2 | order | order_detail | 4 | type | VARCAHR |
| 2 | order | order_detail | 5 | price | NUMERIC |
実際の方法
対象のredshiftクラスタにadminでログインして下記を実行しましょう。
dump_table_column_datatype.sql
SELECT
table_id,
schema_name,
table_name,
column_number,
column_name,
column_datatype
FROM
(
SELECT
c.oid::BIGINT AS table_id,
n.nspname AS schema_name,
c.relname AS table_name,
a.attnum AS column_number,
QUOTE_IDENT(a.attname) AS column_name,
CASE
WHEN STRPOS(
UPPER(format_type(a.atttypid, a.atttypmod)),
'CHARACTER VARYING'
) > 0 THEN REPLACE(
UPPER(format_type(a.atttypid, a.atttypmod)),
'CHARACTER VARYING',
'VARCHAR'
)
WHEN STRPOS(
UPPER(format_type(a.atttypid, a.atttypmod)),
'CHARACTER'
) > 0 THEN REPLACE(
UPPER(format_type(a.atttypid, a.atttypmod)),
'CHARACTER',
'CHAR'
)
ELSE UPPER(format_type(a.atttypid, a.atttypmod))
END AS column_datatype
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 = 'r'
AND a.attnum > 0
ORDER BY
a.attnum
)
ORDER BY
schema_name,
table_name,
column_number