株式会社グレンジで、データ分析と分析用サーバ運用を担当しているy-encoreです。
この記事は、グレンジ Advent Calendar 2018の20日の記事となっています。
BigQueryとは？
公式ドキュメント によれば、
「機械学習が組み込まれ、高速かつ高スケーラビリティでコスト効率に優れた、分析用フルマネージド クラウド データ ウェアハウス」
だそうです。
少し扱ってみての私の感想は
・大規模データにも余裕で対応し
・スケーラビリティも高く
・どんなクエリを打ってもかなり速くて
・扱いを間違えなければ料金も良心的で
・機械学習にも若干対応している
・フルマネージドな
分析用の基盤、といった感じだと思ってます
MySQLの既存データをBigQueryにインポートする
GCPの扱いについては省略して、BigQueryのWebUIを使って作業をします。
BigQueryにインポート先のテーブルを用意する
データセットがない場合には先に用意しておきます。
WebUIのリソースの欄から現在扱っているプロジェクトを選択し、「データセットを作成」からデータセットを作成します。
ここではデータセットIDを「mysql_import_test_dataset」としておきました。
有効期限などの設定は、必要ならしておきましょう。
データセットを作成したら、今度はテーブルを作成します。
WebUI上でのテーブルの作成方法は主に2通りで、クエリエディタからCREATE TABLE文を発行するか、
「テーブルを作成」ボタンからGUI操作でテーブルを作成するかです。
今回は少し細かい設定をしたいので、クエリエディタからCREATE TABLE文を発行してテーブルを作成します。
BigQueryにCREATE TABLEでテーブルを作成する
BigQueryで扱える型
下記リンク先の公式ドキュメントを参照したほうが早い気もしますが、ここでは主に使う型について軽く触れます。
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja
INT64: 整数型
UNSIGNEDは存在しないので、MySQLで扱っている時にBIGINT UNSIGNEDを使っている場合には注意が必要です。
また、整数型はこれ以外に現在は存在しない模様なので、整数系は全部これになります。
FLOAT64: 浮動小数点型
計算結果などはだいたいこれで返ってきます。
浮動小数点型も64bitのこれ以外は存在しない模様
STRING: 文字列型
可変長文字列型で、最大容量の記載は特に見つかりません。
UTF-8エンコーディングであることに注意。
他に気をつけることは、バイト型のBYTESとの相互変換は明示的に行わなければいけないこと、変換不可な場合はエラーが返ることくらいです。
DATE: 日付型
DATETIME: 日時型
TIME: 時間型
TIMESTAMP: タイムスタンプ型
およそMySQLのそれぞれの型と同じです。
後述しますが、BigQueryのテーブルのパーティションに利用できるカラムの型はDATE型とTIMESTAMP型だけなので、少し考慮する必要があります。
ARRAY: 配列型
STRUCT: 構造体型
MySQLから移行する場合にはあまり使わない型です。
が、集計などで出てくることがままあるので名前だけ並べておきます。
CREATE TABLE文を発行する
CREATE TABLEの書式の公式ドキュメントはこちら。
下記のような形でCREATE TABLEを発行します。
各カラムを型名とともに定義しているだけなので、MySQLを扱ったことがある人であれば雰囲気はつかめると思います。
CREATE TABLE mysql_import_test_dataset.daily_active_user (
user_id INT64 NOT NULL,
login_date DATE NOT NULL,
device_unique_id STRING NOT NULL,
os STRING NOT NULL,
user_level INT64 NOT NULL,
solo_quest_count INT64 NOT NULL,
multi_quest_count INT64 NOT NULL,
registration_data DATE NOT NULL,
create_time DATETIME NOT NULL
)
PARTITION BY login_date
OPTIONS(
description="This is test table!"
)
※実際のプロダクトで使われているものとは異なります
特徴的なのは、Primary KeyやIndexの設定が無いことかなと思います。
（BigQueryの仕様上、そういったものがない）
OPTIONSのdescriptionについてはお好みで。無くても特に問題はありません。
他にもOPTIONSは設定できますが、ここでは省略します
PARTITION BYの設定について
PARTITION BYは必要がなければ別に設定しなくても良いのですが、データ量が大きくなりそうなら入れておいたほうが無難です。
BigQueryは探索したデータ量に応じて課金されるシステムで、クエリの実行時は基本全件探索になります。
テーブルがパーティションされていて、そのパーティションを検索クエリで指定した場合のみ探索量を絞ることができるため、クエリ料金の節約になります。
このPARTITION BYで指定できるのは、
DATE(_PARTITIONTIME)
DATE(<timestamp_column>)
<date_column>
の三種類だけなので、上記の例だとcreate_timeはパーティション基準のカラムとして選択できません。
ここで突然出てきた「_PARTITIONTIME」は疑似カラムと呼ばれるもので、これを指定するとデータ挿入時の日時でパーティションを行ってくれます。
既存データを一括でインポートする場合には単純にやるとすべて同じパーティションに入れてしまう点には注意が必要です。
ただし、DATE(_PARTITIONTIME)がPARTITION BYに指定されている場合には、データの挿入時に無理やり指定パーティションにデータの挿入ができるので、回避が可能です。
DATETIME型でしかパーティションに向いたカラムがない場合には、挿入データの取得時にDATE型のカラムを追加してしまうのが個人的には楽です。
MySQLからインポート用のデータを作成する
BigQueryにMySQLの既存データをインポートする際は、一回CSVに吐き出すと楽です。
コマンドで直接打っても良いですが、シェルスクリプトを書いていきます。
sql="
SELECT
user_id,
login_date,
device_unique_id,
os,
user_level,
solo_quest_count,
multi_quest_count,
registration_data,
create_time
FROM
daily_active_user
WHERE
login_date BETWEEN '2018-11-01' AND '2018-11-30'
"
mysql -uXXXXXX log_database -e "${sql}" > daily_active_user_20181101_20181130.csv
CSVをGoogle Cloud Storageにアップロードする
ローカルにあるファイルをBigQueryに直接読み込ませることもできますが、一度GCSにアップロードしてから読み込ませたほうが速いです。
下記gsutil cpコマンドで一発です。
もちろんアップロード先は扱いやすいところに適当に置きましょう。
gsutil cp ./daily_active_user_20181101_20181130.csv gs://your-bucket/
BigQueryにデータを挿入する
こちらもbq loadコマンド一発で挿入できます
bq --location=US load --noreplace --source_format=CSV --null_marker="NULL" --field_delimiter="\t" --skip_leading_rows=1 mysql_import_test_dataset.daily_active_user gs://your-bucket/daily_active_user_20181101_20181130.csv
--location
テーブルのロケーションを指定します
--noreplace
データを上書きするか追加するかの指定で、--noreplaceで追加です
--source_format=CSV
csvからデータを読み込みます
--null_marker="NULL"
nullを示す文字列をNULLに指定します
--field_delimiter="\t"
フィールド区切り文字文字をタブ文字に指定します
--skip_leading_rows=1
今回出力したCSVには1行目にカラム名指定が入っているので、ヘッダ行を1行読み飛ばします。
あとは、インポート先のテーブルと、インポートするデータを指定します。
エラーが出る場合には、大体の場合は型が不正です。
特にマルチバイト文字はエラーが起こりやすいので、確認する必要があります。
データを指定パーティションに挿入する
今回は必要ありませんが、PARTITION BY DATE(_PARTITIONDATE)のテーブルに対してデータを指定パーティションに挿入する場合は、
bq --location=US load --noreplace --source_format=CSV --null_marker="NULL" --field_delimiter="\t" --skip_leading_rows=1 mysql_import_test_dataset.daily_active_user\$20181101 gs://your-bucket/daily_active_user_20181101.csv
のように、テーブル名に\$YYYYMMDDを付ければ、そのパーティションに挿入されます。
インポートしたデータを確認する
今回作成したテーブルをBigQueryのWebUIから確認します。
ページ左側のリソース欄から作成したテーブルを選択し、プレビューを選択すると、最初の50件が表示されます。
正しいデータが表示されていれば作業完了です。
BigQueryを少しだけ使ってみる
今回作成したテーブルに対してクエリを発行します。
WebUIでページ左側のリソース欄から作成したテーブルを選択し、「テーブルをクエリ」ボタンを押すと、
SELECT FROM `test-project.mysql_import_test_dataset.daily_active_user` WHERE login_date = TIMESTAMP("2018-11-01") LIMIT 1000
このようなクエリが入力されます、が、動かない状態です。
2019/2/6 追記:
PARTITION BYの対象としてDATE型のカラムを設定しているとき、自動補完クエリがTIMESTAMP型で比較しようとしていたものが修正され、DATE型で比較されるようになっていました。正しいクエリにするにはSELECT句内のカラム指定をするだけでOKです。
追記ここまで
修正して、さらに、「展開→フォーマット」をして、
SELECT
user_id,
login_date
FROM
`test-project.mysql_import_test_dataset.daily_active_user`
WHERE
login_date = "2018-11-01"
LIMIT
1000
を入力した状態にすると、クエリエディタの右下に緑色の文字で「このクエリを実行すると、10.01 MB が処理されます。」といった表示が出ます。
赤のビックリマークが出ている状態の場合は作成したクエリにエラーが残っているので、修正の必要があります。
問題がなければ「実行」を選択します。
結果はWebUI上で確認できる他、CSV/jsonでの直接ダウンロードや、CSV形式でのGCSエクスポート、BigQueryの新規テーブルへの保存などができます。
クエリ料金について
先程の緑の文字で表示されていた「このクエリを実行すると、10.01 MB が処理されます。」という表示で、実行しようとしているクエリがどのくらいデータを探索するかを示しています。
この処理バイト数が、クエリ課金の容量バイト数になります。
クエリ料金はドキュメントの通り、1TBあたり5$となっています。
（また、毎月1TB分のクエリ無料枠もあります）
ちなみに、GCSからBigQueryにデータを読み込む時には料金はかかりません。
BigQueryにおいては、クエリで参照されないカラムについては探索が行われません。
そのため、必要なカラムのみにアクセスするようにクエリを作成することでクエリ料金を抑えることができます。
逆に、SELECT *などと全カラムにアクセスすると、テーブルすべてに対して全件探索をすることになり、処理バイト数が巨大になる可能性があるので注意します。
各カラムは参照されるかどうかがデータ量に関わるので、WHERE句内でのみ使われているカラムなどもクエリ料金に加算されることにも注意です。
期間アクティブユーザを出してみる
今回のテーブルには、各日付ごとにその日のアクティブユーザがユーザIDごとに保存されています。
期間中に1度でもログインした記録のあるアクティブユーザのユーザID数を出してみます。
SELECT
COUNT(DISTINCT user_id) AS term_active_user_count
FROM
`test-project.mysql_import_test_dataset.daily_active_user`
WHERE
login_date BETWEEN "2018-11-01" AND "2018-11-30"
単純なクエリですが、移行前のMySQL上で同じクエリを実行すると10秒弱かかるくらいのデータ量です。
しかしこれがBigQuery上だと1秒ちょっとで終わります。
単純なクエリですら速度に大幅な違いが出るあたり、BigQueryの底力が伺えます。
さいごに
「MySQLから移行してBigQueryを少し使ってみた」ということで、「使ってみた」成分がだいぶ薄いのですが、データの移行からBigQueryでクエリを投げるまでの流れをなんとなく掴んでいただけたら幸いです。
BigQueryはより大規模であったり、より複雑なクエリを使う場合に素晴らしい効力を発揮します。
MySQLで処理をしていた頃ではやりたくなかった重さのクエリでも、BigQueryならば投げることができて、使っていると楽しくなるのでおすすめです。
この記事は株式会社グレンジのグレンジ Advent Calendar 2018の20日の記事でした。
19日→CRIを使ったピッチ変更実装のハマりポイント
21日→CEP & C++ネイティブコードによるPhotoShop拡張
他の記事も是非見てみてください。
以上、ここまで見てくださってありがとうございました！