使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。
ステージに配置したファイルに対してロードせずに直接クエリを実行できる仕組み。
外部テーブルの基本
ファイルの構造を自動的に解釈してテーブルにしてくれる訳ではなく、
いったんレコードがVARIANT型のVALUEカラムに書かれる。
外部テーブルに対するSELECT文の中で、VALUEカラム内の値を取得して新規に列を作る。
この列を仮想列と言ったりする。
外部テーブルは、クエリ実行のたびにファイルにアクセスすることになるため遅い。
Materialized Viewを作成することで、高速に外部テーブルにアクセスできる。
複数ファイルとパーティション化
外部テーブルの実体は外部ステージ上のファイルであって、通常、複数のファイルから構成される。
複数のファイルが1つの外部テーブルとして扱われるところがポイント。
stackoverflowにドンピシャの記事があってとても参考になった。
Snowflake External Table Partition – Granular Path
/appname/lob/ という論理ディレクトリの下に Granular にファイルが配置されている例。
例えば、/appname/lob/2020/07/24/hoge.txt のように論理ディレクトリ以下に日付を使って
ファイルが配置されることを想定している。
CREATE EXTERNAL TABLEする際、LOCATION を /appname/lob/ とすることで、
/appname/lob/以下に配置された複数のファイルが1つの外部テーブルで扱われる。
その際、それぞれのファイル名が metadata$filename に渡される。
公式によると、外部テーブルのパーティショニングを行うことが推奨されている。
パーティション化された外部テーブル
下記の例では、ファイル名からYYYY/MM/DDを抽出して日付化して、パーティションキーとしている。
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE EXTERNAL TABLE Database.Schema.ExternalTableName( date_part date as to_date(substr(metadata$filename, 14, 10), 'YYYY/MM/DD'), col1 varchar AS (value:col1::varchar)), col2 varchar AS (value:col2::varchar)) PARTITION BY (date_part) INTEGRATION = 'YourIntegration' LOCATION=@SFSTG/appname/lob/ AUTO_REFRESH = true FILE_FORMAT = (TYPE = JSON); |
パーティショニングとは、名前が似ているがマイクロパーティションの事ではないと思う。
概念的に似ていて、データを別々の塊として格納し、検索時にプルーニング的な何かを期待する。
ガバナンス系オブジェクトとの関係
以前、列レベルセキュリティのマスキングポリシーをまとめたように、
テーブルと独立してマスキングポリシーを定義し、カラムに適用(Apply)することで列を保護する。
外部テーブルは上記のように、VALUE列にKey-Valueで列値が入り、
その後、SELECT文内で仮想列を定義する、という仕様で、外部テーブルに列がある訳ではない。
このため、マスキングポリシーを適用する先がない、という問題が起こる。
ただ、VALUE列全体にApplyすることは出来る様子。
Masking Policyはビューのカラムに対して適用することができるが、
これは、外部テーブルに設定したMaterialized Viewにも設定できる。
本記事の図のように、Materialized Viewを用意した上であれば、
Masking Policyを適用することができる。