By Ajay Lakshminarayanarao and Allen Fung

Google Bigqueryは強力なツールです。SQLについて最低限の知識しかないソフトウェアエンジニアでも、簡単に使うことができます。大量のデータを数秒で分析することができます。ShareThis では、何テラバイトもの実用的なソーシャルデータを毎日見ており、分析、チェック、処理を行う上で不可欠な要素となっています。

最近直面した問題の一つに、Bigquery(BQ)テーブルの行を国に基づいて分離することがありました。BQは列挙型ストレージをベースにしています。BQは、レコード全体を1つのボリュームに格納する代わりに、列値に基づいて行を分割し、各値をそれぞれのストレージボリュームに格納します。この方式を利用するために、私たちは以下のように各国の値を取得するための効率的な低コストのクエリソリューションを設計しました。

問題：BQテーブルを国別に分離するには
わかりやすい方法

BQテーブルを国別に分離するためには、国の数だけテーブルを照会する必要があります。

A、B、C、D、E の各列を照会する必要があるとします。各列のサイズは 20GB です。課金可能なバイト数は、このテーブルでは100GBとなります。

国の数だけクエリを実行する必要があるとすると、請求可能なバイト数は24,000GB、つまり24TB近くになります。1TBの料金を5ドルとすると、1つのBQテーブルにかかる総費用は120ドルとなります。

最適化された方法

ここでのコツは、必要な数のフィールドを1つのカラムとしてグループ化することです。これだけでは請求バイト数は減りません。2つ目のコツは、分離した列でピボットすることです。この場合は国別になります。

以上の手順を踏むと、結果のテーブルには国の数だけの列が含まれることになります。各列は国のデータを表し、その国だけの前にグループ化されたデータを持ちます。

100GBのBQテーブルでは、米国を表す列が20GB、主要10カ国が平均3GB程度、残りの230カ国が平均0.5GB程度となり、確率的な結果が得られます。

さて、課金バイトは各国のデータの大きさに基づいています。アメリカへの問い合わせにかかる課金額はわずか0.1ドル、主要国への問い合わせにかかる課金額はそれぞれ0.015ドル、その他の国はそれよりもはるかに小さい金額になります。

課金可能な総バイト数は、グループ化されたテーブルで100GB、すべての国を組み合わせたクエリで165GBとなります。合計コストは約1.5ドルです。

グループ化されたテーブルの生成

CONCATを使って、必要な列を1つの列にまとめることができます。CSVファイルの一行を表す形式や、有効なJSONオブジェクトで行を作成することができます。

第2段階では、240個のCASE文からなるクエリを作成し、国ごとにCASE文を作成して特定の列にピボットさせます。このクエリは大きくなりますが、BQは最大256KBまでのクエリサイズをサポートしています。そこで、この2つのステップを1つのクエリにまとめ、各case文を連結します。

クエリの例。

SELECT
( CASE WHEN geo.ISO = 'US' THEN CONCAT( A,B,C,D,E ) )AS COUNTRYUS,
( CASE WHEN geo.ISO = 'CA' THEN CONCAT( A,B,C,D,E ) )AS COUNTRYCA ...
FROM
MERGELOG_20160101です。