MaterialsEN中文ホームブログ技術ブログBigQueryに入門してみたBigQueryに入門してみた作成日:2015/07/07更新日:2018/07/11技術ブログデータ解析BigQueryデータ解析こんにちは。業務でBigQueryに触る機会があったので、自身の整理も兼ねて入門記事を書いてみようと思います。目次は、BigQueryとは準備CLIで実行してみるAPIライブラリから実行してみるという流れで書いていきます。1.BigQueryとはBigQueryはGoogleのフルマネージドのクラウドサービスです。その特徴は、分散処理により、1大量の構造化データに対してSQLクエリを、2超速に実行出来るという点です。ビッグデータの分散処理と言うと、MapReduceが有名です。MapReduceはGoogleが技術論文として公表したものであり、その実装としてApachがオープンソースのHadoopを開発し、普及しました。使用が広がるにつれてMapReduceの問題点としていくつかの物が挙げられるようになり、それに対応して様々なフレームワークや改善策が開発されてきました。処理をSQLライクに書く事によってMapReduceジョブに変換してくれるpigやhiveといったツール。メモリキャッシュを利用するSpark。ありますがそれぞれだけで記事が書けそうな情報量です。2.0と呼ばれるインフラのコンポーネント群を追加しました。分散ファイルシステムだが、内容は公開されていない。地球規模でレプリケーションするデータストアで、原子時計で同期して地理間の時間順序問題を解決している。ビッグデータコレクションに対して慣用句的なJavaコードを書けるシステム。MapReduceとして変換・最適化されて実行される。Colossusなどに保存されているデータに対してクエリ実行出来る、分散SQLエンジン。分散・並列処理をする上で、処理そのものの質を高める内側の方向を前提として、その規模を広げる方向への改善が2.0と言えるのかと思いました。BigQueryはこのStack2.0の上に構築されたサービスであり、SQLエンジンとしてDremelを使用しています。Dremelはこれらの地球規模の分散環境を利用して、数千台のディスクから並列にデータを読み込む事が出来ます。これらのデータはColumnIOと呼ばれるフォーマットで保存されています。ColumnIOはその名前の通り列指向でデータを保持するもので、クエリで必要となる列だけを選択出来る事と、列内のデータの類似性による高い圧縮率によって、高速化を実現しています。以上のようなGoogleならではの巨大な分散環境と、列指向のデータフォーマットによって高速なクエリ実行を可能にしているそうです。前述したMapReduceから始まるフレームワーク群とBigQueryを比較すると、目的による使い分けになると思います。ジョブの詳細なコーディングが必要とされるMapReduceは裏を返せば複雑な変換処理を大規模に行えるということです。スピードの要求されないバッチなどにはこちらが最適でしょう。反対に、SQLによって表現出来る処理をアドホックに行いたいならBigQueryが役立つでしょう。コストの問題も見過ごせません。BigQueryはColumnIOの名の通り、クエリ課金も列で読んだデータ量で計算されます。安いと言われるBigQueryですが、実行するクエリと使用するデータの形によってはコストが跳ね上がる事もあります。クエリに使用する列が長い文字列になる場合などです。コストが上がる上にスピードがさほど要求されないようなケースでは既存のフレームワークの方が正解になる場合も十分あり得ます。2.準備さて前置きが長くなりましたが、実際に実行してみようと思います。BigQueryを実行するにはGoogleアカウントと課金情報の登録が必要です。現在Googleのクラウドサービスには60日間+3万円分の無料体験があり、安心して試す事が出来ます。Googleのsdkページでgoogle-cloud-sdkをインストールする必要があります。→GoogleCloudSDK(https://cloud.google.com/sdk/)インストールの方法もこのページにあります。loginとコマンド入力して認証に進みます。ブラウザが使える環境なら勝手にブラウザが立ち上がり、アカウント選択画面になります。開発者アカウントとして使う物を選択してください。これで登録は終了です。consoleに移動出来ます。BigQueryは左下「ビッグデータ」欄にあり、クリックするとWebUIが現れます。新しいprojectやdatasetを設定する時はWebUIが分かりやすくて良いと思います。クエリ実行もここで出来るので何が出来て何が出来ないのか雰囲気をつかむのに良いです。ちなみに左下のpublic:samplesはサンプルデータなので、最初から使う事が出来ます。QUERYからサンプルデータにクエリ実行してみます。wikipediaの更新履歴データ約3億件を対象にタイトルとコメントをselectしてみます。BigQueryのデータ階層はproject→dataset→tableとなっています。クエリ実行時に使うテーブルはprojectが選択されている事を前提として、dataset名.table名といった指定になるのでSSのようなFROM句になります。2.5秒で完了し、18GBをシークした旨が表示されています。簡単なクエリですが3億件を対象にを2.5秒で終了ですので、早さの一端が伺えますね。WebUIでも十分色々な事が出来ますが、自動化や処理に組み込む事を考えると、CLIやコードからの実行がしたくなります。以降ではそれを試してみようと思います。3.CLIで実行してみる準備で使用したCLI環境をそのまま使用出来ます。google-cloud-sdkにはBigQuery実行コマンドも含まれており、bqから始まるコマンドがそれにあたります。lsと入力してプロジェクトが表示出来ればうまく認証されています。CLI環境で使用するprojectを固定するためのコマンドを入力します。YOUR_PROJECT_IDでプロジェクトセット完了です。CLIの使い方ですが、公式のCLIページ(→https://cloud.google.com/bigquery/bq-command-line-tool)は網羅的ではないので、CLIのhelpを見るのがベストだと思います。helpとすれば使えるコマンド一覧で表示されます。表示されたコマンド毎の詳細も表示出来ます。queryと入力します。使用出来るオプションなどが一覧で表示出来ます。それではさきほどのクエリをCLIから実行してみます。(※この先のSSなのですが、表全体を表示するために大変文字が小さくなっています。申し訳ないのですが拡大するなどしてご覧ください)実行出来ました。結果は標準出力されています。次にオプションをつけて実行してみます。次の例ではジョブIDを振って(自分でIDを振らない場合は自動でランダムな文字列になります)実行しつつ、実行結果を別テーブルに保存します。保存先のtableが無いのでまずはdatasetとtable作成コマンドから実行してみましょう。tableを作成する時はschemaを設定します(schemaが長い場合はjson形式などのファイルにして読み込ませる事で指定する事も出来ます)。successfullyに作成出来たので、結果を保存するオプションをつけつつ実行してみます。WebUIで確認してみましょう。保存されているようです。他にもバルクロードや、テーブルデータのエクスポートなどもCLIから行う事が出来ますので、シェルスクリプトと組み合わせるなどすれば色々な操作が出来そうです。簡単にコマンドだけ紹介しておきます。バルクロードバルクロードしたデータをそのまま新テーブルとします。そのため引数にテーブル名とスキーマを渡しています。エクスポートエクスポートはGoogleCloudStorageに向けて行いますので、バケット名を渡しています。実行時間を見てもらえれば分かる通り、案外長くかかります。リアルタイムに結果が欲しいケースでは使いにくい速度です(ネット環境にもよると思います)。一応ロードに関しては手元→BQとアップロードするのではなく、予めGoogleCloudStorageにアップしておきGCS→BQとロードすると早くはなるようです。CLIでのクエリ実行結果をそのままファイルとして手元にダウンロードする機能は調べた限りありません。一応標準出力の形式をcsvやjsonに整形するオプションはありますので、リダイレクトと各種コマンドで出来ない事は無いですが、結果の行が大きい場合はクエリ結果を一旦テーブルとして保存し、そのテーブルをGoogleCloudStorageにエクスポートしてから手元にダウンロードするという手段になります。いった場合に上記のエクスポート機能が使えます。最後に、APIのClientLibraryを使ってコードから実行してみます。4.コードから実行してみるBigQueryにデータをロードする場合はGoogleCloudStorageからや、手元からのアップロードに加えて、ストリームとして挿入する機能もあります(→https://cloud.google.com/bigquery/streaming-data-into-bigquery)。今回は信頼と実績のfluentdを使ってこの機能を試しつつ、コードからクエリ実行をしてみようと思います。box上のCentOSで上記を動作させました。本筋では無いのでtd-agentの詳細は省きますが、inputとしてtwitter上のツイートをひたすら拾って、outputとしてBigQueryにinsertするというプラグインの組み合わせです。この設定でtd-agentを実行する事により常時データがBigQueryにinsertされている状態になります。Java)を使って以下のようにコーディングしました。}}実行するだけのコードなので流れはとても単純です。GoogleServiceClientクラスで認証を行い、認可されたクライアントを返し、そのクライアントを使ってQueryJobHandlerクラスでクエリを実行して、結果のコレクションを返します。(※今回はP12keyファイルを使っています。consoleから、APIと認証->認証情報->認証情報を追加->サービスアカウントと進めば取得出来ます。その時にサービスアカウント用のメルアドも発行されます。ClientLibraryなどで使うアドレスはこちらの物で、Googleアカウントのメールアドレスとは別です。)結果のコレクションの中身は以下のようになっています。値・・・(1セルに対してgetVすると具体的な値が取得出来る)これらの値を分解して、実行時刻をくっつけて、最終的にcsvとして出力しています。クエリそのものは定数クラスに書いてある通りです。メインに渡したカウントしたいワード4つをツイートから抽出します。td-agentによってツイートデータがBigQuery側に保存されている(FROM句のstream_dataがそのテーブルです)ので、それに対して上記クエリを実行します。今回はFROM句に細工があります。[bq_test.stream_data@-600000–1]この、「@-600000–1]が何かというと、BigQueryのテーブルデコレーターという機能です。これはテーブルにデータがインサートされた時間をミリ秒単位して指定して、その部分にだけクエリを実行するという機能になります。-1]という意味です。10分前〜1ミリ秒前にstream_dataテーブルにインサートされたデータのみを対象としてクエリ実行します(これは相対指定ですが絶対指定も出来ます)。このような指定を試したかというと、折角リアルタイム処理に適したstream-insert機能なので、10分毎の速報値を取ってみようと思ったからです。ちなみにリアルタイムに拘らずに時間別のツイート数が知りたいのであれば、元々TwitterAPIに呟いた時間の出力がありますので、このようにする必要はありません(笑)。“これから”にしてみます。それではシェルでラップして10分毎のcron実行で一晩放置してみようと思います。ZZZ...さて一晩置いて結果はどのようになったでしょうか。ログを見ると全て正常終了しているようです。結果のcsvを見てみたのですが、「これから」のツイートが多すぎます。「これから〜行く」などの予定を表す意味の使い方がだんだんと減るのではと思ったのですが、「これからもよろしく」などの用法も拾ってしまいますね。単語の選択がよろしく無かったです。いうわけで残り3つのワードについてグラフにしてみました。結果を見てみると日付変更のあたりで「日曜」の呟きが増えているのが分かります。「終電」「寒い」は案外増えていないですね。「終電」はもっと増えそうなものなのですが、そもそも手軽に試せるレベルではTwitterのStreamAPIで取得出来る件数がそれほど多く無い(一晩で30万件程度でした)ので、グラフでよく分かるほどの増加は無いという事でしょうか。ゴールデンタイムに芸能人の名前やテレビ番組名でやってみると数も多くなりそうで面白いかも知れないですね。いずれにせよBigQueryのStreaming-insertとそれに対するコードからの実行を試せたので入門としては良かったかなと思いました。最後にさて長々書いてきましたが、まだまだ心残りもあります。コードからの実行は出来る事が多いです。今回のクエリ実行の方法は最も手軽な方法です。Job(JobはAPI上で抽象的な概念で、クエリだけでなくロードやテーブル操作も含む)に設定内容をベタベタ貼り付けて1から作る方法もあります。そのようにクエリジョブを作った場合は色々なオプションや状態追跡が出来るので柔軟な実行が可能です。そちらも試したかったのですが、残念ながらブログには間に合いませんでした。引き続き無料期間の終了までに試したいと思います。最初にも言いましたが、無料期間が60日と長く3万円分を使い切る事も無いと思いますので、皆さんもぜひ体験してみてください。それではここまで読んでいただきありがとうございました。MaterialsEN。