肋骨が折れたかもしれん。痛え。それは置いといて…BigQuery。処理能力を体感したかったのでとりあえずMySQLの本番データをつっこんだ。fluentdでログも突っ込んでるんだけど、そっちはデータが溜まってないからまだおもしろくないかな。それについてはまた別途。まあ、fluentdでデータ突っ込むのはいろんな人がqiitaとかブログに上げてるし書くまでもないかもしれないけどね。



0. 作業の流れ

  1. MySQLからダンプを抜く
  2. ダンプをCloud Storageにuploadする
  3. Cloud Storage からbigqueryにインポートする
  4. クエリ投げる
という流れになる。この記事では深く言及しないが、Google Compute Platformのコンソールでプロジェクトの作成やら課金の登録やらが済んでいて、作業を行うマシンにはコマンドラインツールがインストール済みであるとする。
コマンドラインツールはcurl https://sdk.cloud.google.com | bashで一撃で入る。対話式にいろいろ聞かれるのでそこはよしなに。現時点でインストールされるバージョンはGoogle Cloud SDK 0.9.27。インストールしたら、gcloud auth loginで認証して、gcloud config set project xxxxxxxxxで操作のターゲットとなるプロジェクトを設定する。

1. MySQLからダンプを抜く

dスキーマのt0,t1,t2,t3,t4,t5,t6,t7,t8,t9の10個のテーブルをcsv形式で抜くとする。

1
2
mysqldump -uroot --tab=/data/sano_work/csv/ --fields-terminated-by=, --lines-ter
minated-by="\n" d t0 t1 t2 t3 t4 t5 t6 t7 t8 t9

なおここで、対象テーブルのカラムに「,」が含まれてしまっている場合、fields-terminated-byを適宜変更することで解決できる。例えばセパレータを「|」にする場合は次の通りである。

1
2
mysqldump -uroot --tab=/data/sano_work/csv/ --fields-terminated-by="|" --lines-ter
minated-by="\n" d t0 t1 t2 t3 t4 t5 t6 t7 t8 t9

これで、/data/sano_work/csv/配下に*.txtというファイル名でcsvが吐かれる。

2. Cloud Storageにupload

ダンプを抜いたら圧縮してからuploadした(しなくても良い)。ここでの例は、gzipしたファイルは「csv」というディレクトリに入っていて、cloud storageには「ore」というバケットが作成されているものとする。gsutilコマンドでcloud storageへのアップロードができる。

1
2
3
4
5
gzip *.txt

cd ..

gsutil rsync -d csv gs://ore/

注意点だが、圧縮したデータをcloud storageからbigqueryにロードする場合は、ファイルあたり4GB(CSV)までの制限があるので注意。なお、圧縮しない方がロードのパフォーマンスは良い模様。

https://cloud.google.com/developers/articles/bigquery-in-practice?hl=ja

Uncompressed or Compressed

When loading data from Cloud Storage, files can be either uncompressed or gzipped. Here are the trade-offs that need to be considered:

* Maximum file size limit for load jobs – The maximum file size for compressed file is 1GB whereas the limit for uncompressed file is 1TB. The exception for this is CSV files with new lines in strings where the maximum file size is 4GB.[4]
* Load Performance – Uncompressed file is loaded in parallel.
* Storage Cost – CSV and JSON files yield very good compression ratios and are more cost effective.
* Network bandwidth – Compressed files consume less network bandwidth.

For more information about the trade-offs between the two, please refer to Preparing Data for BigQuery.

ちなみに、こんな感じで怒られる。

bq load –source_format=CSV –field_delimiter=’|’ xxxxxx.xxxxx gs://ore/t0.txt.gz schema.json Waiting on xxxxxxxxxxxxxx … (13s) Current status: DONE
BigQuery error in load operation: Error processing job
‘xxxxxxxxxx:xxxxxxxxxx': Source is too large: gs://ore/t0.txt.gz. Maximum size is 4.00Gi.

3. bigqueryにロード

データセット(ds)を作る。xxxxはプロジェクトIDである。ここではbqコマンドを活用しているが、細かい仕様については公式ドキュメントを見てください。もちろんだが、GUIから対話式にロードすることもできる。

1
bq mk xxxxx:ds

で、cloud storageに入れたgzipをインポート。圧縮してあると割と時間かかる。圧縮後1.5G(圧縮前6Gくらい、60000000レコード程度)のファイルがインポートに小一時間かかった。

1
2
3
4
bq load --source_format=CSV --field_delimiter='|' ds.t0 gs://ore/t0.txt.gz schema.json
bq load --source_format=CSV --field_delimiter='|' ds.t0 gs://ore/t1.txt.gz schema.json
...
bq load --source_format=CSV --field_delimiter='|' ds.t0 gs://ore/t9.txt.gz schema.json

schema.jsonは次のような形式である(さっきの公式ドキュメントを(略))。GUIからインポートする場合はダイアログにhoge:string,moge:integerなどと指定する。今回はMySQLからデータを抜いたので、nameはカラム名、typeはDBの整数型はINTEGER、その他はSTRINGなどと指定しておけばとりあえずはいいだろう。

1
2
3
4
5
6
7
8
9
10
[
{
"name": "hoge",
"type": "STRING"
},
{
"name": "moge",
"type": "INTEGER"
}
]

4. クエリを投げる

xxxxプロジェクトのdsデータセットのテーブル一覧を見る(show tables的な)

bq ls xxxx:ds

xxxxプロジェクトのdsデータセットのtable_nameテーブルの構造の確認する(show create table table_name的な)

bq show xxxx:ds.table_name

なんかselectしてみる(全テーブルのカウント)

bq query –project_id xxxx “SELECT count(*) FROM ds.t00, ds.t01, ds.t02, ds.t03, ds.t04, ds.t05, ds.t06, ds.t07, ds.t08, ds.t09″

bq01

2秒くらい(画像は0sってなってるけど、たぶん結果がキャッシュされちゃってて…)

なんかselectしてみる(group byしてorder byとか)

bq query –project_id xxxx “SELECT foo, count(*) as c FROM ds.t00, ds.t01, ds.t02, ds.t03, ds.t04, ds.t05, ds.t06, ds.t07, ds.t08, ds.t09 group by foo order by c desc limit 10″

bq02

3秒くらい(画像は0sってなってるけど、たぶん結果がキャッシュされちゃってて…)

まあ早いね。

おわり

肋骨が痛い(‘A`)

 

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <img localsrc="" alt="">

Set your Twitter account name in your settings to use the TwitterBar Section.