Redshiftでテーブルのカラム番号・名前・型・桁数を抽出する

データアナリティクス事業本部のueharaです。

今回はAmazon Redshiftでテーブルのカラム番号・名前・型・桁数を抽出するという処理をしてみたいと思います。

方法1

SVV_COLUMNSを利用して、以下のように書くことができます。

SELECT 
  table_schema AS schema_name,
  table_name,
  ordinal_position AS column_number,
  column_name,
  data_type,
  character_maximum_length,
  numeric_precision,
  numeric_scale
FROM SVV_COLUMNS
-- 確認不要なスキーマをフィルタ
WHERE table_schema NOT IN ('admin', 'information_schema', 'pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

NUMERIC(precision, scale)といった、precisionとscaleの桁数をそれぞれカラムを分けて表示したい場合は、こちらを使うと良いと思います。

該当しない項目（例：VARCHARにおけるnumeric_precision等）にはNULLが入ります。

方法2

pg_attribute, pg_class, pg_namespaceを利用して、以下のように書くことができます。

SELECT
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  atr.attnum AS column_number,
  atr.attname AS column_name,
  pg_catalog.format_type(atr.atttypid, atr.atttypmod) AS data_type
FROM pg_attribute atr
JOIN pg_class cls ON atr.attrelid = cls.oid
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE
  column_number > 0 AND
  NOT atr.attisdropped AND
  -- 確認不要なスキーマをフィルタ
  schema_name NOT LIKE 'pg_%' AND
  schema_name NOT IN ('admin', 'information_schema')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

データ型と桁数を同じカラム内に表現したい場合は、こちらを使うと良いと思います。

その他の方法

カラムの型・桁を抽出したいテーブルが属するスキーマがsearch_pathに設定されている場合、PG_TABLE_DEFを利用して次のように書くこともできます。

set_pathについては公式ドキュメントをご確認下さい。

WITH tmp AS (
  SELECT 
    table_schema AS schema_name,
    table_name,
    ordinal_position AS column_number,
    column_name
  FROM SVV_COLUMNS
)
SELECT
  schemaname AS schema_name,
  tablename AS table_name,
  column_number,
  "column" AS column_name,
  type AS data_type
FROM PG_TABLE_DEF ptd
JOIN tmp ON
  ptd.schemaname = tmp.schema_name AND
  ptd.tablename = tmp.table_name AND
  ptd."column" = tmp.column_name
-- 確認不要なスキーマをフィルタ
WHERE schema_name NOT IN ('pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は「方法2」の表示結果と同じになります。

最後に

今回はAmazon Redshiftのテーブルのカラム番号・名前・型・桁数を抽出するを抽出するという処理をしてみました。

参考になりましたら幸いです。

参考文献

• SVV_COLUMNS - Amazon Redshift
• search_path - Amazon Redshift
• PG_TABLE_DEF - Amazon Redshift