###### tags: `Snowflake`
# Snowflake Certification Preparation
## 1.概要とアーキテクチャ
### Snowflakeの差別化要素
* Snowflakeのアカウントの種類
* Standard(スタンダード)
* 暗号化はエンタープライズレベル
* タイムトラベルは1日前まで
* Enterprise(エンタープライズ)
* マルチクラスタ
* タイムトラベルで最大90日間
* Business Critical(ビジネスクリティカル)
* 全ての場所でデータを暗号化
* HIPAA準拠にする場合はこれ
* フェールオーバ/フェールバック対応はこれ
* AWS PrivateLinkサポート
* Virtual Private Snowflake
* 顧客専用の仮想サーバー
* ただし、ウェアハウスは全てのエディションで占有可能


* Web UIのワークシートは別々のセッショになるから、シートごとにロールとか変えられる

### Snowflakeのアーキテクチャ
* Snowflakeは以下で構成される
* クラウドサービス
* クエリ処理
* データベースストレージ

* テーブル
* PERMANENT
* 最もベーシック
* Enterpriseの場合は最大90日間Time Travelで戻せる
* TEMPORARY
* セッション内のみ（ワークシート変えたら見れなくなる）
* ETL/ELTに利用
* これでもしっかり請求される
* TRANSIENT
* 存続が必要だけど、PERMANENTほどではないとき
* EXTERNAL
* こいつだけTime Travel不可
* 外部データレイク上のSnowflake
* 外部ステージを介してアクセスされる
* Read Only
* ビュータイプ
* STANDARD
* SELECTステートメントに名前を定義したもの
* SECURE
* 承認されたユーザのみビューの定義などが確認できる
* MATERIALIZED
* クエリ結果が格納されている（？）
* 最もテーブルに近いらしい

* オブジェクト

* めっちゃスキーマに属している

### クラウド サービス レイヤー
* 以下を担う
* メタデータの管理
* テーブル定義
* マイクロパーティションの参照先
* テーブルのバージョンなど
* 認証
* インフラの管理 など
### データストレージ レイヤー
* ストレージレイヤーに取り込むときはカラムナ形式に圧縮
* マイクロパーティション
* 下の例はCountry毎にパーティションを分けている。（USとその他）
* マイクロパーティション内のデータは列方向に読み取る

* 実際のストレージ使用料に対して請求される
* 1ヶ月あたりの1日平均テラバイト
* オンデマンド
* 使用したストレージ容量（圧縮済みデータに対して課金）
* 価格はクラウドプラットフォームによって異なる
* キャパシティ
* 一定の量をコミットして前払い
* 容量の70%で顧客に通知
* Fail-Safeのためのテーブルも課金対象⇨Transientはコストを抑えられる。
* キャッシュの結果、クラウドストレージに対する課金はSnowflakeはしない。

### コンピュート レイヤー
* ウェアハウスを作るときのオプション
* Auto-suspend
* Auto-resume

* 使用する仮想ウェアハウスの数・サイズ・使用期間で課金

* ワークロード毎にウェアハウスを作成するのがベストプラクティス

## 2.クライアント、コネクタ、およびエコシステム
### クライアントとインターフェイス
* JDBC/ODBCを利用する場合はSnowflake Web UIから取得する。
> JDBC(Java Database Connectivity：RDBに接続するための標準JAVA API)/ODBC(Open Database Connectivity)


### SnowSQL
### エコシステムの概要
### コネクタ
* 「**Partner Connect**」によってSnowflake上でサードパーティの製品が利用できる。ただし「**ACCOUTNADMIN**」のみ。
* Informatica, Fivetran, Stitch, Looker
<img src="https://i.imgur.com/4tolBTS.png" width="150"><img src="https://i.imgur.com/dKa0tKH.png" width="150"><img src="https://i.imgur.com/Opqg5nI.png" width="150">


## 3.Snowflakeキャッシング
### 概要

