Google Driveと Cloud DatastoreのデータをBigQueryで使用する

本連載では、5回にわたってスプレッドシートを使用したGoogle DriveとApp Engineのデータ交換について紹介しました。今回は、特別編として連載第2回で少し触れたApp EngineのDatastoreをBigQueryと連携する方法について説明します。具体的には、Datastoreに書き込まれたSpreadデータをCloud Storage経由でGoogle BigQuery（以降、BigQuery）へデータロードします。BigQueryは巨大なデータを様々なフォーマットで格納し、SQLライクなクエリーを超高速に実行できるGoogle提供のツールです。

App EngineのCloud Datastoreに書き込まれたデータは、アプリケーションによってはビッグデータとなり、その内容を解析することで企業等にとって有用な情報を得られます^（注）。そのためにはビッグデータ処理システム（ここではBigQuery）へのデータロードが必要ですが、Google Drive経由、またはApp EngineのUIからCloud Datastoreに書き込まれたデータはプログラムの記述が不要で、マウス操作と簡単なキー入力のみでBigQueryにデータロードできます。

注）ビッグデータの解析は、マーケティングにおける評判分析（自社製品の強み弱み）、需要の抽出、ナレッジの自動蓄積（顧客電話対応センターで頻出するQ&Aの分類）、リスク管理（故障、不具合の関連情報）などの他、高度にはゲノム（遺伝情報）解析などにも有効利用できます。

1．システム構成とデータロードの準備

ここから、BigQueryへのデータロードについて具体的な手順を見ていきますが、今回の処理内容は図1右の「BigQueryロード」で示す部分です。AppEngineから書き込まれたCloud Datastoreのデータを一旦Cloud Storageにロードし、そのデータをBigQueryにロードする必要があります。ただし、その手順は前述したようにプログラム記述は必要なく、マウス操作が主体で補助的にキー入力を行う程度です。

1．1 連携システムの構成

図1：連載のシステム全体図とCloud DatastoreからBigQueryへのデータロード

図1では、App Engineで受注管理システムのようなアプリケーションを作成し、そのデータをApp Engine標準のCloud Datastoreに書き込む動作を想定しています（コラム１）。Cloud Datastoreにはアプリケーションで作成されたデータをEntity単位で、理論的にはデータ量の制約なく追加登録できるため、大量のデータを保存できます。Cloud Datastoreに書き込まれたデータをEntity単位で一括してCloud Storageにロードし、同様にCloud StorageからBigQueryへロードすることで大量のデータを処理できます。

また、本連載でも見てきたように、センサの出力データなども処理対象のビッグデータとして大変有効です。TwitterやFacebookといったSNSの書き込みデータを自動で収集してくれるツールなどもあり、ExcelやCSV形式で出力してそのままデータソースとして使用できます。

なお、ここからの処理手順では、Googole Developers Console（以下、Developers Console）とGoogle App Engineの管理者画面（以下、App Engine管理者画面）を使用します。

1．2 BigQueryを使用するための設定を行う

BigQueryにデータロードするための事前準備として、下記の設定を行う必要があります。

（1）Googleアカウントを取得する

本連載を読まれている方であればGoogleアカウントを取得されていると思いますので、ここでは省略します。取得されていない方は連載第1回「3.3.1 Geogleアカウントを取得する」（http://thinkit.co.jp/story/2013/10/01/4472/page/0/1）を参照してください。

（2）Google Developer Consoleで新規プロジェクトを作成する

次に、BigQuery用に新規プロジェクトを作成します。初めてBigQueryを使用する場合は、使用料が300ドルまで課金されない無料体験版を利用できます（図2）。

図2：Google Cloud PlatformのBigQueryページ（https://cloud.google.com/bigquery/?hl=ja）

無料体験版を利用する場合は、図2の画面左下「今すぐ試す」、または右上「無料体験版」をクリックして、ユーザー登録を行います。Google Cloud Platformを使ったことがある人は、https://console.developers.google.com/projectをクリックしてGoogleアカウントにログインし、Google Developer ConsoleのProjects画面からプロジェクトを選択します。

