Google BigQuery / Tableauを使ってみた


TableauからGoogle BigQueryへ接続してみました。
弊社で利用しているTreasureDataからデータ出力してBigQueryへロード、Tableauから接続まで実際に行った手順について記載します。

TreasureDataからAmazonS3へデータ出力

まず、データが蓄積されているTreasureDataからAmazonS3へファイル出力します。
やり方は簡単で、コンソール上の”Result Export”から”AWS S3/Riak CS”を選択して、APIキーや出力先のディレクトリを選択するだけです。
Compressionオプションで”gz”を指定したのですが、これが仇になるとは。。。詳細は後述します。
(GoogleCloudStorageへ直接出力できたら嬉しいな、なんて思いつつ)

AmazonS3からGoogleCloudStorageへデータ転送

AmazonS3からGoogleCloudStorageへのデータ転送はgsutilを使います。

gsutilのインストール

[code]
wget https://storage.googleapis.com/pub/gsutil.tar.gz
tar xvzf gsutil.tar.gz
cd ./gsutil
./gsutil config
[/code]

すると認証用のURLが表示されるので、アクセスして認証コードを入れるだけです。簡単です。

configファイルの編集

インストール設定が完了すると、.botoファイルが生成されます。
AmazonS3から転送を行うためのAPIキーとシークレットキーを設定する箇所があるので設定します。

[code]
#aws_access_key_id = <your aws access key ID>
#aws_secret_access_key = <your aws secret access key>
[/code]

あとは以下のような感じで、S3からCloudStorageへの転送ができます。

[code]
gsutil -m rsync -r s3://(bucket)/(path)/ gs://(bucket)/(path)/
[/code]

BigQueryへのロード

bqコマンドを利用するので、GoogleCloudSDKをインストールします。
インストール方法についてはhttps://cloud.google.com/sdk/に書いてあります。
※コンソールからでもロードはできます。

ファイルについてはtsvで出力したので、以下のコマンドでロードします。

[code]
bq load –source_format=CSV –field_delimiter="\t" (Dataset).(Table) gs://(bucket)/(path)/xxxxxx.gz schema.json
[/code]

フォーマットとしてCSV、tsvファイルなのでデリミタとしてタブ、ロードするファイルのCloudStorageのパス、テーブルスキーマを設定しているjsonを指定します。

loadコマンドのオプションについてはここに詳細が記載されています。

実に簡単だなーと思ったら、ここでつまずきました。ロード失敗。
よくよく調べてみると、CSVフォーマットで圧縮ファイルの場合4GBが上限
ちゃんと書いてありましたね。
https://cloud.google.com/bigquery/loading-data-into-bigquery#quota

かなり馬鹿っぽいんですが、再度ダウンロード、解凍、アップロードしなおしてなんとかロード成功。

今回ロードするファイルがいくつかあり、4GBを超えるものとそうなでないものがあったので、圧縮と非圧縮ファイルのロードのパフォーマンスを比べてみました。
結果は以下のとおりです。

ファイル 圧縮 サイズ サイズ(非圧縮) ロード時間(秒) MB/sec
001 6.25GB 26.76GB 259 105.8
002 6.25GB 26.75GB 249 110.0
003 6.25GB 26.78GB 249 110.1
004 6.25GB 26.78GB 250 109.6
005 ◯(gz) 68.51MB 294MB 35 8.4
006 ◯(gz) 145.21MB 622MB 87 7.1

圧縮と非圧縮でファイルサイズが全然違うので、これだけではなんともいえないですが、
これだけ見ると、非圧縮のほうが10倍くらい速いですね。

BigQueryを使ってみる

本当にデータがロードされたのかコンソール上からクエリを投げて確認してみます。
とりあえず、カウント。
bq1
約3億2千万レコードがロードできました。1.2秒で出力されました。

Tableauから接続

やっとここまで来ました。Tableau経由で接続してみたいと思います。

  • Tableauのデータ接続から”Google BigQuery”を選択します。
  • 認証画面が出るので、GCPが利用できるGoogleアカウントのID/パスワードを入力します。
  • プロジェクト>データセットを選択するとテーブル一覧が表示されるので選択します。

あとは他のデータソースと同じ形でいけます。
今回、BigQueryにはAudienceOneのテストデータを入れました。
なので、こんな感じでユーザの分析ができます。3億レコード入ってますがサクサクできました。

bq2

TableauからはRedshiftに接続して利用することが多かったのですが、BigQueryもアリですね。
とはいえ、レコード数が多くなるとクエリ単位に課金されてしまうので、若干コストが心配ですが。


DACエンジニア採用情報

  関連記事

fastavroとjqでAVRO形式のファイルからデータを取得しよう

AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。 コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。 弊社内での一部システムのログデータにも利用されているのですが、専用の …

Amazon Redshiftのパフォーマンスチューニング #1 アーキテクチャ概要

ご挨拶 こんにちは。システム開発部の中村です。 現在新卒入社2年目で、普段は受託開発の要件定義等の業務が主担当だったりします。 このブログの発起人というか、まあ言い出しっぺという事で初稿を上げさせて頂きます。 今回はAmazon Web ServiceのDWHサービス、Redshiftのパフォーマン …

HivemallでMinhash!〜似てる記事を探し出そう。〜

こんにちは。俺やで。 前回の投稿に続き(間が空きましたが)、 ビッグデータに対応したHiveで使える機械学習ライブラリ、 「Hivemall」の使い方、第2弾となります。 今回はMinhashという手法について書きたいと思います。 ※前回 【超入門】Hivemallで機械学習 〜Treasure D …

Tableauを利用してMySQLとRedshiftのクロスDBジョインを実現する

はじめに RedshiftやTreasureDataなどのデータマート用のDBにはID単位の解析結果が格納され、ローカルのMySQLにはIDに紐づいた名称マスタが管理されている構成の場合、データマートのクロス集計結果に対してIDに紐づいた名称を付与したいことがあります。 データマート用に用意したDB …

GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得

またまたTreasureDataネタです。 ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。 はじめに ログデータだけではなく、公 …

Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう

DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にt …

ディープラーニングで「顔が似ているAKB48のメンバーを教えてくれるbot」を構築

概要 こんにちは、システム開発部の中村です。 今回は、Facebook Messenger APIを利用して、 画像をアップロードすると、似ているAKB48のメンバーを教えてくれるbotを実装しました。 尚、ディープラーニングやTensorFlowそのものの解説というより、 「エンジンとしてディープ …

巨大データベースのスケールアップと引越作業

はじめに ビッグデータ解析部でオーディエンスデータ解析基盤の開発、運用を担当している Mike です。 弊社ではインターネット広告配信ログをはじめとする「ビッグデータ」と呼ぶにふさわしいデータボリュームを扱うオーディエンスデータ解析基盤を構築しています。今秋、そのうちの1構成要素である、データサイズ …

D3.jsとその活用事例について

D3.jsとは? D3とは「Data Driven Document」の略で、データに基づいてドキュメントを操作するための JavaScript ライブラリです。 ご存知の方も多いと思いますが、ちょっとだけD3.jsの基本的な使い方、そして弊社プラットフォームでの利用についてご紹介したいと思います。 …

PyStanによるはじめてのマルコフ連鎖モンテカルロ法

はじめに こんにちは。システム開発部の中村です。 社内で行っている『データ解析のための統計モデリング入門』(所謂緑本)の輪読会に参加した所、 大変わかりやすい本だったものの、Macユーザには悲しい事に実装サンプルがWinBUGSだったため、 9章の一般化線形モデルのベイズ推定によるアプローチをPyt …