### メタデータ キャッシュ
* メタデータが完全に回答できるようなSHOW, MIN, MAXなどの時に利用される
* 仮想ウェアハウスは使用されない

### クエリリザルトキャッシュ
* クエリ結果はクラウドサービスレイヤーに格納されている
* 結果セットは24時間保持される。毎回リセット。30日経過するとリセット
* 制御の仕方は`ALTER SESSION SET USE_CACHED_RESULT=TRUE/FALSE`
* アカウント/ユーザー/セッション レベルで`USE_CACHED_RESULT`パラメーターによって制御される。
* けど、別ユーザが同じクエリを投げていたらキャッシュが使用される
* **仮想ウェアハウスは使用しない**

### ローカルディスクキャッシュ
* 結果ではなく、述語をみたすパーティションや選択した列などが格納されている
* 仮想ウェアハウスのSSDに格納
* ローカルディスクキャッシュを使っているかは「**Remote Disk IO**」と「**Percentage Scanned from Cache**」
* ウェアハウスが停止・再開するとリセットされる
* キャッシュまとめ

## 4.SnowflakeでのSQLサポート
### データ記述言語(DDL)
* 以下のように列の定義変えてバイト数減らそうとするとエラーがでる

ALTER TABLE permanent
  MODIFY COLUMN comments
  SET DATA TYPE VARCHAR(1000);


* サンプルから新しいテーブルを作成する

CREATE TABLE my_orders AS
SELECT $1 AS key, $3 AS status, $4 AS price, $5 AS date, $6 AS priority
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

* 同じ列名でテーブルを作る

CREATE TABLE like_orders LIKE SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.ORDERS;

* 自動で表の中を埋める系。自動でインクリメントさせたり、固定値で埋めたり。。
* AUTOINCREMENTとDEFAULT

CREATE OR REPLACE TABLE autoincDefault_demo 
(part_num integer AUTOINCREMENT, part_name varchar(30),
restocking_fee number(8,2) DEFAULT 5.50 );

### データ操作言語(DML)
### クエリとフィルター
* OEDERBYの挙動
* ASCの場合はNULLが最後にくる。NULLS FIRSTオプションで変えられる。
* DESCの場合はNULLが最初にくる。NULLS LASTオプションで変えられる。

* PIVOTの挙動
* セルの中で登場する値（今回だったらJANとか）を列にして集計

### 照合
* GET_DDL
* 指定されたオブジェクトを再作成するために使用できるDDLを返す。

### サブクエリ
* 反復的なサブクエリはTempテーブルを使用すると良い。

### クエリタグ
* `SET`を使って変数をセットしている。変数は最大256Byte
### クエリプロファイル
* 過去「**14日間**」のクエリをクエリ履歴に保持する
* 実行中のクエリも見れる
* 他の人のクエリは見れるが、クエリによる結果はわからない。
* ノードの所要時間は当然リモートIOがでかい

## 5.データのロード
### コンセプト
* 「**ファイルフォーマット**」と呼ばれるオブジェクトに解析結果を格納する。「**TYPE**」でCSVとかJSONとかが指定できる
* データのロードの仕方は以下の3つ
* SNOWSQLによるPUT(or COPY?)
* SNOWPIPEによる継続的なデータインジェスチョン
* サードパーティのETLツール
* ステージから既にシステムにCOPYされたデータを、クローンされたテーブルに際読み込みはできるらしい。
* メタデータが、テーブルごとにどのファイルから読み込んだのかは把握している。ただし、テーブルがクローンされてもメタデータはクローンされない
* データのロードで速度に寄与する要素
* ステージ上（S3）の物理的な配置（リージョンとか）
* GZIP圧縮効率
* ロード後にステージ上の元ファイルを消す場合は`copy into mytable purge = true;`
* そのほかのオプションとして型変換やコンカチが可能
* 外部ステージからロードする場合は以下の権限が必要だよ
* s3:GetObject
* s3:GetObjectVersion
* s3:ListBucket
* 外部ステージ（S3）にアンロードする場合は以下の権限が必要だよ
* s3:DeleteObject
* s3:PutObject

