GoogleSpreadSheetからBigQueryにデータを読み込む際にハマった３つの罠

5587 ワード
DataPortal GoogleSpreadSheet 前処理 BigQuery zendesk zendesk テキストリンク

GoogleSpreadSheetからBigQueryにデータを読み込む際に数時間ハマってしまったのでその時の対処方法を書きます。
慣れている方の方がハマりそうなので記事にしてみました。

自己紹介＆はじめに

10月からJOINさせていただいております増田です。データサイエンスチームに所属してデータアナリストとして社内の様々なデータを活用すべく、データの収集、分析、可視化などを行っています。

グロービスでは顧客からの問い合わせをZendeskを利用して管理しています。顧客の問い合わせ状況などをAPIを利用し、GoogleSpreadsheetに読み込みDataPortalを利用してに読み込みをして可視化をしていました。
現在データサイエンスチームでは社内全体のデータ分析基盤としてBigQueryを利用しており、問い合わせについても分析することになり、GoogleSpreadSheetからBigQueryに読み込みを行おうとしたところで今回の問題が発生してしまいました。

ハマったポイント　0 の罠

正直、google同士だし簡単に終わるだろうと思っていましたが、普段の作業の思い込みにより数時間悩むハメになってしまいました。

GoogleSpreadSheet からインポート

すでに毎日ZendeskからGoogleSpreadSheetにはGoogle Apps Scriptを利用してデータが取り込まれています。
そのファイルをそのままBigQueryに読み込むだけの作業だと考えていました。

公式ドキュメントもあるように手順通り画面から操作してきました。

1.保存先のデータセットを選ぶ。
2.読み込み元のGoogleSpreadSheetを選ぶ。
3.BigQueryに読み込みデータを分析する。

という手順で進めました。

URLを貼り付け設定をしたところ無事読み込みができました。

プレビューがない。

あれ、何かがおかしい。足りない。
いつものテーブルにはプレビューがあるのにプレビューが存在しない！

普段の詳細画面

まぁ、スキーマは表示されているから問題ないと思い詳細情報も確認しました。

表のサイズも０B、行数も０

詳細情報を確認するも、表のサイズは０B、行数も０ という表示になっていました。

うまく読み込めていないのかなと思いました。
他のテーブルの場合はこのように表示されます。

詳細を押すと行数やデータ量が表示されるためデータが読み込めていないかと思い込み初めてしまいました。
本当にデータがはいっているのか・・・。
クエリエディタでテーブルの存在があるのか確認しました。

SELECT
  *
FROM
  zendesk.raw_tickets

やはり０B(しかしエラーではありません。0Bが処理されます。）

  • プレビューがない。
  • 行数も０行
  • クエリデータ量も０B

この３つ状況が重なりデータの読み込みに失敗してしまったと思い込んでしまいました。

このあとは、

  • URLの貼り付けがあっているのかの確認
  • Google ドライブの権限が正しくあるのか確認
  • BigQuery側の権限があっているのか確認
  • スキーマをすべて手入力で読み込めるのか確認
  • GoogleSpreadsheetを１ページのみに変更して読み込めるのか確認

など、いろいろと行いましたが、結局０Bのままでした。

Googleドライブは外部データソースである

悩みに悩んでいたところで、ふと何か思い立ったのかは思い出せないのですが、おそらく実行ボタンを間違えて押してしまいました。
実行してみると

結果

データが表示される

0B表示ですが、ちゃんとデータが処理されました。

原因はどうやら外部データソースを参照する場合は、表示上０Bと表示されているだけということでした。
（公式のドキュメントには特に記載が見当たりませんでしたが外部データソースについて説明がありますので貼り付けておきます。）

外部データソースの概要

外部データに直接アクセス（外部といってもgoogle内部）にアクセスしてそのまま処理をして返すようなイメージでしょうか？
クエリの画面上は０Bでしたが、結果をみるとしっかり容量がわかり課金されているようです。
（事前にクエリのサイズがわからないのは注意が必要ですね。）

データを読み込むだけの作業に悩んで数時間取られてしまいました。
思い込みは危険だなと反省しました。

この後もGoogleSpreadSheet側にエラーがあるとBigQuery側で読み込みができず、GoogleSpreadSheet側でクレンジングしたりといくつか壁に当たりましたが、無事BigQueryにデータを読み込みすることができ、今はこのような形で可視化することができました。

＜DataPortalを利用しての可視化例＞

最後に

今回のハマったポイントはまさに思い込みでした。普段の感覚や０の表示に気を取られて気づかない点でした。
逆に普段からBigQueryを使っている人がハマりやすいポイントではないかと思いましてまとめてみました。

GoogleSpreadSheetは非常に柔軟で近年でビジネスサイドでもEXCELの代わりに使っている方も増えています。
扱うデータがの範囲が広がれば広がるほどデータベース化ににないデータを扱う機会も増えると思います。
日々更新業務で変更するがシステム化するほどでもないデータなどを分析する際に、EXCELからGoogleSpreadSheetに変えてもらえば、
BigQueryに読み込み分析するといったことも可能になりますので、うまくGoogleSpreadSheetを活用していこうと考えております。

ここまで読んで頂きありがとうございました。
引き続きグロービスのアドベントカレンダーをお楽しみください！
https://qiita.com/advent-calendar/2019/globis