32
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Posted at

#はじめに
某ノンアルコール梅酒の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

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

//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回で言えよ!ってなりますから。

#最後に
すいません、ざっくりで。
もし、わかんないこととか『こう書けば良い』とかご意見いただければです。
うちの子たちも見ているんで。(笑)

32
32
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
32
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?