* WebUIの場合の上限は20MB
* storage integrationはIAMの操作とかができるっぽい。integrationは複数のステージと紐付けできるらしい。
> 1. An external (i.e. S3) stage references a storage integration object in its definition.
> 2. Snowflake automatically associates the storage integration with a S3 IAM user created for your account. Snowflake creates a single IAM user that is referenced by all S3 storage integrations in your Snowflake account.
> 3. An AWS administrator in your organization grants permissions to the IAM user to access the bucket referenced in the stage definition. Note that many external stage objects can reference different buckets and paths and use the same storage integration for authentication.

* ロードのパフォーマンスに影響があるのは以下
* ステージの物理的な位置
* GZIPの圧縮効率

### 一括ロードの概要
* ローカルストレージから内部ステージに。外部ステージ(クラウドストレージから)Snowflakeに。
* コマンドはPUTを使う。これはSNOWSQLでしか使えない。（ワークシートでは使えない）。ちなみにローカルにファイルを取ってくるのはGETを使う。でも、(名前付き)外部ステージにはGET使えない。
* ステージからテーブルにデータを突っ込むためには、データが以下のいずれかに存在している必要がある
* ネームド内部ステージ⇨PUTでステージされる
* ネームド外部ステージ（S3とか）
* 外部ストレージ（S3とか）

* ステージの一覧を確認するには`LIST @~`
* PUT何もしてなくても色々出てくるが

* ステージに関するメタデータは以下。メタデータの列は列名でしかクエリが投げられない。
* METADATA$FILENAME：現在の行が属しているステージ上のファイル名
* METADATA$FILE_ROW_NUMBER：

* COPYコマンドは以下を指定できる
* FILES：ファイル名
* PATTERN：正規表現による選択
* FILE_FORMAT:フォーマット
* VALIDATION_MODE:エラーの扱いをどうするか

* COPYコマンドのオプション
* COPYのタイミングでTRUNCATECOLUMSとかREORDERCOLUMNSとかできる。から、一時領域が不要になる
*

### データロードの推奨事項
### データロードにおける変換と検証
* ここでいう変換は以下のような変換。この時は`VALIDATION_MODE`は使えない。
* 他にも`FLATTEN JOIN GROUP BY`は非対応

### 継続的なデータのロード
* ソースアプリケーションがホースみたいにステージにファイルを突っ込む
* ステージからSnowflakeには突っ込まずにインジェスチョンキューに入る
* パイプがCOPY INTOでテーブルに突っ込む
* 複数のパイプオブジェクトがあったとしても、キューは単一のため順序は保証されない
* 細かいデータが大量にある場合に向いてる
* 仮想ウェアハウスはいらない。サーバレス。課金はコンピュート使用量とAPIのコール数。
* 認証はJSON Web TOKENによるキーペア認証
* 履歴の期限は14日
* 同一のファイル名は不可。同じファイルを読まないようにしているから。
* Snowpipeは内部/外部ステージ両方に対応。AUTO-INGESTIONは外部のみ。
* ユーザの指定ウェアハウスは使わない（？）

> SNOWPIPE has three APIs
> Data File Ingestion
> 1. Endpoint: insertFiles⇨ファイルのリストが読み込まれた
> Load History Reports
> 1. Endpoint: insertReport
> 2. Endpoint: loadHistoryScan

### データのアンロード
* 半構造化データ（JSON,XMLなど）などをローカルにアンロードする場合は、VARIANT型の列からアンロードする必要がある

* VariantにアクセスするBIにとっての理想はVIEWオブジェクトで見せること

* 複数のファイルをアンロードする場合は上限を拡張させるために`MAX_FILE_SIZE`を利用する。（デフォルトは16MB）

