• クラウドエースに関する記事
14分で読める

BigQuery データ分析入門ガイド

こんにちは。クラウドエース編集部です。
今回はBigQuery の入門ガイドと称し、基本から活用方法までを解説していきます。

はじめに

BigQuery とは何か

BigQuery は、Google Cloud が提供するフルマネージド型のクラウドデータウェアハウスです。ペタバイト級のビッグデータを高速に分析できる拡張性と、SQL でのインタラクティブな分析を特徴としています。

チュートリアルの目的と対象読者

このガイドは、BigQuery の基本的な使い方から実践的な活用方法までを学ぶことを目的としています。対象読者は、SQL の知識があり、データ分析に興味を持つエンジニアやデータアナリストを想定しています。

BigQuery の基本概念

BigQuery はプロジェクト、データセット、テーブルという 3 つのレイヤー構成によって管理されています。

プロジェクト

BigQuery を利用するための最上位の単位で、プロジェクトごとに料金を支払うことになります。
プロジェクト内には BigQuery 以外にも、Cloud Run や Cloud SQL などのサービスも含まれます。

データセット

BigQuery のテーブルをグループ化するための単位で、アクセス制御などを行う場合はデータセットの単位で行うことになります。

テーブル

実際のデータを格納する実体で、カラムの定義(スキーマ)を持ちます。
また、テーブルには以下の 3 つの種類があります。

  • 標準テーブル・・・通常のテーブルでカラムを定義してデータを格納します。
  • 外部テーブル・・・外部ストレージを参照することでテーブルとして扱えるようにしたものです。Amazon S3、Google Cloud Storage、Google Sheets など様々な外部ストレージを利用することができます。
  • ビュー・・・SQL クエリを使用して定義した論理的なテーブルです。格納されているデータの一部分だけを閲覧できるようにしたい場合や、他のテーブルと結合した結果を閲覧したい場合などに利用します。

BigQueryの設定

プロジェクトの作成

https://console.cloud.google.com/projectcreate
こちらの URL からチュートリアルで利用するための新しいプロジェクトを作成します。
既存のプロジェクトを利用する場合はこの手順は不要です。
プロジェクト名は任意で構いませんが今回は「bigquery-tutorial」で作成をしています。

サンプルデータセットの確認

https://console.cloud.google.com/bigquery
上記 URL に遷移すると以下のような画面が表示されます。
プロジェクト名が今回利用するプロジェクトなっており、検索画面で「games_wide」で検索すると以下のキャプチャの通りテーブルがヒットすることを確認してください。

このテーブルは BigQuery でデフォルトで登録されているサンプルのデータセットで、2016 年のメジャーリーグの試合のデータが入っており、一度の投球で一つのレコードが作成されています。
今回のチュートリアルでは主にこのデータを利用して BigQuery でデータ分析をしていきます。

「games_wide」テーブルを選択するとキャプチャのようにどのような種類のデータ(スキーマ)が登録されているか確認することができます。

フィールド名「gameId」が種類「STRING」、モード「NULLABLE」で登録されているということは、「gameId」は文字列のデータで、データが存在しない(NULL)の場合もあり得るフィールドであるということがわかります。

