Snowflakeのデータベースに関するメタデータ取得を試してみた

2022.02.03

こんにちは!DA(データアナリティクス)事業本部 サービスソリューション部の大高です。

Snowflakeは、そのアーキテクチャとして「クラウドサービス」、「クエリ処理」、「データベースストレージ」という3層のレイヤーで構成されています。このうち、「クラウドサービス」レイヤーでは、データベースのスキーマ情報などの「メタデータ管理」も担っています。

今回は、この「クラウドサービス」を利用して、データベースに関するメタデータ取得を試してみたいと思います。

メタデータの取得について

とは言っても、「クラウドサービス」からのメタデータの取得に特別なやり方があるわけではなく、単純にクエリを実行するだけです!

通常のクエリと少し違う点としては、「仮想データウェアハウスを必要としない」という点となります。例えば、SELECT文の実行であれば「仮想データウェアハウス」の利用、つまりコンピュートの利用を行うので「クレジット」が掛かります。「クレジット」が掛かるということは、契約プランに応じた料金が発生することになります。

一方で、メタデータの場合には仮想データウェアハウスを利用せずに情報の取得ができるのでちょっとお得です。このメタデータの取得にはSHOWコマンドを利用することができます。

なお場合によっては、細かい条件での絞り込みをしたい場合にRESULT_SCANという仮想ウェアハウスを必要とするテーブル関数を組み合わせて利用するケースもあるのですが、今回はこちらは利用せずに試してみます。

SHOWコマンドの注意点

SHOWコマンドの注意点としては、実行時の「ロール」におけるアクセス権限をもっている情報だけが取得できるという点です。例えば利用する「ロール」に権限の無いテーブルの情報などは取得できません。

すべての情報を取得したいケースなどでは、ロール階層がより上位のSYSADMINロールなどの利用が必要になってきます。(今回はSYSADMINロールを利用します)

また、結果が1万件を超える場合には、SHOWコマンドはエラーになります。そのような場合にはLIMIT句を利用して1万件以内に収めるか、INFORMATION_SCHEMAの利用を検討します。ただし、INFORMATION_SCHEMAを利用する場合には「仮想ウェアハウス」を必要とするので、この点には注意が必要ですね。

クエリ実行環境について

今回はSnowSQLを利用してクエリ実行を行います。また、以下のコマンドを利用して出力フォーマットを変えています。

!set output_format=expanded

データベースの情報一覧

それでは実際に試してみます。まずは「データベース」からです。

>SHOW DATABASES;
(...snip...)
***************************[ 25 ]***************************
created_on     | 2020-01-13 07:44:34.247 -0800
name           | SNOWFLAKE_SAMPLE_DATA
is_default     | N
is_current     | N
origin         | SFSALESSHARED.SFC_SAMPLES.SAMPLE_DATA
owner          | ACCOUNTADMIN
comment        | TPC-H, OpenWeatherMap, etc
options        | 
retention_time | 1
(...snip...)

データベース情報が取得できました。

以下はドキュメントで見つけられなかったので一部推測も含みますが、それぞれ以下のような意味となっているようです。

カラム名 内容
created_on データベースが作成された日時
name データベースの名前
is_default データベースが現在のユーザーのデフォルトであるか
is_current データベースがセッションで使用されているか
origin (不明。おそらくClone元のデータベース名)
owner データベースを所有するロール
comment データベースについてのコメント
options (不明)
retention_time Time Travelの履歴データが保持される日数

スキーマの情報一覧

次に「スキーマ」情報です。ここでは例としてSNOWFLAKE_SAMPLE_DATAデータベースを対象とします。

>SHOW SCHEMAS IN SNOWFLAKE_SAMPLE_DATA;
(...snip...)
***************************[ 4 ]***************************
created_on     | 2021-11-11 01:13:47.476 -0800
name           | TPCH_SF1
is_default     | N
is_current     | N
database_name  | SNOWFLAKE_SAMPLE_DATA
owner          | 
comment        | TPC-H scaling factor 1
options        | 
retention_time | 1
(...snip...)

スキーマ情報が取得できました。今回はデータベースを指定したかったので、IN [データベース名]を付けて実行しています。

以下はドキュメントで見つけられなかったので一部推測も含みますが、それぞれ以下のような意味となっているようです。

カラム名 内容
created_on スキーマが作成された日時
name スキーマの名前
is_default スキーマが現在のユーザーのデフォルトであるか
is_current スキーマがセッションで使用されているか
database_name スキーマが格納されているデータベース
owner スキーマを所有するロール
comment スキーマについてのコメント
options (不明)
retention_time Time Travelの履歴データが保持される日数

