Open37

Snowflake雑メモ

takertaker

いろいろ調べた中での雑メモを投下する。

takertaker

snowflake database

dbt-snowflake-monitoringで以下の権限を付与せよ、とあり調べた結果でた。

grant imported privileges on database snowflake to role your_dbt_role_name;

これは「snowflake」databaseへの権限付与ということになるが、snowflake databaseとは？
となった。
https://docs.snowflake.com/ja/sql-reference/snowflake-db

らしい。Redshiftでいうpg_*系のやつと思われる。
これらで一部のschemaにアクセスするための権限を付与するためのものが冒頭の構文。

takertaker

grant impoted ~

通常権限付与するときは grant [all|select|usage..]などであったが、impotedは見覚えがなかったので調べた。
どうやら共有データベースに対するアクセス権限らしい。

takertaker

snowsql

cliで実行するときはsnowsqlとか言うのを使えば良い。
インストールはbinaryをダウンロードすればよい
https://developers.snowflake.com/snowsql/

でダウンロード可能。

takertaker

設定ファイルは

${HOME}/.snowsql/config

に入る
パラメータは

  • 接続パラメータ
  • 構成オプション
  • 変数

があるらしい

接続パラメータ

[connections]ブロックを作り、
https://docs.snowflake.com/ja/user-guide/snowsql-start#configuring-default-connection-settings
に従っていじれば良い。
アカウントの切り替えをどうすればいいのかなって思ったが、接続には名前がつけられる
[connection.name]のように。
snowsqlの実行にこの値を -c optionにつければ良いとのこと。
いけてるね。

構成オプション

[option]ブロックを作り、
https://docs.snowflake.com/ja/user-guide/snowsql-config#label-snowsql-options

のそれぞれをよしなにいじって、configファイルに入れれば良い

以下が弄ると良さそう & 知っておくと便利そう

  • editor
    • emacsなので
  • empty_for_null_in_tsv
  • execution_only
    • バッチ実行をcliからさせたい場合
  • exit_on_error
  • header
  • output_file
  • output_format
  • timing
  • timing_in_output_file

基本はデフォルトで良さそう。
残りはなにか実験をするときなどに知っておくと捗りそうなオプション

接続後も、 !set option_name=valueで設定可能

変数

変数定義ができるらしい
接続中も設定できるので使いようはあるかも？

!define var=value
%var

で使えるらしい

takertaker

CLIでぐるぐるDDLの適用をしたい場合のワンライナー

ls -1 ${DDL_DIRS} | xargs -L 1 -P 1 -I{} bash -c "snowsql -d '\"${DB_NAME}\"' -s '\"${SCHEMA_NAME}\"' -o \"execution_only=True\" -o \"exit_on_error=True\" -o \"quiet=True\"" -f {}

これで標準出力に出さずにエラーが発生すれば終了してくれる。

takertaker

テーブルに関して

マイクロパーティションについて

Athenaとかprestoのように静的なパーティション（つまり、パスなどで制御された）は安定したパフォーマンスではあるが、メンテなどが大変である。
そのため、snowflakeではマイクロパーティションという機能を導入している。
マイクロパーティションはデータを50MB ~ 500MB（非圧縮状態におけるサイズ）に分割する。
テーブルはこのマイクロパーティションにマッピングされる。このときデータは列志向で保存される。
そのため、snowflakeではデータを細かく分割できる（≒プルーニング）
この処理は自動的に行われるとのこと。
またマイクロパーティションはメタデータを保持しており、列の範囲・データの数など。

takertaker

クエリプルーニング

クエリのフィルタをうまく働かせるとよりパフォーマンスが出やすいっぽい。
フィルタの述語と、マイクロパーティションの範囲がうまく噛み合うと良い？
ここがうまくいくと不要なデータのスキャンをしなくて良いので良いよねっていう話。
ただ

すべての述語式をプルーニングに使用できるわけではありません。たとえば、Snowflakeは、サブクエリの結果が定数になる場合でも、サブクエリの述語に基づいてマイクロパーティションをプルーニングしません

サブクエリで得られた値を用いて、日付のフィルタをした場合でもそれはプルーニングできないよ、と。
ここらへんはBQのpartitioningのイメージがそのまま使えそう。
ある程度決定的な状態でないとプルーニングできない、という理解で一旦。

takertaker

データクラスタリング

データはある列でソートされ、その列を順にクラスタリングされる、という話。
マイクロパーティションのデータで表した例を出している？
ただ、この例では日付でソートされている、と書いているが、ソートされていないようなきがする・・・？

