Google BigQueryでデータを分析してみた

このエントリーをはてなブックマークに追加

こんにちは。データデザイン部の張です。
普段はデータサイエンティストとしてお客様からデータを受領し、加工・分析を行っています。
プログラミング言語を用いて、加工・分析を行うことが多いですが、大容量のデータである場合は時間がかかりすぎ、加工・分析に限界があります。そこで、そのような状況の場合、弊社ではBigQueryを活用します。

今回はBigQueryを用いたデータ分析のメリットと使い方を紹介します。
使用したデータは、弊社が提供している「地理関連統計データ」の「人口年次推移」のデータです。こちらのリンクに詳細が書いてあるので確認いただければ幸いです。

目次

  • BigQueryでデータ分析を行うメリット
  • BigQueryを使い始めましょう
    • プロジェクトの作成
    • データセットの作成
    • テーブルの作成
  • 分析データの加工
    • 3つのテーブルの結合
    • 生成されたテーブルの確認
    • 3年平均人口数の算出
    • 計算結果の確認
    • 結果の出力
  • まとめ

BigQueryでデータ分析を行うメリット

プログラマーでない人にも簡単に使えます
高度な並行処理のため、大量のデータに対する処理速度が早いです。

BigQueryを使い始めましょう

プロジェクトの作成

まず、「Google Cloud Platform」のページを開いてください。
左上の横線3つの「ナビゲーションメニュー」をクリックし、「ビッグデータ」の下にある「BigQuery」をクリックします。

 

次に、上部の青い欄の「プロジェクトの選択」を押します。

 

右上の「新しいプロジェクト」を押します。

 

「プロジェクト名」と「ID」、「場所」を記入し、「作成」をクリックします。場所は分からない場合、記入しなくても大丈夫です。

 

左側の「リソース」の下に作成したプロジェクト名が表示されていることを確認してください。

データセットの作成

右側の「データセット作成」を押し、「データセットID」を入力したのち、「データロケーション」を「東京(asia-northeast1)」選択してください。

デフォルトは「USマルチリージョン」のなっています。同一プロジェクト内のデータセットリージョンが一致していない場合、お互いに参照できないことがあるため、統一を推奨します。今回は「東京」にしました。
「デフォルトテーブルの有効期限」を「無期限」を選択します。「テーブル作成後の日数」を選択すると、テーブルの期限が設定されます。
「暗号化」のところは自由に選択してください。今回は「Googleが管理する鍵」を選択します。
記入と選択がすべて完了したら、「データセットを作成」を押します。

先程作成したプロジェクトの下に作ったデータセットがあることを確認してください。

テーブルの作成

先程作成したデータセットをクリックし、右側の「テーブルを作成」を押します。

 

分析対象となるデータを読み込みの方法は4つあります。Google Cloud Storage、Google drive、あるいはGoogle Cloud Bigtableからデータを引っ張ってくる、PCのローカルからデータをアップロードすることができます。「空テーブル」の場合はBigQuery上に、データが入っていないテーブルを作ることができます。今回はローカルに保存しているデータを使用するため、「ソース」の「テーブルの作成元」を「アップロード」します。

アップロードしたいファイルを選択し、「ファイル形式」を「csv」に指定します。BigQueryのテーブルの列名はローマ字のみ対応しているため、ファイルをアップロードする前にデータのカラム名をローマ字に変更してください。データのバリューが日本語の場合、文字化けを出やすいため、アップロードする前に文字コードがUTF-8になっていうこと確認してください。

「送信先」に先程作成した「プロジェクト名」、「データセット名」と作成したい「テーブル名」を入力します。
「テーブルタイプ」はデフォルト(「ネイティブテーブル」)のままで大丈夫です。

「スキーマ」の「自動検出」を選択します。もちろん、自分でフィールドを編集することができます。
今回使用するデータの中に列名が既に存在しているため、「自動検出」に指定します。

「パーティションとクラスタの設定」は「パーティショニングない」にします。

上記の設定が終わったら、「テーブルを作成」を押します。

 