その他の種類やモードの説明はこちらをご参照ください。(https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#data_type_sizes)

次に、スキーマの 2 つ隣にあるタブ「プレビュー」タブを開いてください。こちらでは実際のデータを閲覧することができます。

「プレビュー」は実データを簡単に見てデータの利用イメージを掴むために頻繁に利用されます。

ここまでで、データセットにどのようなテーブルがありそこに登録されているデータを確認する方法を紹介しました。
ここからは、実際に SQL クエリを利用してデータを取得、分析していきます。

簡単なクエリの実行

基本的な SELECT

「クエリ」のプルダウンから「新しいタブ」を選択すると、クエリを入力する画面が表示されます。
デフォルトでクエリが記入されていますが、それらを削除し、以下のクエリを記入してください。

SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro'

このクエリは SELECT 句、FROM 句、WHERE 句で構成されています。

まず、「SELECT 句」です。これは取得するカラム(列の名前)を指定します。今回は「アスタリスク(*)」を指定していますが、これは SQL 構文で「全て」を意味する文字列です。必要なカラムが決まっている場合はカラム名を指定することでデータの可読性を良くし、クエリコストを削減することができます。

次に、「FROM 句」です。これはどこのテーブルからデータを取得するかを指定します。
今回は「bigquery-public-data.baseball.games_wide」と書きましたが、これは プロジェクト名.データセット名.テーブル名 というルールで記載をする必要があり、今回は「bigquery-public-data」プロジェクトの「baseball」データセットの「games_wide」テーブルを指定していることになります。(サンプルデータは bigquery-public-data プロジェクトに存在しているため、今回作成したプロジェクト名とは違いますが問題ありません。)
 
最後に「WHERE 句」です。これはデータを取得する条件を指定します。今回は「hitterFirstName」カラムの値が「Ichiro」であることを条件としています。
 
このクエリを「実行」すると取得結果を確認できます。
これで、2016 年のメジャーリーグでのイチロー選手がバッターとして打席に立った時の全データを取得することができました。
 
また「WHERE 句」は AND や OR を使って条件を組み合わせることができます。
例えば AND を利用する例として、Ichiro という選手が複数いるような場合では以下のように、条件を AND で複数追加することでさらにデータを絞り込むことができます。

SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' AND hitterLastName='Suzuki'

OR を使う場合の例として、イチロー選手がバッター、又は、ダルビッシュ選手がピッチャーとして出ていたデータを取得したい場合は以下のように OR 条件を使うことで一度のクエリで取得することもできます。
(同一のカラムに対して、OR 条件でクエリをしたい場合は IN 句を利用することも可能です。)

SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' OR pitcherLastName='Darvish'

取得データ数の指定(LIMIT 句)

必要なデータ数が決まっている場合や、データ量が多く、取得に時間がかかるような場合は、「LIMIT 句」を使うことで取得するデータ数の上限を設定することができます。
以下のクエリでは最大 10 件のデータを取得するようにしています。

SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' LIMIT 10

データの並び替え(ORDER BY 句)

取得したデータを並び替えたいときは「ORDER  BY 句」を利用します。
「ORDER BY」は特定のカラムを指定して、昇順(ASC)、降順(DESC)を選択することでデータを並び替えることができます。
例えば、試合開始日が新しい(最近)順でデータを取得したい場合は以下のように書くことができます。

SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' ORDER BY startTime DESC

データのカウントとグループ化(COUNT 句と GROUP BY 句 )

取得するデータをカウントしたりグルーピングすることも簡単に実行できます。
例えば、以下のようにクエリの最後に「COUNT 句」を追加すると、クエリに一致したデータ数だけを取得することができます。

SELECT COUNT(*) FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro'

上の例ではイチロー選手が打席にたった時の投球数をカウントしましたが、試合数ごとの投球数を取得したい場合、試合(gameId)ごとにデータ数をカウントする必要があります。
このような時にグルーピングができる GROUP BY 句が役に立ちます。
以下のようなクエリを使うと、gameId が同じものは一つのグループとして取得することができるため、簡単に各試合ごとの投球数をカウントすることができます。
(GROUP BY 句を利用する場合、そのカラムを SELECT 句で指定する必要があります)

SELECT gameId, COUNT(gameId) FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' GROUP BY gameId

カラム名の変更(AS 句)

取得したデータのカラムの表示名を AS 句を利用することで表示名を変更することができます。
取得した状態のカラム名だとわかりにくい場合や、次項に説明する複雑なクエリを利用していく場合は可読性を向上させるために頻繁に利用されます。

SELECT gameId, COUNT(gameId) as pitchCount FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' GROUP BY gameId

複雑なクエリの実行

ここまでは、データを取得するだけのクエリでしたが、ここからはもう少し複雑なクエリを利用してデータの分析をしていきたいと思います。

集計関数を使った

集計関数を使うと特定の列に対して簡単にデータ処理を行うことができます。
先ほどすでに利用していた COUNT も集計関数の一つです。ここでは利用頻度の高い SUM(合計) と AVG(平均) を使います。(その他の集計関数はこちらをご参照ください。 https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions

AVG

ダルビッシュ投手の球種ごとの平均球速を算出してみたいと思います。
pitchTypeDescription(球種)でグルーピングをして、球種ごとの平均値を算出します。

SELECT pitchTypeDescription, AVG(pitchSpeed) as avgPitchSpeed FROM `bigquery-public-data.baseball.games_wide` WHERE pitcherLastName='Darvish' GROUP BY pitchTypeDescription

SUM

次に、SUM を使って来場者の合計を取得したいと思います。

SELECT SUM(attendance) FROM `bigquery-public-data.baseball.games_wide`
このクエリでこのデータ上に存在する来場者の合計を求めることができました。
しかし、今回扱っているデータは投球ごとに集計されているため、この来場者の合計はあまり有用なデータとして使うことはできません。
そこで、次項で説明するサブクエリを使って、全試合の合計来場者数を計算してみたいと思います。

サブクエリ

サブクエリは、クエリ結果に対してさらにクエリをする方法です。

ここでは全試合の来場者数の合計を集計してみたいと思います。
まず、以下のクエリを実行すると、各試合ごとの来場者数を取得することができます。

SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId

この結果の attendance の合計を取得することができれば、全試合の合計来場者数を算出することができることがわかると思います。それでは、サブクエリを使ってこの結果にさらにクエリを実行してみます。

SELECT SUM(attendance) as sumAttendance FROM (SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId)

このように FROM 句の後に先ほどのクエリを付け足すことでサブクエリとして使うことができます。
一方で、サブクエリを使うと、クエリが長くなってしまい可読性が落ちてしまうため、WITH 句を利用して、サブクエリ結果を一時的なテーブルとして扱うことで可読性を高めることができます。
WITH 句を利用したクエリは以下の通りです。

WITH games_attendance AS(SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId)
SELECT SUM(attendance) as sumAttendance FROM games_attendance

ウィンドウフレーム(ROWS BETWEEN)

ウィンドウフレームは特定の範囲を一つのデータとして扱う方法で、移動平均を求める場合などに頻繁に利用されます。

ここでは、Rogers Centre 球場の来場者数の 5 日分のデータを 1 つのウィンドウフレームとして、移動平均を計算したいと思います。
まず、Rogers Centre 球場での試合の来場者数を日付順に取得します。

SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime

ANY_VALUE はそのカラムの適当な値を取得する集計関数です。今回は gameId でグループ化したデータ内では startTime は全て同一の値であるため、ANY_VALUE  を利用して代表値を取得しています。

次にこの結果を利用して、移動平均を計算します。
 
WITH games_attendance AS (SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime)
SELECT startTime, AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 4 preceding AND current row) as ma from games_attendance ORDER BY startTime
AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 4 preceding AND current row)

