見出し画像
Photo by multiplier197

dbtのDWHをRedshift(serverless)からSnowflakeに切り替えたときのおはなし

あきよん

　フリーランスでアナリティクスエンジニア的なお仕事をしています。
最近（2023年10月 ~ 12月頃）、データウェアハウスをRedshift (serverless)からSnowflakeに切り替える作業の一旦を担いました。
備忘録を兼ねて、私が担当したdbtに関連する切り替え作業の中で、印象に残った部分をいくつかご紹介したいと思います。

カラム名が小文字(Redshift)から大文字へ(Snowflake)

　Redshiftはカラム名が小文字、snowflakeはカラム名が大文字です。Snowflakeに移行する上で、これが一番苦労する要因になった違いでした。
　ドキュメント(yamlファイル)を作成する際に、dbt-osmosisを使用しています。

　ここではdbt-osmosisの詳細は述べませんが、DWHの内容から自動でドキュメントを作成してくれます。
　新規で作成したものはドキュメントのカラム名が大文字で作成されますが、既存のドキュメントdbt-osmosisで変更を加えても小文字のままです。
ドキュメントのカラム名の大文字、小文字は揃えたい、既存の小文字に揃える方が楽ですが、デフォルトは大文字なので、大文字に揃えることにしました。（モデルのsqlにおけるカラム名は小文字で揃えました。）
　既にドキュメントのモデルやカラムの説明を丁寧に書いているものはカラム名を手動で小文字から大文字に変換、あまりちゃんと書いてないものはモデル名のみ残し、カラム内容を削除してからdbt-osmosisでドキュメントを再作成し、説明を追加する、という手順でドキュメント作成しました。
　手動でカラム名を小文字から大文字に変換、という部分でvs codeのショットカット機能を重宝しました。

　テーブルをアンピボットしてカラム名が列の値にすう場合、そのままですと、値が小文字から大文字になってしまいます。
マクロ(dbt_utils.unpivot)をオーバーライドして値を小文字する処理を追加するなどしました。

dbt-utils.unpivot

　大文字、小文字の違いで一番大変だったのは、BIツール側の置き換えでした。QuickSightのデータセット置き換え時のカラムのマッピングにおいて、大文字小文字だけの違いの場合も、自動でマッピングしてくれなかったため、大量の手作業が発生しました。(腱鞘炎にならなくてヨカッタ。。。)

SQL関数や型の違い

　SQL関数がそのままでは動かないことやエラーがでることがありました。
Redshift特有のもの、引数の取りうる値が異なるもの、そのままではエラーがでるものなど、さまざまでした。
印象に残ったものを紹介します。

Redshiftにしかない関数の場合

　名前が違うだけ、のことが多いので、Redshift特有のものは同じ働きをするSnowflakeの関数名に置き換えました
例: json_parse → parse_json

引数でエラーが出る場合

　引数の内容でエラーが出たものは、Snowflakeの関数がとりうる引数の値を調べて置き換えました。
例: regexp_substr関数の引数regex_parameters においてRedshiftにあるpがSnowflakeにはない

そのままではなにかしらエラーが出る場合

　そのままではエラーが出るものは、エラーが出ないような別関数を置き換える、エラーが出ないような前処理を追加しました
例えば、型変換の
‘2023-12-11’::date
のような処理で、これまでRedshiftでエラーが出た経験はなかったのですが、Snowflakeでは値の内容によってはエラーになりました。
そのような場合は、try_to_dateのような関数で置き換えました。

文字列の型の違い

　varcharの最大文字列長が、Redshiftは65,535 バイトで、一方のSnowflakeは16,777,216バイトという大きな違いがあります。
　Snowflakeに移行することで、文字列長の制限によって発生していた分析の制限事項が概ね無くなった感じです。文字列の分析における自由度がぐっとあがったので、今後に期待です!

またこの変更によって、python udfにおいても型宣言がぐっとシンプルになりました。(python udfに限らないですが)
varchar(32)やvarchar(max)としていたものがvarcharのみでOKといった具合です。

SQLの挙動が違う場合やSnowflake特有の便利記法がある

joinのときに使うusing句の挙動が違う

　これちょっと知らなくてビックリポイントだったのでちょっと詳しく紹介します。

　以下のようなクエリで2つのcteをjoinすると、

with
c1 as (
    select
        'a' as col2,
        1 as col1
    union all
    select
        'b' as col2,
        2 as col1
    union all
    select
        'c' as col2,
        3 as col1
),

c2 as (
    select
        1 as col1,
        'A' as col3
    union all
    select
        2 as col1,
        'B' as col3
    union all
    select
        3 as col1,
        'C' as col3
),

c3 as (
    select
		c1.*,
        c2.col3
    from
        c1
    left join
		c2 using (col1)
)

select * from c3

c3のカラム順は
Redshiftではcol1, col2, col3
Snowflakeはcol2, col1, col3
となります。最後のselect文を*でかくと、Redshiftのカラム順はc1のカラム順そのままにですが、Snowflakeはusingに使用したカラムが指定した順に入ります。(onを使った場合はSnowflakeでも並びがそのままになります。)カラム順が変わるといろいろ下流モデルでの処理に支障がでたりすることもあるので、要注意ポイントだなあと思いました。

