BigQuery独自エラーのトラブルシュート集

投稿者: | 2020年12月10日

このエントリーは、GMOアドマーケティング Advent Calendar 2020 の 10日目の記事です。

こんにちは、GMOアドマーケティングのmizkichです。
DMP(Data Management Platform)の開発運用を担当しています。

このDMPで利用しているBigQueryは、一千億を超えるレコードでも数分で処理してくれる、非常に高い高速性が魅力です。

その反面、通常のリレーショナルデータベース(RDB)では起こらないエラーがたびたび発生してしまいます。

この記事では、BigQuery独自の様々なエラーを回避する方法を紹介します。

 


・はじめに

従来のRDBでは、最も効率的な実行計画を意識してSQLを書けば、PARSE(評価)もEXECUTE(実行)も正常に高速に処理されました。
BigQueryでは、実行計画を意識しただけでは複雑なSQLは実行出来ません。

BigQueryでもRDBと同じように、SQLをPARSEした後にEXECUTEを行います。
しかしPARSEとEXECUTEで評価方法が異なっており、尚且つPARSE側の評価方法は実行計画と大きく乖離しています。

EXECUTE側で最速で処理されるSQLを書いても、PARSE側で「効率が悪いから直せ」と改悪を要求されます。
これは、PARSE側の評価方法に、独自のコスト算出式が利用されているためです。

以後、BigQueryのエラーメッセージに対してどう修正すればエラーを回避出来るのかを紹介していきます。

 


・リソース超過エラー

Resources exceeded during query execution: Not enough resources for query planning – too many subqueries or query is too complex.
和訳「クエリの実行中にリソースが超過しました:クエリプランニングに十分なリソースがありません-サブクエリが多すぎるか、クエリが複雑すぎます。」

このエラーは、PARSE処理でSQLが複雑だと評価され、コストオーバーと認定されたために発生するエラーです。
RDBで同様のエラーが出た場合はテーブルの件数やデータ分布やテーブル同士の結合方法などを見直しますが、BigQueryでは全く手法が変わります。

第一の対処として、テーブルの結合方法を変えます。

BigQueryではUNIONが最もコストが高く、続いてEXIST, LEFT JOIN, INNER JOINの順でコストが低いと評価されます。
私の感覚ではありますが、これはRDBのほぼ逆の順序です。

BigQueryでコストオーバーになった場合には、「UNION ALLを分解し、LEFT JOINで結合する」「EXISTSでの存在確認をやめ、INNER JOINで結合する」などの対処が必要です。
(RDBの常識からすると異常な対処ですが、これがBigQueryなのだと割り切っています。)

第二の対処として、事前の絞り込みをやめます。

RDBでテーブル同士をJOINする場合には、コストの肥大化を防ぐために事前にWHERE句で絞り込み、データ件数を少なくしてから結合するかと思います。
BigQueryでコストオーバーになった場合には、関数の呼び出し回数が少なくなる様にSQLを書き換えます。

「JOINで結合した後にWHERE句で絞り込みを行う」など、WHERE句や集計関数の利用回数が最小となるように対処します。

そのほか、SELECT COUNT(*)用のサブクエリを辞めてSELECT COUNT(*) OVER()を利用する方法もあります。
OVERを活用するとJOIN自体を無くせるためリソース超過エラーが消えますが、EXECUTEが劇的に低速になる諸刃の剣です。

 


・メモリ超過エラー

Resources exceeded during query execution: The query could not be executed in the allotted memory.
和訳「クエリ実行中にリソースを超過しました:割り当てられたメモリでクエリを実行出来ませんでした。」

このエラーは、クエリで処理出来るデータ件数を超え、メモリオーバーと認定されたために発生するエラーです。
WITH句を利用すれば、WITH句それぞれ毎にメモリ使用量が評価されるため、WITH句を増やす程多くのデータを処理することが出来ます。

このエラーは、以下の手順で解消します。

  1. 年次処理などは、サブクエリを増やし、月次毎の小計を出してから集計する。
  2. サブクエリをWITH句に外出しする。
  3. メモリオーバーと認定されたサブクエリを、ID抽出処理とカラム取得処理に分割する。

1番目と2番目は比較的納得しやすいかと思います。
3番目は、SELECT *など数十個のカラムを出力するサブクエリに有効な手段です。

カラム数もレコード数も多いテーブルに対して、キーを抽出する処理とカラムを取得する処理を分けることで、サブクエリあたりのメモリを減らすことが出来ます。
(「メモリが減る」とは、「PARSERがメモリ超過が解消されたと判断する」の意味であり、実際にEXECUTEでのメモリ使用量が少なくなるかは不明です。)

 


・複雑な相関サブクエリ

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.
和訳「他のテーブルを参照する相関サブクエリは、それらを効率的なJOINに変換するなどして相関を解除出来ない限り、サポートされません。」

このエラーは、INやEXISTSの中でUNION, INTERSECT, EXCEPTなどを利用すると発生します。
UNIONを使ったテーブルは、WITH句で外部化したとしてもINやEXISTSに利用した時点でこのエラーが出てしまいます。

このエラーは、UNIONを使ったテーブルをGROUP BYで囲うことで回避することが出来ます。
GROUP BYで囲うと「UNIONを使った」判定が消え、IN句やEXISTS句で利用出来るようになります。

 


・行サイズ超過エラー

Cannot query rows larger than 100MB limit.
和訳「100MBの制限を超える行をクエリすることは出来ません。」

このエラーは、 1レコードの容量が100Mを超えた場合に発生するエラーです。
最終的な抽出結果としての容量ではなく、サブクエリなど途中経過を含む全ての工程のどこかで100Mを超えるとエラーになります。

このエラーは主に、ARRAY_AGGやARRAY_CONCAT_AGGなどの配列関数を利用するときに多く発生します。

このエラーを解消するには、「ARRAY_AGG(DISTICT)」など配列化の後にDISTINCTを行うのではなく、DISTINCTを事前に行ってからARRAY_AGGを行う必要が有ります。

 


・まとめ

BigQuery PARSERが出すこれらのエラーは、正しいSQLを書けるエンジニアからすると、邪魔以外のなにものでもないです。
対処すると多少SQLが遅くなりますので、BigQueryからエラーがでない限りは通常の実行計画を意識したSQLを書くのが良いかと思います。

この対処を行ったSQLは、目的に対して遠回りなSQLになり、レビューが難解になります。

レビューを依頼する側と受ける側、どちらにもマニュアルにない知識が必要となりますので、この記事がお役に立てると幸いです。

 


明日は、mnaka1115さんによる「TAXELの単一障害点を解消する」です。

引き続き、GMOアドマーケティング Advent Calendar 2020 をお楽しみください!

 

■エンジニア採用ページ ~福利厚生や各種制度のご案内はこちら~

https://www.gmo-ap.jp/engineer/

The following two tabs change content below.

アバター

mizkich

Full Stack Engineer @ GMO AD Marketing, Inc.

DMP(Data Management Platform)の開発運用担当してます。
(Java x Ruby x Kubernetes x BigQuery x Hazelcast x etc)

マイブームは犬と車と登山とスノボ。

カテゴリー: AM BigQuery
アバター

mizkich について

Full Stack Engineer @ GMO AD Marketing, Inc. DMP(Data Management Platform)の開発運用担当してます。 (Java x Ruby x Kubernetes x BigQuery x Hazelcast x etc) マイブームは犬と車と登山とスノボ。