BigQuery と Snowflake を徹底比較 2022-12-21 渡部徹太郎 風音屋TechTalk #2 1


ID ：fetaro 名前：渡部　徹太郎 学生：東京工業大学でデータベースと情報検索の研究 (@日本データベース学会) 職歴： 　*大手SIer 　　- オンライントレードシステム基盤 　　- オープンソース技術部隊 　* 大手Web 　　- ビッグデータ分析基盤 　* ベンチャー 　　- データエンジニア * 風音屋アドバイザー エディタ：emacs派→ InteliJ派 趣味：ギター、麻雀、自宅サーバ 自己紹介 2


● 1. BigQuery と Snowflakeの概要と登場の背景 ● 2. BigQuery と Snowflakeの徹底比較 ○ 2.1 ユーザにとっての使い勝手比較 ○ 2.2 管理者にとっての使い勝手比較 ○ 2.3 処理速度比較 ● 3. まとめ 目次 3


本資料の内容は、個人で調査できる範囲で調査した結果をもとに記載しています。 もし間違いがあればご指摘ください。 申し送り事項 4


BigQuery と Snowflakeの概要と 登場の背景 1 5


SQLを実行して結果がみれる、分析用のDWH(データウェアハウス)サービス BigQueryとSnowflakeって何？（初学者向けの説明） ここにSQLを書く↑ ここに結果が出る↑ ここに結果が出る↑ ここにSQLを書く↑ BigQueryの画面 Snowflakeの画面 テーブル一覧↑ テーブル一覧↑ 6


● BigQueryとSnowflakeに共通する点 ○ フルマネージドなDWH(データウェアハウス)サービス。 データをテーブル形式で保存し、SQLでクエリを実行する、データベースの一種 ■ データの抽出と集計に特化したデータ分析用途のDB。OracleやMySQLなどのデータ操作用途 のDB(OLTPのDB)とは根本的に違う。 ■ 他には AWS Redshift, Azure Synapse Analytics, Treasure Dataなどが同系列の製品 ○ Webブラウザから使える。 ■ Webブラウザからアカウントを作り、データをロードしたら、あとは画面からSQLを実行する と結果が表示される ○ 完全従量課金。初期コスト０。 ■ （主に）蓄積しているデータの保存料金と、実行したクエリに対して課金される ● 異なる点 ○ BigQueryはGCPのサービスでありGCPの中でのみ利用可能であるのに対し、 Snowflakeはソフトウェアであり、AWS, GCP, Azureのどこでも利用可能 ○ Snowflakeはどのサイズの計算リソースを指定する必要があるのに対し(※1)、 BigQueryは指定不要 BigQueryとSnowflakeって何？ （初学者向けの説明） 7
 (※1)Enterprise版以上のSnowflakeにはQuery Acceleration Servicesという機能あり、突然重い処理が来た 時には事前に指定した計算リソースの倍数分だけ自動で計算リソースを使ってくれる機能がある

● データ分析は、ビジネスの現場で、トライアンドエラーを繰り返す事が重要 なぜBigQueryとSnowflakeを比較するのか？ ビジネスの現場で トライアンドエラー ITリテラシが低い人でも できる必要がある スモールスタートでき、 辞めたいときに辞められる エンジニアが居なくても運用できる Webブラウザから簡単に使える 初期コスト０ 完全従量課金 この二つの要件を満たすものはBigQueryとSnowflake 8


DWHの歴史とBigQueryとSnowflake 2010 2020 2015~ snowflakeリリース ★2020上場 評価額700億ドル超 ★2004 Google MapReduce論文 ★2003 Google File System論文 2011~ BigQueryリリース ★2016~ Tokyoリージョン ★2006~ Hadoopプロジェクト開始 Hadoop戦国時代 オンプレDWH時代 サーバ管理不要！ 従量課金！爆速！ 導入コスト数億... ★2010~ Dremel論文 (by Google) Hadoop疲れ 運用しんどい 汎用サーバを並べる だけでDWHができ る！ Hadoopの弱点を克服 BigQuery使いたいけど、 うちの会社はGCPがダメ なのよね GCP以外でも、 BigQueryみたいな体験がで きる！！ 参考： Snowflakeの創立 ：https://www.slideshare.net/mmotohas/snowflake-architecture-and-performancedb-tech-showcase-tokyo-2018 Snowflakeのリリース：https://docs.snowflake.com/en/release-notes.html Hadoopの歴史：https://xtech.nikkei.com/it/article/COLUMN/20120215/381721/ ★2012~ Snowflake創立 9


