カテゴリー: Snowflake

なんだか分かった気がして分からないSnowflakeのウェアハウスの同時実行特性。 ウェアハウスが1度に何個のクエリを同時処理するか だいぶ抽象化されているがウェアハウスは並列化された計算可能なコンピュータ。 処理すべきクエリが大量にあった場合、1つのウェアハウスがそれらを同時に処理しようとする。 1個のウェアハウスが同時に最大何個のクエリを同時処理するか、 MAX_CONCURRENCY_LEVEL により制御できる。 デフォルト値は 8 に設定されている。 MAX_CONCURRENCY_LEVEL を超えると、クエリはキューに入れられる。 または、マルチクラスタウェアハウスの場合は、次のウェアハウスた立ち上がる。 もちろん、クエリ1個が軽ければ同時処理は早く終わるが、重ければ時間がかかる。 逆に言うと、MAX_CONCURRENCY_LEVEL を小さくすると、1つのウェアハウスが同時処理する 最大クエリ数の上限が下がり、クエリ1個に割りあたるコンピューティングリソースが増える。 MAX_CONCURRENCY_LEVEL を大きくすると、クエリ1個に割り当たるリソースが減る。 同時実行数を減らすとキューイングされる数が増える MAX_CONCURRENCY_LEVEL を小さくするとクエリの処理性能が上がるが、 キューに入るクエリの数が増える。 ちなみにデフォルトだと、キューに入ったクエリは永遠にキューに残り続ける。 キューに入ったクエリをシステム側でキャンセルする時間を STATEMENT_QUEUED_TIMEOUT_IN_SECONDS により設定できる。 ウェアハウスサイズとの関係 ウェアハウスサイズアップはスケールアップ、マルチクラスタはスケールアウト、 なんかと、テキトーに丸めて表現されたりするが、 ウェアハウスサイズアップにより、コンピューティングリソースが増加するため、 同じ MAX_CONCURRENCY_LEVEL であっても クエリ1個に割り当たるリソースが増えることで、 結果的に所要時間が小さくなる。見かけ上、並列性が上がった風になる。 ウェアハウスサイズ、ウェアハウス数の2つのファクタによりリソースの総量が決まるため、 これらを固定した状態で MAX_CONCURRENCY_LEVEL を増やしたとしても、 結果として処理できる量が増えたりはしない様子。 同時実行性能が関係するパフォーマンスのベスプラ お金をかけずに性能が上がるということはない。 MAX_CONCURRENCY_LEVELはあくまで微調整用途。 大量の小さなクエリをガンガン処理するといったケースでは、ベスプラでは 順当にウェアハウス数を増やして、その上でウェアハウスサイズを増やすべきとされている。 この辺りが、「気分でウェアハウスサイズを増やしても性能上がらないなー」 という現象の一因（、かもしれない）。

