・08.Google App EngineとBigQuery 導入編(1/3)
・09.Google App EngineとBigQuery 実践編(2/3)
・10.Google App EngineとBigQuery ノウハウ編(3/3)
CA Beat エンジニアリーダーのヤマサキ(@vierjp)です。
BigQuery3連続も今回で最後、「10.Google App EngineとBigQuery ノウハウ編(3/3)」です。
前回までの記事で一通りの使い方を説明してきましたが、
その上で、実際に業務の中で使ってみると困ったりハマったりする事があります。
今回は
・BigQueryの制限とその回避策
・日本時間を扱う際のTips
・BigQueryでApp EngineのDatastoreのデータを解析するために考慮しておくと良いこと
です。
○Joinの制限
前回書いた通り、BigQueryは構文的にjoinができます。
しかし、joinするテーブル(メインのテーブル以外)のサイズに制限があります。
ドキュメントでも「BigQuery supports "small" joins between tables.」という書き方をしています。
参考:JOIN(Query Reference - Google BigQuery)
joinするテーブル(メインでない方のテーブル)のサイズが一定以上に大きい場合
「Large table [dataset_id].[table_id] must appear as the leftmost table in a join query」
というエラーが発生します。
サイズの制限がレコード数ではなく「圧縮されたデータで8MB未満」なので
はっきりと条件を測りづらいところです。
(圧縮率も書いてはありますけど)
以下は「BigQueryでJoinの制限を回避する方法」として試行錯誤した内容ですが、
後半に行くほど苦しい内容になっていきます。
後半は「事前に考えておかないと面倒になるかも」という
失敗談として読んでいただくのが良いかもしれません。
注文データ(OrderDeatil)と商品マスタ(Item)をJOINして
月別に商品毎の売上を取得する場合を考えてみましょう
・まずは普通に
SELECT
LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_MONTH(PARSE_UTC_USEC(d.orderDate))),7) as orderMonth,
i.itemId as itemId,
i.itemName as itemName,
count(*) as count,
SUM(d.price) as sales
FROM
[Order.OrderDetail] d
JOIN [Order.Item] i on d.itemId = i.itemId
WHERE
d.cancelFlag=false
GROUP BY
orderMonth, itemId, itemName
ORDER BY
orderMonth,itemId
| Row | orderMonth | itemId | itemName | count | sales |
|---|---|---|---|---|---|
| 1 | 2012-10 | item1 | 商品名01 | 2 | 200 |
| 2 | 2012-10 | item2 | 商品名02 | 2 | 400 |
| 3 | 2012-10 | item5 | 商品名05 | 1 | 500 |
| 4 | 2012-11 | item1 | 商品名01 | 2 | 200 |
| 5 | 2012-11 | item3 | 商品名03 | 2 | 600 |
このクエリをエラーなく実行できるかどうかは「商品マスタ」のデータ量次第です。
このように「注文明細」をメインのテーブルに「商品マスタ」をjoinする場合、
商品の量が少なければ良いですが、商品が大量に(数十万～数百万件？)ある場合には苦しくなっていきます。
逆に「店舗マスタ」のように十分に少ないであろうデータをjoinするなら余裕です。
このJoinするテーブルの大きさに関する制限は
前述のように「サイズ」に対する制限です。
よって、以下のような方法でサイズを減らすことができます。
・サブクエリにしてサイズを減らす
SELECT
LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_MONTH(PARSE_UTC_USEC(d.orderDate))),7) as orderMonth,
i.itemId as itemId,
i.itemName as itemName,
count(*) as count,
SUM(d.price) as sales
FROM
[Order.OrderDetail] d
JOIN
(SELECT
itemId, itemName
FROM [Order.Item]
WHERE
deleteFlag=false
) i on d.itemId = i.itemId
WHERE
d.cancelFlag=false
GROUP BY
orderMonth, itemId, itemName
ORDER BY
orderMonth,itemId
| Row | orderMonth | itemId | itemName | count | sales |
|---|---|---|---|---|---|
| 1 | 2012-10 | item1 | 商品名01 | 2 | 200 |
| 2 | 2012-10 | item2 | 商品名02 | 2 | 400 |
| 3 | 2012-10 | item5 | 商品名05 | 1 | 500 |
| 4 | 2012-11 | item1 | 商品名01 | 2 | 200 |
| 5 | 2012-11 | item3 | 商品名03 | 2 | 600 |
もちろん結果は同じです。
このように、JOINする際はテーブルをそのままJOINするのではなく、
サブクエリにした上で、
・取得するカラムを必要最低限にする
・不要なレコードをWHERE条件で省く
・場合によってはgroup byしてユニークにする
といった方法で「結果の表データ」の"サイズ"を減らすことである程度回避できます。
*「売った後に削除された商品があるかもしれないから削除済み商品を除外しちゃまずいんじゃない？」
という業務的なツッコミはなしでお願いします。
「商品の削除は3ヶ月に一回の見直しで行われ、今回の集計対象期間より前に削除されています。」
ってことで。(´・ω・｀)
どちらをメインのテーブルにするかも重要です。
joinできないほど商品数が多いなら、逆にするのも良いでしょう。
・全件必要なテーブルをメインにして、サブクエリは少しずつ指定
SELECT
LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_MONTH(PARSE_UTC_USEC(d.orderDate))),7) as orderMonth,
i.itemId as itemId,
i.itemName as itemName,
COUNT(*) as count,
SUM(d.price) as sales
FROM
[Order.Item] i
JOIN
(SELECT
orderDate, itemId, price
FROM [Order.OrderDetail]
WHERE
cancelFlag=false
AND orderDate>='2012-10-01' and orderDate<'2012-11-01'
)d on d.itemId = i.itemId
GROUP BY
orderMonth, itemId, itemName
ORDER BY
orderMonth, itemId
| Row | orderMonth | itemId | itemName | count | sales |
|---|---|---|---|---|---|
| 1 | 2012-10 | item1 | 商品名01 | 2 | 200 |
| 2 | 2012-10 | item2 | 商品名02 | 2 | 400 |
| 3 | 2012-10 | item5 | 商品名05 | 1 | 500 |
注文データは時間とともに増えていくデータですが、
月単位で取得・集計するなら必ずしも全データを一度に取得する必要はありません。
10月分と11月分を別々に集計しても良いはずです。
上記の方法でもサイズ制限でエラーが出るなら
注文データの期間を1ヶ月から10日にすればサイズはさらに1/3になります。
このように結果のレコード数が少なくなるように小さいクエリに分けるのも有効です。
その上で一ヶ月間のアイテム毎の注文数を求めたいなら、
一ヶ月を10日ずつに分けたクエリの結果を
「Sales201210_1」「Sales201210_2」「Sales201210_3」
とそれぞれ別のテーブルとして保存した上で、
次のようにUNIONしてから「GROUP BY」します。
・小分けにJOINして結果をUNIONしてからGROUP BY
SELECT
orderMonth,
itemId,
itemName,
SUM(count) as count,
SUM(sales) as sales
FROM
[Order.Sales201210_1],
[Order.Sales201210_2],
[Order.Sales201210_3]
GROUP BY
orderMonth, itemId, itemName
ORDER BY
orderMonth, itemId
| Row | orderMonth | itemId | itemName | count | sales |
|---|---|---|---|---|---|
| 1 | 2012-10 | item1 | 商品名01 | 2 | 200 |
| 2 | 2012-10 | item2 | 商品名02 | 2 | 400 |
| 3 | 2012-10 | item5 | 商品名05 | 1 | 500 |
ここまで来るともうかなり苦しいですね。
集計内容や条件によっては、
日が経ってデータが増えるとまたエラーが出てしまうような、
その場しのぎの対策にしかならないかもしれません。
サブクエリで「レコード数」を絞って回避できるなら良いですが、
「カラム数」を絞って回避したような場合は長期的には怪しいと言えるでしょう。
そして何より面倒です。
根本的な対策としては、
1.解析の都合上冗長化しても構わないならDatastoreの設計レベルで冗長化しておく
2.冗長化した項目の値が変わる頻度が低いなら、「冗長化した上で値変更時にTaskQueueで非同期更新する」
3.上記のように小分けの期間にした上で自動化する。
のが良いでしょうか。
○"基本的に"データの編集はできませんが・・・
導入編で引用した中にもありましたが、データの編集はBigQueryにとって苦手な操作になります。
「データの編集」とは「インポート済みの値の変更」や「スキーマ定義の変更」を指します。
たしかに「インポート済みの値の変更」や「スキーマ定義の変更」自体はできないのですが、
状況によっては別の方法で目的を満たす事ができるしれません。
導入編でアップロードしたテスト用の適当なCSVデータから作成した「Test」を例にします。
| id | item1 | item2 | item3 | date |
|---|---|---|---|---|
| AAAA | item1A | item2A | item3A | 2012-11-21 00:00:00 |
| BBBB | item1B | item2B | item3B | 2012-11-22 00:00:00 |
| CCCC | item1C | item2C | item3C | 2012-11-23 00:00:00 |
に対して、
以下のクエリを実行します。
SELECT id,
UPPER(item1) as item1,
LOWER(item2) as item2,
REGEXP_REPLACE(item3,'item','Hoge') as item3,
date,
FORMAT_UTC_USEC( PARSE_UTC_USEC(date) + 32400000000) as dateJst,
false as HogeFlag
FROM [test.Test]
LIMIT 1000
| Row | id | item1 | item2 | item3 | date | dateJst | HogeFlag |
|---|---|---|---|---|---|---|---|
| 1 | AAAA | ITEM1A | item2a | Hoge3A | 2012-11-21 00:00:00 | 2012-11-21 09:00:00.000000 | false |
| 2 | BBBB | ITEM1B | item2b | Hoge3B | 2012-11-22 00:00:00 | 2012-11-22 09:00:00.000000 | false |
| 3 | CCCC | ITEM1C | item2c | Hoge3C | 2012-11-23 00:00:00 | 2012-11-23 09:00:00.000000 | false |
item1を全て大文字に、
item2を全て小文字に、
item3の「item」を「Hoge」に置換、
dateはそのまま、
「dateJst」という名前で「dateに+9時間した日本時間"相当"の文字列」を追加
「HogeFlag」という名前で定数「false」を追加。
クエリ実行後に「Save as Table」でクエリ結果を「Test2」という名前で保存する。
クエリ結果をそのまま使って新しいテーブルを作成しました。
一応これで「値の変更」、「既存の値を元にしてカラムの追加」、「固定の値を指定してカラムを追加」
に近い事ができました。
まあ"変更"はしてないですし、
クエリ結果のデータ量があまりに多いと「Response too large to return」というエラーが発生してしまうので、
そこそこ大きいテーブルだと「Save as Table」できず、この方法は使えません。
「Response too large to return」が発生した際には、
前述の「JOINの制限回避」と同様に、データを日付等で一定の範囲ずつ取得して「Save as Table」で保存、
クエリする際にUNIONする、という上述の「JOINの制限回避」と同じ方法を取ることはできますが、
やはり参照時にUNIONの手間が増えるのが面倒です。
あとは小分けにしたテーブルを手動で「Download as Table」してからもう一度追記アップロードするしか。
(これはもう再アップロードですけど)
BigQueryはクエリ結果のサイズ制限があるので、
「Save as Table」だけでなく、「Append to Exist Table」のような機能があって小分けにしてどんどん追記できれば、
参照時にUNIONしなくて済んで助かるのですが。
小分けにしたテーブルを手動で「Download as Table」してから追記Uploadするのと同じと考えれば、
実現不可能ってことは無さそうな気がしてしまいます。
もしくはもっとシンプルに「同じスキーマのテーブルを一つにまとめる機能」でもいいのですけど。
「データの編集」の代わりとしてのこの方法は特にオススメするわけではないですが、
ここでの考え方が前述の「JOINの制限回避方法」に繋がっていたりもしています。
何かの役に立つ機会もあるかもしれないということで、一応ご紹介しておきます。
○日本時間の日付で集計したい場合
例えば日付ごとにGROUP BYしたいような場合を考えてみましょう。
「実践編」で書いたように、
BigQueryの日付関数にTimezoneの指定が無いためちょっとややこしいです。
以下のクエリを見てください。
SELECT
timestampUtc,
FORMAT_UTC_USEC(timestampUtc) as readableTimestampUtc,
FORMAT_UTC_USEC(UTC_USEC_TO_DAY(timestampUtc)) as readableDayUtc,
FORMAT_UTC_USEC(timestampUtc+ 32400000000) as readableTimestampJst,
FORMAT_UTC_USEC(UTC_USEC_TO_DAY(timestampUtc+ 32400000000)) as readableDayJst
FROM
(SELECT PARSE_UTC_USEC('2012-11-13 23:00:00') as timestampUtc)
結果
| timestampUtc | 1352847600000000 |
| readableTimestampUtc | 2012-11-13 23:00:00.000000 |
| readableDayUtc | 2012-11-13 00:00:00.000000 |
| readableTimestampJst | 2012-11-14 08:00:00.000000 |
| readableDayJst | 2012-11-14 00:00:00.000000 |
Hour以下を切り捨てる関数は「UTC_USEC_TO_DAY(timestamp_usec)」で、
引数はUnix時間です。
Unix時間をそのまま引数に渡した場合、
関数の名前通り、UTCの時刻からHour以下が切り捨てられます。
そのため、保持している時刻のTimezoneを意識しないと、期待と異なる結果になる場合があります。
保持しているのがUnix時間で11月13日23時(UTC)だとした場合、JSTでは11月14日8時です。
「日本時間の日付」で集計するならこれは11月14日のデータとして集計されなければなりません。
しかしそのまま「UTC_USEC_TO_DAY」すると、UTCの時刻からHour以下が切り捨てられ、
11月13日として扱われてしまいます。
日本時間で集計したいのであれば、
先に+9時間してからHour以下を切り捨てます。
・まず+9時間します。(timestampUtc+ 32400000000)
→これで11月14日8時(UTC)になります
・その上でUTC_USEC_TO_DAYする。
→11月14日0時(UTC)になる。
実際には11月14日8時(UTC)と11月14日8時(JST)」は同じ時間ではないので、UNIX時間にParseした時を考えると違和感があるのですが、
「TimeZoneを指定してHour以下を切り捨てる」ということができないなら
やはりUTCの時間に無理矢理+9時間して扱うしかないのかな、と。
データ上で日付を文字列で保持している場合、
日付文字列がUTCの時刻なら「PARSE_UTC_USEC」でUnix時間にした上で+9時間してください。
日付文字列がJSTの時刻なら、上でいう「+9時間済み」として、そのままPARSE_UTC_USECしてください。
最後に、
LEFT(readableDayJst,10)で、「2012-11-14」という文字列にします。
(「2012-11-14 00:00:00.000000」という形式の文字列の10文字目までを取得しています)
○BigQueryでDatastoreのデータを解析するために考慮しておくと良いこと
現状はDatastoreのデータをBigQueryに持っていくために、
一度csv形式でローカルにdump→ローカルからBigQueryにアップロードという手順が必要ですが、
「導入編」に書いた通り、
いつのまにかGoogle Cloud Storage経由でDatastoreからBigQueryにコピーできるようになっていました。
(公式のリリースはまだ見てないのですが)
これまでcsv形式でdumpしてからアップロードしてきた事を考えると間違いなく便利な機能ですが、
直接コピーするということは
「DatastoreからBigQueryにデータをコピーする間に値やスキーマを変換するタイミングが無い」
という事でもあります。
よって、Datastore設計の時点からBigQueryでの解析を考慮しておくのが良いでしょう。
BigQueryのドキュメントを見ると、「データをコンバートしつつBigQueryに転送する方法」として、
自前のプログラムから転送する方法が書いてありますが、
転送コスト的にも手間的にも、できればバックアップファイルから転送したいところです。
Codelab: Extract and Transform App Engine Datastore Data for Analysis with BigQuery - Google BigQuery — Google Developers
・Keyを構成する項目を個別のプロパティとして定義しておく
Keyをビジネス的な値を連結して生成している場合、Keyの構成項目を個別のプロパティとして定義しておくと、Joinする際に楽で良いです。
BigQueryだけで使う目的ならこのプロパティにインデックスは必要ありません。
インデックスが必要無いならプロパティを追加しても「Datastore Write」の課金額は増えませんし、
Keyのname値を構成する値は最大で500文字なので、データ量的にもそれほど気にすることは無いでしょう。
・時刻の保持の仕方
Entityに保持する時刻はDate型で保持するか、
BigQueryの関数でそのままUnix時間に変換できる「YYYY-MM-DD HH:MM:SS」形式の文字列が良いと思います。
時刻を「文字列で保持するのかDate型で保持するのか」は現状慣れたやり方で困ってないならどちらでも良いと思います。
CA BeatではString型で保持しているのですが、少数派でしょうか？(´・ω・｀)
弊社はずっとString型で保持していて、特に困った事もないので、
今さらDate型にする気もない(今より面倒になるケースが無いか心配)のですが。。
ただ、例えばDatastoreViewerでTimezoneを選択してDate型を表示できるようになったら、
文字列型で保持しているのはデメリットになるかもしれませんが。
・joinの制限を考慮して冗長化を検討する
前述のように、joinする際は「メインのテーブル」以外はサイズの制限があります。
「レコードが増え続ける大量データを持つデータ」同士をJOINする必要があるなら、
冗長化を検討しておくのが良いでしょう。
・Slim3を使っている場合はスキーマバージョン名を変更する
初期状態のままSlim3からEntityを投入した場合、
「slim3.schemaVersion」という名前で「Entityのスキーマのバージョン名」を示すプロパティが生成されます。
この名前の「.」がBigQuery側で拒否されてApp Engineのバックアップから直接インポートできません。
(「.」はBigQueryのカラム名として禁止文字なので。)
Modelクラスのアノテーションで
@Model(schemaVersion = 1, schemaVersionName = "schemaVersion")
のように指定することでこのプロパティ名を変更することができます。
(この場合Entityに生成されるプロパティ名は「schemaVersion」になります)
*twitterで「あおうさ」さん(@bluerabbit777jp)から教えていただきました。ありがとうございます。m(__)m
https://twitter.com/bluerabbit777jp/status/273938186416312320
追記:運用中のアプリのスキーマバージョン名を変えるのはやめた方がいいと思います。念のため。
○ノウハウ編まとめ
今回はBigQueryを業務で使う中で苦労した点やその回避策、
あるいは設計段階でBigQueryでクエリを投げる事を考慮していなかったための失敗例でした。
・大量データ同士をJOINする必要がある場合にはJOINの制限を考慮して冗長化すること
・Datastoreに保持する日付の形式をBigQueryで扱いやすいようにしておくこと
・Key値を構成する値を個別のカラムに保持してJOINの余地を残しておくこと
以上は経験から来る推奨事項です。
3日連続BigQuery特集も今回で終了です。
(REST-APIと Datastoreのデータインポートに関してそのうちまた書くつもりですが)
全部読んでくださった方、本当にありがとうございます(´▽`)
それではまた次の記事でお会いできればと思います(｀･ω･´)ﾉｼ
★宣伝★
CA BeatではTwitter、Facebookページの運営も行っております！
ブログの更新情報だけでなく、役立つスマホトピックニュースを
選りすぐって配信しております。
ブログ右サイドバーからぜひフォロー、いいね！してくださいねヽ(´▽`)ﾉ