DWHの歴史とBigQueryとSnowflake - Google Trendsの結果 ★GCP Tokyo リージョン ★Snowflake 　上場 Hadoop戦国時代 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 10


BigQuery と Snowflake 徹底比較 ユーザにとっての使い勝手 2.1 11


機能比較 BigQuery snowflake DDL, DML ○ ○ トランザクション ○ ○ 一時テーブル ○ ○ ビュー ○ ○ マテリアライズドビュー ○ ○ ストアドプロシジャ ○ ○ UDF ○ (SQL, JS) ○ (SQL, JS, Java, Python) 地理空間データサポート ○ ○ インフォメーションスキーマ ○ ○ パラメータ ○ ○ データ共有 ○ ○ 検索インデックス ○ ○(Enterprise版のみ) 機械学習　　(表の列を推論して埋める ) ○(BigQueryML機能) テーブルの更新を別にテーブルに記録 ○(テーブルストリーム機能 ) GCP内の他のDBへの直接クエリ ○(federated query機能) GoogleAnalytics、Fireabase、GooglePlayのストアデータ利用 ○ Snowflake機能一覧：https://docs.snowflake.com/ja/user-guide/intro-supported-features.html BigQuery機能一覧：https://cloud.google.com/bigquery/docs/ 12
 よく使う機能はどちらも完備

Web UI比較　ー　BigQuery テーブル一覧 SQL開発 実行結果 データ追加ボタン Google アカウントでロ グイン プロジェクトの選択 データセット一 覧 プロジェクト一 覧 結果保存 ボタン データ探索ボ タン tab 13
 特徴的な箇所 共通する箇所 凡例

Web UI比較　ー　Snowflake テーブル 一覧 ロールの選択 計算リソースの選択 実行結果 SQL開発 スキーマ 一覧 データベース 一覧 列の説明 値の統計値 （１万行以下の場合） 14
 ワークシー ト 画面

ワークシートという画面もあり、作業毎に使い分けられる Web UI比較　ー　Snowflake　ー　ワークシート画面 15