テーブルの情報一覧

次に「テーブル」情報です。ここでは例としてSNOWFLAKE_SAMPLE_DATAデータベースのTPCH_SF1スキーマを対象とします。

>SHOW TABLES IN SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1;
***************************[ 1 ]***************************                     
created_on                   | 2021-11-11 01:13:48.624 -0800
name                         | CUSTOMER
database_name                | SNOWFLAKE_SAMPLE_DATA
schema_name                  | TPCH_SF1
kind                         | TABLE
comment                      | Customer data as defined by TPC-H
cluster_by                   | 
rows                         | 150000
bytes                        | 10747904
owner                        | 
retention_time               | 1
automatic_clustering         | OFF
change_tracking              | OFF
search_optimization          | OFF
search_optimization_progress | NULL
search_optimization_bytes    | NULL
is_external                  | N
(...snip...)

テーブル情報が取得できました。今回はデータベースとスキーマを指定したかったので、IN SCHEMA [データベース名.スキーマ名]を付けて実行しています。

各カラムの意味はドキュメントに記載されているので、主要なものを抜粋したのが以下になります。

カラム名 内容
created_on テーブルが作成された日時。
name テーブルの名前。
database_name テーブルが格納されているデータベース。
schema_name テーブルが格納されているスキーマ。
kind テーブルタイプ: TABLE (永続テーブル用)、 TEMPORARY、または TRANSIENT。
comment テーブルのコメント。
rows テーブル内の行数。外部テーブルの場合はNULLを返します。
owner テーブルを所有するロール。
is_external テーブルのタイプ。値には、 Y (外部テーブル)または N (標準テーブル)が含まれます。

個人的に気を付けたいのはkindis_externalです。一般的な用途ではkindTABLEだけで十分そうです。また、is_externalYNのどちらも利用したいですが、外部テーブルかどうかの意識はしておきたいですね。

カラムの情報一覧

最後に「カラム」情報です。ここでは例としてSNOWFLAKE_SAMPLE_DATAデータベース、TPCH_SF1スキーマのCUSTOMERテーブルを対象とします。

>SHOW COLUMNS IN TABLE SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
***************************[ 1 ]***************************                     
table_name    | CUSTOMER
schema_name   | TPCH_SF1
column_name   | C_CUSTKEY
data_type     | {"type":"FIXED","precision":38,"scale":0,"nullable":false}
null?         | NOT_NULL
default       | 
kind          | COLUMN
expression    | 
comment       | 
database_name | SNOWFLAKE_SAMPLE_DATA
autoincrement | 
(...snip...)

カラム情報が取得できました。今回はデータベースとスキーマとテーブルを指定したかったので、IN TABLE [データベース名.スキーマ名.テーブル名]を付けて実行しています。

各カラムの意味はドキュメントに記載されているので省略します。個人的にはすべて気になる情報です。data_typeにはJSON形式で値が入っており、少しこれまでと違うので注意が必要ですね。

また、expressionというカラムの意味はドキュメントにも記載がなく分かりませんでした。(現在は利用されていないものなのかもしれません)

一応、デフォルト値を指定したカラムにおけるexpressionなのかなと思い試してみましたが、その場合にはdefaultカラムにちゃんと情報が表示されるので違うようですね。

CREATE TABLE default_val_sample(
  id NUMBER AUTOINCREMENT,
  now DATE DEFAULT CURRENT_DATE()
);
>SHOW COLUMNS IN TABLE OOTAKA_SANDBOX_DB.PUBLIC.default_val_sample;
***************************[ 1 ]***************************                     
table_name    | DEFAULT_VAL_SAMPLE
schema_name   | PUBLIC
column_name   | ID
data_type     | {"type":"FIXED","precision":38,"scale":0,"nullable":true}
null?         | true
default       | 
kind          | COLUMN
expression    | 
comment       | 
database_name | OOTAKA_SANDBOX_DB
autoincrement | IDENTITY START 1 INCREMENT 1
***************************[ 2 ]***************************
table_name    | DEFAULT_VAL_SAMPLE
schema_name   | PUBLIC
column_name   | NOW
data_type     | {"type":"DATE","nullable":true}
null?         | true
default       | CURRENT_DATE()
kind          | COLUMN
expression    | 
comment       | 
database_name | OOTAKA_SANDBOX_DB
autoincrement |

まとめ

以上、Snowflakeのデータベースに関するメタデータ取得を試してみました。

データベースに関する基本的なメタデータ取得は上記のクエリで取得できそうですので、今後必要なときには今回まとめた情報を元に取得を試みてみたいと思います。

どなたかのお役に立てば幸いです。それでは!