AUG 24, 2023|7 min read
Snowflake情報スキーマの使用
注：本記事は(2016年11月1日)に公開された(Using the Snowflake Information Schema)を翻訳して公開したものです。
他の優れたデータベースと同様に、Snowflakeには、ユーザーに公開するデータディクショナリがあります。私たちはこれを情報スキーマと呼んでいます。実際には、メタデータレイヤーに対するビューのセットで、Snowflakeで構築したデータベース、スキーマ、テーブルに関する情報の一部を簡単に調べることができます。
表示できるオブジェクトとは
情報スキーマには、直接クエリできる18のビューがあります。全リストは、こちら
のドキュメントでご覧いただけます。
重要なことは、Snowflakeの各データベースには個別の情報スキーマがあり、クエリは現在のデータベースに関するデータのみを返すということです。
さらに、SQLを記述する際には、情報スキーマのビュー名を完全修飾する必要があり、特に例にあるように「information_schema」を使用する必要があります。
参照しやすいように、情報スキーマのスキーマをリバースエンジニアリングしてデータモデル図にし、適切なPKとFKを追加しました。この図は、スキーマに対してSQLを記述する際のガイドとして使用できます（説明を含むPDFのダウンロードはこちら）。
情報スキーマの使用方法
データディクショナリに対してクエリを作成したことがない人のために、いくつか例を挙げてみましょう。
1.スキーマにテーブルとビューをリストする
簡単に開始する方法は、データベーススキーマの1つにテーブルとビューをリストすることです。
SELECT table_name, table_type
FROM kent_db.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY 1;このSQLの例（およびこの記事のすべての例）は、特定のスキーマ（PUBLIC）を指定していることに注意してください。データベースに複数のスキーマがある場合は、可能な限り述語にスキーマ仕様を含めることをお勧めします（データベース内のすべてを実際に確認したい場合を除く）。また、情報スキーマビューの値は通常文字列であり、大文字と小文字が区別されるため、述語句で参照する場合は必ず一重引用符で囲むようにしてください。
結果は次のようになります。
2.データディクショナリを構築する
たとえば、レポートにテーブルに関するデータディクショナリタイプのリストを作成するとします。テーブルに対してCOMMENTを記録している場合は、このクエリを実行してリストを作成できます。
SELECT 'Comment on table.'||table_name||' is '''||nvl(tables.comment||'''', 'No Comment Recorded''')
FROM kent_db.information_schema.tables tables
WHERE table_schema = 'PUBLIC';このSQLは、PUBLICスキーマの各テーブルのコメントを表示するだけでなく、テーブルにコメントを追加していない箇所も指摘します。
注：ほとんどのBIツールはデータディクショナリからコメントをリバースエンジニアリングできるため、この情報を使用してBIツールでメタデータを構築し、システム内のさまざまなテーブルの意味をユーザーに知らせることができます。
もう1つの可能性は、特定のスキーマで定義したすべての制約のリストが必要な場合です。次のようなSQLを実行できます。
SELECT table_name, constraint_type, constraint_name
FROM sales.information_schema.table_constraints
WHERE constraint_schema = 'TPC_DV_OPT'
ORDER BY table_name;結果は次のようになります。
3.SQLを動的に生成する
動的SQLは、情報スキーマのデータを使用してSQLステートメントを生成する方法です。たとえば、CREATEスクリプトのリグレッションテストを行うために、データベースをクリーンアップし、ほとんどのテーブルを削除する必要があるとします。これを行うには多くの方法があります。SQLスクリプトを使用してこれを行う場合は、スクリプトを手書きで記述できます。テーブルが少数しかない場合は問題ありませんが、動的にスクリプトを生成する方がよいでしょう。
Snowflakeの情報スキーマを使用すると、次のように実行できます。
SELECT 'drop table '||table_name||' cascade;'
FROM kent_db.information_schema.tables tables
WHERE table_schema = 'PUBLIC'
ORDER BY 1;この出力は、実行可能なSQLコマンドのセットである必要があります。また、スキーマが進化してテーブルが追加されると、このスクリプトは次に実行するときに新しいテーブルを選択するため、編集することを覚えておく必要はありません（これが「動的SQL」の「動的」の部分です）。
Snowflake UIを使用すると、これらの結果をエクスポートし、スクリプトに保存してから実行できます。
このようなメタデータにアクセスすることで、可能性は無限に広がります。
Snowflakeコンピューティングのすべてのアクションとアクティビティに関する最新情報については、これまでどおり、このブログサイトやSnowflake Twitterフィード（@SnowflakeDB
）に注目してください。
Authors