図3：Google Developer ConsoleのProjects画面

筆者の場合、すでに多くのプロジェクトが登録されており図3のような画面表示になりますが、最初の登録ではブランク画面になっているはずです。なお、登録済のProject ID等は見えないようにグレイの枠で囲ってあります。新たなプロジェクトで処理を行う場合は、図3の画面左上「Create Project」（青色）をクリックするとプロジェクト作成用の画面（New Project）が表示されるので、この画面から「Project name」と「Project Id」、「Billing account」を指定してプロジェクトを作成します。

BigQueryにデータロードする場合は、課金登録をしてテーブルを作成する必要があります。上記で作成したプロジェクトを選択し、画面左ペインのメニューから「Billing」をクリックすると表示される画面で「Enable billing」をクリックし、カード情報を入力します。無料体験版であればユーザー登録時に入力したカード情報が適用されますが、300ドルまでは課金されません。また、無料体験版でなくても月1テラバイトまでの使用には課金されず、他のビッグデータ処理システムと比べて使用料がかなり安くなっています（コラム2）。

次に、画面左ペインのメニューから「BigData」の下にある「BigQuery」を選択すると、図4のように左ペインに予め用意されているサンプルデータが表示されます。「gdelt-bq-full」などは Dataset名で、その下のeventsなどはTable名です。

図4：サンプルテーブル

例えば、「events」を選択して画面右の「Query Table」をクリックすると、画面上部にクエリー文の入力フィールドと基本構文が表示されます（図5）。

図5： 「Query Table」クリックでの画面表示

画面上部の入力フィールドに表示されているクエリー文に「*」を追加して

SELECT * FROM [gdelt-bq:full.events] LIMIT 1000

とし、「RUN QUERY」（赤色）をクリックすると、図6のようにクエリーの実行結果が表示されます。これで、BigQueryが動作していることを確認できます。

図6：クエリー実行結果の表示

2．Cloud DatastoreからCloud Storageへデータロードする

BigQueryの動作が確認できたので、続いてCloud DatastoreからCloud Storageへのデータロードを実行します。Cloud DatastoreのデータをBigQueryへロードするには、Cloud Storageを介して行う必要があります。ここでは、まずCloud DatastoreからCloud Storageへのデータロード手順を解説し、その後Cloud StorageからBigQueryへのデータロード手順を解説します。なお、BigQueryにロードする元データは連載で使用したExcelデータよりも項目数の多いテーブルを使用しています。

【App Engineの管理者画面】

Cloud DatastoreからCloud StorageへデータロードするにはApp Engineの管理者画面を使用しますが、同時にDevelopers Consoleからの設定も必要になります。

①App EngineのMy Applications画面（https://appengine.google.com/）から、作成したprojectのProject IDを選択します。Project IDはApplication欄に表示されている名前です。

②表示される画面の左ペインからAdministrationの下にある「Application Settings」を選択し、スクロールすると「Built-ins」があるので、「Datastore Admin」を「enable」に設定します。初期設定の「Enable」ボタンをクリックすると「Enable」に設定できますが、表示は「Disable」になります（図7）。

図7：「Datastore Admin」を「Enable」にすると表示は「Disable」になる

【Developers Console】

③Developers Consoleに戻り、使用するプロジェクトでCloud DatastoreのデータをロードするCloud Storageのbucketを設定します。bucketの設定はDevelopers Consoleの左ペインで「Cloud Storage」を選択し、「Storage browser」をクリックして行います（図8）。

図8：Storage browserをクリックして表示になる

Bucketを新規作成する場合は、画面上部の「Create bucket」をクリックします。図9のように画面表示が変わるので、「Name、Storage class」および「Location」を入力しします。ここで、Nameにはバケット名（任意ですが、ここではdaysale150429）を、Storage classはデフォルト表示（Standard）のままにして、Lcationに「Asia」を選択します。

