カテゴリー: Snowflake

概要 データをコピーせずにアカウント間で共有する仕組み。 いわゆる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を作りまくって他人から見えない問題をシュッと解決できる。