GoogleCloudPlatform
GoogleBigQuery
BigQuery

データインポートとDATASETとTABLEと。

More than 3 years have passed since last update.


はじめに

某ノンアルコール梅酒のCM。おっしゃる通り。ごめんなさい。

と、いうことでデータインポートとDATASETとTABLEについて。

とにかく安いんでぼこぼこデータ追加しちゃえばいいんですけど、私なりの考えをまとめてみました。


前提

CloudSDKは入れておきましょう。

https://developers.google.com/cloud/sdk/

まぁ、webの管理コンソールでも全部できちゃいますけどね。

サイオスさんも詳しく書いてくれていますよ。

http://sstd-bigdata.blogspot.jp/2013/07/google-bigquery-bq-command.html


データ準備

さて、どんなデータ入れるかな?実務のデータを入れると速さを肌で感じることが出来ますね。

ただし、こういうものの特徴でデータ追加は出来るけど、更新・削除はできません。

そんな時のことを考えると以下のようなところに気を付けてデータを作ると良いと思います。

私自身なんども泣きましたから。(笑)

①ユニークな値を入れておく。

元データに持っているユニークな値を入れておきましょう。

例えば伝票番号とか、レコード番号とか。入れる時に付与するんじゃなくて、あくまで元データのものを入れておいてください。

②データを入れ込んだ時間(もしくは元データに持っているならそれ)を入れておく。

『更新した時間』を持っていると良いです。秒単位まで。TIMESTAMPで持たせます。

①と組み合わせて重複レコードもユニークに見せることが出来ます。

また、BigQueryはフルスキャンが売りですが、WHERE句などでTIMESTAMPの範囲を指定するときちんと見てくれる気がする。

ちなみにTIMESTAMPがNULLだと怒られます。そんな時はSTRINGにしちゃえば良いです。

③出来るだけ非正規化する。

『この項目使うかなぁ?』と思ったら入れちゃいましょう。

BigQuery上の結合(JOIN)も速いけど、やっぱり入れておくと後で『しまったぁ!』と思いません。

レコード数にもよりますが、多少データ量増えても月に10円ぐらいしか変わんないと思いますよ。

でも、①②を入れておくとあとで追加も工夫次第で簡単にできます。

④その他

・文字コードはUTF-8

・『'』、『"』、『半角スペース』は全角に変換。(意外とある。半角スペースでどハマりしました。)

・作ったデータはgzipで圧縮


先に言っておく!

ここからDatasetとかTableとかFieldとかのお話をしますが、先に言っておきます。

名前の先頭に数字は使っちゃダメ!

例えば7days_dataなど。先頭以外だと問題ないです。Day7_dataは大丈夫。

警告も無しに作れます。でもね、JOINとかすると『そんなテーブルなど無い!』と怒られます。単独だと大丈夫なんだけどなぁ。サポートに聞いたら『あ、ホントだ!』と言われました(笑)

理由?そんなもの知らない。そういうもんなんです。気にするな!


DATASET

簡単にいうとOracleのスキーマみたいなもの。

データセット単位で共有設定が出来ます。ユーザーだけじゃなくGoogleグループ単位でも共有可能です。

(※Apps For Businessのドメインと紐づけている場合は、そっちのグループじゃないとダメっぽい?)

うちではVIEW専用とか商品マスタ専用とか注文専用とかに分けたりしてます。

移動(コピー)も楽なんで最初のうちは考えなくても大丈夫です。


TABLE

うちの場合は更新するものが多いので『データインポート用』と『参照用』に分けてます。

『元データ』⇒『データインポート用』⇒『参照用』って感じで入れてます。

(理由とかやり方はのちほど)

TABLE_WILD_CARDが使えるようになったので、日付単位でテーブルを分けるってのも良いですね。

データリカバリが楽になりますし、クエリで見るデータ量も削減できます。ただし、TABLE_WILD_CARDは色々と制限があるので注意が必要です。

TABLE_WILD_CARD使うとJOINが出来ないとか、VIEWが作れないとか。そのあたりは工夫しだいですけどね。

FIELDの種類はこちらに。

https://developers.google.com/bigquery/query-reference?hl=ja#castingfunctions

(こんなページのど真ん中じゃなくて、最初に書こうよ(笑))

文字列はSTRING、整数値はINTEGER、小数点がある数値はFLOAT、日付はTIMESTAMPで。あとは使ったことない!すんません。

準備のところでも書きましたが、もし日付のFIELDにNULLのデータがある場合はSTRINGで指定しちゃいましょう。時間(日付)で計算させたければ、あとでSELECT文のところでTIMESTAMPと型変換すりゃいいです。


データインポート

さて、準備が出来たら入れちゃいましょう。

やり方は色々あります。が、バルクインサートしか書きません。(笑)

Googleの人が見たら、『いやいや、StreamingInsertもウチの売りなんですけど』と言われそうですが、それは別の機会に。今回はこっちに焦点を当てます。

bqコマンドやブラウザからローカルデータを指定して、アップロードも出来ますが時間かかります。

gsutilコマンド使ってGoogleCloudStorageに準備したgzipのデータをアップロードして、bqコマンドでデータインサートするのが楽だし速いと思います。

コマンドなどはこちら。

https://developers.google.com/bigquery/bq-command-line-tool?hl=ja

うちの場合はこんな感じ。

bq load --source_format=CSV testset.day7_data gs://testdata/test.csv.gz "PRODUCT:STRING,DATAYMD:TIMESTAMP,AMNT:INTEGER,DIVID:FLOAT"

bqはコマンド、loadでデータ読み込み指定、DATASET名.TABLE名でロード先を指定、gs://~でGoogleCloudStorageのファイルパスを指定、"PRODUCT:STRING~"でField名と型を指定。

この時にTABLEが無い場合は勝手に作ってくれます。

慣れるまではloadの後に--max_bad_records 1000とかでエラー行を無視する限界を指定すると良いかと。

指定しない場合は1つでも見つかるとそこで全部ロールバックされます。これを指定するとwebの管理コンソールで『Job History』を見るとダメなデータのパターンが見えてきますよ。1回ずつのトライ&エラーは辛いですよ。1回で言えよ!ってなりますから。


最後に

すいません、ざっくりで。

もし、わかんないこととか『こう書けば良い』とかご意見いただければです。

うちの子たちも見ているんで。(笑)