More than 5 years have passed since last update.

BigQueryはほんとスゴイので、あえてExcelから検索してみる

Posted at 2019-03-14

#はじめに
BigDataの処理のためにBigQueryを使っている例はたくさんあるが、最終的にレポートとして出力する際には、Excelの帳票を求められる機会が多いのではないだろうか。この記事はそういったケースで活用できる。

#始める前に
前提条件として、Google アカウントの作成とプロジェクトの作成を事前に行っているものとする。下記ページにアクセスし無料トライアルボタンを押して手順に従って欲しい。アカウントの生成とデフォルトのプロジェクトの作成をすることができる。
https://cloud.google.com/
後で利用するため、デフォルトで作られたプロジェクトID文字列を記録しておく必要がある。

#BigQueryの準備
BigQueryは大量の情報を検索するのに向いたDWHということは述べたとおりだ。利用方法も簡単で、一般に企業内のデータベースで検索するときに使われる標準SQLで検索することができる。また、BigQueryには、ユーザが用途に応じて作成したデータベースのほかに、一般公開データセットが含まれており、だれでも簡単に活用することができる。一般公開データには、今回参照するような気象データのような統計データのほかに、Wikipediaの全文情報や、ヒトゲノム情報やガンの情報のような医学情報等、様々な種類の情報が提供され、いつでも利用することができる。
#BigQueryの設定
BIGQUERY ウェブ UI に移動する
https://bigquery.cloud.google.com/?hl=ja
今回はGHCN Dailyを検索する。これは気象観測点の記録のデータベースだ。検索するにはクエリエディタに以下のクエリをコピーする。このクエリは日本全国の気象観測点の一年分の気温を出力する。

#standardSQL
SELECT
  W.date date,
  S.name name,
  S.latitude latitude,
  S.longitude longitude,
  W.element element,
  W.value precipitation  
FROM
 `bigquery-public-data.ghcn_d.ghcnd_2018` W
JOIN
 `bigquery-public-data.ghcn_d.ghcnd_stations` S
ON
W.id = S.id
WHERE
  starts_with(W.id,'JA') and
  W.element = 'PRCP'
order by
  date

「このクエリを実行すると、1.1GB が処理されます。」と表示され、コピーがきちんと出ているか確認する。テスト実行を押して正しく検索できるかを確認する。

#（補足）認証プロキシを使用している場合の設定
認証プロキシを使用している場合、3DMAPが起動しない場合がある。
下記のページより、MAPEをインストールし、認証プロキシ画面非対応のツールの対応設定を行う。
https://github.com/ipponshimeji/MAPE/blob/master/Documentation/ja/Index.md
#Microsoft Excelからクエリを発行
クエリの動作確認ができたら、次はExcelからクエリを発行してみよう。クエリの発行には次の情報が必要になる。

Project ID
BigQuery Connectorの発行した認証key
実行するクエリ

#BigQuery Connector for Excelの設定
BigQuery Connector for Excelを使用するには、ページにアクセスし専用の認証キーを発行する必要がある。まず、以下のページをブラウザで開く。
Google BigQuery Connector for Excel
https://bigquery-connector.appspot.com/

初回アクセスでは認証キーが発行されていないためYour Keyが表示されない。ページ下部にスクロールし、鍵の期限を決めてCreate Keyを押す必要がある。鍵を生成したらYour Keyにキーが表示されるので、テキストエディタなどにペーストして保存する。
そして、Click here to download IQY fileをクリックし、IQYファイルを保存する。認証キーとIQYファイルは次のステップで利用するので確実に保存すること。
Microsoft Excelからクエリを発行
準備ができたらいよいよExcelからクエリを発行してみよう。クエリの発行には次の情報が必要になる。実行するクエリは後から変更できるように、以下のようにセルにパラメータを用意すると便利だ。

A1セル:クエリ前半
A2セル:クエリ後半
A3セル:Project ID
A4セル:認証キー

Excelの制限で、256文字以上のクエリはクエリを分割する必要がある。今回の場合は２つのセルに分割してクエリを保持する。下記テキストをExcelにコピーしてほしい。

"#standardSQL
SELECT
  W.date date,
  S.name name,
  S.latitude latitude,
  S.longitude longitude,
  W.element element,
  W.value precipitation  
"
"FROM
 `bigquery-public-data.ghcn_d.ghcnd_2018` W
JOIN
 `bigquery-public-data.ghcn_d.ghcnd_stations` S
ON
W.id = S.id
WHERE
  starts_with(W.id,'JA') and
  W.element = 'PRCP'
order by
  date"

クエリを実行するには、メニューの「データ」→「データの取得と変換」内の「既存の接続」を選択する。参照ボタンを押し、先ほどダウンロードしたIQYファイルを指定するとパラメータの入力画面に移る。入力内容は以下の４つだ。

データの出力先:既存のワークシートの指定セル、新しいシートから選択できる。 OK をクリックする。
クエリの選択ダイアログ: クエリを入力したセルを選択する。
プロジェクトID
認証キー

実行すると、結果が指定したセルに出力される。たったこれだけの設定で簡単にExcelからBigQueryにクエリーを送り、結果をスプレッドシートとして得ることができる。
3DMAPでの可視化
BigQueryから得られたスプレッドシートには、観測地点の位置情報が含まれる。これを3DMAPに設定することで簡単に可視化できる。ステップとしては以下の通りだ。

Excelの挿入メニューから3DMAPを選ぶ
3DMAPを開く」を選択
3DMAPが開かれるのでフィールドリストより以下を設定

nameを場所にドラッグアンドドロップ、都市を選択
longitudeを場所にドラッグアンドドロップ、緯度を選択
latitudeを場所にドラッグアンドドロップ、経度を選択
precipitationを高さにドラッグアンドドロップ、最大値を選択
elementを分類にドラッグアンドドロップ
dateを時間にドラッグアンドドロップ、日付を選択

上記の処理だけでMAP上に集計結果が表示される。

#まとめ
ビックデータを活用するには、分析者が使いやすい方法でデータにアクセスし素早く検索できることが必要だ。BIツールを使わずともExcelだけで地図上にデータを表示することは可能だ。このような簡易な方法によるビックデータへのアクセスも、選択肢の一つとして活用してほしい。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up