● Classic Consoleという旧バージョンのUIもある Web UI比較　ー　Snowflake ( Classic Console） ワークシートは tab 16


● Snowflakeはクエリを発行する前に計算リソースである「仮想Warehouse」を指定する(※1) ○ 仮想Warehouseはサイズごとにあり、サイズ毎にcreditがことなる ○ creditの金額はプランと地域で決まる。 計算リソース指定　ー　Snowflakeのみ　 サイズ credit 金額 XSmall 1 credit / h $2.85 / h Small 2 credit / h $5.70 / h Medium 4 credit / h $11.40 / h ︙ ︙ ︙ 6XLarge 512 credit / h $1459.20 / h ○ アドホックなクエリ(※2)の最小課金は60sec ■ 10msで終わるクエリを投げても60secはWarehouseは動作し、60secは課金される。ただし、続けざ まにクエリを投げれば起動中のWarehouseで処理される ○ アドホックな分析作業でコストを最小にするには頻繁に切り替える必要がある（※1） ■ 例えば、 　　(1)全量を集計をする重いクエリ→(2)結果を見る軽い参照クエリ→(3)重い集計集計クエリ という順序で実行する場合、都度60秒以上かかるようにWarehouseを選択しないといけない 表１: WarehouseのサイズとTokyoリージョン(Standardプラン)の金額 表2:AWSのリージョンとStandardプランのクレジットの値段(2022年12月時点) 17
 (※1)Standardプランより高価なEnterpriseプラン以上ではQuery Acceleration Servicesという機能あり、突然重い処理が来た 時には事前に指定したWarehouseサイズの倍数分だけ自動で計算リソースを使ってくれる機能がある (※2)自動化の際に用いるSnowpipeやTaskでは秒単位課金

● BigQueryのほうが機能が多いし手間も少ない。ユーザフレンドリーと感じる ● BigQueryが優位だと感じる点 ○ 計算リソースを指定しなくて良い ○ Googleアカウントがあれば使える(Google Workspaceを導入している会社にならすぐ使える) ○ Web画面上でのクエリの整形 ○ クエリ結果をGoogle Spread Sheet、別テーブル、クリップボードに保存 ○ Web画面でのデータアップロード。アップロード時の列名と型推定。 ■ Snowflakeの新UIはできない。 ■ Snowflakeの旧UIならできるが、画面動線がわかりにくい。かつテーブル定義やフォー マット定義を先にする必要がある ○ Web画面上での列のdescriptionの書き換え ■ ただし、テーブルをdropするとdescriptionも消えちゃう ○ GCP内のDBに直接クエリできる(Federated Query) ○ GoogleAnalytics、Fireabase、GooglePlayのストアデータなど、簡単にロードできる ユーザにとっての使い勝手　ー まとめ 18


BigQuery と Snowflake の徹底比較 管理者にとっての使い勝手 2.2 19


● CLIはSnowflakeのほうが使いやすい。 ● BigQuery ○ 「bq」コマンドがあるが、APIラッパーといった感じであり、便利とは言えない。 ○ 「gcloud」コマンドも必要になることがある。 ● Snowflake ○ 「snowsql」というCLIツールがある ○ SQLに補完が効くし、表示もきれい ○ snowflakeは全てSQLで管理できるため、snowsql があれば管理はすべて完結する CLI 比較 20


データロード比較 データソース BigQuery Snowflake オブジェクト ストレージ ファイルの手動 ロード ・bq コマンド ・LOAD DATA文 ・ステージ＋COPY文 ファイルの定期 ロード ・BigQuery Scheduler + 外部テーブル ・BigQuery Data Transfer Service ・Task (定期実行/ワークフロー) + ステージ + COPY文 ファイルが置か れたら自動で ロード ・Cloud Functions + LOAD DATA文 ・SnowPipe + ステージ + COPY文 DB ・独自の作り込み ・ETL製品の利用 ・GCP内部がデータソースならばfederated query(外部テーブル)やdatastreamが利用可能 ・独自の作り込み ・ETL製品の利用 ストリームデータ ・ストリーミングインサート ・Snowpipe Streaming (2022年11月時点でプレビュー※1） ※1) https://resources.snowflake.com/ja/2022-data-cloud-world-tour-japan/s2-3-20221024fix ● Snowflakeは単体でもデータロードの機能を備えている。 ● BigQueryは、GCPの様々な機能と連携して、データをロードする 21
 表：データソース毎の典型的なデータロードパターン

GCP プロジェクト ● Cloud Pub/SubとCould Functionsを利用した、GCSにファイルが置かれたらロードする方法 データロード比較　ー　BigQuery　ー　ファイルが置かれたらロード Cloud Pub/Sub Cloud Functions 対象テーブ ル 2.オブジェクト 作成通知 3. 4. GCS 1.作成 LOAD API 呼び出し BigQuery 参考：https://dev.classmethod.jp/articles/cloud-functions-gcs-trigger-load-data2bigquery/　　 22
 サービスアカウント BigQuery LOADジョブ 5. 使う ・ジョブが実行できる ・テーブルにアクセスできる ・GCSにアクセスできる

● ステージ、COPYコマンド、SnowPipeを利用した、S3にファイルが置かれたらロードする方法 データロード比較　ー　Snowflake　ー　ファイルが置かれたらロード（AWSの場合） SnowflakeのAWS データベース 仮想Wrewhouse ユーザのAWSアカウント 通知受けるSQS SnowPipe 対象テーブ ル 2.オブジェクト作成通知 snowpipeの 実行ユーザ このロールを 利用できる (assume roleできる) S3にアクセスでき る 3.起動 ユーザのS3 1.作成 COPY コマンド IAMロー ル IAM ポリシ 使う ステージ (=パスのprefix) 4.ファイルロード 参考:https://docs.snowflake.com/ja/user-guide/data-load-snowpipe-auto-s3.html 23
 0.ステージの定義

● BigQuery ○ GoogleWorkspaceなどSaaSと同じ発想 ■ Googleアカウントのユーザごとのアクセスコントロール ■ GCPのIAMで制御 ■ GoogleGroupでロールに相当する管理は可能 ● Snowflake ○ PostgreSQLなどのRDBMSと同じ発想 ■ ロールごとのアクセスコントロール ● クエリを発行する前にロールを指定する必要がある ○ 例１：GRANT文の発行はSECURITYADMINロール ○ 例２：普段の分析はANALYSTロール ○ →管理者にとっては慣れると使いやすいが、ユーザにとっては１手間増える 　（デフォルトのロールを指定すれば回避可能） アクセスコントロール比較 24


● Snowflakeのクエリ履歴のほうが使いやすい クエリ履歴比較　 Snowflakeのクエリ履歴画面 BigQueryのクエリ履歴画面 25
 画面から ユーザや処理時間での 絞り込みが容易にできる 処理時間が表示される （内訳も色で表現）

セキュリティ比較 BigQuery Snowflake ユーザ認証（多要素認証・シングルサインオ ン・Oauth） ○（Googleアカウントでログイン） ○ 暗号化（鍵持ち込み可能） ○ ○ タイムトラベル（過去のテーブルへのクエリ） ○（デフォルトで７日前まで、それ以降はスナッ プショットで代用） ○（最大９０日＋７日間の fail-safe） 列レベルアクセスコントロール ○ ○ 行レベルアクセスコントロール ○ ○ タグ付けとアクセスコントロール (列レベルで) ○（Policy Tag機能） ○ データ共有の管理 ○（Googleアカウントがあれば誰でも） ○ VPC(AWS or GCP) or VNet(Azure)内への展開 （VPC Service Controllで代用） ○ 26
 基本的な事項はどちらも完備

● Snowflakeのほうが厳格な管理ができる。管理者フレンドリーと感じる ● Snowflakeが優位だと感じる点 ○ SQLだけで管理が簡潔 ○ CLIが使いやすい ○ 厳格な計算リソース管理ができ、課金死しない ○ クエリ履歴が見やすい ○ VPC内部へのデプロイ ○ 簡単なIP制限 管理者にとっての使い勝手　ー まとめ 27


BigQuery と Snowflake の 処理速度比較 2.3 28


● 結局SnowflakeとBigQueryどっちが速いのか？ ○ →結論：業務による。やってみないとわからない ● 世間の評価 ○ このブログ　では「どちらも優れたパフォーマンス」と評価。 ○ このブログ　では「Snowflakeは通常、BigQuery や Redshift と比較した場合、TPC ベース のパブリック ベンチマークでのパフォーマンスに関して、ほとんどのクエリでトップになり ますが、ごくわずかです。」と評価 ○ 2019年のこのブログ　ではSnowflakeがBigQueryよりも速く安いという結論。しかしそれら よりもAzure SQL Data Warehouseが一番いいという結論。 ● 渡部の所感 ○ さすがにBigQueryのほうが底力あると思う ■ Snowflake ● どのクラウドでも動くかわりに、ハードウェア最適化の恩恵がうけられない。 ■ BigQuery ● GCPの膨大なハードウェアを使える。 ○ 双方のアーキテクチャを見るとそう感じる　→次頁以降で説明 処理速度比較 29


Snowflake 専用VPC 処理速度比較　ー　Snowflakeのアーキテクチャ （AWSを利用した場合） EC2 EC2 クラウドサービス 3.SQL 4.実行結果 クライアント 1.データロード マイクロパーティションとよ ばれる方法で分割 専用の列指向フォーマット で格納 Snowflake専用S3 キャッシュ キャッシュ 仮想Warehouse ユーザのS3 EC2上の計算リソースの集 合を仮想Warehouseとして 指定 2.計算リソース の指定 ネットワーク EC2とS3間は最大 100 Gbps 30


処理速度比較　ー　BigQueryのアーキテクチャ Dremel on Borgクラスタ BigQuery API 3.SQL 4.実行結果 クライアント 1.データロード Colossusストレージ ユーザのGCS どれだけ計算リソースを使うか動 的に計算 できるだけ短期間に処理をおらわ せるようにする slot ・・・ ︙ ︙ ・・・ 数万のマシンと数十万のコア を割当 GCPの全体で使ってるスト レージ。数十ペタバイトまで シームレスにスケーリング。 引用元： https://cloud.google.com/blog/products/bigquery/bigquery-under-the-hood?hl=en Jupiterネットワーク 1Pbps。100,000 台の マシンが 10 Gbs で他のマシ ンと通信できる 31


まとめ 3 32


ユーザに優しいBigQuery ● 計算リソース指定しなくてもいい ● Googleアカウントがあれば誰でも使える ● WebUIでデータのアップロードやエクス ポートがしやすい。特にGoogle Spread Sheet連携 ● 画面でSQLが整形できる ● Google系のサービスのデータを簡単に ロードできる 渡部の所感 管理者に優しいSnowflake ● GCPじゃなくても動く ● 計算リソースの管理が厳格で課金死しない ● VPC内部に閉じ込められて安心 ● IPアドレス制限が簡単 ● クエリ履歴見やすい ● SQLでなんでもできる ● CLIでなんでもできる データベース Webサービス 受ける印象は 33


補足資料 34


35
 補足資料：勉強会の登録時のアンケート結果 アンケートフォーム 結果 データウェアハウ ス チェックされた数 BigQuery 136 Snowflake 82 Redshift 52 その他 26 databricks 8 Synapse 4 合計 308 ※複数選択可