Snowflakeで特定のデータベースやスキーマのテーブルのDDLや情報をまとめて取得する

データ事業本部の鈴木です。

Snowflakeで特定のデータベースやスキーマのテーブルのDDLや情報をまとめて取得したいことがあったのですが、意外とパッとやり方が分からなかったため、調べた内容をまとめます。
どんなDDLでテーブルを作成したか調べる際に役立つほか、最近だとデータベース内のデータを分析するためにLLMのプロンプトに使ったりもします。

1. GET_DDLでDDLをまとめて取得する方法

以下で紹介されているように、GET_DDLでobject_typeとしてDATABASEやSCHEMAを指定すると取得できました。

https://stackoverflow.com/questions/73771873/get-ddl-of-all-tables-under-schema-in-one-database-in-snowflake

以下のようにテーブル個別に取得しようとした場合、object_nameは直接テーブル名の文字列を指定する必要があるため上手く動きませんでした。

with tmp as (
SELECT 
    *
FROM 
    データベース名.INFORMATION_SCHEMA.TABLES
)
select get_ddl('TABLE', table_name)
from tmp;

Invalid value [TMP.TABLE_NAME] for function '2', parameter EXPORT_DDL: constant arguments expected

そのため、GET_DDLだとできないのかな？と思っていたのですが、DATABASEやSCHEMAの上の階層で指定すれば取得できました。

WITH source_data AS (
  select get_ddl('SCHEMA', 'データベース名.スキーマ名') as ddl
)
SELECT 
  REGEXP_REPLACE(value, '\n|\t', '') AS parsed_value
FROM source_data,
LATERAL FLATTEN(input => SPLIT(ddl, ';\n')) f;

なお、上記例ではget_ddlから取得したDDLが一つの文字列になっているため、改行などで分割・クレンジングしています。

LATERALについては以下のブログが分かりやすいです。

https://zenn.dev/indigo13love/articles/450d4d58654b43

2. COLUMNSビューからメタデータを取得する方法

DDLそのものではありませんが、COLUMNSビューからカラム単位のメタデータを取得することもできました。

https://docs.snowflake.com/ja/sql-reference/info-schema/columns

SELECT 
    COLUMN_NAME, DATA_TYPE, COMMENT
FROM 
    SAMPLE_DB.INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'テーブル名'
ORDER BY 
    table_schema, table_name, ordinal_position;

テーブルに対するコメントも取得したい場合は、TABLESビューのメタデータも取得してテーブル名で結合するとよさそうです。

https://docs.snowflake.com/ja/sql-reference/info-schema/tables

終わりに

Snowflakeで特定のデータベースやスキーマのテーブルのDDLや情報をまとめて取得する際の方法をご紹介しました。
ほかの方法もあるかもしれませんが、上記2つが使えればだいたいのケースには対応できるのではと思います。
参考になりましたら幸いです。