### ストリームおよびタスク
#### ストリーム
* テーブルに変更が加わった際に､変更部分だけを取り出したものが「**ストリーム**」
* 特定の時点（オフセット）からの変更点を保存している。
* オフセットを確認するには`select system$stream_get_table_timestamp('MYSTREAM');`でタイムスタンプが得られる
* `SHOW STREAMS`で一覧が見れる。`HISTORY`のオプションを入れてあげると、消されたStreamsの情報も見れる。

* ストリームを使用する場合は以下のオブジェクトに対して操作権限が必要
* DATABASE：USAGE
* SCHEMA:USAGE, CREATE, CREATE STREAM
* TABLE:SELECT

* Materialized Viewは非対応
* iかいのDMLで複数変更があった場合は最新だけ保持する（？）

#### タスク
* ジョブのこと。単一のSQLやストアドプロシージャの実行ができる
* ジョブ実行中に他のジョブが実行されようとすると、それはスキップされる
* タスクの履歴を見る場合はinformation schema内のtask_historyテーブルに対してクエリを投げる

select *
  from table(information_schema.task_history())
  order by scheduled_time;

* タスクが無限ループに入ったら1時間で切断される
## 6.関数
### Snowflake関数の概要
### 高性能関数
### ユーザ定義関数
* 通称UDF
* 対応しているのはSQLとJavascript

### ストアド プロシージャ
* UDFとストアドの違いは以下の通り
* UDFの方が何となく制約があるイメージ
* オブジェクトに対する操作(DDL,DML)ができない
* 値を必ず返す必要がある
* でも行のセットは返せる

## 7.セキュリティの管理
### セキュリティの概要
### アクセス
* 階層型セキュリティ
1. アクセス：IPアドレスの指定によるアクセス制御
2. 認証：パスワード、MFA、SSOによるユーザ認証
3. 認可：ロールや権限によりオブジェクトのアクセスを制限
4. データ保護：AES256暗号化、暗号化鍵のローテ

### 認証
* IPブラックリストが優先される

* ネットワークポリシーは、ACCOUNTADMINかSECURITYADMINが設定する

* MFAはユーザごとで設定する

### 認可
### データ保護
### インフラストラクチャ

## 8.アクセス制御およびユーザ管理
### コンセプト
### システムロール
* ACCOUNTADMIN：最強権限。全ての権限を保有。
* SHAREオブジェクトはACCOUNTADMINしか扱えない
* SECURITYADMIN：権限付与の管理を担う。

* USERADMIN:全てのユーザとロールの作成を担う。

* SYSADMIN:メジャーなオブジェクトの作成を担う。

* ロールは1つ以上付与される。権限はユーザに直で付与されない。（ロールを介す）
*
### カスタムロールおよび継承
### 所有権
* オブジェクトを作成すると、現在のロールが所有者になる。
* 所有者は所有権を委譲できる。

GRANT OWNERSHIP ON WAREHOUSE dev_ws TO ROLE dba

### 構成およびアクセス管理
### まとめ

## 9.半構造化データ
### 概要
* データ型は3つある
* VARIANT：標準SQLの型
* OBJECT：キーと値のペア。値はVARIANT
* ARRAY：さまざまなサイズの配列。値は　VARIANT。

### 半構造化データのクエリ
* 「car_sales」というテーブルに以下のVARIANT列がある場合を考える
* dealershipの値を取得するには`select src:dealership from car_sales;`構文としては`select [VARIANT型の列名]:階層1のkey`

{
  "customer": [
    {
      "address": "San Francisco, CA",
      "name": "Joyce Ridgely",
      "phone": "16504378889"
    }
  ],
  "date": "2017-04-28",
  "dealership": "Valley View Auto Sales",
  "salesperson": {
    "id": "55",
    "name": "Frank Beasley"
  },
  "vehicle": [
    {
      "extras": [
        "ext warranty",
        "paint protection"
      ],
      "make": "Honda",
      "model": "Civic",
      "price": "20275",
      "year": "2017"
    }
  ]
}

### 半構造化データのロードおよびアンロード

## 10.継続的なデータ保護