少しややこしくなってきましたが、括弧の中から順にみていくと理解しやすいです。
startTime 順のデータ(ORDER BY startTime)に対して、4 行前のデータ(4 preceding)から現在処理しているデータ(current row)までの attendance を平均[AVG(attendance)]しています。これで移動平均を求めることができます。

結合(JOIN)

ここまでは一つのテーブルに対してのみクエリを実行してきましたが、結合(JOIN)を利用すると、複数のテーブルを一つのテーブルのように結合して扱うことができます。
結合する方法はいくつかありますが、ここでは最もよく利用する INNER JOIN を説明します。
他の結合方法についてはこちらを参照ください。(https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join_types)

INNER JOIN は 2 つのテーブルにあるデータのうち指定したデータが条件に合致した場合のみ、結合を行います。

ここでは WITH 句を利用して、擬似的にもう一つのテーブルを作成し、それらを結合したいと思います。

例として、Rogers Centre 球場の来場者数の移動平均と、各試合の来場者数を結合し、来場者数の移動平均の値との差を確認したいと思います。これにより平均的な来場者数からばらつきの大きい試合を確認することができるようになります。

WITH games_attendance AS (SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime),
games_ma AS (SELECT gameId, startTime, AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 7 preceding AND current row) as ma from games_attendance ORDER BY startTime)