作成したテーブルがデータセットの下に存在していることを確認してください。
同じ手順で2016年、2017年、2018年の全国の人口データをそれぞれテーブルを作成します。

 

分析のデータの加工

3つのテーブルの結合

これから、2016、2017、2018年の市区町村単位の3年平均人口を計算します。計算で使用するデータと計算結果を見やすくするため、2016、2017、2018年それぞれの人口数のフィールドを1つのテーブルにします。
下記のSQLコードを実行します。実行する前に右側の緑の文字で処理されるデータの大きさを確認できるため、課金に気になるときに便利だと思います。処理が終わったら、「新しいテーブルほげほげが作成されました」が表示されます。

結合するときは、JOINLEFT JOINRIGHT JOIN全部使えますが、LEFT JOINあるいはRIGHT JOINを使うとOUTER JOINになるため、データが勝手に消えません。今回はLEFT JOINを使います。

CREATE TABLE
`projectA.population_calculate.population3years` AS (
SELECT
p2016.PrefCode,
p2016.CityCode,
p2016.OrganizationCode,
p2016.PrefecturesName,
p2016.CityName,
p2016.TotalPopulation2016,
p2017.TotalPopulation2017,
p2018.TotalPopulation2018
FROM
`projectA.population_calculate.population2016` AS p2016
LEFT JOIN
`projectA.population_calculate.population2017` AS p2017
ON
p2016.OrganizationCode = p2017.OrganizationCode
LEFT JOIN
`projectA.population_calculate.population2018` AS p2018
ON
p2016.OrganizationCode = p2018.OrganizationCode)

生成されたテーブルの確認

左側のデータセットの下に新しいテーブルが存在することを確認します。
生成されたテーブルをクリックし、2016、2017、2018年の人口数のフィールドが入っていることを確認します。

3年平均人口数の算出

3年の人口数から、平均の人口数を算出するため、下記のSQLコードを実行します。

WITH句は中間生成テーブルを保つことができます。中間生成テーブルに対するクエリは課金対象外のため、課金額を気にされる方はおすすめです。

CASTはデータ型を変更するときに使う関数です。今回は3年平均人口を整数に変更する目的で使用しています。

CREATE OR REPLACE TABLE
`projectA.population_calculate.population3years` AS (
WITH
TotalPopulation3yAvg AS (
SELECT
OrganizationCode,
CAST((TotalPopulation2016+TotalPopulation2017+TotalPopulation2018)/3 AS INT64) AS TotalPopulation3yAvg
FROM
`projectA.population_calculate.population3years`)
SELECT
y.OrganizationCode,
y.PrefecturesName,
y.CityName,
y.TotalPopulation2016,
y.TotalPopulation2017,
y.TotalPopulation2018,
A.TotalPopulation3yAvg
FROM
`projectA.population_calculate.population3years` AS y
LEFT JOIN
TotalPopulation3yAvg AS A
ON
y.OrganizationCode = A.OrganizationCode)

計算結果の確認

更新されたテーブルをクリックし、3年平均人口数のフィールドが存在していることを確認します。

結果の出力

右側の「エクスポート」を押し、「GCSにエクスポート」を選択してください。
後でGCSに保存された場所でファイルをダウンロードできます。

まとめ

今回はBigQueryをデータ分析に使った理由と使い方を簡単に説明しました。
大容量のデータの処理に困っている方は、ぜひBigQueryでの処理・分析をやってみてください。

地理関連統計データのご提供

「地理関連統計データ」は、AI(人工知能)モデル開発や、BI(ビジネスインテリジェンス)活用において必要とされるデータです。

各種統計データの中でも企業からの需要が特に高い地理関連統計データを要件に合わせて選定し、機械判読しやすい形式に加工して提供しております。詳しくはこちらのリンク先からご覧ください。

地理関連統計データを詳しくみる

WRITER
Zhang Chenyi

データサイエンティスト

張 晨伊Zhang Chenyi

不動産、流通業界のデータサイエンスを担当。統計学やDeep Learningを活用し、モデル構築の実績あり。

最新記事

ページTOPへ