Snowflakeの便利記法 group by all

　集計でgroup by するときに、Snowflake にはgroup by allという便利な記法があります。

　Redshiftでは通常
group by 1, 2, 3
のように書きますが、dbt_utils.group_byを使用して
dbt_utils.group_by(n=3)
のように書いていました。
集計していないカラム全てを集計の次元に含める場合に使えるので、ほとんどこのgroup by allで置き換えることができました。
これだと次元数が変更になってもメンテナンスいらずで楽ですね。

インクリメンタルモデル関連の違い

　重めのテーブルはインクリメンタルモデルで実体化しています。

RedshiftとSnowflakeではインクリメンタルモデルのストラテジーの初期値が異なる

　デフォルトのインクリメンタルモデルのストラテジーは、
Redshiftはappend
Snowflakeはmerge
です。
　Snowflakeはmergeを採用した場合、厳密にユニークになるユニークキーを採用しないとエラーが出ます。そういった事情もあり、今回は全てのインクリメンタルモデルでdelete+insertを採用しました（project.ymlでデフォルトをこちらに指定しました。）。
厳密な検証はできていませんが、移行中に検討した感じでは、mergeよりもdelete+insertのほうが処理が速い印象でした。
この辺のところは、今後の開発やリファクタリングにおいて、厳密に検討をしたい部分です。

Snowflakeはモデル毎にデータウェアハウスのサイズが指定できる

　Redshiftにはない、Snowflakeの便利機能です。（下の記事に丁寧に書かれています。）

　Snowflakeでは以下のようにconfigでデータウェアハウスのサイズがモデル毎に指定できます。

config({'snowflake_warehouse': 'name_of_large_dwh'})

　この機能はインクリメンタルモデルに限ったことではないのですが、インクリメンタルモデルと関連が深いので、ここで紹介しました。インクリメンタルモデルのフルリフレッシュの処理は重たいことが多いため、通常の更新はデフォルトの小さいサイズ、フルリフレッシュを行うときだけ大きなサイズを指定するなどしました。
　Redshiftのときは、この機能がなかったため、（私ができる範囲では）時間切れでエラーになってしまうことも多く、期間を区切って少しずつ値を入れていく、というような泥臭い方法をとることも多々ありました。Snowflakeではこの苦労とはバイバイできる、さらに実体化にかかる時間もぐっと短くなるとメリット尽くしでした。（もちろん課金には注意が必要ですが！！！）

移行作業で重宝したパッケージ

　移行において入れておいてヨカッタなあ！作業がとっても楽になった、これらがなければもっと時間かかっていたよ、と感じたパッケージを紹介します。パッケージの開発者のみなさまに感謝です。

dbt-osmosis

　さきほど、カラム名が小文字から大文字への部分で紹介したパッケージです。膨大な量のドキュメントの移行、作成はこれがあったからこそ乗り切れました。

elementary

　テストの便利マクロを使っています。これを使うことでテスト書くのが楽、そして移行時に生じたエラーも早期発見できました。

dbt-utils

　カラム値の取得やピボット、アンピボットなどでよく使っています。カラム小文字大文字問題もこのパッケージのマクロを使っていたので、サクッと修正できました。また、テスト系のマクロも使用しています。

Metics (まもなく廃止されますが。。。涙)

　集計モデルを効率よく作成できるパッケージです。私たちは重宝していますが、まもなく廃止される、dbtのバージョンによってはもう非対応?なので新規導入は非推奨です。

　Metricsパッケージを多用して、集計モデルを作成していました。Metricsのマクロを使えば、sqlの内容がほぼ同じになるため、さらにこのパッケージのマクロをオーバーライドするなどして、引数の内容を変えるだけ、という風に工夫していたため、Metricsパッケージで書かれた集計モデルはほぼ修正いらずでした。（大元のマクロ修正で済みました。）
　ありがとうMetricsパッケージさん!!!という思いでいっぱいです。廃止されるの切ないくて悲しいですが、MetricFrowに期待です。

クエリの実行時間について

　圧倒的にSnowflakeの方が短い(速い)です。アドホックな分析で重たい処理をする場合、Redshiftでは重たくて結果が返ってこないため、一度create tableしてから参照する、という方法をとっていたものでも、Snowflakeでは短時間で結果が返ってくるため、create tableが不要になりました。ただ、モデルによってはRedshiftの方が速い場合もあったので、一概には言えないかもしれません。得手不得手があるのかも。
　唯一、Snowflakeで感じたデメリットの１つ（唯一かも）が、BIツールのQuickSightで、Redshiftでは正常に表示できていたビジュアルが、Snowflake移行後は重たくてエラーになり動かなくなった場合があったことでした。このときは、カスタムSQLでの処理を見直し、dbt側でさらに予め集計をしたモデルを作成することで、エラーを回避しました。

移行後の感想

　RedshiftからSnowflakeへの移行はdbtを使う上でもメリットがたくさんありました。そして今回の移行処理がdbtが導入されていない時点で行われていたら、一体どうなっていただろう。。。と思うとdbtって本当に素晴らしい！と感じさせられました。dbt x Snowflakeでさらに強力な分析基盤を築いていきたいと考えています。
最後までお読みいただきありがとうございました。


いいなと思ったら応援しよう！