DWHを選定する
社内のDWHがハード保守期限を迎える為、クラウド上への移行を計画。AWS上に社内環境移行を推進中である為、DWHもデファクトスタンダードであるRedshiftを本命に考えておりました。本来はGCPに社内環境を構築してBigQueryを使いたかったのすが、GCPでOracleが使えない制約があり、AWSを選択しました。(AZULEは使用経験がなかった為、今回の選定から除外)
DWH候補は下記の通り。
■列指向DB
・Redshift ← AWS環境ならデファクトスタンダード
・RedshidtSpectrum
・Athena
・Snowflake ← 革新的DWHとして高い評価
■行指向DB
・Aurora
・SQLserverExpress
比較前に思っていたこと
■売上データは増えても1000万行程度なので、列指向DBでなくても速度出るのでは？
■可視化に使用予定のPowerBIは、オンライン上にデータ展開するので、行指向DBでいいのでは？
■仕組みから変えるようになると大変なので、できれば最初に列指向DBを入れておきたい
■費用に見合う速度がでなかったら意味ないので、その場合は行指向DBでいいかも
ある程度使ってみて見えたこと
■AWSのサービスを使いこなすのが難しい
・RedshidtSpectrumがよくわからない
・Athenaもすぐ使えそうなのに使えない
・Glueもよくわからない
・LakeFormationが便利そうだがよくわからない
■PowerBIで気づいたこと
・インポートモードは列指向DBの利点を活かせない(全件読み込むのが無駄)
・列指向DBを使う場合はDirectQueryを使う方がベター(Snowflakeなら爆速)
・EC2側のスペックが低いと描画が遅い
・(わかっていたが)PowerBI(Web)にプッシュするので、On-Premises DataGatewayが必要になる
・On-Premises DataGatewayを入れたサーバが起動していないとPBIが使えない
・On-Premises DataGateway用のEC2が必要で、費用が掛かかる上に単一障害点となる
・クラウドDWHであるSnowflakeを使っても、On-Premises DataGatewayが必要となる
■Redshiftで面倒だったこと
・テーブル定義の作成：テーブル定義作成時は長さまで定義が必要
・データ投入が面倒：CSVはgzip圧縮してS3に置いてから取り込む
・速度向上の為に列ごとの圧縮方法指定が必要
・起動が致命的に遅い(10分程度かかる)
・停止し忘れ = 課金。リザーブドインスタンスだと高くて割に合わない
■Snowflakeでいいなと思ったこと
・課金体系：(計算速度 × 利用時間) + ストレージ費用
・従業課金だが、BigQueryのような1回のクエリで高額となるような心配はない
・とにかく早い。6年分(約50GB)をロードするのに約13秒！！！！
・SQLの実行速度も恐ろしく早い(XSサイズでも超早)
・費用もRedshiftの3分の1未満と低価格
見えてきたDWHに求める要件
■こうであって欲しい
・使った時間じゃない従量課金(BigQueryやAthenaみたいに)
・パフォーマンスが良くて安いこと(BigQueryやAthenaみたいに)
・自動停止(うっかり停止忘れがまあまあな金額になるのが怖い)
・自動起動(使うときは瞬時に使いたい)
・理解が簡単なもの(構築で挫折しないもの)
・データ投入が簡単なもの
Snowflakeを使わない理由がなかったので、Snowflakeを選定。
Snowflakeに合わせて、BIツールも再選定
■比較したBIツール
・PowerBI
・QuickSight
・Tableau ← コスト面で折り合わないのでさらっと確認のみ
・Re:Dash
・Metabase
■結果としてQuickSightを選定
・機能はPowerBIに劣る部分が多いが、ビジュアルの良さや操作の簡便さは好印象
・Viewerライセンスがあり、従量課金で最大5ドル。
(全員に使って欲しいが、使ってもらえるかわからない導入時には最適)
・デスクトップアプリがないので、動作は少しもっさり
・作成したビジュアルをバックアップする手段がないので本番運用時の対策が必要
・SPICEを使うと速度はほんの少しだけ早くなり、SnowFlakeのコストが大幅に下がる
(SPICEに展開したデータを使用する為、SnowFlakeにクエリを投げる必要がない)
選定までに行った調査は以上です。
おまけ：Snowflakeへのデータロード手順
■Snowflakeにデータをロードする手順
●通常の手順
・Snowflakeにテーブル作成
(GUI or SnowSQLにてテーブル定義を実行)
・S3にバケットを作成
(IAMにてポリシー制御も必要)
・データソースからCSVに出力してS3にアップロード
(AWSCLIを使えばスケジューラ実行可能)
・Snowpipe or SnowSQLにてcopy into コマンドでデータロード
(CSVデータを並列で取り込める為、指定のサイズに区切っておくこと)
※copy into コマンドで取り込む際の注意点
・Snowflakeはdeleteしたデータを含むCSVファイルはリロードできない
(再度CSV出力 → S3にアップしてcopy intoしてもダメ)
COPYコマンド実行時に「FORCE=TRUE」オプションを付けることでリロード可能
(CSVファイル名で判定しているので、ファイル名を変更しても取り込める)
例) 当月の間は修正伝票が入ることがあるので毎日取り込み直したい
①30日分のデータを出力 → ファイル名+日付
②30日分のデータをSnowflakeからDelete
③ ①で出力したファイルを取り込めば、常に最新の30日分を取り込める
●Talendを使った手順
・Snowflakeにテーブル作成
・データソースからデータを取得し、Snowflakeに更新
(当社ではOracleからデータを取得し、直接SnowFlakeに更新している)
※通常の手順だと4ステップ必要だが、2ステップでロード可能
※ステージング不要(Talendから直接Snowflakeテーブルに更新できる)
※テーブルがないと参照先/更新先が指定できない
→ テーブル右横の3点ボタンを押し、カスタマイズテーブルでテーブル名を指定できるが、
謎のエラーが起きたり、一部の列はデータが欠損したのでおすすめしない
最後までお読みいただきありがとうございました。