最初にBigQueryとSnowflakeの概要と、登場の背景を説明します。 その後、ユーザにとっての使い勝手と、管理者にとっての使い勝手を、ベンダーフリーな立場でそれぞれします。 最後に、BigQueryとSnowflakeどっちが速いのか？といった疑問に対して、アーキテクチャをもとに考察します。
BigQuery と Snowflake を徹底比較2022-12-21渡部徹太郎風音屋TechTalk #21
View Slide
ID ：fetaro名前：渡部 徹太郎学生：東京工業大学でデータベースと情報検索の研究(@日本データベース学会)職歴： *大手SIer - オンライントレードシステム基盤 - オープンソース技術部隊 * 大手Web - ビッグデータ分析基盤 * ベンチャー - データエンジニア* 風音屋アドバイザーエディタ：emacs派→ InteliJ派趣味：ギター、麻雀、自宅サーバ自己紹介2
● 1. BigQuery と Snowflakeの概要と登場の背景● 2. BigQuery と Snowflakeの徹底比較○ 2.1 ユーザにとっての使い勝手比較○ 2.2 管理者にとっての使い勝手比較○ 2.3 処理速度比較● 3. まとめ目次3
本資料の内容は、個人で調査できる範囲で調査した結果をもとに記載しています。もし間違いがあればご指摘ください。申し送り事項4
BigQuery と Snowflakeの概要と登場の背景15
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とSnowflake8
DWHの歴史とBigQueryとSnowflake2010 20202015~ 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-2018Snowflakeのリリース：https://docs.snowflake.com/en/release-notes.htmlHadoopの歴史：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 202210
BigQuery と Snowflake 徹底比較ユーザにとっての使い勝手2.111
機能比較BigQuery snowflakeDDL, 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.htmlBigQuery機能一覧：https://cloud.google.com/bigquery/docs/ 12 よく使う機能はどちらも完備
Web UI比較 ー BigQueryテーブル一覧SQL開発実行結果データ追加ボタンGoogleアカウントでログインプロジェクトの選択データセット一覧プロジェクト一覧結果保存ボタンデータ探索ボタンtab13 特徴的な箇所共通する箇所凡例
Web UI比較 ー Snowflakeテーブル一覧ロールの選択 計算リソースの選択実行結果SQL開発スキーマ一覧データベース一覧列の説明値の統計値（１万行以下の場合）14 ワークシート画面
ワークシートという画面もあり、作業毎に使い分けられるWeb UI比較 ー Snowflake ー ワークシート画面15
● Classic Consoleという旧バージョンのUIもあるWeb UI比較 ー Snowflake ( Classic Console）ワークシートはtab16
● Snowflakeはクエリを発行する前に計算リソースである「仮想Warehouse」を指定する(※1)○ 仮想Warehouseはサイズごとにあり、サイズ毎にcreditがことなる○ creditの金額はプランと地域で決まる。計算リソース指定 ー Snowflakeのみ サイズ credit 金額XSmall 1 credit / h $2.85 / hSmall 2 credit / h $5.70 / hMedium 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.219
● 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内部がデータソースならばfederatedquery(外部テーブル)や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/SubCloud Functions対象テーブル2.オブジェクト作成通知3.4.GCS1.作成LOAD API呼び出しBigQuery参考：https://dev.classmethod.jp/articles/cloud-functions-gcs-trigger-load-data2bigquery/ 22 サービスアカウントBigQueryLOADジョブ5.使う・ジョブが実行できる・テーブルにアクセスできる・GCSにアクセスできる
● ステージ、COPYコマンド、SnowPipeを利用した、S3にファイルが置かれたらロードする方法データロード比較 ー Snowflake ー ファイルが置かれたらロード（AWSの場合）SnowflakeのAWSデータベース仮想WrewhouseユーザのAWSアカウント通知受けるSQSSnowPipe対象テーブル2.オブジェクト作成通知snowpipeの実行ユーザこのロールを利用できる(assume roleできる)S3にアクセスできる3.起動ユーザのS31.作成COPYコマンドIAMロールIAMポリシ使うステージ(=パスのprefix)4.ファイルロード参考:https://docs.snowflake.com/ja/user-guide/data-load-snowpipe-auto-s3.html23 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.328
● 結局SnowflakeとBigQueryどっちが速いのか？○ →結論：業務による。やってみないとわからない● 世間の評価○ このブログ では「どちらも優れたパフォーマンス」と評価。○ このブログ では「Snowflakeは通常、BigQuery や Redshift と比較した場合、TPC ベースのパブリック ベンチマークでのパフォーマンスに関して、ほとんどのクエリでトップになりますが、ごくわずかです。」と評価○ 2019年のこのブログ ではSnowflakeがBigQueryよりも速く安いという結論。しかしそれらよりもAzure SQL Data Warehouseが一番いいという結論。● 渡部の所感○ さすがにBigQueryのほうが底力あると思う■ Snowflake● どのクラウドでも動くかわりに、ハードウェア最適化の恩恵がうけられない。■ BigQuery● GCPの膨大なハードウェアを使える。○ 双方のアーキテクチャを見るとそう感じる →次頁以降で説明処理速度比較29
Snowflake 専用VPC処理速度比較 ー Snowflakeのアーキテクチャ （AWSを利用した場合）EC2EC2クラウドサービス3.SQL 4.実行結果クライアント1.データロードマイクロパーティションとよばれる方法で分割専用の列指向フォーマットで格納Snowflake専用S3キャッシュ キャッシュ仮想WarehouseユーザのS3EC2上の計算リソースの集合を仮想Warehouseとして指定2.計算リソースの指定ネットワークEC2とS3間は最大 100Gbps30
処理速度比較 ー BigQueryのアーキテクチャDremelonBorgクラスタBigQuery API3.SQL 4.実行結果クライアント1.データロードColossusストレージユーザのGCSどれだけ計算リソースを使うか動的に計算できるだけ短期間に処理をおらわせるようにするslot ・・・︙ ︙・・・数万のマシンと数十万のコアを割当GCPの全体で使ってるストレージ。数十ペタバイトまでシームレスにスケーリング。引用元：https://cloud.google.com/blog/products/bigquery/bigquery-under-the-hood?hl=enJupiterネットワーク1Pbps。100,000 台のマシンが 10 Gbs で他のマシンと通信できる31
まとめ332
ユーザに優しいBigQuery● 計算リソース指定しなくてもいい● Googleアカウントがあれば誰でも使える● WebUIでデータのアップロードやエクスポートがしやすい。特にGoogle SpreadSheet連携● 画面でSQLが整形できる● Google系のサービスのデータを簡単にロードできる渡部の所感管理者に優しいSnowflake● GCPじゃなくても動く● 計算リソースの管理が厳格で課金死しない● VPC内部に閉じ込められて安心● IPアドレス制限が簡単● クエリ履歴見やすい● SQLでなんでもできる● CLIでなんでもできるデータベースWebサービス受ける印象は33
補足資料34
35 補足資料：勉強会の登録時のアンケート結果アンケートフォーム結果データウェアハウス チェックされた数BigQuery 136Snowflake 82Redshift 52その他 26databricks 8Synapse 4合計 308※複数選択可
fetaro
ama_ch
makotu
nagiss
gl_tsukasa
soracom
torounit
claudioed
adavis
line_developers_tw
gawa
autifyhq
oracle4engineer
marktimemedia
vanstee
tmm1
matthewcrist
marcduiker
holman
gr2m
moore
hursman
mraible
morganepeng
eileencodes