図9：Bucke名とStorage bclassのLocationを指定

指定後、「Create」をクリックすると画面10のように画面表示が変わり、生成したBucket名（daysale150429）が画面上部に表示されます。

図10：Bucket作成後の画面表示

【App Engineの管理者画面】

App Engineの管理者画面に移り、Application欄にあるProjectIDを選択します。表示される画面の左ペイン「Dataセクション」下の「Datastore Admin」をクリックすると図11の画面が表示されるので、App Engine DatastoreからCloud Storageへロードするデータの「Entity Kind」（ここではdaysale）を選択します。

図11：App EngineのDatastore Admin画面

④Entity Kindを選択後、画面下部にある「Backup Entities」をクリックすると図12の画面が表示されます。

図12：「Backup Entities」クリック後の画面表示

「Backup storage destination」をデフォルトの「Blobstore」から「Google Cloud Storage」に変更します。図13の画面表示に変わるので、画面下部の「Google Cloud Storage bucket name」に図10の画面で指定したCloud Storageのbucket名（dayale150429）を入力し、他はそのままにして「Buckup Entries」をクリックします。

図13：Backup storage destinationをGoogle Cloud Storageに変更しbucket名入力後の画面表示

【Cloud DatastoreからCloud Storageにデータロードを実行】

バックアップJOBが開始され、図14の画面表示に変わります。バックアップ（Cloud DatastoreからCloud Storageへのデータロード）が正常に実行されたどうかは図14下部の「Back to Datastore Admin」をクリックすれば確認できます。

図14：Backupジョブの開始

「Back to Datastore Admin」をクリックして表示される画面をスクロールすると、「Completed Operations」から実行したバックアップ（図12のBuckup name:「datastore_backup_2015_04_29」）が正常に完了した（Completed）ことを確認できます。

図15：Backup完了後のDatastore Admin画面

3．Cloud StorageからBigQueryへデータロードする

ここまでの手順で、Cloud DatastoreからCloud Storageへのデータロードは完了しました。ここからはCloud StorageからBigQueryへのロード手順を説明します。ここまでと同様に、マウス操作とキー入力だけで行うことができます。

【Developers Console】

Developers Consoleの左ペインで「Cloud Storage」から「Storage browser」をクリックして、Bucket一覧を表示します。その中から先ほどデータロードしたBucket（「daysale150429」）をクリックすると、図16の画面が表示されます。

図16：daysale150429の内容表示

右ペインの一覧にある2行目の「xxxxxxx.backup_info」 はBigQueryへのデータロード時に使用するので、書き留めて（コピーして）おきます。画面上から見えるのは前半だけですが、リンクを右クリックして「リンクアドレスをコピー」を選択し、コピーをテキストファイルに張り付けると次のような文字列になっています。この中の太字部分を後で使用します。

https://console.developers.google.com/m/cloudstorage/b/daysale150429/o/
ahRzfnNvbmljLXNvbHN0aWNlLTQyMnJBCxIcX0FFX0RhdGFzdG9yZUFkbWluX09
wZXJhdGlvbhiyksUBDAsSFl9BRV9CYWNrdXBfSW5mb3JtYXRpb24YAQw.daysale.
backup_info

次に、Developers Consoleの左ペインでBig Dataの下にあるBigQueryを選択すると、図17のような画面が表示されます。

図17：BigQuery選択後の画面表示

画面の左ペインでプロジェクト名（ここでは 「gcpproj」）右の「▼」をクリックすると図18のようにプルダウンメニューが表示されるので、「Create new dataset」をクリックします。

図18：「Create new dataset」をクリック

図19のCreate Dataset画面が表示されるので、BigQueryのDataset ID（daysale150429）を入力して「OK」をクリックします。Dataset IDは任意に指定できますが、ここでは分かり易いようにCloud Storageのbucketと同じ名前にします。

図19：Dadaset IDを入力

