記事・メモ一覧

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。 今回は外部関数(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万字越えの記事は終了。

概要 データをコピーせずにアカウント間で共有する仕組み。 いわゆる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からデータをロードしてみた。