カテゴリー: Snowflake

以前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を作りまくって他人から見えない問題をシュッと解決できる。