SELECT games_ma.gameId,ANY_VALUE(games_ma.startTime) as startTime, AVG(games_ma.ma) as ma, AVG(games_wide.attendance) as attendance, (AVG(games_ma.ma)-AVG(games_wide.attendance)) as gap FROM games_ma INNER JOIN bigquery-public-data.baseball.games_wide as games_wide ON games_ma.gameId=games_wide.gameId GROUP BY gameId ORDER BY ANY_VALUE(games_ma.startTime)

データの可視化

ここまではデータを取得、分析してきましたが、それらを可視化する方法について説明します。

BigQuery 内でのデータ可視化

結合(JOIN)では、来場者数の移動平均と実際の来場者数を比較したデータを分析することができましたが、データを見るだけではどのような傾向があるかわかりにくかったかと思います。
 
そこで、先ほどのデータを可視化してみたいと思います。
クエリ結果のグラフのタブを選択し、グラフの種類を「線」、ディメンションを「startTime」、メジャーを「ma,attendance」を選択すると以下のようなグラフを表示することができます。
これにより、シーズンの最初の方は、来場者数に大きな揺らぎがあるが、シーズン後半は来場者数の揺らぎは少なく、平均来場者数も増加していることがわかります。

Lookerとの連携

クエリ結果は BigQuery 以外でも確認することができます。
特に、Looker Studio を利用すると、複数のデータを 1 つの画面に表示し、詳細に確認したりすることができ、他のユーザとの共有なども簡単に行えるため、データを定期的に確認し経営方針を検討するような場面ではとても利用しやすい BI ツールです。

実際に利用してみたいと思います。
クエリ結果の右上の「データを探索」から「Looker Studio で調べる」に遷移すると、Looker Studio が開きます。

遷移すると以下のようなデータが初期で表示されていますが簡単に編集することができます。

左に表示されている表は不要なので、削除します。
右に表示されている棒グラフを「Line Chart」に変更しキャプチャのとおり SETUP を変更すると、以下のようなグラフを作成することができます。

以上で、データの可視化については終了です。

ベストプラクティス

ここでは、BigQuery を効率的に安全に利用するための方法について説明します。

パーティションテーブルの活用

BigQuery にはパーティションとクラスタリングという概念があります。
パーティションはテーブルを特定の条件で分割する仕組みです。

よく使われる分割方法としては、日付や地域があります。
例えば、膨大なデータのアクセスログを分析するような場合、それらを全て一つのテーブルで管理すると一回のクエリで膨大なデータに対してクエリを実行する必要がありますが、日付でパーティションを作成している場合、特定のパーティションテーブルにのみクエリを実行すれば良いので、コストと速度面で効率的になります。

コストの最適化

SELECT * を避け、必要なカラムだけを指定することで、データのスキャン量を減らし、コストを抑えられます。
クエリ結果を LIMIT 句で制限したり、不要な行を WHERE 句で除外したりすることで、処理するデータ量を減らせます。
また、定期的に実行するクエリは、結果をテーブルに保存しておくことで、繰り返しスキャンするコストを削減したりすることができます。
多くのデータを扱うようになる場合はこれらのコストの最適化を行うことを検討してください。

セキュリティ設定

BigQuery ではデータアクセス関して、様々なセキュリティ設定を行うことができます。
https://cloud.google.com/bigquery/docs/row-level-security-intro?hl=ja
数人でデータを扱う場合、個人情報などの閲覧制限が必要な情報を誰が閲覧できるのか、ということを設計することは非常に重要になります。

参考資料の紹介

このチュートリアルでは、BigQuery の基本的な使い方から、高度な分析手法、データの可視化、ベストプラクティスまで幅広く学びました。
BigQuery は簡単にデータの分析ができるだけでなく、可視化もシームレスに行うことができる優秀なツールです。
さらに詳しくBigQuery を学びたい方は以下の資料を参考にしていただけると良いと思います。

分析方法を学ぶ

データの可視化方法を学ぶ

費用の最適化を学ぶ

 
※ Google Cloud、BigQuery、Looker、Looker Studio、 は Google LLC の商標です。

この記事を共有する

合わせて読みたい