データサイエンスとデータベース

Google BigQueryを使用する意味があるのはいつですか？

私のキャリアの過程で、さまざまな種類のレポートやチャートに対して複雑な分析クエリを作成する必要がありました。ほとんどの場合、日付、週、四半期などで集計されたデータを表示するグラフでした。通常、このようなレポートは、クライアントが傾向を特定し、ビジネスが高レベルでどのように実行されているかを示すのに役立つように作成されます。しかし、何が起こるかデータサイエンティストとエンジニアビッグデータセットに基づいて、はるかに広範なレポートを作成する必要がありますか？

レポートが小さなデータセットに基づいている場合は、リレーショナルデータベースの下にSQLクエリを作成することでタスクを解決できます。このステップでは、クエリを作成するための基本と、クエリをより高速かつ効率的にする方法を知ることが重要です。ただし、レポートがより多くのデータセット（たとえば、テーブル内の数百万以上の行）に依存している場合もありますが、レポートは入力変数（パラメーター）に依存していないか、値の数が非常に少ない場合があります。このようなシナリオでは、SQLクエリが遅くなる可能性があるため、ユーザーがクエリが実行されるまで待つのは最適ではありません。このような場合の最も一般的な方法は、クライアントがレポートを要求する前に、事前にクエリを実行することです。

また、クライアントがリアルタイムでクエリを実行する代わりにキャッシュからデータを取得できるように、いくつかのキャッシュ機能を実装する必要があります。このアプローチは、リアルタイムのデータを表示する必要がない限り、完全に機能します。 1時間または1日前に計算されたデータを表示できます。したがって、実際のレポート/グラフは、リアルタイムデータに基づくのではなく、キャッシュされたデータを使用して表示されます。

GoogleBigQueryに目を向ける

製薬業界で分析プロジェクトに取り組んでいる間、郵便番号と薬名を入力パラメーターとして使用するチャートが必要でした。また、米国の特定の地域における薬物間の比較を示す必要がありました。

分析クエリは非常に複雑で、Postgresサーバー（16 GB RAMのクアッドコアCPU）で約50分実行されました。クエリは郵便番号と薬を入力パラメータとして使用していたため、事前に実行して結果をキャッシュすることができませんでした。そのため、何千もの組み合わせがあり、どのクライアントが選択するかを予測することはできませんでした。

すべての入力パラメーターの組み合わせを実行しようとしても、データベースがクラッシュする可能性があります。そこで、別のアプローチを選択し、使いやすいソリューションを選択するときが来ました。このチャートはクライアントにとって重要でしたが、クライアントはアーキテクチャに大きな変更を加えたり、別のDBに完全に移行したりする準備ができていませんでした。

その特定のプロジェクトで、いくつかの異なるアプローチを試しました。

サーバーの垂直スケーリング（PostgresサーバーにRAMとCPUを追加）
AmazonRedshiftなどの代替DBを使用する。
NoSQLソリューションも調査しましたが、それらのほとんどは非常に複雑で、アーキテクチャに多くの変更を加える必要があり、その多くはクライアントにとって大きすぎます。

最後に、 Google BigQuery 。それは私たちの期待に応え、クライアントが承認することを躊躇するような大きな変更を加えることなく仕事を成し遂げることを可能にしました。しかし、Google BigQueryとは何で、どのように機能しますか？

BigQueryはRESTベースのウェブサービスであり、大量のデータセットの下で複雑な分析SQLベースのクエリを実行できます。データをBigQueryにアップロードし、実行したのと同じクエリを実行した後 Postgres （構文は不気味に似ています）、クエリははるかに高速に実行され、完了するまでに約1分かかりました。最終的には、別のサービスを使用するだけで、パフォーマンスが50倍向上しました。他のDBが同じパフォーマンスの向上を実現していなかったことは注目に値します。寛大に、単にそれらが近くにさえなかったと言いましょう。正直なところ、BigQueryによって提供されるパフォーマンスの向上には本当に感銘を受けました。数字は、私たちの誰もが期待していたよりも優れていたからです。

それにもかかわらず、私はBigQueryを世界で最高のデータベースソリューションとして宣伝するつもりはありません。私たちのプロジェクトではうまく機能しましたが、1日あたりのテーブルの更新数の制限、リクエストごとのデータサイズの制限など、まだ多くの制限があります。 BigQueryを使用してリレーショナルデータベースを置き換えることはできず、単純なCRUD操作やクエリではなく、分析クエリの実行を目的としていることを理解する必要があります。

この記事では、Postgres（私のお気に入りのリレーショナルデータベース）とBigQueryを実際のユースケースシナリオで使用して比較してみます。また、途中でいくつかの提案をします。つまり、BigQueryを使用することが実際にいつ意味があるかについての私の意見です。