ちょっと読みとりきれない。

takertaker

クラスタリングの深さ

ある値の列がマイクロパーティションで重複してしまっている数をマイクロパーティションの平均深度と呼んでいる。
snowflakeの場合、パーティションはかなり細かいため必然的にパーティションは複数で重複する値を持ってしまうため生じる概念。

ここらへんを表示するメタデータが

  • CLUSTERING_DEPTH
  • CLUSTERING_INFORMATION

である。

takertaker

クラスタリングキーについて

本来クラスタリングを改善するためにプルーニングを効かせたいカラムでソートされている必要がある。
が、それを手動で行うにはinsert into ~ order by A でソートなどする必要があり、これには運用的にも金銭的にもコストがかかる。
これをsnowflakeが自動でやってくれるのが「クラスタリングキー」である。
そして、「クラスタリングキー」が設定されているテーブルをクラスタ化されたテーブルと呼ぶ。

これはすべてのテーブルで設定は不要であるが、クラスリングの平均深度が大きい場合にやればいい。

設定は create table, alter table で可能

alter table table_name cluster by (column_a);
create table table_name ... cluster by (clumn_a);
takertaker

クラスタリングキーの特徴

ここらへんはDWHでよくあるdistkeyやparitionと同様
特色でいうとクラスタリングキーと相関がある場合に圧縮効率が高まるとのこと。
これは、相関があるつまり同傾向の値がマイクロパーティションに集まるため圧縮しやすくなる、と思われる。
一方でクラスタリングによりクレジットを消費する。
なので、考えなしにやるのではなくしっかりとした見積もりが必要。
クラスタリングのクレジット消費がクエリ消費を上回るのかを見積もる。

テーブルが頻繁にクエリされるほど、クラスタリングが提供する利益が増えます。ただし、テーブルが頻繁に変更されるほど、クラスタ化を維持するのに費用がかかります。したがって、一般的にクラスタリングは、頻繁にクエリされ、頻繁に変更されないテーブルに対して最も費用効果が高くなります。

とのこと。

takertaker

その他

テキストフィールドでクラスタリングする場合、クラスターキーメタデータは最初の数バイト（通常は5または6バイト）のみを追跡します。マルチバイト文字セットの場合、これは5 文字 より少なくなる可能性があることに注意してください。

へえ

CREATE TABLE ... AS SELECT を使用してテーブルを作成する場合、既存のクラスタリングキーは サポートされません。ただし、テーブルの作成後にクラスタリングキーを定義できます。

へえ。これはなんでなんだろう・・・不思議

takertaker

ストレージコストへの影響

クラスタの更新以外にも影響がある。
これは、クラスタの更新時に行われる挙動として完全に新しいマイクロパーティションとしてデータ領域を確保するためである。
古いものは削除領域として認識されるが、time travelとfail-safeの関係で保持され続ける。
つまり、データのカーディナリティが高く・更新頻度が高いデータはこの関係で加速度的にコストが増加する可能性がある。

takertaker

仮テーブル

