【GCP】BigQueryのメモリオーバーの件

BigQueryでは、世界のユーザーと共有してBigQueryのリソースを使用します。
なので、自分がある時間で使うとき使用できるメモリが少なかったりすることもあります。
 

そして、そういう時にメモリオーバーになって処理が落ちることなどもあります。
そういった場合の対処方法として、以下を挙げたいと思います。
 

エラー内容

エラー内容は以下のようなものが出ました。
なんだか、BigQueryの中のスロットでかなりメモリオーバーになって、処理が回せなくなったみたいでした。

中身を見ると、order byが異常にメモリを食っていることがわかりますね。
98%....
このorder byがかなりネックで、何回もorder byをするとよくないことがわかるかと思います。
並び替えは、order byだ！とすぐ考えてしまうのは時期早尚です。
このorder byをした結果どうしたいかによって、ここで本当にorder by処理が必要になるのかが変わってくると思います。

とにかくorder byはかなりメモリを食うってことがわかるので、極力使わないようにしましょう！
 

order byによるデータの並び替え

これが一番メモリエラーで処理落ちする原因に挙げられます。
通常のテーブルでは順序はランダムに格納され、担保されません。
なので、もし順序を定めて処理をしたい場合は、当然ながら自分でorder byをして並び替えをする必要があります。
しかし、このorder byめちゃ厄介です。
 

データ量が多いテーブルに対してorder byをすると、
例えば一番下にあるデータを一番上の方に持ってくる際に、一旦データをメモリに保存します。
そして別のデータもまた一旦メモリに保存します。
こういう処理をすると、データが一番下にあるものを上に持ってきたり、真ん中にあるものを下の方に持っていったりするために、データを一時保存するんですが、
それによってメモリに入るデータ量をオーバーして処理が落ちてしまうというものです。

実はBigQueryではこのorder byは1つのノード上で行うような仕組みになっています。
なので、そのノードに入るデータを超えたためorder byの処理ができず落ちてしまったということになります。
スロットが上限の2000を超えたというわけではなく、order byをする際は1つのノード上で行い、
そのノードでのメモリがオーバーしたため落ちたということになります。
 

このノード、実はすごい厄介な概念でして、
with句で生成するテーブルも1つのノード上に格納します。

order byの仕方を考える

order byを例えばtimestamp型でやると大事故に繋がります。
要は全ての日付を全走査するためです。
まずtimestampの並び替えをするときに全ての日付、要は年、月、日、時間を見て並び替えします。

order byのメモリ処理を減らすためのポイントとしては、データを近くに置くということです。
近くに配置していればデータの移動距離は抑えれます。
一番下のものを一番上に持ってくると距離が大きくなり死にます。
なので、近くに配置してorderbyをしていくことでそのメモリを抑えることができ、エラーにならなくて済むようになります。
 

例えば、日付であればこういった工夫ができます。
まず年で並び替えしておきます。
そうすると、普通にtimestampで並び替えすると、2001年のデータを一番上に持ってくるとなると、
他の2022年とかあるのですごい動かすことになります。
 

なので結局timestampで日付の並び替えをするのであれば、
最終的には年や月などは近くに並び替えされるので、
だったらtimestampではなく、まず年で並び替えをして、
そして月で並び替える。
そうすれば、年で並び替えているので、月の並び替えは近いもの同士での並び替えになるので、
レコードの移動距離が減ります。
 

最後に日で並び替えをして、これでtimestampの並び替えができ、メモリの処理を減らした上でできるので、メモリ落ちエラーの発生リスクを減らすことができるようになります。
 

パーティションを用いた並び替え

Group byなどをして、各idごとに処理をしたい。
各idごとに最新日付を取りたかったり、アクセス順にまとめたいなどがあると思います。
最終目標が、各idごとに試験受験順などにまとめたいのであれば、
これを使うのがベストかと思います。
 

ぱっと見、order byして並び替えた後に対してgroup byして上のレコードから処理をしていくというやり方が浮かぶと思いますが、
結局全体に対してorder byをしているので、メモリ落ちにつながります。
 

なので、先にgroupbyみたいな、idごとにパーティションを切っておき、
そのid内でorder byをするというような形式を取ることのできるのが、この
partition by処理です。
partition byとwindows関数を用いることで、各idごとにランク付けを行い、
そしてランク付の最新をとってくるような処理を行うことが可能になります。