2021年3月31日のリリースノートでBigQueryにTABLESAMPLE演算子がプレビュー版としてリリースされました。TABLESAMPLE演算子を利用すると、BigQueryで管理されている大規模なテーブルに対してサンプリングを行い、小規模なサブセットを作成することができます。
TABLESAMPLE演算子は、以下のように記述することができます。
TABLESAMPLE SYSTEM ( percent PERCENT )percentに取得したい結果の割合を0〜100の範囲で指定します。percentの値はリテラルまたはクエリパラメータで指定する必要があり、変数を利用することはできません。実際にdatasetという名前のデータセット内にあるmy_tableというテーブルから約30%のデータを取得するクエリは以下のように書けます。
TABLESAMPLE演算子の特徴
次にTABLESAMPLE演算子の特徴を3つ紹介します。
- テーブルスキャン時の料金節約
- クエリ結果がキャッシュに保存されない
- どのようにサンプリングされるのか
1. テーブルスキャン時の料金節約
TABLESAMPLE演算子は、データスキャン前にデータブロック単位でサンプリングが行われます。そのため、クエリ実行時にかかる費用を大きく削減することができます。BigQueryでは主にテーブルのスキャン量に対して費用が発生するので、おおよそpercentで指定した割合まで削減できます。
2. クエリ結果がキャッシュに保存されない
TABLESAMPLE演算子を用いて実行されたクエリの結果はキャッシュに保存されません。そのため、同じクエリを複数回実行した場合には都度料金が発生する点に注意が必要です。加えて、実行のたびにサンプリングが行われるので複数回実行する場合には異なる結果が取得される点にも注意が必要です。
3. どのようにサンプリングされるのか
TABLESAMPLE演算子では、データブロック単位でサンプリングが行われます。データブロックとはBigQuery側でのデータ管理方法で、公式ドキュメントによるとテーブルまたはパーティションが約1GBより大きい場合に、データブロックに分割され管理されるようです。
サンプリング時には、指定された割合に従ってデータブロックをランダムに選択し、ブロック内の全ての行を読み取ります。そのため、テーブルが一つのデータブロックで構成されている場合には、0より大きい割合を指定すると値に依らず全てのデータが返されることになります。2021年5月5日の段階では、サンプリング時にseedを指定することができないため結果の再現性を保証する必要がある場合には注意が必要です。
行単位でのランダムサンプリングを行いたい場合は、RAND関数を利用します。先程の例と同様にデータセットの30%のデータを取得する場合は、以下のようなクエリを実行します。RAND関数では、WHERE句でデータを絞り込んでいることからもわかるように、テーブル全体をスキャンした後に乱数を生成しデータをサンプリングしています。そのため、費用はサンプリングを行わなかった場合と同じで節約することはできません。
標準SQLでは、RAND関数にseedを指定することができなくなりました。加えて、BigQueryでは分散処理のためにクエリが複数のワーカーに分割されて実行されます。そのため、RAND関数で生成された乱数が重複した場合の順序を保証することができません。LIMIT句でデータの件数を絞り込む場合には結果が一致しないことがある点にも注意が必要です。ウインドウ関数を用いることで、結果の再現性を保証して行単位のランダムサンプリングを行う方法があるので、最後に補足として追記しています。
そのほかにも、サンプリングされたテーブルはクエリステートメント内で一回しか利用できないなどの特徴があります。詳しく知りたい方は公式ドキュメントを確認してみてください。
所感
実際の業務などでBigQueryをデータウェアハウスとして利用する際には、一つ一つのテーブルが大きくなってしまうと思います。そのため、テーブルを一度全てスキャンしてからサンプリングを行うという従来の方法では動作確認時などに不必要に料金が発生してしまう問題がありました。その問題に対して、TABLESAMPLE演算子では元のテーブルに変更を加えず、クエリ側に簡単な記述を追加することで費用を削減できるので多くの場面で活用できそうだと感じました。
一方で、機械学習のデータセットをサンプリング及び分割するといった目的での利用は現段階では難しいと感じました。理由として以下の2つがあります。
- 乱数のseedが固定できないため実験の再現性を保証できない
- データブロック単位でのランダムサンプリングとなるので、バイアスが生じる可能性がある。
2つ目について少し補足をします。私が調べた範囲では、BigQuery側でどのようなロジックによってデータブロックが作成されるのかに関するドキュメントを見つけることができませんでした。データブロックがある規則によって生成されるとすると、データブロック単位でランダムサンプリングされた結果は、データセット全体で見るとバイアスを含んだ状態となります。そのため、機械学習モデルの学習に影響を与える可能性が高いと思います。クエリの結果を機械学習モデルに適用する場合には、補足で紹介した行単位でのランダムサンプリングやBigQuery MLで用意されているデータセット分割方法に従うといいと思います。
[補足] ウインドウ関数を用いた行単位のランダムサンプリング
標準SQLでは、RAND関数にseedを指定することができなくなりました。ここでは、ウインドウ関数とハッシュ関数を用いて実験の再現性を担保しながら、行単位のランダムサンプリングを行う方法について紹介します。また、この方法も一度テーブルを全てスキャンした後にサンプリングが行われるのでクエリ実行時に掛かるコストはサンプリングを行わない場合と変わりません。
上のクエリでは、まずテーブル内の全データに対して行番号を付与します。そして、その行番号を元にFARM_FINGERPRINT関数を用いてハッシュ値を計算します。FARM_FINGERPRINTは-1〜1の間の値を返します。そのため、MOD関数を計算する際に扱いやすいようにABS関数で絶対値に変換しています。MOD関数の引数には、データセット全体の1/Xを取得したい時はXを指定します。ハッシュ関数は同じ入力に対して同じ値を返す関数なので、元のテーブルの構成が変わらない限り行番号は固定されます。そのため、複数回実行しても同じ結果が得られ、実験の再現性を保証することができます。
