「日々の学び」一覧

Moden Data Stackの重要な技術として有名なdbtを理解したい。 と言いつつも使う機会がなかなか無いな、という点と、認定試験があるようなので、 まずは認定試験に合格することを目的に、知識を獲得していきたい。 SnowPro Certificationsと同様に参考書も問題集も無いが、 公式に学習要領のようなものが無茶苦茶丁寧に書かれている。 もちろん試験に向けた資料なので偏りだったり不足はあるのだろうけれど、 何も知らない素人がステップバイステップで物事を理解していくには大分心強い。 dbt Analytics Engineering Certification Exam Study Guide この記事では、この学習要領を上から順に読んでいく。 後続の記事で(ブログドリブンで)頭に入れた知識をアウトプットしていく。 [arst_toc tag=\"h4\"] トピック一覧 何を理解したら 「dbtのことを理解した」 と言えるのか。 dbtが備えている機能とその詳細が試験要項に一覧化されている。 初心者には本当に助かる。これらが頭の中で整理されて当たり前になったときが次のステップ。 何も知らない状況では分からない言葉や概念が多い。 振り返らず5分程度で訳を付けて終了。何となく全体像が見えてくる。 Topic 1: Developing dbt models Identifying and verifying any raw object dependencies Understanding core dbt materializations Conceptualizing modularity and how to incorporate DRY principles Converting business logic into performant SQL queries Using commands such as run, test, docs and seed Creating a logical flow of models and building clean DAGs Defining configurations in dbt_project.yml Configuring sources in dbt Using dbt Packages dbtモデルの実装方法と理論について。 dbtは物理的に存在するDBオブジェクトを抽象化するフレームワークであって論理と物理のマップがある。 抽象的な構造から見た具体化方式について理解する。 dbtを構成する概念の理解とDRY(Don\'t Repeat Yourself)法則をどう実現するか。 ビジネスロジックを高性能なクエリに変換する仕組みを理解する。 run,test,docs,seedなどのコマンドの使い方を理解する。 モデルのフローとDAGs、dbt_project.ymlの定義方法、sourceの構成方法を理解する。 Topic 2: Debugging data modeling errors Understanding logged error messages Troubleshooting using compiled code Troubleshooting .yml compilation errors Distinguishing between a pure SQL and a dbt issue that presents itself as a SQL issue Developing and implementing a fix and testing it prior to merging dbtモデルのデバッグ方法について。 ログとして記録されたエラーメッセージの解釈方法を理解する。 dbtによりコンパイル/生成されたコードを使ったトラブルシュート方法を理解する。 .ymlコンパイルエラーに対するトラブルシュート方法を理解する。 SQL自身の問題なのかdbtに纏わる問題なのかを区別する方法を理解する。 (git branchに?)mergeする前に修正/テストする方法を理解する。 Topic 3: Monitoring data pipelines Understanding and testing the warehouse-level implications of a model run failing at different points in the DAG Understanding the general landscape of tooling 作ったパイプラインの監視。 DAG のさまざまなポイントでモデル実行が失敗した場合のウェアハウスレベルの影響の理解+テスト方法。 (ウェアハウスレベルってどこのレベル??) Topic 4: Implementing dbt tests Using generic, singular and custom tests on a wide variety of models and sources Understanding assumptions specific to the datasets being generated in models and to the raw data in the warehouse Implementing various testing steps in the workflow Ensuring data is being piped into the warehouse and validating accuracy against baselines dbtテストの実装。 様々な種類のモデル・ソースに対する generic, singular, customテストの使用方法。 モデルで生成されるデータセットとウェアハウス内の生データに特有の仮定を理解する。(意味不..) ワークフロー内にテストステップを実装する方法。 データがウェアハウスにパイプされていることを確認し、ベースラインに対する精度を検証する。 Topic 5: Deploying dbt jobs Understanding the differences between deployment and development environments Configuring development and deployment environments Configuring the appropriate tasks, settings and triggers for the job Understanding how a dbt job utilizes an environment in order to build database objects and artifacts Using dbt commands to execute specific models デプロイ環境と開発環境の違いを理解する。 コンフィグによってデプロイ環境と開発環境を作る。 ジョブの適切なタスク、設定、トリガーの構成。 dbtジョブがDBオブジェクト・生成物をビルドするために環境をどのように使うかを理解する。 特定のdbtモデルを実行するためのdbtコマンドの使用方法。 Topic 6: Creating and Maintaining dbt documentation Updating dbt docs Implementing source, table, and column descriptions in .yml files Using dbt commands to generate a documentation site Using macros to show model and data lineage on the DAG dbt documentationの作成・維持。 dbt docsの更新、.ymlファイル内に source,table,列定義を書く方法。 documentationサイトを生成するためのdbtコマンドの使用方法。 DAGにおけるモデルとリネージを表示するマクロの使用方法。 Topic 7: Promoting code through version control Understanding concepts and working with Git branches and functionalities Creating clean commits and pull requests Merging code to the main branch gitとのコラボw。版管理。 版管理の概念。Git branchとの連携とその機能。 cleanコミットとプルリク。mainブランチへのマージ。 Topic 8: Establishing environments in data warehouse for dbt Understanding environment’s connections Understanding the differences between production data, development data, and raw data データウェアハウスの各環境とdbtの接続を確立する。 環境との接続確立方法を理解する。本番環境データ、開発環境データ、生データの違いを理解する。 学び方 素人が脱初心者するためのステップ。なんだかやたら丁寧。 各ステップで参照すべきリソースがまとまっている。順番を無視する理由は特にないので最初から見ていく。 Checkpoint 0 - 前提条件 SQLの理解が必要。結合、集計、CTEsの書き方、ウィンドウ関数に詳しくないといけない。 Gitの理解が必要。ブランチ戦略や基本的なgitコマンド、プルリクの方法など。 Checkpoint 1 - Build a Foundation やたら丁寧だな... オンライン動画。 dbt Fundamentals 読み物 dbt viewpoint ドキュメント Source properties Node selection syntax dbt_project.yml General resource properties やってみる Creating a dbt project from scratch to deployment Debugging errors コマンド達 dbt compile dbt run dbt source freshness dbt test dbt docs generate dbt build dbt run-operation Checkpoint 2 - Modularity and Refactoring オンライン動画 Refactoring SQL for Modularity Readings How we structure our dbt projects Your Essential dbt Project Checklist ドキュメント Refactoring legacy SQL to dbt やってみる - Refactoring SQL for performance and clarity Checkpoint 3 - Doing More with dbt オンライン動画 Jinja, Macros, and Packages Advanced Materializations Analyses and Seeds ドキュメント Exposures Env_var Target Schema Database やってみる - Utilizing packages and macros in a dbt project - Implementing all core materializations into a dbt project - Implementing seeds コマンド達 - dbt snapshot - dbt seed Checkpoint 4 - Deployment and Testing オンライン動画 Advanced Testing Advanced Deployment 読み物 The exact grant statements we use in a dbt project The exact GitHub pull request template we use at dbt Labs How to review an analytics pull request How we configure Snowflake ドキュメント Tags Hooks & Operations Custom Schema Threads やってみる - Defining environments in your data platform - Defining environments in dbt - Promoting code through git including use of multiple branches, pull requests - Troubleshooting errors in production runs - Defining dbt jobs for optimal performance まとめ 本来探し回らないと見つからないドキュメントが集まっていて良さそう。 このレベルでまとめるだけで何となく全体像が見えてきたような。 後続の記事で(ブログドリブンで)インプットした内容をアウトプットしていく。

ドキュメントを読んで使ったことが無い機能に詳しくなるシリーズ。 今回は検索最適化サービス(Search Optimization Service)。 他のドキュメントと比較して英語・日本語ともに文体が異なる気がする。 日本語訳が変なのではなく、元の英文の文体が違う。 まず、\"クラスタリング\"とか\"Materialized View\"が構造の名称である一方で、 \"検索最適化\"が目的の名称となっている点に違和感を感じる。 似て非なる索引(index)とは違うのだよ、という何かなのだろうか... [arst_toc tag=\"h4\"] 検索最適化サービスの概要 検索最適化サービスは、1つまたは少数の異なる行のみを返す「ポイントルックアップクエリ」。むむ?。 要は、巨大なテーブルに対するSELECT文の結果が数行となるようなケースを高速化する。 例えば、あるテーブルのageというカラムに対する等価条件を設定したクエリを速くしたい場合、 あらかじめ高速化用のデータ構造を構築し、ageに対する等価条件を使ったクエリを高速化する。 等価条件検索、範囲条件検索、部分文字列検索に加え、 プレビュー機能として、正規表現検索、半構造化型値検索、地理空間関数検索 が用意される。 他の高速化技術との比較 高速化機能として、他にクラスタリング、Materialized Viewがあるが、それぞれ機序が異なる。 クラスタリングは、マイクロパーティションの分散と枝刈りを利用した方法であって、 ナチュラルキーよりも高いプルーニング効率が得られるケースに適用する。 Materialized Viewは、同一テーブルに対して複数のクラスタリングキーを透過的に適用する 用途で用いられる。もちろん直アクセスでは遅い場所の事実上のキャッシュとしても使う。 検索最適化サービスは、ある意味、RDBMSの索引チックな使い方ができる。 良く使うカラムと検索述語を「アクセスキー」として登録し、高速検索用の索引を構築する。 検索最適化サービスの構成に必要な権限 以下の権限が必要。 テーブルの所有権 テーブルを含むスキーマに対する ADD SEARCH OPTIMIZATION 権限 検索最適化サービスを適用したテーブルにアクセスするには普通にSELECTできれば良い。 検索最適化サービスで未サポートのもの 以下に対して検索最適化サービスを適用することはできない。 (逆に以下以外なら適用できる。) 外部テーブル マテリアライズドビュー COLLATE 句 で定義された列 列の連結 分析表現 テーブル列にキャスト（文字列にキャストされる固定小数点数を除く） Materialized Viewは外部テーブルアクセスを高速化できるが、 検索最適化は外部テーブル、Materialized Viewを対象に出来ない。 何かの事情でCOLLATE句を指定した場合、その列は使用できない。 述語を指定する際に、定数側の明示的キャストはサポートされるが、 列側をキャストすると効かなくなる。例えば以下のような違いがある。 ただし、NUMBERからVARCHARへのキャストだけは機能する。 -- 機能する SELECT hoge FROM table as t1 WHERE timestamp1 = \'2020-01-01\'::date; -- 機能する SELECT hoge FROM table as t1 WHERE cast(numeric_column as varchar) = \'2\' -- 機能しない SELECT hoge FROM table as t1 WHERE to_date(varchar_column) = \'2020-01-01\'; (分析表現って何!?) 検索最適化サービスの構成方法 ALTER TABLE と ADD SEARCH OPTIMIZATION を使用する。 テーブル全体(つまり全カラム)に対して適用できるか、カラムを指定することもできる。 カラムを指定する場合、ON句に最適化したい検索述語等を指定する。 例えば以下であればc1,c2列を使用した等価述語が最適化される。 c1列、c2列に対して等価述語(=)を使ったSQLが高速化される。 -- t1テーブルの c1,c2 の等価述語に対して検索最適化を適用 ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1,c2); -- クエリ SELECT hoge FROM t1 where c1 = 100 and c2 = 200; 検索最適化サービスが適用済みか確認する方法 DESCRIBE により該当テーブルへの検索最適化サービスの適用状況を確認できる。 定義した「検索アクセスパス」の詳細と、有効無効が表示される。 DESCRIBE SEARCH OPTIMIZATION ON t1; +---------------+----------+--------+------------------+--------+ | expression_id | method | target | target_data_type | active | +---------------+----------+--------+------------------+--------+ | 1 | EQUALITY | C1 | NUMBER(38,0) | true | +---------------+----------+--------+------------------+--------+ テーブル全体に対して検索最適化サービスを適用していた場合、 列の追加により、追加した列に対して自動的に検索最適化サービスが適用される。 逆に列の削除により、削除した列が検索最適化サービスから除外される。 列のデフォルト値を無効にすると、検索最適化サービスが「無効」になる。 一度「無効」になってしまったら、SEARCH OPTIMIZATION を一度 DROP し、 再度 ADD しなければならない。 検索最適化サービスのコスト 以下のコストがかかる。 ストレージリソース コンピューティングリソース ストレージリソース 検索アクセスパスのためのデータ構造を構築するために使われる。 通常、元テーブルの1/4ぐらいのサイズになるらしい。 ただし、テーブル内の個別の値の数が多ければ多いほどストレージを消費する。 例えば、全カラムを対象としている場合、全カラムの値が全て異なるという状況は最悪で、 元テーブルと同じだけのストレージを消費してしまう。 コンピューティングリソース 検索最適化を維持するためにコンピューティングリソースが使われる。 テーブル内で大量のデータが変更される場合、リソースの消費量が激しくなる。 追加・変更に比例して大きくなる。削除は若干使用する。 ざっくり以下に比例してコストが増加する。 この機能が有効になっているテーブルの数、およびそれらのテーブル内の個別の値の数。 これらのテーブルで変更されるデータの量 SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS 関数によりコストを見積もれる。 -- テーブルに検索最適化を追加する場合の推定コスト select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITHOUT_SEARCH_OPT\'); +---------------------------------------------------------------------------+ | SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITHOUT_SEARCH_OPT\') | |---------------------------------------------------------------------------| | { | | \"tableName\" : \"TABLE_WITHOUT_SEARCH_OPT\", | | \"searchOptimizationEnabled\" : false, | | \"costPositions\" : [ { | | \"name\" : \"BuildCosts\", | | \"costs\" : { | | \"value\" : 11.279, | | \"unit\" : \"Credits\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"estimated via sampling\" | | }, { | | \"name\" : \"StorageCosts\", | | \"costs\" : { | | \"value\" : 0.070493, | | \"unit\" : \"TB\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"estimated via sampling\" | | }, { | | \"name\" : \"MaintenanceCosts\", | | \"costs\" : { | | \"value\" : 30.296, | | \"unit\" : \"Credits\", | | \"perTimeUnit\" : \"MONTH\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"Estimated from historic change rate over last ~11 days.\" | | } ] | | } | +---------------------------------------------------------------------------+ -- すでに検索最適化が行われているテーブルに対するこの関数の出力 select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITH_SEARCH_OPT\'); +---------------------------------------------------------------------------+ | SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITH_SEARCH_OPT\') | |---------------------------------------------------------------------------| | { | | \"tableName\" : \"TABLE_WITH_SEARCH_OPT\", | | \"searchOptimizationEnabled\" : true, | | \"costPositions\" : [ { | | \"name\" : \"BuildCosts\", | | \"computationMethod\" : \"NotAvailable\", | | \"comment\" : \"Search optimization is already enabled.\" | | }, { | | \"name\" : \"StorageCosts\", | | \"costs\" : { | | \"value\" : 0.052048, | | \"unit\" : \"TB\" | | }, | | \"computationMethod\" : \"Measured\" | | }, { | | \"name\" : \"Benefit\", | | \"computationMethod\" : \"NotAvailable\", | | \"comment\" : \"Currently not supported.\" | | }, { | | \"name\" : \"MaintenanceCosts\", | | \"costs\" : { | | \"value\" : 30.248, | | \"unit\" : \"Credits\", | | \"perTimeUnit\" : \"MONTH\" | | }, | | \"computationMethod\" : \"EstimatedUpperBound\", | | \"comment\" : \"Estimated from historic change rate over last ~11 days.\" | | } ] | | } | +---------------------------------------------------------------------------+ まとめ 検索最適化サービスについてドキュメントを読んでいくつか試してみた。 ドキュメントにある「ポイントルックアップクエリ」という言葉が機能を最も適切に表していると思う。 高速化したいクエリが明確な時に使えるのかもしれない。

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。 今回は外部関数(External Function)。 [arst_toc tag=\"h4\"] ユーザ定義関数 まずユーザ定義関数(User Defined Function)について。 UDFは、組み込み関数と同様にSQLから呼び出すことができる。組み込み関数にない機能を自作できる。 コードを共通化して2個以上の場所で発生するコードクローンを除去できる。 ユーザ定義関数は単一の値を返すパターンと、表を返すパターンの2パターンを定義できる。 単一の値を返すパターンはスカラー関数(UDFs)。 表、つまり0個,1個,2個以上の値を返すパターンは表関数(UDTFs)。 サポートされている言語はJava,JavaScript,Python,SQL。 スカラー関数(UDFs) スカラー関数は入力行に対して1つの出力行を返す。 公式のサンプルを読むのが早い。 -- UDFの定義 create or replace function addone(i int) returns int language python runtime_version = \'3.8\' handler = \'addone_py\' as $$ def addone_py(i): return i+1 $$; -- UDFの実行 select addone(1); 2 こんな調子で、create function の中に書き下した処理をインラインハンドラと言う。 処理を修正する際は、alter function を実行して対応する。 確かにお手軽だが、行数が増えてきたり、処理を共通化したりしたい場合は微妙。 あらかじめコードをコンパイルしておき再利用することもできる(ステージングハンドラ)。 ステージングハンドラであれば、より多くの行数に対応できるし、処理の共通化も用意。 ステージングハンドラの実行例は以下の通り。 CREATE FUNCTION my_udf(i NUMBER) RETURNS NUMBER LANGUAGE JAVA IMPORTS = (\'@mystage/handlers/my_handler.jar\') HANDLER = \'MyClass.myFunction\' インラインハンドラは全ての言語で対応できるが、 ステージングハンドラは、JavaまたはPythonが必要。 表関数(UDTFs) 表関数は入力行に対して表、つまり0行,1行,2行,...行 を返す。 実行例は以下の通り。なるほど。 戻り値が表(table)になるため、SQLだと処理と戻り値のギャップが少ないが、 SQLでは手続き的な処理が書けないため自由度は低い。 -- サンプルテーブル・データの作成 create or replace table orders ( product_id varchar, quantity_sold numeric(11, 2) ); insert into orders (product_id, quantity_sold) values (\'compostable bags\', 2000), (\'re-usable cups\', 1000); -- UDTFの定義 create or replace function orders_for_product(PROD_ID varchar) returns table (Product_ID varchar, Quantity_Sold numeric(11, 2)) as $$ select product_ID, quantity_sold from orders where product_ID = PROD_ID $$ ; -- UDTFの実行 select product_id, quantity_sold from table(orders_for_product(\'compostable bags\')) order by product_id; +------------------+---------------+ | PRODUCT_ID | QUANTITY_SOLD | |------------------+---------------| | compostable bags | 2000.00 | +------------------+---------------+ JavaScriptで書くと以下のようになる。手続き的に処理を書いている。 これであればある程度の自由度を獲得できる。 -- UDTFの定義 -- IP アドレスの「範囲」を IP アドレスの完全なリストに変換 CREATE OR REPLACE FUNCTION range_to_values(PREFIX VARCHAR, RANGE_START FLOAT, RANGE_END FLOAT) RETURNS TABLE (IP_ADDRESS VARCHAR) LANGUAGE JAVASCRIPT AS $$ { processRow: function f(row, rowWriter, context) { var suffix = row.RANGE_START; while (suffix <= row.RANGE_END) { rowWriter.writeRow( {IP_ADDRESS: row.PREFIX + "." + suffix} ); suffix = suffix + 1; } } } $$; -- UDTFの実行 SELECT * FROM TABLE(range_to_values('192.168.1', 42::FLOAT, 45::FLOAT)); +--------------+ | IP_ADDRESS | +==============+ | 192.168.1.42 | +--------------+ | 192.168.1.43 | +--------------+ | 192.168.1.44 | +--------------+ | 192.168.1.45 | +--------------+ 外部関数 ユーザ定義関数のロジック(ハンドラ)を外部のコンピューティングリソースに配置することができる。 概念図は以下の通り。Snowflake側から見て、外部関数、API統合、プロキシサービス、リモートサービスから成る。 外部関数(External Function)はスカラー関数(UDFs)だが、自前でコードを書かない。 その実体は、プロキシサービスを介してリモートサービスのコードを実行するためのオブジェクトで、 CREATE EXTERNAL FUNCTION により定義する。 リモートサービスは、外部のコンピューティングリソース。 AWS Lambda、Azure Functionsが分かりやすいが、 HTTPSでJSONを受けてJSONを返せれば、一般的なWebサーバでも良い様子。 プロキシサービスは、要はリモートサービスを抽象化するレイヤの機能を持つ。 AWS API Gateway、Azure API Managementサービスと言うと分かりやすい。 外部関数が直接リモートサービスを叩かない理由は、プロキシサービスに認証機能を持たせるため。 API統合は、外部関数をSnowflakeの世界に持ち込むための抽象化レイヤ。 同様の機能として、S3,Blobへのアクセス手段を抽象化するストレージ統合がある。 使い場合はUDFと同様。 select my_external_function_2(column_1, column_2) from table_1; select col1 from table_1 where my_external_function_3(col2) < 0; create view view1 (col1) as select my_external_function_5(col1) from table9; select upper(zipcode_to_city_external_function(zipcode)) from address_table; リモートサービスの入出力定義 前述のように、リモートサービスはHTTPSでJSONを受けてJSONを返す必要がある。 そのインターフェースは公式に記述がある。 リモートサービスの入力および出力データ形式 基本的に、POSTを受けて同期的に応答するようにする。 ただし、非同期処理もサポートしており、その際はGETでポーリングリクエストを受ける必要がある。 関数 ext_func(n integer) returns varchar のケースにおいて、リクエストヘッダは以下。 いわゆるRPC(Remote Procedure Call)を定義する何かに準じている。 ヘッダ文字列値 sf-external-function-formatjson固定 sf-external-function-format-version1.0固定 sf-external-function-query-batch-idリクエスト固有の識別子。この値を使って処理の成否を確認する。 sf-external-function-name関数名 ext_func sf-external-function-name-base64関数名(base64) base64(ext_func) sf-external-function-signature関数名署名 (N NUMBER) sf-external-function-signature-base64関数名署名(base64) base64(N NUMBER) sf-external-function-return-type戻り値型 VARCHAR(16777216) sf-external-function-return-type-base64戻り値型(base64) base64(VARCHAR(16777216)) リクエスト本文はJSON。 重要なことは、外部関数はスカラー関数であり、1個の入力に対して1個の応答を返す必要があるが、 一度に複数の入力を受けることができ、それぞれに対して応答を返す必要がある、ということ。 複数の入力のセットを公式ではバッチ、と読んでいる様子。 署名 f(integer, varchar, timestamp) を持つ外部関数にシリアル化したJSONを送る。例は以下。 先頭の数字が1個多い気がするが、これは行番号を表す。 { \"data\": [ [0, 10, \"Alex\", \"2014-01-01 16:00:00\"], [1, 20, \"Steve\", \"2015-01-01 16:00:00\"], [2, 30, \"Alice\", \"2016-01-01 16:00:00\"], [3, 40, \"Adrian\", \"2017-01-01 16:00:00\"] ] } リモートサービスの応答定義 レスポンス本文はJSON。リクエスト本文と同様にコレクションの先頭は行番号を設定する必要がある。 公式には、都市名を引数として、その緯度軽度を返す関数の例が書いてある。 このように、入力と対応する応答値を、入力と同じ順番にコレクションに詰めてシリアライズして返す。 この外部関数は、VARIANT型を戻している。このように、スカラー関数ではあるが、 1回の処理が(半)構造を持てるので、事実上複数の入力値に対して複数の応答値を返せる。 -- 呼び出し例 select val:city, val:latitude, val:longitude from (select ext_func_city_lat_long(city_name) as val from table_of_city_names); -- レスポンス { \"data\": [ [ 0, { \"City\" : \"Warsaw\", \"latitude\" : 52.23, \"longitude\" : 21.01 } ], [ 1, { \"City\" : \"Toronto\", \"latitude\" : 43.65, \"longitude\" : -79.38 } ] ] } StatusCode=200で、同期応答完了。 202を返すと、非同期応答となり処理を継続できる様子だが今回は省略。 その他はエラー応答として扱われる。 まとめ 外部関数に関するドキュメントと、その理解に必要なUDFs、UDTSsのドキュメントを読んでみた。 実体はHTTPS上のWebAPIであり、事実上無限の自由度を獲得できると思う。 (これを持ち出す頃には、本末転倒な何かに陥っていないか注意は必要だと思う)

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。 ステージに配置したファイルに対してロードせずに直接クエリを実行できる仕組み。 [arst_toc tag=\"h4\"] 外部テーブルの基本 ファイルの構造を自動的に解釈してテーブルにしてくれる訳ではなく、 いったんレコードが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を抽出して日付化して、パーティションキーとしている。 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)することで列を保護する。 [clink url=\"https://ikuty.com/2023/03/31/column-level-security/\"] 外部テーブルは上記のように、VALUE列にKey-Valueで列値が入り、 その後、SELECT文内で仮想列を定義する、という仕様で、外部テーブルに列がある訳ではない。 このため、マスキングポリシーを適用する先がない、という問題が起こる。 ただ、VALUE列全体にApplyすることは出来る様子。 外部テーブル-セキュリティカラムイントロ Masking Policyはビューのカラムに対して適用することができるが、 これは、外部テーブルに設定したMaterialized Viewにも設定できる。 本記事の図のように、Materialized Viewを用意した上であれば、 Masking Policyを適用することができる。

データをコピーしてしまうと、データをコピーする際の計算資源とストレージが必要になる。 そして、「どちらのデータが正しいか問題」が発生してしまい、由々しき事態になる。 SSOT(Single Source Of True)という考え方があり、なるべくデータは1箇所に集めたい。 Zero Copy Cloning Zero Copy Cloning を使うことにより、1つのデータをコピーせずに参照できる。 Cloneだけで意味が通じるが、「コピーしてないよ」を強調するために\"Zero Copy\"を付けて呼ぶ。 Clone は同一アカウント内のオブジェクト操作レベルで使用する。 つまり、Database、Schema、Table単位で使用する。 Share Share は 同一クラウドプロバイダ内の複数のアカウントで参照するために使用する。 一度 Shareというオブジェクトを作成し、Share から Database オブジェクトを作る。 Share から Databaseオブジェクトを作る際にコピーは行われない。 Cross-Cloud,Cross-Region してしまうと、「コピーせずに」が出来なくなる。 なので、Shareは同一Cloud,同一Regionまで。 Replication Cross-Cloud, Cross-Region でデータをコピーするための仕組み。 DR対応などで異なるリージョンにバックアップを取りたい場合や、 異なるクラウドプラットフォームに別機能を実装する場合などに用いる。 どうしてもコピーが発生してしまう。 一度 Cross-Cloud, Cross-Region で Replication を行い、 Replication先で Share, Clone を行うことで、Cross する回数が最小化される。 Whitepaper Shareについて以下が参考になった。 Snowflake Data Sharing EXTENDING THE BUILT-FOR-THE CLOUD DATA WAREHOUSE BEYOND ORGANIZATIONAL AND APPLICATION BOUNDARIES

Snowpipeは外部ステージ上に置かれたファイルを自動的にSnowflakeテーブルにロードする仕組み。 クラウドプロバイダの機能が透過的に利用されるため、その仕組みを意識する必要がない。 AWS,Azure,GCPがサポートされている。 Snowpipeを設定する際に、登場するオブジェクトにどう権限設定したら良いのかいつも悩む。 オレオレ設定をしてガバくなってないか、調べ直してみて書いてみる。 セキュリティを構成する SYSADMINでリソースを作って、SYSADMINにAssumeRoleして操作をする、というのは良くない。 別にロールを作ってそこに必要な権限を集めると良さそう。 既に外部ステージが存在し、そこに続々とファイルが配置されている。 また、既にSnowflakeにテーブルが存在し、外部ステージに到着するファイルを読み込みたい。 このシチュエーションで、外部ステージとSnowflakeテーブルを結ぶパイプを作成する。 パイプと関連する全リソースにアクセス可能なロールは以下のように作る。 パイプから見れば、外部ステージ、テーブルは既存のオブジェクトであり、 使用する、つまり USAGE が必要。またデータ取得のため、外部ステージから READ が必要。 同様に、パイプから見れば、データベース、スキーマは既存のオブジェクトであり、 USAGEが必要。またデータ投入のため、テーブルに対して INSERT が必要。 パイプがテーブル内のデータを参照して処理をするため テーブルに大して SELECTが必要。 例えば SYSADMIN が PIPE を CREATE した場合、その所有者は SYSADMIN になるが、 これを繰り返すと、SYSADMIN が全ての所有者になってしまう。 今回、Snowpipeに関わるオブジェクトを操作・閲覧可能なロールを作る、という話なので、 新ロールを PIPE の所有者にする。

なんだか分かった気がして分からない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万字越えの記事は終了。