Snowflakeのデータベースに関するメタデータ取得を試してみた
こんにちは！DA(データアナリティクス)事業本部 サービスソリューション部の大高です。
Snowflakeは、そのアーキテクチャとして「クラウドサービス」、「クエリ処理」、「データベースストレージ」という３層のレイヤーで構成されています。このうち、「クラウドサービス」レイヤーでは、データベースのスキーマ情報などの「メタデータ管理」も担っています。
今回は、この「クラウドサービス」を利用して、データベースに関するメタデータ取得を試してみたいと思います。
メタデータの取得について
とは言っても、「クラウドサービス」からのメタデータの取得に特別なやり方があるわけではなく、単純にクエリを実行するだけです！
通常のクエリと少し違う点としては、「仮想データウェアハウスを必要としない」という点となります。例えば、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 （標準テーブル）が含まれます。 |
個人的に気を付けたいのはkindとis_externalです。一般的な用途ではkindはTABLEだけで十分そうです。また、is_externalはYとNのどちらも利用したいですが、外部テーブルかどうかの意識はしておきたいですね。
カラムの情報一覧
最後に「カラム」情報です。ここでは例として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のデータベースに関するメタデータ取得を試してみました。
データベースに関する基本的なメタデータ取得は上記のクエリで取得できそうですので、今後必要なときには今回まとめた情報を元に取得を試みてみたいと思います。
どなたかのお役に立てば幸いです。それでは！
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