### Cloning
* 「**ゼロコピークローン**」とは、テーブルを複製して同一のマイクロパーティションを参照させることで「**スナップショット**」を取得する方法
* ゼロコピークローンはクラウドサービスレイヤーの「**メタデータ管理**」の機能
* CLONEコマンドを使用することでメタデータに新しいエントリが追加され、クローンされたテーブルの情報が追記されていく

### Time Travel
* 保存期間のカスタマイズをするには`SET DATA_RETANTION_TIME_IN_DAYS`を使う。

* タイムトラベルの仕組みはマイクロパーティションが不変であることを利用する。

### Fail-safe
* テーブルタイプがスタンダードのものしか対応していない
* 3日経つとSnowflakeスタッフしかさわれない領域にいく
* 設定の変更は不可能
* フェイルセーフ用のデータストレージコストが発生する

### データベースのレプリケーションとフェイルオーバー
* データベースのみが対象
* フェイルオーバーはSnowflakeが担当
* Business Critical以上のサービス
* リージョン異なってもOK
* クラウドサービスも同じじゃなくてOK

## 11.データ共有
### 概要
* 共有できるオブジェクト達
* テーブル
* セキュアビュー
* セキュアUDF
* セキュアビューの共有の流れ。これで普通にクエリも投げられる。
1. セキュアビューを作成する
2. Shareオブジェクトに対して、共有するテーブルへのSELECT権限を付与する
3. Shareオブジェクトにアカウントを追加する


### データプロバイダー
### データコンシューマー
* コンシューマーのアカウント数は無制限
### リーダーアカウント
### Snowflake Data Marketplace / Data Exchange

## 12.パフォーマンスおよび同時実行
### EXPLAIN
* 実行前の試用に使う。JSON形式で吐くことも可能。

### SQLのパフォーマンスのヒント
* パーティションプルーニング
* 無駄なパーティションの読み込みを削減する
* 水平方向、垂直方向にプルーニングが可能

### データクラスタリング
* 自然データクラスタリング
* 日付や時間などが含まれるデータがある場合はそれらに基づいてマイクロパーティションが作成される
* 年月日とかいい感じに自動でマイクロパーティション化してくれる
* クラスタリングキーは列または式で、各テーブルで指定してあげればいい
* テーブルにクラスタリングを追加（？）する場合はDBとスキーマに対して`USAGE`か`Ownership`の権限が必要
### 仮想ウェアハウスのスケーリング
* 仮想ウェアハウスのリソースが不足している場合は、クエリにキューが登録される。

## 13.アカウントとリソースの管理
### コスト管理

### リソースモニター
* 設定は「アカウント」または「ウェアハウス」レベル
* アクションは通知やウェアハウスの停止などができる。クォータの使用量などに限らず特定の日時で停止なども可能。

* 操作するには「**ACCOUNTADMIN**」権限が必要
* リソースモニタはオブジェクト

* クレジットの使用料は月初にリセット
* warehouse作られた瞬間からリソースモニターがトラッキングする
### INFORMATION_SCHEMA
* メタデータの詳細な情報を提供する。例えばオブジェクトの権限リストの参照ができる。
* SELECTなどで意図せず大量の情報を参照しようとしちゃう場合はエラーを吐く

### SNOWFLAKEデータベース

## 14.プレビュー機能
### 概要
### リリース
* 週毎にリリース。金曜日に完了。
### Snowsight
### パブリックプレビュー機能
### プライベートプレビュー機能
* Enterprise以上で使える列のマスキング機能。以下の2種類がある。
* 動的データマスキング
* 外部トークン化

### ロードマップ

## その他
* SnowflakeのデフォはAWSのus-west-2
* AWSのus-central−2は対応していない
* GCPの場合はus-central-1のみ対応
* Azureの場合はwest-us-1は対応していない
* レプリケーションはデータベースのみ対応
* ELTとETL
* SNOWFLAKEデータベースに含まれるACCOUNT_USAGEはスキーマ名
* バルクorパイプの使用判断で、構造化or半構造化は関係ない