Snowflake

外部テーブルと使い方

投稿日:

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。
ステージに配置したファイルに対してロードせずに直接クエリを実行できる仕組み。

外部テーブルの基本

ファイルの構造を自動的に解釈してテーブルにしてくれる訳ではなく、
いったんレコードが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を抽出して日付化して、パーティションキーとしている。

パーティショニングとは、名前が似ているがマイクロパーティションの事ではないと思う。
概念的に似ていて、データを別々の塊として格納し、検索時にプルーニング的な何かを期待する。

ガバナンス系オブジェクトとの関係

以前、列レベルセキュリティのマスキングポリシーをまとめたように、
テーブルと独立してマスキングポリシーを定義し、カラムに適用(Apply)することで列を保護する。

外部テーブルは上記のように、VALUE列にKey-Valueで列値が入り、
その後、SELECT文内で仮想列を定義する、という仕様で、外部テーブルに列がある訳ではない。
このため、マスキングポリシーを適用する先がない、という問題が起こる。
ただ、VALUE列全体にApplyすることは出来る様子。

外部テーブル-セキュリティカラムイントロ

Masking Policyはビューのカラムに対して適用することができるが、
これは、外部テーブルに設定したMaterialized Viewにも設定できる。
本記事の図のように、Materialized Viewを用意した上であれば、
Masking Policyを適用することができる。

-Snowflake
-,

Copyright© ikuty.com , 2024 AllRights Reserved Powered by AFFINGER4.