ますます多くの人々が一緒に働くことによって生じる非効率性は、次のように呼ばれます。

サンプルデータ

PostgresとGoogleBigQueryを比較するために、国、年齢、年、性別でグループ化された各国の公開人口統計情報を取得しました（これから同じデータをダウンロードできます）リンク）。

データを4つのテーブルに追加しました。

populations
locations
age_groups
populations_aggregated

最後のテーブルは、前の3つのテーブルからの集計データです。 DBスキーマは次のとおりです。

サンプルデータのデータベーススキーマ。

populations最終的に作成されたテーブルには、690万行を超える行が含まれています。それほど多くはありませんが、私のテストには十分でした。

サンプルデータに基づいて、実際の分析レポートやチャートの作成に使用できるクエリを作成しようとしました。そこで、次のレポートのクエリを準備しました。

米国の人口は年ごとに集計されています。
最大の国から始まるすべての国の2019年の人口。
毎年上位5つの「最も古い」国。「最古」とは、総人口に占める60歳以上の割合が最も高い国を指します。クエリでは、毎年5つの結果が得られるはずです。
男性と女性の人口の差が最も大きい、年ごとに集計された上位5か国。
「最も古い」国から「最も若い」国まで、各国の年齢の中央値（平均）を取得します。
毎年上位5つの「死にかけている」国を見つけます。「死にかけている」とは、人口が減少している国を意味します（人口減少が最も高い）。

クエリ＃1、＃2、および＃6は非常に簡単で簡単ですが、クエリ＃3、＃4、および＃5は、少なくとも私にとってはそれほど簡単に書くことはできませんでした。私はバックエンドエンジニアであり、複雑なSQLクエリを作成することは私の専門ではないため、SQLの経験が豊富な人なら、よりスマートなクエリを作成できる可能性があることに注意してください。ただし、現時点では、PostgresとBigQueryが同じデータで同じクエリをどのように処理しているかを確認する必要があります。

合計24のクエリを作成しました。

非集約テーブルを使用しているPostgresDBの場合は6（populations、locations、age_groups）
populations_aggregatedを使用しているPostgresDBの場合は6テーブル
集計テーブルと非集計テーブルを使用しているBigQueryの6 + 6クエリ。

集計データのBigQueryクエリ＃1と＃5を共有して、単純な（＃1）クエリと複雑な＃5クエリの複雑さを理解できるようにします。

年ごとに集計された米国の人口クエリ：

select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

毎年、国ごとの年齢の中央値を古いものから若いものへと並べ替えてクエリします。

--converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

注：すべてのクエリは私のbitbucketリポジトリにあります（リンクは記事の最後にあります）。

試験結果

クエリを実行するために、2つの異なるPostgresサーバーを使用しました。最初のものは、SSDドライブに支えられた1つのCPUコアと4GBのRAMを備えています。 2番目のサーバーには16個のCPUコアと64GBのRAMがあり、SSDドライブも使用していました（2番目のサーバーには16倍のCPUとRAMの可能性があります）。

また、テストの実行中にデータベースに負荷がかかっていないことに注意してください。クエリを実行するためだけに作成しました。実際の状況では、他のクエリが同時に実行される可能性があるため、クエリに時間がかかります。さらに、並行して実行されるクエリはテーブルをロックする可能性があります。クエリ速度を確認するために、pgAdmin3とBigQueryWebインターフェイスを使用していました。

私のテストでは、次の結果が得られました。

macosはどの言語で書かれていますか

	Postgres （1 CPU 4 RAM、SSD）		Postgres （16 CPU 64 RAM、SSD）		BigQuery
	集約	集約されていない	集約	集約されていない	集約	集約されていない
クエリ1（年ごとに集計された米国の人口）	1.3秒	0.96秒	0.87秒	0.81秒	2.8秒	2.4秒
クエリ2（2019年の国別人口）	1.1秒	0.88秒	0.87秒	0.78秒	1.7秒	2.6秒
クエリ3（年別の最古の上位5か国）	34.9秒	35.6秒	30.8秒	31.4秒	15.6秒	17.2秒
クエリ4（男性と女性の人口の差が最も大きい上位5か国）	16.2秒	15.6秒	14.8秒	14.5秒	4.3秒	4.6秒
クエリ5（国ごとの年齢中央値、年）	45.6秒	45.1秒	38.8秒	40.8秒	15.4秒	18代
クエリ6（年間の「死にかけている」国のトップ5）	3.3秒	4.0秒	3.0秒	3.3秒	4.6秒	6.5秒