所謂、temporary table(だが別途一時テーブルの項目がある・・）
とくに不思議な点はないものの、一時テーブルのコストも請求対象に入るらしいのでこれは注意が必要。
（BQどうだったっけ、かからないっぽい https://cloud.google.com/bigquery/docs/estimate-costs?hl=ja)

また、実テーブルと同じ名前のテーブルを作成できるらしい（びっくり）

takertaker

一時テーブル

fail safeコストがかからないよ。
あーtransient tableの話か。
つまりsnowflakeでは、通常のテーブルはtime travel & fail safeが有効なテーブルを指している。
一時テーブルというワードの使い方に注意が必要だな。

takertaker

外部テーブルについて

これを読む前にstageについての理解を深めたほうが良さそうなので、後で見直す

takertaker

テーブルデザイン

列の長さの指定

長かろうが、短かろうがクエリパフォーマンスに違いはない。
データのサイズへの影響も最小限である。
運用上の観点で適切な列が良い(異常なデータが入った際のエラー、外部ツールとの連携時の問題）

takertaker

クローニングについて

データベース・スキーマでクローンすることで、ソースのすべての権限を継承する。
クラスタリングキーも継承するが、自動クラスタリングは停止しているため注意が必要。

takertaker

データ型について

snowflakeのデータ型についていろいろみた
テーブルにドキュメントがなくてちょっととまどった

https://docs.snowflake.com/ja/sql-reference/data-types

takertaker

数値型

NUMBER

固定少数点型。
基本的にNUMBERのエイリアスとなるので気にしないでいい。

指定できる精度がストレージに与える影響

整数桁に関しては影響しない。各マイクロパーティションごとに範囲に従って決定してくれる。
少数に関しては影響するため注意が必要である。

FLOAT

とくになし

takertaker

文字列型

最大長の指定は「文字数」
BINARYで指定するとバイト数となる。

takertaker

日付型

形式について

これらのデータ型はすべて、最も妥当であり曖昧でない日付、時刻、または日付と時刻の形式を受け入れます。 自動的に形式を検出するように構成されている 場合に、Snowflakeが認識する形式については、 AUTO 検出でサポートされている形式 をご参照ください。

フォーマットはRedshiftの形式がちかい（多分一緒）
HH24,12が違うか？

takertaker

CREATE TABLE 構文について

CREATE [ OR REPLACE ]
    [ { [ LOCAL | GLOBAL ] TEMP[ORARY] | VOLATILE } | TRANSIENT ]
    TABLE [ IF NOT EXISTS ] <table_name>
    ( <col_name> <col_type>
                             [ COLLATE '<collation_specification>' ]
                                /* COLLATE is supported only for text data types (VARCHAR and synonyms) */
                             [ COMMENT '<string_literal>' ]
                             [ { DEFAULT <expr>
                               | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
                             [ NOT NULL ]
                             [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
                             [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
                             [ inlineConstraint ]
      [ , <col_name> <col_type> [ ... ] ]
      [ , outoflineConstraint ]
      [ , ... ] )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>'
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]
takertaker
CREATE [ OR REPLACE ]
    [ { [ LOCAL | GLOBAL ] TEMP[ORARY] | VOLATILE } | TRANSIENT ]
    TABLE [ IF NOT EXISTS ] <table_name>

or replaceがあるのは嬉しい。

  • LOCAL/GLOBAL
    • 他データベースの互換性のために用意されている。そのため省略して良い
  • TEMP/VOLATILE
    • temporaryテーブル（仮テーブルの指定）
  • TRANSIENT
    • 一時テーブルかどうか
    • これかつ、TEMPがない場合は永続テーブルになる（fail safe/time travelがon)

つまり、

  • create table→永続テーブル
  • create temp table→仮テーブル
  • create transient table→一時テーブル

というのを意識すれば良い

takertaker
( <col_name> <col_type>
                             [ COLLATE '<collation_specification>' ]
                                /* COLLATE is supported only for text data types (VARCHAR and synonyms) */
                             [ COMMENT '<string_literal>' ]
                             [ { DEFAULT <expr>
                               | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
                             [ NOT NULL ]
                             [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
                             [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
                             [ inlineConstraint ]
      [ , <col_name> <col_type> [ ... ] ]
      [ , outoflineConstraint ]
      [ , ... ] )

結構見慣れないのが多い

<col_name> <col_type>

普通に列名と型の指定

                        [ COLLATE '<collation_specification>' ]
                           /* COLLATE is supported only for text data types (VARCHAR and synonyms) */

collate...
データの照合する際の仕様らしい。
照合・・・？
https://docs.snowflake.com/ja/sql-reference/collation#label-collation-specification

文字列型のときに利用できる、文字列をどのように比較するのかを制御するやつ。
ロケール指定や、大文字小文字のルールも制御できる。
日本語の場合デフォルトで問題ないと思う（ja_jpは当然サポートされていないので。）
フランス語を見たいとき・大文字小文字を区別したくないときに選択していくことはあるかも。

                             [ { DEFAULT <expr>
                               | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */

default式。
autoincrementも指定ができるらしい。開始値、ステップ値も指定可能。
defaultの値にはUDFも含められるとのこと（only SQL)

auto incrementの注意

Snowflakeはシーケンスを使用して、自動インクリメントされた列の値を生成します。シーケンスには制限があります。 シーケンスのセマンティクス をご参照ください。

                             [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]

masking policy

  • 作成したmasking policyを指定する
    • xxxロールだったらデータをmaskingするといった動的なマスキングのルールが適用できる
takertaker
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>'
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>'
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]

出た。stage。
テーブルのデフォルトのファイル形式らしい。
stage_file_formatか、個別で指定を行う、というものらしい。
stage_file_formatというのは、stageで利用するファイル書式をいちいち指定しないでいいように共通化するためのフォーマットっぽい。
これはいいですね。。。
なので、このテーブルのデータを取り込みたいとき/unloadしたいときのためのデフォルトのファイルフォーマットを指定しよう。という部分。

  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]

テーブルにロードするときのオプション
テーブルに取り込まれるデータのデフォルト仕様？ということなのか？
あっても良いとは思うんだけど、そんなに便利なイメージがわかない（運用上ミスが出そうな気すらする）

  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]

time travelの保持期間

  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]

テーブルストリームに関する設定らしい・・・？
テーブルストリーム自体はCDC文脈で利用されるおmのらしい。
んーぱっとはわからないので、いったんおいておく。

  [ CHANGE_TRACKING = { TRUE | FALSE } ]

へー
https://docs.snowflake.com/ja/user-guide/streams-intro
ここらへんに詳細がありそうなので読んでおく

  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]

列で書いたcollationのデフォルト設定、これはいいね。
collationを設定した場合は、これも忘れないようにしないといけない

  [ COPY GRANTS ]

create or replace, create table like, create table clone
を使ったときにsource tableのアクセス権限を保持するかを指定できる
データ共有のときに聞いてくるのかな？
あんまり必要・不必要なシチュエーションがわかない

takertaker

既存のDWHとあまり重要な点は変わらないと思われる。
stage周りの設定に関しては考えたいところだが、基本的には不要と思われる。
入力するデータ（ファイル）・出力したいデータに合わせてそのフォーマットなどは変わるためここで制御して良いものではないと思われる。

もちろん指定することでこのテーブルのI/F定義のように振る舞えるため、組織が巨大になったときには活きるとも思う（stageのoptionを見ればどんなデータを用意すべきかの合意ができる的な意味で）

takertaker

(といいつつ、まだstageに関するドキュメントは読んでないので読めば認識は変わりうる。

takertaker

snowflakeは日本語の列名はdouble quoteで囲む必要がある（Redshiftは不要だった）

takertaker

terraform 関連について

takertaker

terraformというかdbtに絡む話だが、snowflakeではdouble quoteがついた状態での小文字大文字の区別がデフォルトで有効となる。
これがオンになっているとだいぶしんどい。

実際にdbtでは
https://docs.getdbt.com/reference/project-configs/quoting
オフになっている。
区別できるのがベストではあるものの、実運用でこれをいちいちdouble quotingするのも大変であるというのが理由。

そのとおりだと思うが、ここでterraformとの関連がついてくる。
terraformではschemaなどを作成する際の挙動がquotingしてしまう。
そのため、terraformで作成したリソースとのうまく噛み合わない。

例えばterraformでスキーマ名を小文字で作成すると、double quoteで囲む必要がある。
quotingをTrueにしたとしても、sourceはquotingされずに苦しんだ。

そのため、https://docs.snowflake.com/ja/sql-reference/parameters#quoted-identifiers-ignore-case を有効化することが良い。
一方で、この状態で小文字のschema名を作成すると、snow flakeはスキーマ名を大文字で返すが、terraformはリソース管理を小文字で行うため、再度createしようとする、という問題が起きる
https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/244

なので、snowflakeをterraformで管理する場合は、大文字で記述するのが良い。

takertaker

cloneについて

CREATE CLONEについて

takertaker
システム内の既存のオブジェクトのコピーを作成します。このコマンドは主に、データベース、スキーマ、およびテーブルの ゼロコピークローン を作成するために使用されます

ゼロコピークローンとは
https://docs.snowflake.com/ja/user-guide/tables-storage-considerations#label-cloning-tables

Snowflakeのゼロコピークローニング機能は、テーブル、スキーマ、またはデータベースの「スナップショット」を迅速に取得し、基礎となるストレージを最初に共有するオブジェクトの派生コピーを作成する便利な方法を提供します。これは、追加のコストが発生しない（クローンオブジェクトに変更が加えられるまで）インスタントバックアップを作成する場合に非常に役立ちます。

ここでいう、追加のコストというのはstorageの話？

takertaker

クローン元とクローン先は独立はしている。

データベース、スキーマ、およびテーブルの場合、クローンは、既存のデータを変更する、または新しいデータを追加する操作がクローンで実行されるまで、オブジェクトのデータストレージ全体に寄与 しません 。

fm。

基本的にはテーブルのコピーを取りたい場合は、ctasではなくcloneを使ったほうが良さそうである。