この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。
Snowflakeでテーブル名の一覧取得をどうすれば良いのか気になったので、ちょっと調べてみました。
大きく2つあり、SHOW TABLESでの取得と各データベース内のINFORMATION_SCHEMA内のビューTABLESで取得できるようです。それぞれ見ていきたいと思います。
SHOW TABLES
１つ目は「SHOW TABLES」コマンドでの取得になります。下記のヘルプにある通り、様々なSHOW XXXXXのコマンドのうちの一つで自分がアクセス権限をもつテーブルの一覧が取得できます。
SHOW TABLES — Snowflake Documentation
SHOW [ TERSE ] TABLES [ HISTORY ] [ LIKE '<pattern>' ]
[ IN { ACCOUNT | DATABASE [ <db_name> ] | [ SCHEMA ] [ <schema_name> ] } ]
[ STARTS WITH '<name_string>' ]
[ LIMIT <rows> [ FROM '<name_string>' ] ]ぱっと見て、あまり見かけないTERSEとHISTORYいうパラメータがありますね。
TERSE
これを記述すると出力されるカラムが簡潔になるオプションのようです。具体的にはそれぞれ以下のようになります。
SHOW TABLES
created_on name database_name schema_name kind comment cluster_by rows bytes owner retention_time automatic_clustering change_tracking
2019-04-22 16:01:59.825 -0700 CALL_CENTER SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL TABLE LINEAR(CC_CALL_CENTER_SK) 60 11776 1 ON OFF
2019-04-22 16:02:02.350 -0700 CATALOG_PAGE SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL TABLE LINEAR(CP_CATALOG_PAGE_SK) 50000 2777088 1 ON OFF
2019-04-22 16:02:03.063 -0700 CATALOG_RETURNS SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL TABLE LINEAR(CR_RETURNED_DATE_SK, CR_ITEM_SK) 14405363575 828692696576 1 ON OFFSHOW TERSE TABLES
created_on name kind database_name schema_name
2019-04-22 16:01:59.825 -0700 CALL_CENTER TABLE SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL
2019-04-22 16:02:02.350 -0700 CATALOG_PAGE TABLE SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL
2019-04-22 16:02:03.063 -0700 CATALOG_RETURNS TABLE SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCLTERSEをつけると大分スッキリしますね。なお「現在このパラメータはサポートされていません。」という記述がヘルプに見られましたが2020/02/12時点では動くようでした。
HISTORY
このパラメータを付けると、タイムトラベル機能での保持期限内であれば、DROP済みでPERGEしてしていないテーブルも表示できます。
出力にdropped_onカラムが追加され、ドロップしていないテーブルであればNULLが、ドロップしたテーブルであればドロップした日時が出力されます。
INFORMATION_SCHEMA.TABLES
２つ目は「INFORMATION_SCHEMA」の「TABLES」ビューから取得する方法です。
Information Schema — Snowflake Documentation
こちらは、以下のように「DATABASE」を指定するので、その「DATABASE」配下のテーブルを取得します。また、Warehouseも利用する必要があります。
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA INFORMATION_SCHEMA;
SELECT * FROM TABLES;結果は以下のようになります。「SHOW TABLES」に比べて細かい情報が取得できるのが分かります。また、取得できるレコードとして「INFORMATION_SCHEMA」配下の「VIEW」もレコードとして取得されました。
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_OWNER TABLE_TYPE IS_TRANSIENT CLUSTERING_KEY ROW_COUNT BYTES RETENTION_TIME SELF_REFERENCING_COLUMN_NAME REFERENCE_GENERATION USER_DEFINED_TYPE_CATALOG USER_DEFINED_TYPE_SCHEMA USER_DEFINED_TYPE_NAME IS_INSERTABLE_INTO IS_TYPED COMMIT_ACTION CREATED LAST_ALTERED AUTO_CLUSTERING_ON COMMENT
SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL CALL_CENTER BASE TABLE NO LINEAR(CC_CALL_CENTER_SK) 60 11776 1 YES YES 2019-04-22 16:01:59.825 -0700 2019-09-23 12:48:56.455 -0700 YES
SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL CATALOG_PAGE BASE TABLE NO LINEAR(CP_CATALOG_PAGE_SK) 50000 2777088 1 YES YES 2019-04-22 16:02:02.350 -0700 2019-09-23 12:48:56.458 -0700 YES
SNOWFLAKE_SAMPLE_DATA TPCDS_SF100TCL CATALOG_RETURNS BASE TABLE NO LINEAR(CR_RETURNED_DATE_SK, CR_ITEM_SK) 14405363575 828692696576 1 YES YES 2019-04-22 16:02:03.063 -0700 2019-09-23 12:48:56.460 -0700 YESまとめ
以上、Snowflakeでのテーブル名の一覧取得についてでした。取得方法や取得できる情報が異なるので、それぞれ用途によってうまく使い分けをしていきたいと思います。
どなたかのお役に立てば幸いです。それでは！
24