これらの結果をクエリ＃1とクエリ＃5の棒グラフで表示します。

クエリ1および5のクエリ結果。

注意： Postgresデータベースは米国ベースのサーバーにあり、私はヨーロッパに拠点を置いているため、Postgresのデータ送信にさらに遅延がありました。

BigQueryのパフォーマンスと結論

得られた結果に基づいて、私は次の結論を出しました。

角度4フォーム検証の例

Postgresを垂直方向にスケーリングする場合、16倍でも、単一のクエリを実行した場合のパフォーマンスは10〜25％にすぎません。つまり、CPUコアが1つとRAMが4GBしかないPostgresサーバーは、CPUコアが16とRAMが64GBのサーバーに必要な時間と非常に似た時間でクエリを実行していました。もちろん、より大きなサーバーははるかに大きなデータセットを処理できますが、これではクエリの実行時間はそれほど改善されません。
小さなテーブル（locationsテーブルには約400行、age_groupsには100行）を使用したPostgres結合の場合、1つのテーブルにある集計データでクエリを実行する場合と比較して大きな違いはありません。また、1〜2秒実行されているクエリの場合、内部結合を使用したクエリの方が高速ですが、実行時間の長いクエリの場合は状況が異なります。
結合を使用するBigQueryの状況は、まったく異なります。 BigQueryは結合が好きではありません。集計データと非集計データを使用するクエリ間の時間差は非常に大きくなります（クエリ＃3と$ 5の場合は約2秒でした）。つまり、BigQueryの場合、必要な数のサブクエリを実行できますが、パフォーマンスを向上させるには、クエリで1つのテーブルを使用する必要があります。
Postgresは、単純な集計やフィルタリングを使用しているクエリ、または小さなデータセットを使用しているクエリの方が高速です。 Postgresで5秒未満しかかからないクエリは、BigQueryでは動作が遅くなることがわかりました。
BigQueryは、長時間実行されるクエリに対してはるかに優れたパフォーマンスを発揮します。データセットサイズの違いが大きくなると、これらのクエリが完了するまでにかかる時間の違いも大きくなります。

BigQueryを使用することが理にかなっている場合

それでは、この記事で説明した主要な問題に戻りましょう。 実際にGoogleBigQueryを使用する必要があるのはいつですか。 私の結論に基づいて、次の条件が満たされたときにBigQueryを使用することをお勧めします。

リレーショナルデータベースで5秒を超えて実行されるクエリがある場合に使用します。 BigQueryの考え方は、複雑な分析クエリを実行することです。つまり、単純な集計やフィルタリングを行うクエリを実行しても意味がありません。 BigQueryは、大量のデータセットを使用して動作する「重い」クエリに適しています。データセットが大きいほど、BigQueryを使用してパフォーマンスを向上させる可能性が高くなります。私が使用したデータセットはわずか330MB（メガバイト、ギガバイトでさえありません）でした。
BigQueryは結合を好まないため、実行時間を短縮するには、データを1つのテーブルにマージする必要があります。 BigQueryを使用すると、クエリ結果を新しいテーブルに保存できるため、新しい集計テーブルを作成するには、すべてのデータをBigQueryにアップロードし、すべてのデータを統合するクエリを実行して、新しいテーブルに保存するだけです。
BigQueryは、キャッシュが組み込まれているため、データが頻繁に変更されず、キャッシュを使用するシナリオに適しています。これは何を意味するのでしょうか？同じクエリを実行し、テーブルのデータが変更（更新）されていない場合、BigQueryはキャッシュされた結果を使用するだけで、クエリの再実行を試みません。また、BigQueryはキャッシュされたクエリに料金を請求していません。 注意： キャッシュされたクエリでさえ、結果を返すのに1〜1.2秒かかります。
リレーショナルデータベースの負荷を軽減したい場合は、BigQueryを使用することもできます。分析クエリは「重い」ものであり、リレーショナルデータベースでそれらを使いすぎると、パフォーマンスの問題が発生する可能性があります。そのため、最終的にはサーバーのスケーリングについて考えることを余儀なくされる可能性があります。ただし、BigQueryを使用すると、これらの実行中のクエリをサードパーティサービスに移動できるため、メインのリレーショナルデータベースに影響を与えることはありません。

最後に、実際の生活でBigQueryを使用する方法についてもう少し説明します。実際のプロジェクトでは、レポートのデータが週単位または月単位で変更されていたため、BigQueryにデータを手動でアップロードできました。ただし、データが頻繁に変更される場合は、リレーショナルデータベースとBigQueryの間でデータを同期するのはそれほど簡単ではない可能性があり、これは覚えておく価値のある警告です。