2022.04.01

【トップゲート主催】ゲーム業界様向けGCP活用のポイント〜BigQuery編〜

BigQuery
GGG Study
ゲーム
データ分析

弊社トップゲートではGoogle Cloudのプレミアパートナーとして、Google Cloudに関する様々なセミナーを開催しています。この記事では、ゲーム業界様向けにGCPの使い方を解説した「GGG Study」の内容についてご紹介します。

GGG Studyは全6回に分けてGCPの各スキルを習得していただくための勉強会です。第4回では、BigQueryについて解説していきます。

BigQueryの概要

まずはBigQueryについての基本的な情報をご紹介します。

BigQueryとは

BigQueryとは、ペタバイトクラスのデータを瞬時に分析できるフルマネージドのデータウェアハウスです。標準のSQL言語に対応しており、標準的なRDB( Relational Database)のような正規化されたテーブルを利用することも可能です。

また、コネクターにより様々な可視化ツールとの連携を容易に実現することができます。

設計思想

Dremel
BigQueryはGoogle社内で利用されていたDremelというデータ分析システムを外部ユーザー向けに改良し、公開したものです。Dremelではテラバイトスケールのデータ解析を秒単位で実現するため、膨大な数のディスクを並列させて稼働させる検証が行われていました。その経験やノウハウがBigQueryに継承されています。

BigQueryの特徴

BigQueryの特徴をいくつかご紹介します。

パフォーマンス

BigQueryの特徴はなんといってもその高速なデータ処理です。上図の例では、17億件のデータのフルスキャンが6.7秒で完了しています。

BIツールとの連携

また、BigQueryではBIツールと連携することにより、非技術者でも簡単に操作することができます。例えばTableauやQuickView、Google SpreadsheetやMicrosoft Excelといったソフトウェアを利用することが可能です。

ETL（Extract/Transform/Load）

BigQueryでは、分析に必要なデータ加工を行うことも可能です。例えば、膨大な元データに対する検索のクエリ結果をBigQueryのテーブルとして出力することができます。

データ処理エンジンとの連携

HadoopやCloud Dataflowといったデータ処理のエンジンと連携することができます。複雑なデータ加工や、リアルタイムによるデータストリーミング分析などを実現することが可能です。

迅速にデータ分析を開始

優位性
従来のデータウェアハウスでは、そもそもデータ分析を開始するまでに時間が必要でした。インフラ基盤を用意し、様々なセットアップを行なってからデータウェアハウスソフトを導入して初めて分析を開始することができます。運用もメンテナンスやアップグレードなどの考慮が必要です。

一方、BigQueryではサーバーレスとして設計されています。インフラはもちろん、ソフトのインストールやメンテナンスが不要なマネージドサービスとして提供されているため、分析業務に注力することができます。

BigQueryによるデータ分析

ここからは実際にBigQueryによる分析の流れを確認し、データ分析の方法についてご紹介します。今回はパブリックデータセットを用いてデータ分析を行います。

前提知識

分析に入る前に、BigQueryのリソースに関するいくつかの用語を解説します。
前提知識

プロジェクト

GCPにおけるリソースの管理単位です。全てのリソースはプロジェクトに紐付けられ、プロジェクト単位でユーザー管理や課金を行います。

データセット

テーブルの集合体です。IAMの権限設定はデータセット単位で行います。

テーブル

データの集合体で、一般的なRDBのテーブルとほぼ同義になります。

将来的には、テーブル単位でのIAMの権限設定が可能になることが予想されます。

参照：テーブルへのアクセス制御の概要

1. パブリックデータセットをプロジェクトに追加

パブリックデータセットとは？
参照：パブリックデータセット

パブリックデータセットとは、様々な分野において一般公開されているデータを成形し、データセットとしてGoogleが提供しているものです。すぐに利用することができ、誰でもクエリを実行することができるようになっています。今回はこのパブリックデータセットを利用して分析を行います。

データセットの追加
パブリックデータセットを利用するためには、GCPが全てのユーザーに公開しているプロジェクトをリソースツリーに追加する必要があります。bigquery-public-dataプロジェクトを追加することで、その配下にあるパブリックデータセットを利用することができるようになります。

2. SQLを書く