基本的には同一のテーブルに対して設定できるクラスタリングキーは2個から3個。 ただし2個以上設定すると、1個の場合と比較して原理的に効果が落ちる。 パフォーマンスを落とさずにクラスタリングキーを追加できないか。 ベーステーブルへのクエリ実行において透過的にMaterialized Viewが使われる Snowflakeの特徴を利用して、任意の個数のクラスタリングパターンを追加する。 そんな内容が書かれた記事を見つけたので読書感想文を書いてみる。 Defining multiple cluster keys in Snowflake with materialized views [arst_toc tag=\"h4\"] オレオレクラスタリングの必要性と難しさ 色々な理由でナチュラルクラスタリングに頼らず自力でクラスタリングキーを設定する場合がある。 その際、クエリの傾向をみて、より適切なプルーニングが行われるカラムを選ばないといけない。 (\"適切な\"といのは、想定する使われ方と異なる使われ方がされた場合にプルーニング対象が減り クエリパフォーマンスが落ちる頻度を減らすということ。) ナチュラルクラスタリングは良く出来ていて、オレオレキーがナチュラルキーを超えるのは難しい。 結構なケースで「あっちが立てばこっちが立たず」という状況になる(、と思う)。 腕力に任せてオレオレキーの数だけテーブルを作る ストレージの料金は安いのだから、クラスタリングキーの数だけテーブルを作ってしまえ。 という、マッチョなやり方を取ることもできる。 同一テーブルに対して一度に設定できるクラスタリングキーは限られているが、 CREATE TABLE文に CLUSTER BY を付けることできるので、 クラスタリングキーごとにテーブルを複数作ってしまえば、この問題はある程度解決する。 注文管理テーブル的なテーブル\"注文\"があったとして、\"注文日付\"、\"顧客キー\"、\"店員キー\"の いずれかを条件とするクエリが均等に来るだろうという前提。 以下のようにCREATE TABLE文に CLUSTER BY をつける。 --注文日付をクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_注文日付\" cluster by (\"注文日付\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) --顧客キーをクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_顧客キー\" cluster by (\"顧客キー\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) --店員キーをクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_店員キー\" cluster by (\"店員キー\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) もちろん通常のTableであれば利用者側が用途に応じて利用するテーブルを選択しないといけない。 利用者側にそれぞれのテーブルを提供し、利用者は用途に応じてテーブルを使用する必要がある。 もう一歩、使い勝手が良くない。利用者が1つのテーブルを触る際に、意識せずに(透過的に) 最適なクラスタリングが行われたデータにアクセスさせることはできないか。 Materialized Viewを使うとこれが可能になる。 Materialized Viewの復習 Snowflakeに限らず、普通のRDBMSにも Materialized View は存在する。 Materialized Viewの各列に設定したサブクエリを事前計算して、クエリ時に再利用する仕組み。 以下のようにすると、cntは事前に処理され、クエリ時に再利用される。 create materialized view \"注文_aggregated_by_日付\" as ( select \"日付\", count(*) as cnt from \"注文\" group by 1 ) Snowflakeの場合、ベーステーブルに対してクエリを行うと、自動的にMaterialized Viewが評価される。 例えば、以下のクエリは\"注文\"ではなく\"注文_aggregated_by_日付\"が評価される。 select \"日付\", count(*) as cnt from \"注文\" group by 1 meta-dataにMaterialized Viewの定義が記録され、特定のルールでベーステーブルを触ると meta-dataからルックアップされたMaterialized Viewが評価される、的な動きだろうと思っている。 ベーステーブルを変更(INSERT,UPDATE)すると、自動的にMaterialized Viewに反映される。 その際、ベーステーブルにおいて自動クラスタリングが行われる。(瞬時ではなく後で非同期に行われる) Materialized Viewの効果的な使い方 ベーステーブルをあらかじめ\"日付\"でソートしておく。 ナチュラルキーとして\"日付\"が選ばれて自動クラスタリングが行われ、\"日付\"のブロックごとに パーティションが並ぶことになる。 create table \"注文\" as ( select \"日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" order by \"日付\" ) \"注文\"テーブルは\"日付\"の順で並んでいるのだから、\"日付\"を条件としたクエリを叩くと、 超絶有効にプルーニングが効くことになる。 select \"日付\", count(*) as cnt from \"注文\" where \"日付\" between date\'1993-03-01\' and date\'1993-03-31\' group by 1 \"注文\"テーブルに対して\"顧客キー\"を指定してクエリを行なった場合、 もし Materialized View を作っていなかったとしたら、Full Scan的な検索になるはず。 select * from \"注文\" where \"顧客キー\"=52671775 そこで、以下のように\"顧客キー\"をクラスタリングキーとしてMaterialized Viewを作っておく。 create materialized view \"注文_clustered_by_顧客キー\" cluster by(\"顧客キー\") as ( select \"日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"注文\" ) ; すると、以下のクエリは\"注文\"ではなく \"注文_clusterd_by_顧客キー\"に対して行われる。 select * from \"注文\" where \"顧客キー\"=52671775 Materialized Viewなので、ベーステーブルである\"注文\"に対して追加・更新を行うと、 ベーステーブルから派生させてMaterialized Viewも自動更新される。 ユーザには\"注文\"を提供するだけで良い。 (Materialized Viewへのアクセス権が必要) コストが許す限り望むだけのMaterialized Viewを作っておくことで、 ベーステーブルに対するクエリが最適なクラスタリングが行われたMaterialized Viewに 通される。

アーキテクチャの理解がまぁまぁ進んでホワイトペーパーを読んで「へー」と思える感じになったが、 ガバナンス系の機能について完全に知ったかレベルで「言葉だけ聞いたことがあるな」ぐらい。 知識の幅と深さを広げてみようと思う。妄想と憶測の個人のメモなのはご容赦ください。 公式は以下。 列レベルのセキュリティについて 列レベルセキュリティの下にダイナミックデータマスキング、外部トークン化という単元がある。 今回はこれらを読んでいく。 [arst_toc tag=\"h4\"] 中央集権的な権限管理 Enterpriseな分野において\"職務分掌\"という考え方がある。SoD, Segregation of Duties。 職務分掌とは、社内においてそれぞれの役職や部署、個人が取り組む業務を明確にし、責任の所在や業務の権限を整理・配分することです。1人の担当者が2つ以上の職務を重複して持つことで不正を行う機会が発生してしまうことを未然に防ぎ、内部統制やコンプライアンスを実現します。 職務分掌/ポリシー管理 ユーザ・ロールに付与するアクセス権を中央管理できるか否かを表す言葉。 セキュアビューを使うことによってもユーザ・ロールに対して見せる/見せないを制御できるが、 セキュアビューはRBACに基づく権限設定するわけで、所有者に全能の権限変更を許してしまう。 つまり、せっかく閲覧制限しても所有者であれば自力で閲覧可能に出来てしまう。 こういうことがないように、中央集権的な権限管理を行いたいという欲求がある。 \"SoD対応\"という感じで使われる。ダイナミックデータマスキングと外部トークン化はSoD対応。 見えてはいけないデータをどうやって隠すか? ざっくり、DBに生データを格納して読み取り時に必要に応じてマスクする方法と、 DBにマスクしたデータを保存して読み取り時に復元する方法がある。 前者はダイナミックデータマスキング、後者は外部トークン化。 どちらを利用するか選択するか、という話ではなく、 ダイナミックデータマスキングを行う際に追加で外部トークン化するか否か、決めるイメージ。 DB内のデータを外部トークン化して物理的に読めない状態としないでも、 生データの読み取り時に動的にマスクすることで要件を達成することはできる。 ニーズに合わせてどちらがより適切か選択する必要があるが、これが結構細かい。 「どちらがより強力か」みたいな簡単な比較にはならない。 外部トークン化は、同一のデータを同一のマスク値に変換する特徴がある。 中身は見えないがカウントを取るとその数は一致する。 対して、ダイナミックデータマスキングは、見えない人には無いものとして扱われるから、 マスク値を使ってグループ化したり、カウントを取ったりすることはできない。 その他、それぞれの比較が以下に書かれている。 ダイナミックデータマスキングまたは外部トークン化の選択 マスキングポリシー マスキングポリシーはスキーマレベルのオブジェクト。テーブルと独立して定義できる。 要は、保護対象と保護方法が独立していて、保護対象に依存しない形で保護方法を定義する。 クエリの実行時にマスキングポリシーが評価されて、クエリ実行者は適用済みデータを取得する。 ユーザIDとかロールとかによって保護するか否かを制御する内容を書いておく。 ロールAには見えるけど、ロールBには見えない、という結果になる。 公式に実行イメージがあるので貼ってみる。 マスキングポリシーを作ってテーブルのカラムにあてているんだな、とか、 文字列型のカラム値を加工して文字列型の値を返す何かを書くんだな、とか分かる。 -- Dynamic Data Masking create masking policy employee_ssn_mask as (val string) returns string -> case when current_role() in (\'PAYROLL\') then val else \'******\' end; -- Apply Masking Policy to Table column ALTER TABLE {テーブル名} ALTER COLUMN {カラム名} SET MASKING POLICY　employee_ssn_mask 通常、所有者はオブジェクトに対して全権を持つが、マスキングポリシーはアクセス制御と 独立していて、所有者に対してデータを保護することもできる。それぐらい独立している。 「機密を守る」専門の部署を作って、そこで統一的にデータ保護を設計する、 みたいな運用をすることもできる。 UDFsを使ってマスキングポリシーの管理をしやすくする 上のような感じでマスキングポリシーを作ってルールをハードコードし続けると、 いわゆるコードクローンが発生したり、共通化が難しくなるなど、結構面倒なことになる。 こういったケースには、ユーザ定義関数 (User Defined Functions, UDFs)が有効。 例えば、以下のように SHA2 でコードするUDFを定義し、マスキングポリシー内でUDFを 評価することができる。こうしておけば UDFを再利用したりリファクタしたりしやすくなる。 CREATE OR REPLACE FUNCTION .sha2_array(SRC ARRAY) RETURNS ARRAY LANGUAGE SQL AS $$ ARRAY_CONSTRUCT(SHA2(ARRAY_TO_STRING(SRC, \',\'))) $$ ; CREATE OR REPLACE MASKING POLICY .array_mask AS (val ARRAY) RETURNS ARRAY -> CASE WHEN .has_mask_full_access(current_role()) THEN val -- unmask WHEN .has_mask_partial_access(current_role()) THEN .sha2_array(val::ARRAY)::ARRAY -- partial mask ELSE TO_ARRAY(\'***MASKED***\') -- mask END; ALTER TABLE IF EXISTS .test MODIFY COLUMN names SET MASKING POLICY .array_mask; マスキングポリシー条件 上の実行イメージのように、手続き型の処理ではなく、関数評価による宣言的な設定を行う。 上の例ではマスキング対象を判定するcase whenを使用している。 この条件として、条件関数、コンテキスト関数、カスタム資格テーブルを利用できると記述がある。 上の例では、current_role コンテキスト関数を使用し、ロールに応じたマスキングを行う。 公式にはコンテキスト関数のうち invoker_role, invoker_share について言及がある。 カスタム資格テーブルはマルチテナントデザインパターンのホワイトペーパーに出てきた。 ユーザID、またはロールIDと、それらが何のリソースにアクセスできるかを1つのテーブルに したもの。例えば current_role に対して該当列をマスクするか否かを自前のテーブルを 使って表現できる。深読みしないでおく.. セキュアビューが増えてしまう代替案としてマスキングポリシーを使う セキュアビューを利用することによってユーザ・ロールに対する行レベルセキュリティを実現できる。 (列レベルセキュリティではないが、選択的にデータを見せるという意味で同列) CREATE TABLE widgets ( id NUMBER(38,0) DEFAULT widget_id_sequence.nextval, name VARCHAR, color VARCHAR, price NUMBER(38,0), created_on TIMESTAMP_LTZ(9)); CREATE TABLE widget_access_rules ( widget_id NUMBER(38,0), role_name VARCHAR); CREATE OR REPLACE SECURE VIEW widgets_view AS SELECT w.* FROM widgets AS w WHERE w.id IN (SELECT widget_id FROM widget_access_rules AS a WHERE upper(role_name) = CURRENT_ROLE() ) ; BI用途が例として上がっているが、ビューからビューを作って、その先のビューを.. といったようにビューは大量に作られることが常なので、セキュアビューだけによって ユーザ・ロールに対する閲覧制限をかけるというのはかなり大変なことになる。 テーブル・ビューなどのオブジェクトと独立して定義できるマスキングポリシーなら、 どれだけBIが複雑になっても確かに管理が容易になりそう。 マスキング対象列からマスキング非対象列へのコピー 結構ハマったのでメモ。ハマって苦しんだ時何度読んでも理解できなかった...。 考慮事項 ダイナミックデータマスキングが設定された列の値を使うとき、 使う人が非マスク値(生データ)を閲覧できる場合は生データをコピーしてしまう。 生データが閲覧できずマスク値が表示されてしまう場合はマスク値をコピーしてしまう。 外部トークン化 外部関数を使って外部APIに実装されたトークン化処理を実行する、ということなのだが、 信じられないぐらい実装例をWebで見つけられなかった。(探し方が悪いのか..) 外部関数とは、要はクラウド上で利用可能なAPIを指す。 AWSで言うとAPI Gatewayの先にLambdaがあり、API統合により透過的に利用可能にしたもの。 Lambdaにトークン化コードを書いておきAPI Gatewayから呼べるようにしてAPI統合する。 既成の外部トークンプロバイダが存在し、Snowflakeから以下が利用できる様子。 ALTR,Baffle,Comforte,Fortanix,MicroFocus CyberRes Voltage, Protegrity,Privacera,SecuPI,Skyflow ちょっと深そうなので省略。

全般的に参考にできるリソースが少ない中で、公式が出すホワイトペーパーは本当に参考になる。 マルチテナントの設計方針が書かれている以下のホワイトペーパーを読んでみた。 もちろん、マルチテナント的な応用をする際に参考になりそうな話ばかりなのだが、 それだけに限らない「オブジェクト配置に関する設計の大方針」が説明されているな..と。 Design Patterns for Building Multi-Tenant Applications on Snowflake この記事では、ホワイトペーパーを読んで気づいた何かを書き連ねてみる。 もちろんホワイトペーパーのメインはマルチテナントなのだからそう読むべきだが、 「テナント」を「グルーピングされたデータ」と仮定して追加で得られた感想を書いてみる。 英語力、文章の読解力の不足、認識違い、などなどにより誤りが多数あるかもしれませんが、 個人のメモ、ということでその点ご容赦ください。。 [arst_toc tag=\"h4\"] オブジェクト単位で持つかテーブル内に混在させるか SnowflakeはDatabase,Schema,Tableなどのオブジェクトが割と疎に結合する構造になっている。 Snowflakeのベストプラクティスを重視すると、オブジェクト同士を疎結合したくなる欲求にかられる。 確かにZero Copy CloningやSharingなど、アーキテクチャの優位さを活用しやすいのだが、 脳死で疎結合にする前に、密結合パターンとのメリデメを一応考えた方が良いと思う。 疎結合にするということは、結合により多くの手間がかかるということだと思う。 実際にオブジェクト単位にデータを保持しようとすると、オブジェクトの数が増えすぎたり、 増えたオブジェクト間のデータを結合するのに手間を要したり、一言で書くと面倒になる。 本当にリソース単位で持つべきなのか、従来のように1つのリソースの中で論理的なグルーピングを するのに留めた方が良いのか、ちょっと考えた方が良いと思う。 1つのテーブルにテナントを混在させる (Multi-tenant table,MTT) 複数のテナントを扱おうとしたとき、「テナントID」などを利用して1つのテーブルを共有するモデル。 何しろ、テナントがいくら増えても1つのテーブルで対処できるという簡単さがある。 そのテーブルに付随する様々なオブジェクトが増えることがない。 レコード数が増えることでクエリのパフォーマンスが落ちるのではないか、という懸念については、 ちょうど「テナントID」をクラスタキーと設定することで、テナントがマイクロパーティションに 割り当たることになり、むちゃくちゃ効率的にプルーニングが効くため、問題ない。(エアプ) テナントが1つのテーブルを利用するためにテナント間でスキーマ定義を合わせる必要があり、 差異がある場合には、最小公倍数的な定義にする必要があり無駄が発生する。 1つのクエリで複数のテナントを横断できることがメリットでありデメリットでもある。 原理的に他のテナントのデータを触れてしまうということは便利だが確実に安全でない。 権限モデルの適用対象となるテーブルよりも小さい単位で論理的なブロックを作ると、 結果として権限モデルが関与しない場所で、列レベルセキュリティ、行アクセスポリシー、 資格テーブル等を駆使して「自前の権限設計」をしないといけなくなる。 CLONEの最小粒度はテーブル、SHAREの最小粒度はデータベースであり、 これらを積極的に使った「Snowflakeっぽい」疎結合なアーキテクチャを導入しづらい。 後述するが、複数のテナントでウェアハウスを強制的に共有してしまう。 つまりテナントごとのコンピュートコストを算出できないし、テナントごとにスケールできない。 共有することでコスト効率は良くなるため、コスト算出を分離しないで良いならメリット。 オブジェクトごとにテナントを配置する(Object per tenant,OPT) テナントをTable単位,Schema単位,Database単位など、オブジェクト単位で分離する。 1回のSQLでオブジェクト横断的にアクセスできなくなるのがメリットでありデメリット。 何しろ、RBACによるアクセス制御が効くようになるのでセキュリティ設計が簡単になる。 オブジェクトを疎結合しましょう、というSnowflake的なデザインパターンだと思う。 箱を増やすと、それに付随するリソースを何個か作らないといけないのが常なので、 扱うテナントが増えるとすぐに管理できないくらいオブジェクト数が増える。 ホワイトペーパーには10から100数十ぐらいではないか、と書かれている。 個人的にはテナントを3個も作ったらもうIaCツールが欲しくなる。 CLONE,SHARE はOPTを前提にしているとも言える。 OPTを選択するとSnowflakeっぽいアーキテクチャにしやすい(と思う)。 アカウントごとのテナントを配置する(Account per tenant,APT) テナントをアカウント単位で分離するという考え方。いわゆる「dedicated」な配置方式。 同一アカウント内で複数のオブジェクトに触ることは出来るが、アカウントを跨って触ることはできない。 物理的に触れない場所に分けておく方式。OPTよりもさらにグループ間の独立性を高めることができる。 リソースの共有を物理的に遮断する配置方式なので、コンピュートの効率性は良くない。 テナントごとに完全にコストを見積もることができる。 アカウントの上に「組織」の概念があるため、管理上の連続性はある。 ただ、大体のクラウドサービスと同様に、アカウントの上の管理は「取ってつけた感」があると思う。 そもそも「組織」内の複数アカウントを紐づける概念なので、 テナントがビジネス上の異なる組織であったりしたら、その時点で怪しくなる。 今回は薄く読んでおくに留める。 MTT,OPT,APTまとめ ホワイトペーパーにPros.Cons.がまとまっていたのでざっくり意訳してみる。(テキトー...)。 特にOPT,APTのスケーラビリティが低く、用途によってMTT一択となる可能性がある。 MTT OPT APT データモデルの特徴 各テナントは同一のShapeを持つ必要がある。「テナントID」等によりグループを識別できるようにする。 各テナントは異なるShapeを持つことができる。 各テナントは異なるShapeを持つことができる。 スケーラビリティ 上限なし 10から数百では? 10から数百では? セキュリティ的な何か 開発者がセキュリティを意識する必要がある。開発者がRBAC,列レベルセキュリティについて熟練していないといけない。 権限管理テーブルの管理さえ厳密にしておけば、あとはRBACにさえ気をつければ良い。 最大限、セキュリティに振ったモデル。いわゆるdedicatedな環境。 備考 必ずコンピュートを共有することになる。結果としてコストが浮くし運用が楽になる。 要件次第でグループ間の結合度を自由に決められる。つまり、コンピュートを共有するか否かを決められる。共有すればコストが浮くがグループ間の連携に際して考えないといけないことが増える。 RDBMSを使ったレガシープラットフォームをリプラットする際に親和性が高いのではないか。 欠点 マルチリージョンのShareが難しくなる。高パフォーマンス化のためにシャーディング(クラスタリング?)が必要かもしれない。レコード数が増えるため更新系(UPDATE,MERGE)が大変になる。1つのテナントについてのコストを見積もるのが大変になる。 オブジェクトを作ることは簡単だが、似て非なるオブジェクトの一貫性を保つのは難しい。オブジェクトの増加に伴い、オブジェクト同士を同期するのが大変になる。(IaCのような)自動化の仕組みが必要。 OPTど同様にアカウントを作ることは簡単だが似て非なるアカウントの一貫性を保つのは難しい。コンピュートを共有できなくなる。結果として高コストとなる。OPTど同様に(IaCのような)自動化の仕組みが必要。 MTTで考慮すべきデザインパターン集 同一テーブル内でテナント同士を論理的に分離する方法 まず、異なるテナントを同一のテーブルで認識するため、テナントの識別子を持たせる。 RBACに基づいたアクセス制限が出来ないため、アクセス制限を行う論理的な仕組みが必要となる。 ホワイトペーパーで紹介されているのは、資格テーブル(Entitlements Table)を用意するというもの。 資格テーブルに、どのユーザ、ロールがどのグループにアクセスできるか書いておき、JOINして使う。 Snowflakeにおいて、Viewは背後にある(Underlying)テーブルをViewの外から見えてしまう。 例えばSnowsightでViewの定義を見るとCREATE VIEW文の定義が見える。 Secure Viewを使用すると見えないため、Secure View上で資格テーブルとの結合を行う。 資格テーブルの保護 資格テーブルはグループを論理的に分離する上で重要で、管理を厳密にしないといけない。 誰もが触れる場所に資格テーブルがあったりすると、開発者が意図しないところで不用意に変更されてしまう場合がある。 Secure Viewと資格テーブルを異なるスキーマに配置し、開発者だけが資格テーブルを触れるように しておけば、少なくとも非開発者が資格テーブルを変更してしまう、という事故を防げる。 例えば「ユーザが増える度にINSERT/UPDATEして資格テーブルを書き換える」ような運用をすると、 どこかでミスってガバい資格テーブルになってしまうかもしれない。 ユーザ・ロールの権限の変更により自動的に資格テーブルが更新されるような仕組みを用意しておくべき。 常に回帰テストを実施して、ガバい論理テーブルにならないよう注意すべき。 テナントとパーティションの割り付けとクエリ効率 (根拠が薄いのだが)、クラスタキーを手動設定してオレオレクラスタリングを強制したとしても、 Snowflakeが自動的に決めた(Naturalな)クラスタリングに勝てない場合が多い気がする。 要は、将来叩かれるであろうあらゆるクエリのWHERE句かJOINのON句がいかに多くのパーティションを プルーニングするか、が大事だが、普通、そんなに期待通りプルーニングできないと思う。 テナントIDをクラスタキーとして、そのテナントのデータだけを取得する、ということは、 原理的には該当テナントのパーティション以外をプルーニングすることを保証する訳で、 これは結構期待できるのではないかと思う。(ちょっとやってみたい) ベスプラでは、クラスタキーは2,3個程度とされていて、他に日付などを設定する。 グループの識別子をソートして並べておく。 通常、背後でSnowflakeが自動的にクラスタリングを処理してくれる(自動クラスタリング)。 もしテナント数が増加してテーブルのレコード数が増えると、自動クラスタリングの負荷が上がる。 INSERT,UPDATE等の追加変更操作をトリガとして自動クラスタリングが走るので、 頻繁に追加変更操作をするシナリオだと超絶課金されてしまうかもしれない。 フロント側から透過的にSnowflakeのSSO機能を利用する 昔、Laravel製のWebアプリにOAuthによるSSO機能を実装したことがある。 SnowflakeはSAMLによるSSOもサポートしていて、ほぼノーコードでIdPとのSSOを実現できる。 SnowsightでSnowflakeを使う分にはポチポチ連携して終了で良いのだが、 通常、SnowflakeのフロントにBIツールなどのアプリケーションが存在し、 ユーザはフロントの認証機構を介して透過的にSnowflakeのデータにアクセスしたい。 フロント側のアプリはSnowflakeの以下の機構を利用してSSOを実現することになる。 カスタムクライアント用のSnowflake OAuth の構成 公開鍵認証方式によるOAuthアクセストークンを介したセッションへの認可が行われる。 アクセストークンに対するアクセスとセキュリティはフロント側アプリの実装に任される。 例えば、AWS Secrets Managerなどを利用することで、アプリ側が堅牢になったりする。 フロント側アプリへのログインにより、有効なアクセストークンと対応するセッションが得られ、 セッションを経由して、セッションに紐づくデータにアクセス可能となる。 Snowflake側で認可を無効にすることができる。 とはいえ、OAuthに対応したBIツールなんかでは、画面ポチポチだけで連携できるので便利。 作業用データベースを分離する RBACによるアクセス制御によって、「ここで何をすべきか」、「誰が何に触れるべきか」を定義する。 データベースを分離して権限設定することにより、これらが明確になり管理を単純化できる。 例えば、ELTの際に作成する中間テーブルなどは本体のリソースと異なり一般ユーザに開放する必要はないため、 公開用データベースとは異なる別のデータベースに作成し、別に権限設定をすべき。 開発者のみが中間テーブルなどにアクセスできる仕組みを構築できる。 ワークロードを分離する Snowflakeの大原則は、ウェアハウスの稼働時間に対して課金されること。 ウェアハウスのサイズを大きくすることでワークロードの処理時間が短くなるのであれば、 大きなウェアハウスでワークロードを短時間に終わらすことが目指すところ。 スケールアップにだけ目が行って稼働時間の短縮を忘れるとコスト的に酷い目に合う。 例えば、BIのような用途だと、小さなウェアハウスを予測可能な時間帯で回すことになる。 一方、AdHocな用途だと、大きなウェアハウスを予測不可能なタイミングで回すことになる。 これらのワークロードを1つのウェアハウスに盛ると、大きなウェアハウスを長時間稼働させてしまう。 「小サイズ高稼働」と「大サイズ低稼働」を分けておけば、大サイズは高稼働しないので、 結果的に安くなる。 マルチテナント的には、テナントにデフォルトで共有ウェアハウスを割り当てておき、 オプションで(有料で)占有ウェアハウスを割り当てる、なんていうパターンもある。 ユーザに適切なウェアハウスを割り当てる 複数のウェアハウスを使用・操作できるようにロールのアクセス制御を行いユーザにアタッチすると、 ユーザはセッション内にウエアハウスを選択できるようになる。 ユーザに対してデフォルトのウェアハウスを設定することで、より適切なルーティングが可能となる。 (ロールに対してデフォルトのウェアハウスを設定することはできない。あくまでユーザに設定する。) OPTで考慮すべきデザインパターン集 OPTの特徴 テナントをオブジェクト毎に分離する方法。オブジェクトを疎結合する大方針にあったやり方。 最も簡単に綺麗に分離できる。 データベース毎か、スキーマ毎か、テーブル毎か、パイプラインが満たす要件、開発のライフサイクル、 テナント毎のデータにどれだけ一貫性があるか、などにより選択する。 また、テナント数はどの程度か、テナントは何個のテーブルを使用するか、 CLONEするか、レプリケーションするか、など多くのことを想定しないといけない。 CLONEはデータベース、スキーマ、テーブルの各層で効くが、レプリケーションはDB単位だから、 その観点でオブジェクトを選択しないといけない。 複数のオブジェクトの作成を自動化する テナントが増えると、そのオブジェクトに関係するアクセス権などを維持するオブジェクトを追加する 必要が発生する。それぞれのテナントに個別の設定もあれば一貫性を保つべきものもある。 テナントが少ないうちは手でポチポチやっていても良いが、多くなってくると大変になる。 そういった背景があって、リソース作成を自動化したいという欲求が発生する。 外部のいろいろなIaCツールを使って自動化すると便利になる。 この辺り、昔Webアプリを書いていた頃お世話になっていた何かと久々に再会。 要はアプリケーション開発の一部としてSnowflakeリソースを一貫性をもって構築する。 マイグレーションツールのFlywayがSnowflake対応している。 Flywayを使ってスキーマ定義の一貫性を保てる。 (対応している範囲であれば)Terraformを使ってリソースの一貫性を保てる。 MTTと比較したOPTにおけるユーザルーティングの重要性 Authenticating and authorizingの節、読解が難しい。 ユーザを正しいデータベース、ウェアハウスに繋ぐ方法はMTTと同様だがMTTよりも重要である、 なぜMTTの時よりも重要になるのか、は、MTTと比較して文脈(context)が異なるから、とのこと。 MTTの場合、オブジェクトを共有するため、アクセス権はオブジェクトへのルーティングではなく、 オブジェクト内の資格テーブルが決定する一方で、OPTの場合は、まさにアクセス権そのものになるから、 とか、オブジェクトごとにテナントを分離するのだから、ユーザを誤ったテナントに誘導してはならない、 とか、そういうことを言いたいのだと思う。 重要な位置がMTTとOPTで異なりOPTの場合は重要、ということだろう。 MTT,OPTによるコスト最適なingest/transformation ELTLのingest/transformation用テーブルの配置方法によってコストが変わる。 しばしば、1つのオブジェクトにワークロードを詰める場合、個別のオブジェクトに分散させる よりも低コストである。例えば、複数テナントを分離した各テナント毎に ELTL ingest/transformation用テーブルを用意し、それぞれ別々にパイプラインを 走らせるよりも、共通のテーブルに対して1つのパイプラインを走らせる方が 低いコストで済む。(理由が明示されていないが、感覚的にはオブジェクトごとにクエリが 分割されてしまうことでクエリの実行回数が増加し、結果として長い時間ウェアハウスが 稼働していまうからだと考える) いろいろ組み合わせが考えられる。OPTとMTTを組み合わせたhybrid構造によりコストと 機能を最適化する。つまり、ingest/transformationを共通オブジェクトで行い、その後、 各テナント毎のオブジェクトに展開するか、そのまま共通のデータベースに展開するかを選ぶと良い。 レプリケーションはDB単位なのでOPTが適切 レプリケーションはデータベース単位なので、テナントがデータベース内のリソースを 共有してしまっていると、個別のテナントを選択してレプリケーションすることが難しくなる。 そういった用途があるのであればOPTとしてデータベース単位にテナントを分けるべき。 APTの特徴 1つのテナント毎に1つのSnowflakeアカウント、ウェアハウス、1つのデータベースを用意する。 ただし、アカウントを跨いで出来ることは限られるため、どうしても例外が発生してしまう。 基本的には、アカウント内に存在するデータベースに対してアカウント内のウェアハウスで処理するが、 共通データベースのingestion/transformationデータをSHAREして持ってきたり、 持ってきたデータに追加のELTLをかけたりするために、追加のウェアハウスが必要となる場合がある。 アカウントを跨ってウェアハウスを共有することはできないので、APTの場合最もコストがかかる。 APTで考慮すべきデザインパターン集 APTの認証 フロント側の認証の方法はMTT,OPTと同じものが大体機能する。 APTはアカウント毎にログインURLが異なる。ユーザとアカウントが1対1対応するため、 フロント側を介さずにユーザが直接Snowflakeアカウントにログインすることも可能となる。 評価 評価基準:データ配置/セキュリティ観点 3つのモデルを評価すべきだが、SnowflakeはMTTから評価をスタートすることを推奨している。 つまり、最初にMTTが機能するか、そうでないなら何故かを評価すべき。 データの配置方法・セキュリティ観点での評価チャートは以下の観点に依存する。 データが配置・暗号化に関する契約上の責任 データが配置・暗号化に関する規制上の責任 データの配置・暗号化に関する情報セキュリティ基準 アプリケーション所有者がRBACの強制をどう見ているか アプリケーション所有者が資格テーブルに対する行レベルセキュリティをどう見ているか 顧客のアプリケーションへのアクセス方法 テナント間でテーブルスキーマ等のシェイプがどれだけ近いか MTTを軸に、以下のチャートを見て判断する。 Snowflakeの暗号化フレームワーク Snowflakeの暗号化体系について詳細を知らなかったので一旦まとめてみる。 暗号化キーの階層モデル Snowflakeの大前提の1つに、すべてのデータはat restで暗号化される、という点がある。 セキュリティ標準に基づいてAES256ビット暗号化を行う。その際、暗号化キーを管理する。 暗号化キーの管理方法が厄介なのだが、独自の管理を行うため基本的にユーザの手間はない。 暗号化キー管理については以下。 Snowflakeの暗号化キー管理を理解する Snowflakeにおいて、暗号化キーは階層モデルに基づいて暗号化される。 暗号化キーは以下の4つのレイヤから構成され、各レイヤと対応するキーが用意される。 上位レイヤのキーが下位レイヤのキーを暗号化する。 もちろん、各レイヤの暗号化キーは各レイヤのオブジェクトの暗号化に使われる。 1. ルートレイヤ (Root Keys) 2. アカウントレイヤ (Account Master Keys) 3. テーブルレイヤ (Table Master Keys) 4. ファイルレイヤ (File Keys) 下のレイヤにおいて、より狭い範囲を適用範囲とすることとなる。 ルートキーだけだと巨大な範囲を1つのキーで暗号化せざるを得ないが、 細分化しておくことで、各暗号化キーが対象とする範囲を狭くすることができる。 アカウントレベルで、アカウント毎に暗号化キーが用意されることに注意する。 アカウント毎に異なる暗号化キーによりAES256暗号化が行われ分離される。 暗号化キーのローテーション どれだけ堅牢に暗号化キーを管理していたとしても、長時間晒されることはリスク。 なので、定期的に暗号化キーをローテーションしよう、という発想になる。 Snowflakeにおいて、各暗号化キーは30日ごとに自動的に更新される。 常に最新の暗号化キーで暗号化が行われ、古くなったキーは復号にのみ使われる。 こうすることで、同じ暗号化キーの有効期限を限定できるようになる。 Tri-secret Security Snowflakeをホストするプラットフォームで顧客が管理する暗号化キーを管理する。 Snowflakeが用意する暗号化キーと顧客管理の暗号化キーをセットで使って 複合マスターキーを作る。 複合マスターキーはアカウントレイヤのマスターキーとして使われ、 階層内の(テーブル、ファイルの)暗号化キーをラップする。 (暗号化キーをさらに暗号化するのであって、生データを暗号化したりはしない。) 複合マスターキー内の顧客管理キーを無効化すると、複合マスターキーが関与する 範囲のデータを復号できなくなり、一段セキュリティレベルが上がる。 顧客管理キーは\"アカウントレベル\"のみ。テーブル、ファイルレベルは無い。 \"Tri\"ということで3つの要素からなるセキュリティフレームワークなのだが、 その構成は以下。この項は 2つ目の\"顧客管理キー組み入れ\"。 1. Snowflakeの暗号化フレームワーク 2. 顧客管理キー組み入れ 3. Snowflakeの組み込みユーザ認証 評価基準:暗号化/データ分離/保護の観点 暗号化/データ分離/保護の観点でマルチテナントを選ぶケースもある。 クラウド上でテナントを論理的に分離している訳ではない、という原理があり、 その上で、Tri-secretセキュリティにより暗号レベルで分離している。 MTT<OPT<<APTの順にその分離の強度が上がっていく。どこに落ち着かせるか。 Tri-secretの顧客管理キー組み入れでアップデートされる暗号化キーはアカウントレベルのみ 暗号化キーの階層管理はアカウント、テーブル、ファイルの各レイヤで効き、アカウント間のアクセスを防ぐ。ただしShareした場合は別 Snowflakeは複数のテナントをクラウド上で管理する。物理的に分離している訳ではないが異なる暗号化キーにより暗号レベルで分離する データベースやスキーマは論理的な区分である。物理的にデータを分離している訳ではない 以下の観点で決めていく。 ネットワークポリシー要件。MTT,OPTのように論理的なユーザレベルの制御で良いか。またはAPTのようにアカウントレベルの個別の制御が必要か (APTのように)テナント毎に仮想ウェアハウスを分離し、ウェアハウスキャッシュに生成されるデータを分離する必要があるか テナント毎のコスト管理をどのように行い顧客に課金するか。もしウェアハウスのコンピュートをテナントで共有する場合、経験的で不正確な方法でコスト管理をする必要がある 顧客はどのような方法でSnowflakeにアクセスするか どれぐらい多くのテナントが同時に1つのウェアハウスを使用するか 評価基準:リソース利用、ネットワークポリシーの観点 最後の項目。結構際どいことが書いてある。仮想ウェアハウスキャッシュはキャッシュが作られたときのRBAC,SecureViewも再利用するらしいw IPアドレスによるネットワークアクセス制御はアカウント単位のユーザレベル。(OPT以下では全テナント共有となる) 仮想ウェアハウスキャッシュは、それ以降に発生するクエリにおいて、クエリの一部または全部で再利用される。RBACまたはSecureViewは再利用される Snowflakeのコストはウェアハウス単位で課金される。複数テナントがウェアハウスを共有した場合、クエリ単位、ユーザ単位、テナント単位のコスト見積もりを正確に出せない ユーザ・テナント単位にウェアハウスの使用制限をつけることはできない まとめ Design Patterns for Building Multi-Tenant Applications on Snowflakeを読んでみて、 思ったところを書いてみた。当然、マルチテナントの設計方針について詳細に書かれているが、それ以上に、 「似て非なるデータをどうやって持つか」という、重要な観点がチラチラ気になる内容だった。 ということで1万字越えの記事は終了。

概要 データをコピーせずにアカウント間で共有する仕組み。 いわゆるSSOT(Single Source Of True)を実現できる。 アカウント内のデータベース内の選択した以下のオブジェクトを他のSnowflakeアカウントと共有できる。 テーブル 外部テーブル セキュアビュー セキュアマテリアライズドビュー セキュアUDFs 公式のURLは以下。Secure Data Sharingの紹介 アーキテクチャと仕様 Shareで何が出来るか、はShareのアーキテクチャに依存するところが多い。 Shareのアーキテクチャは公式の以下の図が参考になる。 オブジェクトを提供する側(プロバイダ)と、利用する側(コンシューマ)からなる。 コンシューマは複数のプロバイダからオブジェクトの共有を受けることができる。 プロバイダが Shareオブジェクト を介してコンシューマとオブジェクトを共有する。 重要な点は、オブジェクトはアカウント間でコピーされず、ある時点のポインタが共有される点。 この構造に由来して、以下のような特徴がある。 Shareから作成するオブジェクトは読み取り専用 Shareからオブジェクトを作成する操作は瞬時に完了する コンシューマはストレージを消費しない 共有データに対するクエリを実行するウェアハウスのコストのみ消費する コンシューマはShareの再Shareはできない。 「Share」と「共有」が混在して読みづらいな...。 Shareオブジェクト Shareは Database や Schema と同様にセキュリティ保護可能なオブジェクトである。 その実体は、共有するオブジェクトの権限とコンシューマアカウントをカプセル化したものである。 従って、Shareを開始する際に、プロバイダはこれら2つの情報を設定する。 DB,Schema内に共有するSecure View, View, Table,UDFsなどを作成する Shareオブジェクトを作成する ShareオブジェクトにDB,Schemaに対するUSAGE権限を付与する Shareオブジェクトに共有するオブジェクトに対するSELECT権限を付与する Shareオブジェクトに共有先アカウントを紐づける Shareに含まれるオブジェクトはプロバイダによって100%管理される。 つまり、プロバイダが誰に何を共有するか、共有の開始/終了、等を管理する。 プロバイダとコンシューマがShareを共有していた場合、 もしプロバイダがShareにオブジェクトを追加したとしても、即座にコンシューマに連携されない。 機密データのShare 共有するデータベースに機密データが含まれるシナリオについて公式に別立てされている。 機密データが含まれるテーブルを直接共有した場合、もちろん機密データは共有される。 Secure View,およびSecure UDFsを介すことで機密データを保護しつつ共有できる。 セキュアオブジェクトを使用したデータアクセスの制御 共有するデータベースのスキーマをPrivateとPublicに分ける。 Privateには機密データを含むテーブル、PublicにはSecure Viewを配置する。 PublicのSecure ViewはPrivateの機密データテーブルを参照する。 Secure Viewの外からはPrivateの機密データを参照できないものとする。 PublicのSecure ViewのみをShareの対象とすれば、コンシューマからはPrivateは読めない。

公式ドキュメントを見ながらポチポチしていくとだいたいロード出来てしまうのだが、 公式ドキュメントを良く読んだことがなかったのでなぞってみる。 いくつかあるロード方法のうちストレージ統合を利用し実際にS3からロードしてみた。 Snowflakeへのデータのロード https://docs.snowflake.com/ja/user-guide-data-load.html [arst_toc tag=\"h4\"] ステージ gitで言うステージとほぼ同様の概念で、ローカルにあるデータをSnowflakeのテーブルに アップロードする前に一旦Snowflakeが管理する領域にアップロードすることができる。 Snowflakeが管理する領域の外にあるか内にあるかで外部ステージ/内部ステージに分かれている。 名称 概要 外部ステージ Snowflakeの外のストレージ。例えば主要3クラウドサービスのおブロックストレージAWS S3,Google Cloud Storage,Azure BlobGlacier Deep Archiveなど,そのままでは使えず復元操作が必要なものは対象外。これらに名前をつけて扱えるようにする(名前付き外部ステージ)。例えば名前付きステージとして設定したS3に対してs3 syncコマンドでローカルからコピーし,そこからSnowflakeにロードする。 内部ステージ Snowflake側のストレージ。ユーザーステージファイルを保存するために各ユーザに割り当てられているストレージ。ユーザは内部ステージに置いたファイルを複数のテーブルにロードする。テーブルステージSnowflake上にあるテーブルごとに紐づいたストレージ。複数のユーザで共有する。ここにファイルを置いてテーブルにロードする名前付き内部ステージテーブルとは独立して用意される複数人共有可能なストレージ。 各ブロックストレージが用意する操作により外部ステージにアップロードできる。 PUTコマンドによりローカルから内部ステージに直接アップロードすることもできる。 外部/内部ステージいずれのステージからもCOPY INTOコマンドでSnowflakeテーブルにコピーする。 COPY INTOによるバルクロード 例えばデータファイルが1TBとかあるとステージングも大変だしテーブルへのロードも大変。 どうにかしてステージに上げたとして、COPY INTOコマンドでテーブルにロードすることを考える。 クエリがCOPY INTOのソースとなるが、クエリをいじくることでCOPY INTOする列を変更できる。 省略したり削除したり名前を変えたりキャストしたり、クエリ操作で出来ることが出来る。 1TBのステージをCOPY INTOでロードすると、仮想ウェアハウスがむちゃくちゃ頑張る。 仮想ウェアハウスを盛れば盛るほど信じられない速度でCOPY INTOが完了して気分が良い。 Snowpipeを使用した連続ロード 少量のデータを段階的にロードする方法。断続的にパイプのこちら側から向こう側へデータを流す。 COPY INTOとは異なりSnowflakeが用意するクラウドリソースの課金となる。(不明..) COPY INTOと同様のデータ変換処理をかけられる。(不明..) 外部にあるデータをロードせずにクエリ実行 Snowflakeのテーブルにロードせず、外部のクラウドストレージに置いたままクエリを実行できる! ただ外部のクラウドストレージにあるだけではダメで「外部テーブル」を設定する。 外部テーブルにより外部のクラウドストレージにメタ情報が設定される仕組み。 外部テーブル上のデータにはDMLを実行できない。 とはいえ、外部テーブルにあるままクエリをかけると遅い。 これを解消するのがMaterialized Viewのシナリオで、外部テーブルをオリジンとして Materialized Viewを作っておけば、外部テーブルの変更に対して透過的にアクセスできるし、 クエリ結果キャッシュほどでは無いにせよ、パフォーマンス上の利点がある。 もはやデータのロードではないので、新たに記事をおこして書く。 ファイル形式と半構造化データ 外部ステージに指定されたファイルタイプのファイルを置くことでSnowflakeのテーブルにロードできる。 通常、CSVやTSVなんかを置くイメージ。 特筆すべきはJSONのようにスキーマ定義がコンテナと同一化しているフォーマットを「半構造化データ」 として読み込むことができるということ。JSON以外にHive由来のArvo,ORC,Parquetに対応している。 (半構造化データについては奥が深そうなので別記事対応。) フォーマット名称 Arvo 要はシリアル化されたデータとデータのスキーマを同時に格納するフォーマット。Big Queryも対応しているしこの界隈では有名なんだろうか。Apache Arvo ORC Apache Hive用の列指向フォーマット。The Optimized Row Columnar (ORC) file format provides a highly efficient way to store Hive data. It was designed to overcome limitations of the other Hive file formats. Using ORC files improves performance when Hive is reading, writing, and processing data.the smallest, fastest columnar storage for Hadoop workloads.Apache ORC Pqrquet databricksからApache Pqrquet。列志向フォーマット。　Apache Parquet は、効率的なデータの保存と検索のために設計された、オープンソースの列指向データファイル形式です。複雑なデータを一括処理するための効率的なデータ圧縮と符号化方式を提供し、パフォーマンスを向上させます。 ファイルは基本的にはUTF8。ただASCIIで済むのにUTF8はちょっと贅沢だなと思ったりもする。 圧縮形式 ローカルで.gzに圧縮した後ステージに載せていたが圧縮していないファイルをステージに載せようとすると 自動的にgzip圧縮がかかる。特に圧縮形式にこだわりがないなら自動gzip化で良さそう。 公式には圧縮アルゴリズムが書いてあるが,要はzip,gzip,bz2。 ちなみに、ファイルのロードを指示する際、拡張子を省略した書き方ができるが、 同じファイル名で圧縮形式が違うファイルを同一階層に置いておくと、片方しか読まれない。 ファイルを更新したのにロード操作をしても全然反映されない、とハマる。 名前付きファイル 現物のファイルを上位概念化したオブジェクト。ファイルのメタデータをまとめるために存在する。 例えば現物のファイルをアップロードしようとすると、ローカルのパスとかアクセスのための権限など 様々な情報が必要となる。もしそのファイルを何度も使うとすると毎回同じ内容を書かないといけない。 必要なメタデータをまとめておいてそれを使うことで、手間を省略できる。 クラウドストレージの認証情報をどう持つべきか 例えばS3を外部ステージとして設定することができるが、ではS3の認証情報をどう持つべきか。 以下に3つの選択肢が書かれている。 Amazon S3へのセキュアアクセスの構成 https://docs.snowflake.com/ja/user-guide/data-load-s3-config.html [推奨]ストレージ統合オブジェクト [廃止]AWS IAM ロール [1回限り]AWS STSによる一時認証 ストレージ統合オブジェクト 通常、運用者がアクセスキーとシークレットを触れない場合は多いだろうから、 管理者にストレージ統合オブジェクトの作成を依頼する、という仕組みが良さそう。 AWS IAMロール IAMロールを払い出して利用するパターンは[廃止]となっていていずれ消える様子。 expireしない認証情報はよくない。 AWS STSによる一時認証 AWS STSによりIAMロールを有効期限付きで移譲し一時的にIAMロールアクセスできる。 IAMユーザのアクセスキー、シークレットと有効期限付きのトークンから構成される。 Snowflakeが外部ステージにアクセスする際に都度有効なトークンが必要だが、 1度限りのロードを行う用途なのであればこれも良さそう。 AWS STSについては以下。 https://docs.aws.amazon.com/ja_jp/IAM/latest/UserGuide/id_credentials_temp.html [ストレージ統合オブジェクト版] S3からSnowflakeにデータをロードしてみる CREATE INTEGRATIONロールが必要なため不可能なケースがあるが推奨されているのでやってみる。 gz圧縮済みのCSVを外部ステージ設定したS3にアップロード後,Snowflakeテーブルにロードする。 工程はざっくり以下の通り。 S3にバケットを作成 SnowflakeからS3にアクセス可能なカスタマ管理ポリシーを作成 カスタマ管理ポリシーをアタッチしたIAMロールを作成しIAMユーザと関係させる ファイルフォーマットを作成する ストレージ統合オブジェクトを作成する S3に外部ステージを作成する IAMロールにSnowflakeとの信頼関係を追加する 疎通確認 検証用のダミーテーブル・ダミーデータを作る 外部ステージからSnowflakeテーブルにロードする S3にバケットを作成 まず　ikuty-s3test　というバケット名のS3を用意してみた。 プライベートアクセスのみ可能で、ACLではなくIAMポリシーでアクセス制御をする設定。 SnowflakeからS3にアクセス可能なカスタマ管理ポリシーを作成 指定したカスタマ管理ポリシーでのみアクセス制御できるよう構成する。 ikuty-s3testにアクセスするカスタマ管理ポリシーを以下の通り作成する。 { \"Version\": \"2012-10-17\", \"Statement\": [ { \"Effect\": \"Allow\", \"Action\": [ \"s3:PutObject\", \"s3:GetObject\", \"s3:DeleteObjectVersion\", \"s3:DeleteObject\", \"s3:GetObjectVersion\" ], \"Resource\": \"arn:aws:s3:::ikuty-s3test/*\" }, { \"Effect\": \"Allow\", \"Action\": \"s3:ListBucket\", \"Resource\": \"arn:aws:s3:::ikuty-s3test\" }, { \"Effect\": \"Allow\", \"Action\": \"s3:ListAllMyBuckets\", \"Resource\": \"*\" } ] } カスタマ管理ポリシーをアタッチしたIAMロールを作成しIAMユーザと関係させる IAMロールを作成し、上のカスタマ管理ポリシーをアタッチする。 IAMユーザを作成し、IAMロールと関係させる。 該当ユーザのアクセスキー、シークレットを ~/.aws/credentialsに設定しておく。 s3:ListAllMyBucketsを許可しているため、ポリシーが作用していればaws s3 lsで一覧を取得可。 (aws configureは構成済みとし省略) $ aws s3 ls --profile s3test 2022-05-19 02:15:00 ikuty-s3test .. .. ファイルフォーマットオブジェクトを作成する gz圧縮済みのCSVをファイルフォーマットオブジェクトでラップする。 デリミタはパイプ(|)、1行目はヘッダ、空フィールドはNULL扱い、圧縮はgzip。 --- CSVファイルフォーマットオブジェクトを作成 CREATE OR REPLACE FILE FORMAT my_csv_format TYPE = CSV FIELD_DELIMITER = \'|\' SKIP_HEADER = 1 NULL_IF = (\'NULL\', \'null\') EMPTY_FIELD_AS_NULL = true COMPRESSION = gzip ; ストレージ統合オブジェクトを作成する S3ストレージ統合オブジェクトを作成する。CREATE INTEGRATIONロールが必要。 CREATE STORAGE INTEGRATION my_s3_integration TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = S3 ENABLED = TRUE STORAGE_AWS_ROLE_ARN = \'<>\' STORAGE_ALLOWED_LOCATIONS = (\'s3://ikuty-s3test\') ; S3に外部ステージを作成する 次にikuty-s3testバケットを外部ステージ化する。 ファイルフォーマットとストレージ統合オブジェクトがあれば指定するだけで良い。 create stage my_s3_stage storage_integration = my_s3_integration url = \'s3://ikuty-s3test\' file_format = my_csv_format ; IAMロールにSnowflakeとの信頼関係を追加する IAMロールにアタッチしたカスタマ管理ポリシーだけでは不足で、IAMロールにSnowflakeとの信頼関係を設定する必要がある。 外部ステージを作った時点で外部ステージにAWS_ROLEとAWS_EXTERNAL_IDが設定される。 この2つをIAMロールの信頼ポリシーに設定する。 Snowflake側でステージを確認する。show stageでステージ一覧を得られる。 desc stageで指定したステージの詳細情報を得られる。 その際、AWS_ROLEとAWS_EXTERNAL_IDの2つを記録しておく。 $ show stage created_on name database_name schema_name url has_credentials has_encryption_key owner comment region type cloud notification_channel storage_integration 2022-05-19 02:03:30.712 -0700 MY_S3_STAGE ikuty ikuty s3://ikuty-s3test N N ACCOUNTADMIN ap-northeast-1 EXTERNAL AWS MY_S3_INTEGRATION $ desc stage MY_S3_INTEGRATION parent_property property property_type property_value property_default STAGE_FILE_FORMAT FORMAT_NAME String \"my_csv_format\" STAGE_COPY_OPTIONS ON_ERROR String \"ABORT_STATEMENT\" \"ABORT_STATEMENT\" STAGE_COPY_OPTIONS SIZE_LIMIT Long STAGE_COPY_OPTIONS PURGE Boolean false false STAGE_COPY_OPTIONS RETURN_FAILED_ONLY Boolean false false STAGE_COPY_OPTIONS ENFORCE_LENGTH Boolean true true STAGE_COPY_OPTIONS TRUNCATECOLUMNS Boolean false false STAGE_COPY_OPTIONS FORCE Boolean false false STAGE_LOCATION URL String [\"s3://ikuty-s3test\"] STAGE_INTEGRATION STORAGE_INTEGRATION String MY_S3_INTEGRATION STAGE_CREDENTIALS AWS_ROLE String <> STAGE_CREDENTIALS AWS_EXTERNAL_ID String <> STAGE_CREDENTIALS SNOWFLAKE_IAM_USER String *** DIRECTORY ENABLE Boolean false false DIRECTORY AUTO_REFRESH Boolean false false IAMロールに設定する信頼ポリシーは以下。 { \"Version\": \"2012-10-17\", \"Statement\": [ { \"Sid\": \"\", \"Effect\": \"Allow\", \"Principal\": { \"AWS\": \"<>\" }, \"Action\": \"sts:AssumeRole\", \"Condition\": { \"StringEquals\": { \"sts:ExternalId\": \"<>\" } } } ] } 疎通確認 基本的には「S3にアクセスするためのカスタマ管理ポリシー」と「Snowflakeとの信頼ポリシー」の2つが正しいか。 ステージ内のファイルの一覧を取得するとこれらの疎通を確認できる。 外部名前付きステージの名称の前に「@」を付けることでSnowSQLからアクセスできる。 今回作成した my_s3_stage であれば @my_s3_stage という書き方となる。 list @my_s3_stage ; ... 検証用のダミーテーブル・ダミーデータを作る ロードする先のSnowflakeテーブルを作っていなかったので作る。 CREATE TABLEでもファイルフォーマットを指定できるので良い。 create or replace table my_test_table ( id integer, name string, age integer ) STAGE_FILE_FORMAT = \'my_csv_format\' ; さて、ローカルでスキーマに合うダミーデータを作って圧縮してS3に送る。 これだけ短いとgzip圧縮かけたら余計サイズが大きくなるw # dummy.csvというファイルをローカルに作成 $ cd ~/ && touch dummy.csv $ echo -e \"id|name|agen1|hogehoge|10n2|fugafuga|20n3|foofoo|30\" > dummy.csv $ cat dummy.csv id|name|age 1|hogehoge|10 2|fugafuga|20 3|foofoo|30 # gzip圧縮する $ gzip dummy.csv $ ls -la | grep dummy -rw-r--r-- 1 ikuty ikuty 75 5 02 18:14 dummy.csv.gz # Content-Typeをgzipに指定してS3にアップロード aws s3 cp dummy.csv.gz s3://ikuty-s3test/ --profile=s3test --content-encoding \"gzip\" --content-type \"application/x-gzip\" upload: ./dummy.csv.gz to s3://ikuty-s3test/dummy.csv.gz # 確認 $ aws s3 ls s3://ikuty-s3test/ --profile=s3test 2022-05-19 02:29:02 75 dummy.csv.gz my_s3_stage内をリストすると今上げたファイルがあることが確認できた。 list @my_s3_stage ; name size md5 last_modified s3://ikuty-s3test/dummy.csv.gz 75 *** Thu, 19 May 2022 09:29:02 GMT 外部ステージからSnowflakeテーブルにロードする これまでの設定が全て上手くいくとCOPY INTOで @my_s3_stage から my_test_tableへのロードが完了する。 COPY INTO my_test_table from @my_s3_stage file_format = my_csv_format ; file status rows_parsed rows_loaded error_limit errors_seen first_error first_error_line first_error_character first_error_column_name s3://ikuty-s3test/dummy.csv.gz LOADED 3 3 1 0 できた。 select * from my_test_table ; ID NAME AGE 1 hogehoge 10 2 fugafuga 20 3 foofoo 30 まとめ Snowflakeのデータロードについて公式ドキュメントに書いてあることをなぞってみた。 外部ステージ/内部ステージを介してテーブルにデータをロードできる。 各種クラウドストレージやフォーマットなど、いくつかのステップが抽象化されていて、 複数の組み合わせについて同じ方法で対応できる様子。 そのうちストレージ統合を使い外部ステージに設定したS3からデータをロードしてみた。

SnowPro Coreの頻出テーマだと感じたTime Travel。 資格取得時に固め打ちした記憶があるが、補強ついでにもう少し詳し目に公式を読んでみる。 古くなったり間違っていたりするかもしれないので、事の真偽については公式を参照のこと。 Time Travelの理解と使用 https://docs.snowflake.com/ja/user-guide/data-time-travel.html [arst_toc tag=\"h4\"] Time Travelとは 通常、データ削除後に削除したデータにアクセスするには削除前にデータのバックアップが必要。 バックアップしてリストアして、というのはある意味DB製品の基本的な動作仕様であって、 SnowflakeにもSnowflakeのフルマネージドなポリシーに基づいて仕組みが用意されている。 Snowflakeではデータが自動的・透過的にバックアップされ、 明示的にバックアップ・リストアせずに削除後に削除前のデータにアクセスできる。 何も気にしないでも裏で勝手にバックアップ・削除されるため大分手間が省略される。 当然ストレージコストを余分に消費するが保持期間を設定することでバランスを制御できる。 公式には以下の用途で使われる、と書いてある。 誤って削除したデータの復元 特定時点の復元 任意期間の使用量・操作の分析 データのライフサイクル 重要な観点として、データにはステートがあり、ライフサイクルが決まっている。 ステート 削除種別 用途 通常 - 現在のデータに対するクエリ、DDL、DML、など Time Travel 論理削除 更新・削除された過去のデータへのクエリ過去の特定の時点についてテーブル・スキーマ・DB全体のクローン削除されたテーブル・スキーマ・DBの復元 Fail-safe 物理削除 一定期間(Retantion Period)が過ぎるとデータはFail-safeに移動。操作不可。Snowflakeへ問い合わせて何とかなる可能性がある データの保持期間(Retentiono period) ユーザはデータの保持期間を変更できる。 保持期間は日単位で設定する。デフォルト値は1(24時間)。 ゼロを設定するとTime Travelを使用しない設定。 設定範囲はテーブル種別、Snowflakeのエディションによって異なる。 通常のテーブルについて、エディションごとの設定範囲は以下の通り。 (Temporaryテーブル,Transientテーブルは通常1日を超えて使わないはずなので以下では除外) エディション 0(Time Travelを使用しない) 1日 〜90日 Standard 可 可(デフォルト) 不可 Enterprise+ 可 可(デフォルト) 可 さらに、ACCOUNTADMINロールを持つユーザはユーザの設定範囲を限定できる。 デフォルト値は DATA_RETENTION_TIME_IN_DAYS、 最小値は MIN_DATA_RETENTION_TIME_IN_DAYS。 最小値設定はデフォルト値設定を上書きしない。 デフォルト値が最小値よりも小さい場合、いずれかの大きい方が適用される。 コスト Time Travelは論理削除のステートでありストレージコストがかかる。 データが変更された時点から1日ごとに課金。(ちょっと詳細不明...) テーブルを丸ごとDROPした場合には丸ごと保存されるがなるべく差分が保存される。 ETLなどに使う1日未満のデータはTransientテーブルに格納することになっている。 また、より短いセッション内で使うデータはTemporaryテーブルに格納することになっている。 そのような用途であれば長いTime Travelは不要だし、そもそもFail-safeも不要。 これらのテーブル種別については、Time Travel期間は最大1日となっていて、 さらに後続のステートであるFail-safeに遷移しない。 逆に言うと、Transient,Temporaryテーブルを使うことでTime TravelとFail-safeの 余分なコストを最大1日に抑えることができる。 ちなみにTemporaryテーブルについてはセッションを落としたときにテーブルが破棄されると、 Time Travelの保持期間も終了する。 行ったり来たりだが、Transient,TemporaryについてはFail-safeが無いので Time Travel終了後は完全にアクセス不能となる。 保持期間の変更 テーブルの保持期間を変更すると、現在のデータとTime Travelにある全てのデータに影響する。 変更 影響 保持期間の延長 現在Time Travelにあるデータの保持期間が長くなる。例えば保持期間=10を保持期間=20に変更した場合現在Time Travel3日目のデータの残り期間は7日から17日に伸びる。 保持期間の短縮 現在アクティブなデータには新しい保持期間が適用される。例えば保持期間=10を保持期間=5に変更した場合現在Time Travel 7日目のデータはFail-safeへ遷移。現在Time Travel3日目のデータの残り期間は7日から2日に変わる。 データライフサイクルの遷移はバックグラウンドで非同期に行われるため、 ALTERコマンドで保持期間を変更したとしてすぐに上記の更新が走るわけではない。 オブジェクト階層に対する再帰的な影響 オブジェクトはCompositeパターンに基づき所有関係を持っているが、 階層上、上位のオブジェクトに対する保持期間の変更は再帰的に下位のオブジェクトに反映される。 例えばDBに対する変更はスキーマに対して反映されるなど。 ワイルドカードを使った破壊的な変更は意図しない変更をもたらすため、慎重にやったほうが良い。 最上位のアカウントに対する保持期間の変更は推奨しないという記述がある。 上位オブジェクトのドロップと下位オブジェクトの保持期間 上位オブジェクトをドロップすることで自動的に下位オブジェクトがドロップされる。 その際、下位オブジェクトの保持期間は強制的にドロップした上位オブジェクトの保持期間が設定される。 例えば保持期間10日のデータベースをドロップしたとして、 保持期間15日のスキーマ、テーブルの保持期間は強制的に10日となる。 下位オブジェクトを先にドロップすれば、下位オブジェクトの保持期間が上書きされることはない。 Time Travel中のデータに対するクエリ Time Travel中のデータにアクセスするために特別なストレージにアクセスする、という感じではなく、 SQLの拡張構文が用意され、自然にアクティブなデータとTime Travel中のデータの触り分けができる。 at句とbefore句が用意されている。 例えば公式に書かれている以下のような感じ。 ---at句によりtimestampで指定された時点の履歴データを取得 select * from my_table at (timestamp => \'Fri, 01 May 2015 16:20:00 -0700\'::timestamp_tz) ; ---5分前の時点で履歴データを取得 select * from my_table at (offset => -60*5) ; ---指定されたステートメントによる変更を含まないで、それ以前の履歴データを取得 select * from my_table before (statement => \'8e5d0ca9-005e-44e6-b858-a8f5b37c5726\') ; at句、before句が保持期間外を指す場合、クエリは失敗する。 Time Travel中のオブジェクトのクローン SQLの拡張構文によってTime Travel中のDBやスキーマなどのオブジェクトをクローンできる。 CREATEと共にCLONEを使う。例えば公式に書かれている以下のような感じ。 ---指定されたタイムスタンプで表される日付と時刻のテーブルのクローンを作成 ---my_tableというテーブルをrestored_tableというテーブルにクローン create table restored_table clone my_table at (timestamp => \'Sat, 09 May 2015 01:01:00 +0300\'::timestamp_tz) ; ---現在時刻の1時間前に存在していたスキーマと配下の全てのオブジェクトをクローン create schema restored_schema clone my_schema at (offset => -3600) ; ---指定されたステートメントの完了前に存在していたデータベースと配下の全てのオブジェクトを復元 create database 　　　　　　　restored_db clone 　　　　　　　my_db before 　　　　　　(statement => \'8e5d0ca9-005e-44e6-b858-a8f5b37c5726\'); CLONEも、指定したオブジェクトの保持期間を超えてTime Travel時間を指定するとエラーとなる。 オブジェクトのドロップと復元 オブジェクトの履歴はオブジェクトに紐づく、という書き方が正しいかは不明だが、 オブジェクト自体をドロップした場合の履歴は、オブジェクト配下の変更・削除の履歴とは少し異なる。 Time Travelは通常差分を履歴として残すが、オブジェクトのドロップによって完全な履歴が残る、 と公式に記述がある。 DROPによってオブジェクトをドロップした後、UNDROPによってドロップしたオブジェクトを復元する。 DROPした後、CREATEしたとしてもUNDROP扱いにはならないし、DROPした古いオブジェクトは残る。 永遠に完全な履歴のhistoryが積み重なっていく、historyのある時点のオブジェクトを対象に UNDROPする、という扱いとなる。 UNDROPにより復元するテーブルと同名称のテーブルが存在する場合エラーとなる。 --- mytableという名前のテーブルをdrop drop table mytable ; --- mytableという名前のテーブルをundrop undrop table mytable ; オブジェクトのhistoryについてもSQLの拡張構文で確認できる。 showとhistoryを合わせて使用する。公式は以下の通り。 オブジェクトの保持期間がすぎてTime Travelから消えるとshow historyで表示されなくなる。 --- mytestdb.myschemaスキーマ配下にあるloadから始まるテーブル名の履歴を表示 show tables history like \'load%\' in mytestdb.myschema ; --- mytestdbデータベース配下のスキーマの履歴を表示 show schemas history in mytestdb ; まとめ 自力でバックアップ・リストア操作なしで、Snowflakeが勝手にオブジェクトをバックアップしてくれる。 SQLの拡張構文を通してアクティブなデータと似た形でオブジェクトをリストアできる。 Time Travelに保持される期間はカスタマイズできる。 みたいなことについて、公式ドキュメントを読んで確認してみた。

以前SnowPro core Certificationsに合格したもののなかなか使う機会がなくて、 資格試験対策レベルの薄い知識の維持すら怪しくなってきた。 Materialized Viewについて良くわからず使っていたので、 「やりなおし」のついでに知識をアップデートしていこうと思う記事第2弾。 個人の学び以上でも以下でもなく、内容に誤りがあるかもしれないので、 ことの真偽は公式ドキュメントを参照のこと。 [arst_toc tag=\"h4\"] Materialized Viewとは 何らかの集計を行おうとすると、多くの場合、中間の集計を合わせて最終的な集計結果を得る。 中間の集計を行う際にJOINにより結合を行う場合、それが高コストだと最終的にコスト高になる。 途中の集計結果をどこかに保存できれば、毎回高コストな集計を無駄に実行しなくて良くなる。 そんな時に使うのが Materialized View。「マテビュー」とか省略される。 e-Wordsによると以下の通り。 マテリアライズドビューとは、リレーショナルデータベースで作成されたビューにある程度の永続性を持たせ、参照する度に再検索しなくていいようにしたもの。特定のビューを頻繁に参照する場合に性能が向上する。 SnowflakeにおけるMaterialized Viewについては以下。 マテリアライズドビューの使用 Materialized Viewは透過的にリフレッシュされる 重要な点として、SnowflakeにおけるMaterialized Viewは自動的・透過的にリフレッシュされる。 オリジンとなるデータが変わった場合、アプリケーション側はノータッチでSnowflakeが自動更新する。 アプリケーション側でオリジンの鮮度を意識しないで良いというのはかなり楽。 透過的な自動リフレッシュの機構について、より詳細な内部情報として以下の通り。 ギリギリまでDMLを反映しないでくれる機構がついているっぽい。 クエリの前に実行されたDMLがクエリに影響する場合、クエリ時にDML反映 クエリの前に実行されたDMLがクエリに影響しない場合、スルーで応答 アプリケーション側がノータッチで自動的・透過的にリフレッシュがかかるが、 そのリフレッシュでクレジットが消費される。 普通のViewを使うべきか、Materialized Viewを使うべきか どのようなクエリであればその結果をMaterialized Viewに乗せるべきか。 クエリに時間がかかるなら使うべき。ただしオリジンとなるデータが変われば、 Materialized Viewのリフレッシュが必要となるから、オリジンが頻繁に変わるケースは対象外。 時間がかかる処理として公式には以下の例があがっている。 半構造化データに対するクエリ S3上のファイルなど遅い外部テーブルに対するクエリ 普通のViewにすべきか、Materialized Viewにすべきかの判断基準は以下。 普通のView Materialized View ビューからのクエリ結果(※) 頻繁に更新される ほとんど更新されない クエリ結果の使用 頻繁に使用される あまり使用されない リフレッシュにかかるコスト 処理時間大、ストレージ大 処理時間小、ストレージ小 ※ベースとなるテーブルが「完全に更新されない」まで限定しなくても、「クエリ結果の範囲に限定して更新されない」でOK。 Materialized Viewのパフォーマンス Snowflakeのパフォーマンスを上げる機構として「クエリ結果キャッシュ」がある。 要は同じ条件のクエリに対して、キャッシュがあればクエリを実行せずにキャッシュを返す、というもの。 実際に運用してみると「クエリ結果キャッシュ」を使わせるためには複数の条件があり、 なかなか仕様通りキャッシュを使い続けるのは難しいが、キャッシュが効けば速くなる。 比較することに意味があるのかちょっと怪しいが、 純粋にパフォーマンスを比較すると以下となるらしい。 普通のTable = 普通のView < Materialized View < クエリ結果キャッシュ クエリオプティマイザとMaterialized View アプリケーションがexplicitにMaterialized Viewを指定してやらなくても、 ベーステーブルに対するクエリ結果の行と列がMaterialized Viewに全て含まれている場合、 クエリオプティマイザが自動的にクエリを置換する。 さらに、もしベーステーブルがフィールドによってクラスタ化されていて、 プルーニングが良い結果をもたらすと判断されれば、Materialized Viewではなく ベーステーブルに対するプルーニングが使用される。 BIのようにアプリケーション全域で検索キーが分散し「どう呼ばれるかわからない」ケースでは、 なかなかカスタムでクラスタキーを設計してより良いプルーニング結果を得ることが難しいが、 分析タスクのように「ある程度呼ばれ方が決まっている」ケースならクラスタキーを偏らせる メリットはありそうで、そんなときにMaterialized Viewとベーステーブルのプルーニング、 どちらが良いか、なんてことを考えることもあるんだろう(本当か?) ただ公式には、ベストプラクティスとしてMaterialized Viewを作る場合、 ベーステーブルのプルーニングを解除しMaterialized Viewを優先すべきと記述がある。 マテリアライズドビューとそのベーステーブルをクラスタリングするためのベストプラクティス サブクエリについて暗黙的にMaterialized Viewが使われる、というケースもある。 この場合、クエリプロファイルにシレッとMaterialized Viewが鎮座する、ということになる。 Materialized Viewのメンテナンスコスト コンピューティング、ストレージともにクレジットを使用する。 Materialized Viewに対するクエリ結果が保存され、そのストレージに対してコストがかかる。 頻繁に使われるクエリなのであれば、相対的にストレージのコストが低くなると考えられるが、 もしロクに使われないクエリなのであれば、そのストレージコストが本当に低いのか考えるところ。 透過的なリフレッシュのためにコンピューティングのコストがかかる。 Materialized Viewの上手い使い方 ベーステーブルの多くデータを取得してしまうと、無駄にリソースを使ってしまう。 なるべく行・列が少なくなるようなデータセットをMaterialized Viewに格納すべき。 公式には、ベーステーブルにログがあるとして異常値のみをMaterialized Viewに置く、 という例が示されている。 要はアプリケーションの設計段階で、何をMaterialized Viewとすべきかを検討すべき。 ベーステーブルを頻繁に更新してしまうと、都度自動リフレッシュが実行されてしまう。 そのため、ベーステーブルの更新頻度を下げる必要がある。 SnowflakeはDMLをバッチ処理するように推奨している。 バッチ処理できるようなデータの並びとなるようにしておく必要がある。 DDL,DML 実際にMaterialized Viewを作ってみる。 --- ベーステーブルの作成 create table mv_base (id integer, value integer) ; --- Materialized Viewの作成 create or replace materialized view mv1 as select id, value from mv_base ; ---ベーステーブルにInsert insert into mv_base (id, value) values (1, 100) ; --- Materialized Viewからデータ取得 select id, value from mv1 ; id value --------------- 1 100 ---ベーステーブルにInsert insert into mv_base (id, value) values ( 2,200) ; --- Materialized Viewからデータ取得 select id, value from mv1 ; id value --------------- 1 100 2. 200 Materialized Viewの自動更新を停止することができる。 停止中にデータを取得しようとするとエラーが発生し取得できない。 停止と再開の組みは以下。 alter materialized view mv1 suspend ; select id, value from mv1 ; SQLコンパイルエラー： ビュー「MV1」の展開中の失敗： SQLコンパイルエラー：マテリアライズドビュー MV1 は無効です。 alter materialized view mv1 resume ; select id, value from mv1 ; id value --------------- 1 100 2. 200 まとめ 更新頻度が低く利用頻度が高い中間クエリについて Materialized View に格納すると効果的。 Materialized Viewは自動的・透過的にリフレッシュがかかる。 自動リフレッシュに際してコンピューティングコストがかかるため更新頻度は低い方が良い。 ベーステーブルに対するDMLをバッチ処理とすると自動リフレッシュの頻度を下げられる。 クエリ結果キャッシュよりは遅いが普通のテーブルよりは速い。

以前SnowPro core Certificationsに合格したもののなかなか使う機会がなくて、 資格試験対策レベルの薄い知識の維持すら怪しくなってきた。 資格を取得してからかなり経過したこともあり、控えめにいって知識が陳腐化してしまった。 せっかくなので「やりなおし」のついでに知識をアップデートしていこうと思う。 セキュリティ周りについて正直よくわからず操作している感があるため、 今一度ドキュメントを見直してみる。 個人の学び以上でも以下でもなく、内容に誤りがあるかもしれないので、 ことの真偽は公式ドキュメントを参照のこと。 Snowflakeのアクセス制御 https://docs.snowflake.com/ja/user-guide/security-access-control.html [arst_toc tag=\"h4\"] アカウントの管理 例えば以下のように、オブジェクトへのアクセスを制御する。 誰がどのオブジェクトにアクセスできるのか そのオブジェクトに対してどの操作を実行できるのか 誰がアクセス制御ポリシーを作成または変更できるか アクセス制御フレームワーク 一言で「アクセス権」と言ったところで、確かに世の中には様々な意味をもって使われている。 以下、DAC,MAC,RBACの一般的なまとめ。 SnwoflakeはDACとRBACの両方に基づいてアクセス制御をおこなう。 名称 誰が制御するか 説明 任意アクセス制御DAC:Discretionary Access Control 所有者 オブジェクトには所有者がいて所有者が他者に対してオブジェクトへのアクセスを許可する例えばLinuxのファイルパーミッション。POSIXのACL。実質的に作成したリソースに対するアクセス制御の権限を与えられている。ユーザの自由度が高く管理者に手間をかけない。ルールの統一が難しく、セキュリティ面で効果を期待できない。 強制アクセス制御MAC：Mandatory Access Control 管理者 管理者がアクセスする側(サブジェクト)とされる側(オブジェクト)の両方に対してセキュリティレベルを設定する。例えばレベル1のサブジェクトはレベル 3のオブジェクトにアクセスできない等。所有者であろうとも管理者が定めた規則によりアクセスできないなどの特徴。 ロールベースアクセス制御RBAC:Role-based access control 管理者 セキュリティ概念としてはDACとMACの中間。DACと同様にサブジェクトとオブジェクトに対するアクセス制御を行うが、サブジェクトに対して「ロール」を設定し「ロール」の範囲で自由にオブジェクトにアクセスできる。つまり1つ1つのサブジェクトに個別にアクセス制御をかけるだけではなく複数のサブジェクトにアクセス制御をかける。「組織」、「部署」に親和性が高い。それぞれの部署向けにロールを作成し、部署に属したサブジェクトがロールの範囲でオブジェクトにアクセスできる。 DACとRBACの両方、とはいったいどういうことか。 まずRBACの側面から説明できるアクセス制御は以下の通り。 オブジェクトにアクセスする能力を「権限」と呼ぶ。「権限」を「ロール」に付与する。 「ロール」を他の「ロール」に割り当てたり、「ユーザ」に割り当てる。 こうして「ユーザ」は「ロール」の範囲でオブジェクトにアクセスできる。 ここまでで、「ユーザ」が「ロール」を介してオブジェクトにアクセスできる構造となる。 これだけだとRBACの説明の通り、管理者によってのみアクセス制御がおこなわれ「ユーザ」はおこなわない。 さらに、オブジェクトには「所有者」がいる。 オブジェクトを作成すると、「所有」という名前の「権限」ができる。 「所有者」には「所有権限」が割り当たった「ロール」が紐づく。 「所有権限」がある「ロール」を持っていると、オブジェクトに対する権限をロールにGRANT,REVOKEできる。 「所有権限」がある「ロール」を他に移すこともできる。 通常「所有者」にはオブジェクトに対する全ての権限が与えられる。 つまり、「所有者」であれば、DACのように「ユーザ」がアクセス制御を書き換えることができる。 DACをRBACで実装している、といった感じ。 基本的にはRBACだが、所有者に限りロールを変更できる自由さがある。 オブジェクトの階層構造と所有 SnowflakeにおいてオブジェクトはOOPでいうCompositeパターンに従う。 上位オブジェクトは下位オブジェクトのコンテナとなり、全体として階層構造を形成する。 例えば「組織」は「アカウント」を所有できるし、「アカウント」は「ユーザ」を所有できる。 オブジェクトには、オブジェクトに対してSQLを実行する権限があったりする。 例えばvwhにはSQLを実行する権限があり、もしその権限を付与されたロールをもっていれば、vwhでSQLを実行できる。 またテーブルにデータを追加する権限が付与されたロールをもっていれば、テーブルにデータを追加できる。 システム定義ロール RBACとDACを両立する上でその境界にある概念の解釈が微妙なものがあったりする。 DACにより所有者は所有するオブジェクトに関して生殺与奪の権を持つことになっているが、 いくつかのロールについてはお上が決めたルールに逆らえない。 このようなロールを「システム定義ロール」と呼び、所有者が放棄できないし、ロールから権限を無くせない。 ロール名 説明 ORGADMIN 組織レベルで運用を管理するためのロール。組織内にアカウントを作成する、組織内の全アカウント表示、組織全体の使用状況などの表示。 ACCOUNTADMIN SYSADMINとSECURITYADMINの2つをラップするロール。システムにおける最上位のロール。アカウント内の限られた数のユーザにのみ付与。 USERADMIN ユーザ、ロールの管理ができる。CREATE USER、CREATE ROLEの権限が付与されている。アカウントにユーザ、ロールを作成できる。 SECURITYADMIN USERADMINロールがSECURITYADMINロールに付与されている。USERADMINに加え、オブジェクトへのアクセス権を付与する権利が与えられている。 SYSADMIN アカウントでウェアハウス、データベースを作成する権限が与えられている。システム管理者に付与する。間違ってACCOUNTADMINロールをシステム管理者に付与しないこと。 PUBLIC 全てのユーザー、ロールにデフォルトで割り当てられるロール。PUBLICロールはオブジェクトを所有できる。全てのユーザに割り当てられているため、全てのユーザがPUBLICロールが所有するオブジェクトにアクセスできる。明示的なアクセス制御が不要で誰でも触れてよいオブジェクトをPUBLICに所有させる。 カスタムロール USERADMINロールを付与されているユーザによって、 オブジェクトを所有するロールを新たに作成できる。 ただし、RBACによってDACを実現している都合上、Snowflakeの掟に従ってロールを作るべき。 RBACベースのDACにおいて、「システム管理者」だとか「ユーザのレベル」はあくまでも「上位のロール」を付与されているか、でしか決まらない。 システム内のオブジェクトの所有者として機能するロールを作成する場合、 RBACベースのDACに配慮しないと、 「システム管理者」ですら触れない謎のオブジェクトを作り出してしまう。 「システム管理者」は、アカウント内のお全てのオブジェクトを表示、管理できるようにしたい。 もしSYSADMINロールにカスタムロールが割り当てられていないなら、 システム管理者はそのカスタムロールが所有するオブジェクトを表示、管理できない。 SECURITYADMINロールのみが表示し管理できる、という謎の状況になってしまう。 だから、新たに作成するロールは必ずSYSADMINロールに付与する必要がある。 ロールの所有関係は階層構造を持てるから、階層を上に辿ると必ずSYSADMINがある必要がある。 推奨されるロールの階層構造 Snowflakeが推奨するロールの階層構造は以下のような感じ。 矢印は「付与関係」。矢印の先のロールに、矢印の元のロールが付与されている。 ACCOUNTADMINには全てのロールが間接的に付与された状態とすること。 カスタムロールはSYSADMINに間接的に付与された状態とすること。 カスタムロールをSYSADMINを超えて直接ACCOUNTADMINに付与しないこと。 カスタムロールをSECURITYADMIN、USERADMINに付与しないこと。 最初にハマるダメケースとベストプラクティス 最上位のロールであるACCOUNTADMINを割り当てるユーザは組織内で限定するべき。 逆に言うと、もし1人しかいないACCOUNTADMINロールを持つユーザがDBを作ってしまったならば、 他のACCOUNTADMINロールを持たないユーザがそのDBを表示・管理することはできない。 ベストプラクティスは、オブジェクトの所有者となるロールをSYSADMIN配下にぶら下げること。 SYSADMINロールが付与されたユーザであれば、オブジェクトの表示・管理ができる状態とすること。 テクニックとしてオブジェクトの所有権とオブジェクトに対する権限を分ける手法があり、 もし所有者となるロールに全ての権限を付与すれば、そのロールさえユーザに割り当てれば、 ユーザはオブジェクトに対する表示・管理ができるようになる。 一方、所有者となるロールに異なるロールを割り当て、所有者となるロールには権限A、 下位のロールには権限Bを与える、という構成とすることもできる。 その際、所有者となるロールには権限A,権限Bが付与された状態となる。 SYSADMINの下に複数の管理ロールをぶら下げ、 それら全てのロールに共通して権限を付与したい、という場合には、 それぞれの管理ロールに対して、その共通の権限を付与したロールを割り当てれば良い。 この状態にするためにロールを使い分ける。 USERADMINロールを付与されたユーザがユーザとそのユーザ用のロールを作成する SYSADMINロールを付与されたユーザがオブジェクトを作成する SECURITYADMINロールを付与されたユーザが新たなユーザのロールに所有権を移動する まとめ SnowflakeのセキュリティはRBACベースのDAC。 基本的にはRBACなので管理者がロールを作って割り当てる。 ただDACを実現するためにロールがオブジェクトを所有するという概念が導入されていて、 RBACとDACの境界に解釈が難しい部分がある。Snowflakeが定めるベストプラクティスに従うと良い。 ACCOUNTADMINでDBを作りまくって他人から見えない問題をシュッと解決できる。