左ペインに、登録したdataset IDの「daysale150429」が表示されます（図20）。

図20：生成されたdataset（daysale150429）が左ペインに表示される

次に、「daysale150429」をマウスオーバーすると右に表示される「▼」をクリックし、ブルダウンメニューから「Create new table」を選択します（図21）。

図21：「Create new table」を選択

図22の「Create and import」ステップの画面が表示されます。

図22： 「Create and import」ステップの画面表示

ここでは「skip」をクリックして、次の画面（Choose destionationフェーズ）に移ります（図23）。

図23： TabkeIDを入力

Choose destinationステップでは、Cloud StorageのTable ID（任意入力ですが、ここでは「daysale150429tbl」）を入力して「Next」をクリックします。図24の画面に変わります。

図24：TabkeID入力後の画面表示

Select dataフェーズでは、「App Engine Datastore Backup」を選択します。図25の画面に変わります。

図25：「Cloud Datastore Backup」選択後の画面表示

 gs://フィールドの入力には、Cloud Storageのバケット名と、図16の画面でコピーしたテキストを使用します。

gs://[bucket名]/[ .backup_info] 

ここでは、 bucket名に「daysale150429」、.backup_infoに「ahRzfnNvbmljLXNvbHN0aWNlLTQyMnJBCxIcX0FFX0RhdGFzdG9yZUFkbWluX09wZX
JhdGlvbhiyksUBDAsSFl9BRV9CYWNrdXBfSW5mb3JtYXRpb24YAQw.daysale.backup_info」を入力します（画面26）。なお、実際の入力内容は下記のようになります。

gs://daysale150429/ahRzfnNvbmljLXNvbHN0aWNlLTQyMnJBCxIcX0FFX0RhdGFzdG9yZUFkbWluX09wZXJhdGlvbhiyksUBDA

sSFl9BRV9CYWNrdXBfSW5mb3JtYXRpb24YAQw.daysale.backup_info

図26：入力後の画面イメージ

入力後、「NEXT」をクリックするとSpecify schemaステップに移ります（画面27）。

図27：「NEXT」クリック後の画面表示

Schema（スキーマ）には、バックアップのデータソースとなっているCloud Datastoreのものが使用されます。ここでは何も操作せず、「NEXT」をクリックして図28の画面を表示します。

図28：「NEXT」クリック後の画面表示

【BigQueryへのデータロード実行】

図28では許容されるエラー数を指定できますが、デフォルト（「0」）のまま画面下部の「Submit」をクリックするとCloud StorageからBigQueryへのデータロードが開始されます。データロード実行中は、図29のようにJobHistoryに（1running）、Dataset/Tableに「（loading）」が表示されます。

図29：Cloud StorageからBigQueryへデータロード中の画面表示

データロードが完了すると（loading）が消え、左ペインの「daysale150429tbl」をクリックするとロードされたデータのSchemaが、Schema表示画面右上の「Details」をクリックするとBigQueryにロードされたデータの詳細情報が表示されます（画面30）。これでBigQueryへのデータロード完了を確認できます。

図30：BigQueryにロードされたデータのDetail表示

次に、画面右上の「Query table」をクリックするとクエリー入力画面が表示されるので、下記のようにクエリー文を入力して「RUN QUERY」をクリックします。

SELECT * FROM [daysale150429.daysale150429tbl] LIMIT 1000

図31のように表示され、データのロードを確認できます。

図31：クエリーでロードされたデータを表示

終わりに

ここまで、今回の特別編と併せて全6回にわたってExcelデータのアップロードからBigQueryへのロードまですべての処理の流れを見てきました。BigQueryのクエリー文については触れませんでしたが、RDBのSQL文とほとんど同じなので、習得は難しくないと思います。また、拙著「Google BigQueryではじめる自前ビッグデータ処理入門」にもより実践的なクエリー文を掲載していますので、興味のある方はぜひ参照してください。

Google BigQueryではじめる自前ビッグデータ処理入門