今回はWikipediaにおける2015年12月の記事のビュー数を集計し、結果を表示するSQLを実行します。SQLの構文を解説する前に、まずは使用するデータについてコンソールで確認してみます。

テーブルの選択
追加したデータセットの中からwikipediaデータセットのpageviews_2015テーブルを選択すると、そのテーブルの情報を確認することができます。

「スキーマ」タブを見るとそれぞれのフィールド名やデータタイプ等を見ることができます。

また、「詳細」タブを見るとデータサイズや行数を確認することができます。今回の例では1.5TBのデータ容量を持っており、368億行の行数を持つかなり大きな規模のテーブルであることがわかります。

「プレビュー」タブでは、実際のデータを見ることができます。
データセットの内容が確認できたところで実際にデータの検索を行なっていきます。

SQL文
wikipediaデータセットのpageviews_2015テーブルの中から検索を行います。datehourが12月1日以降のデータに関して、SELECT文とSUM関数でviewsを集計し、そのtitleを取得します。最後にviewsの多いものから順に100タイトルを表示します。

3. クエリ実行

BigQueryのコンソールからクエリを実行します。画面上のエディタにクエリを記載すると、クエリでスキャンされるデータの容量が表示されます。問題なければ「実行」ボタンを押下してクエリを実行します。

クエリ結果
クエリの実行結果が表示されます。12月の中ではトップページが一番view数が多かったことがわかります。

そして、今回は、155.5GBのデータ量を9.4秒で処理したことも同時にわかります。

料金モデル

最後にBigQueryの料金体系についてお話しします。

オペレーション	料金	詳細
アクティブストレージ	$0.023 /GB	毎月10GBまで無料
長期保存	$0.016 /GB	毎月10GBまで無料
ストリーミング挿入	$0.012 /200 MB	挿入に成功した行が課金対象最小サイズは1KBで計算
クエリ	$6.00 /TB	毎月1TBまで無料

上の表は2020年7月時点での東京リージョンにおける1ヶ月あたりの料金を表しています。BigQueryでは、無料枠が用意されており、範囲内であれば無料で利用することができます。また、長期保存は90日間UPSERTのなかったテーブルに適用されます。

例えばアクティブストレージを100GB利用し、クエリは月6TBを想定すると、その利用料金は
100 GB × $ 0.023 + 6 TB × $ 6.00 - 無料枠
= $ 38.3 - $ 6.23
= $ 32.07 /月
となります。

コスト最適化機能

BigQueryでは下記の機能を利用することで、クエリを実行する際に効率的にスキャンを行うことが可能です。これにより、オンデマンドの料金を削減し、コスト最適化を図ることができます。

パーティション分割テーブル

大きなテーブルを小さなパーティションに分割し、クエリを実行した際にスキャンしたバイト数を減らすことによってパフォーマンスとコスト削減を最適化する機能です。

例えば1年間の売り上げが日付とともに格納されているテーブルでこの機能を利用すると、1日ごとにパーティションを分割することができます。クエリを実行した場合にはテーブル全体がスキャンされるのではなく、パーティションごとに実行されます。また、ユーザーから見ると1つの大きなテーブルに見えています。

クラスタ化テーブル

クラスタ化テーブルを利用することで、テーブルの列の内容に基づいてデータが自動的にソートされます。これにより、フィルタ句を使用するクエリやデータ集計のクエリなど、特定のタイプのクエリはフルスキャンをする必要がなくなり、コスト最適化を図ることができます。

キャッシュ

BigQueryでは、1度実行したクエリ結果のテーブルは24時間キャッシュされます。同じクエリを重複して実行すると、キャッシュに保存した結果が利用されます。

カスタムコスト管理

1日に処理されるクエリのデータ量に対して上限を設定することができます。これにより想定していたコストを超えない範囲で利用することが可能です。

定額料金

BigQueryでは、オンデマンド料金に加えて、定額料金体系を提供しています。定額料金ではスロットと呼ばれる処理能力の単位を購入し、購入した分のリソースに関しては追加料金なく利用することが可能です。しかし、クエリを実行した場合に容量が割り当てられたスロット数を超過する場合はキューに格納され、ジョブとして格納されることになります。