自分は BigQuery で機械学習モデルの前処理、テラバイト級の特徴量エンジニアリングを含む、ほぼ全ての変換工程を行っています。この記事では、自分の BigQuery の用途とその中で BigQuery のコストを抑えるのに効果的であった汎用的な方法と裏技を紹介します。

※ パロ元：BigQueryで150万円溶かした人の顔

速くて安い BigQuery は、データウェアハウスとしても、特徴量エンジニアリングツールとしても優れており、機械学習モデルを用いたサービスを構築する際には、ベースラインとして一候補に挙がるでしょう。

BigQueryの用途

データの保存はもちろんですが、データの変換にも使っています。
購買履歴にCROSS JOIN UNNEST(GENERATE_DATE_ARRAY('2015-12-01', '2019-12-01'))してARRAY_AGGすることで、日別の情報を固定長で取り出したりしています。
このようにデータベースに計算を担わせることは、従来のリレーショナルデータベースでは、悪しきこととされてきましたが、BigQuery では、計算にコストがかからないため、むしろ積極的に活用しています。

しかし、 BigQuery 上の計算には割り当てと上限があります。
この割り当ての範囲内で、取得データ量を減らすことで、速さと安さを両立し、他のデータ変換ツールの追随を許さない圧倒的なコストパフォーマンスを実現することができます。

2019/12/03 現在
割り当てと上限の詳細な説明は公式ページ: Quotas and limits をご覧ください。
ここでは、BigQuery でデータ変換を行う際に引っ掛かる上限を抜粋しています。

クエリジョブ	上限	注意点
インタラクティブクエリの同時実行レートの上限	同時実行クエリ 100 個	同時に多量のクエリを投げる場合に注意が必要。実行優先度が低いならばバッチクエリにして回避する。
クエリ実行時間の上限	6 時間	他のリソース系例外の方に出くわすことが多い。このエラーが出た時は再実行してみると案外通ったりする。
解決済みレガシー SQL クエリおよび標準 SQL クエリの最大長	12 MB	クエリに大量のデータを埋め込むと発生する。テーブルに切り出して対応する。
行の最大サイズ	100 MB	ARRAY_AGG で起こることが多い。情報量を落としたり、もう一段 GROUP BY してから ARRAY_AGG することで対応する。
テーブル、クエリ結果、ビュー定義での最大列数	10,000	STRUCT や ARRAY を使わずに平らなテーブルを意識して作ると超える。まとめて良さそうなものは STRUCT や ARRAY にまとめると列数が削減できる。

BigQuery の料金

オンデマンドクエリを利用する際、極めて重要なのは読み取りデータ量に対して $5/TB の料金が発生する点です。これと毎月ストレージコスト $0.02/GB がかかるだけで、BigQuery の請求が完結する点は恐ろしく明快だと言えます。（US (multi-region) 2019/12/03 現在）（定額クエリやストリーミングインサートは、本記事の対象外とさせていただきます。）
つまり、読み取るデータ量が小さければ、お財布に優しい料金で膨大な計算を BigQuery に担ってもらえるということです。

料金の詳細な説明は公式ページ: BigQuery pricing をご覧ください。

読み取りデータ量を削減する汎用的な方法

view を使わない

BigQuery における view はクエリを簡略化するくらいの効果しか持ちません。基本的にキャッシュされず、参照のたびにデータが読み出されます。クエリのメンテナンス性に目を瞑れるなら、WITH 句の方が安く済むケースがあります。

BigQuery テーブルを GCS の avro に吐き出して読みとる

AVRO のデータ圧縮に頼る方法です。
読み取りの分、所要時間は伸びますが、読み取りデータ量を20%程度削減することができます。

BigQuery テーブルを partitioning する

普通にパーティショニングするのはもちろんですが、主キーを無理やり、date型に変換し、パーティショニングすることもできます。ギガバイト級のデータを噛み砕いて、テラバイト級のデータを作成する際には、データの保存の前に、以下の UDF でパーティショニング用の列を作って、後から分割参照可能なようにしておきます。

CREATE TEMP FUNCTION
  TO_PARTITION_DATE(s ANY TYPE) AS (DATE_FROM_UNIX_DATE(MOD(ABS(FARM_FINGERPRINT(CAST(s AS STRING))), 69396)));
SELECT
  TO_PARTITION_DATE("a") -- 2129-09-29

テーブルをサンプリングする

全件参照が重要ではないテーブルの場合、データ変換の前に、予めテーブルを削っておくと、その後いくつかの工程を踏んだ際に、トータルの参照コストが抑えられます。

再現性が必要な場合

SELECT *
FROM UNNEST(GENERATE_ARRAY(1,10000))key
WHERE MOD(ABS(FARM_FINGERPRINT(key)), 10) = 0 )

再現性が不要な場合

SELECT *
FROM UNNEST(GENERATE_ARRAY(1,10000))key
WHERE RAND()<0.1

入力より出力が大きくなるような変換は中間保存しない

特徴量エンジニアリングではしばしば、入力より出力が大きくなるようなデータ変換をおこないます。その際、出力を保存してしまうと、その後段で参照するテーブルが大きくなり、コストがかさみます。サイズが10倍以上になるような場合には、WITH 句で SQL を区切るにとどめ、WITH 句を参照してその後の変換を行う方がコストメリットが出やすいでしょう。

特徴量を削減する

BigQuery とは別サービスになりますが、Google Cloud AutoML Tables には ARRAY を投入することができます。しかしこれが厄介なことに、リッチな特徴量を作ると容量に効いてきます。次元削減を実施するか、機械学習後に、寄与していない特徴量をそもそも作らないようにする工夫をしています。

2019/12/03 現在、データ容量は以下のようになっています。
データサイズな説明は公式ページ: Pricing Data size calculation をご覧ください。

データの種類	サイズ
INT64/INTEGER	8 バイト
FLOAT64/FLOAT	8 バイト
NUMERIC	16 バイト
BOOL/BOOLEAN	1 バイト
STRING	2 バイト + UTF-8 エンコードされた文字列のサイズ
BYTES	2 バイト + 値のバイト数
DATE	8 バイト
DATETIME	8 バイト
TIME	8 バイト
TIMESTAMP	8 バイト
STRUCT/RECORD	0 バイト + 含まれているフィールドのサイズ
GEOGRAPHY	16 バイト + 24 バイト × GEOGRAPHY 型の頂点の数（頂点の数は ST_NumPoints 関数で確認できます）

読み取りデータ量を 0 にする裏技

お待ちかねの裏技的にデータ量を一切消費しない、誇張なく 1 円も溶かさない話です。（2019/12/05 現在）

Lv.1 UNNEST でテーブルを作る

SELECT *
FROM UNNEST([STRUCT('a' AS a, '1' AS b), STRUCT('b' AS a, '2' AS b)])

クエリの中で生成されたデータは課金されません。UNNEST でどんな大きなデータを作っても読み取りデータ量は 0 になります。CSV, JSON, AVRO などから、UNNEST のデータに変換するコードを用意しておくと、テスト用データとしても使えるのでおすすめです。

Lv10. CREATE FUNCTION で永続データを作る

CREATE FUNCTION dataset.function_name()AS([STRUCT('a' AS a, '1' AS b), STRUCT('b' AS a, '2' AS b)]);

上の方法と組み合わせて使います。ポイントは永続化できる点です。ARRAY を入れるなら UNNEST して擬似テーブルに、スカラ値を入れるなら、擬似定数として呼び出すことができます。関数の中でテーブルを参照すると、クエリの度に読み込みコストが発生するので注意しましょう。

Lv20. ERROR でテーブル参照する

SELECT
  ERROR(TO_JSON_STRING(ARRAY(
      SELECT
        STRUCT(MAX(geo_id))
      FROM
        `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`)))

BigQuery は成功したクエリのみ課金されます。つまり絶対に失敗する SELECT ERROR でテーブルを読み取ると課金されません。ERROR 関数は STRING 引数を取れるので、TO_JSON_ARRAY_STRING と ARRAY 関数を組み合わせて、テーブルを JSON で返すようにします。このエラーを各種クライアントの実装かキャッチして、JSON 展開することで、無料のテーブル参照が実現できます。

Lv100. ZERO BYTE STRUCT でテーブルを作る

この記事で使う用語として ZERO BYTE STRUCT を定義します。これは NULL や STRUCT に NULL を入れたデータ、ARRAY に STRUCT(NULL) を入れた状態で、参照コストが 0 であるデータと定義します。先述の通り、BigQuery では NULL の参照コストがかかりません。しかし、NULL と STRUCT(NULL) は明確に区別されます。この仕様により、参照コスト 0 にもかかわらず、1 bit の情報量を持つことができます。
もしお暇な方がいれば以下のクエリを実行し、テーブルに保存し、容量を確認してみてください。

CREATE TEMP FUNCTION
  CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(b BOOL)AS(
  IF
    (b,
      STRUCT(NULL),
      STRUCT(STRUCT(NULL))));
CREATE TEMP FUNCTION
  CONVERT_INT64_TO_ZERO_BYTE_STRUCT(i INT64)AS(ARRAY(
    SELECT
      CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(i&1<<u=0)
    FROM
      UNNEST(GENERATE_ARRAY(0, 63))u
    ORDER BY
      u));
SELECT
  CONVERT_INT64_TO_ZERO_BYTE_STRUCT(i)
FROM
  UNNEST(GENERATE_ARRAY(1,1000000))i

表のサイズが 0 B になっていることを確認できましたか？それでは戻しましょう。

CREATE TEMP FUNCTION
  CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(b BOOL)AS(
  IF
    (b,
      STRUCT(NULL),
      STRUCT(STRUCT(NULL))));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_BOOL(s STRUCT<_ STRUCT<INT64>>)AS(s._ IS NULL);
CREATE TEMP FUNCTION
  CONVERT_INT64_TO_ZERO_BYTE_STRUCT(i INT64)AS(ARRAY(
    SELECT
      CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(i&1<<u=0)
    FROM
      UNNEST(GENERATE_ARRAY(0, 63))u
    ORDER BY
      u));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a ARRAY<STRUCT<_ STRUCT<INT64>>>)AS((
    SELECT
      SUM(
      IF
        (CONVERT_ZERO_BYTE_STRUCT_TO_BOOL(a[
          OFFSET
            (u)]),
          0,
          1<<u))
    FROM
      UNNEST(GENERATE_ARRAY(0,ARRAY_LENGTH(a)-1))u));
SELECT
  CONVERT_ZERO_BYTE_STRUCT_TO_INT64(CONVERT_INT64_TO_ZERO_BYTE_STRUCT(i))
FROM
  UNNEST(GENERATE_ARRAY(1,1000000))i

元に戻っていること、課金されるバイト数が 0 B であること確認できたでしょうか。
このように情報を埋め込むことができ、全ての列をこの形に変形すれば、テラバイト情報量を持った 0 バイトテーブルを作成することが可能です。
もちろん zero byte 変換に計算時間はかかりますが、これが許容できるのであれば、データ変換のたびに、zerobyte 形式にデータを変換して保存、読み出して次のデータ変換とすれば、参照コストなしで、無限のデータを扱うことができます。

ここで自作した関数群があるので紹介します。
CONVERT_{type}_TO_ZERO_BYTE_STRUCT と
CONVERT_ZERO_BYTE_STRUCT_TO_{type} です。
（{type}は、INT64, FLOAT64, STRING, BYTES, BOOL, DATE, DATETIME, TIME, TIMESTAMP に対応）
それぞれの関数は対応する型の引数を受け取ると、ZERO BYTE STRUCT に変換を行います。その逆関数が CONVERT_ZERO_BYTE_STRUCT_TO_{type} で、元の型の値に戻すことができます。

CREATE TEMP FUNCTION
  CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(b BOOL)AS(
  IF
    (b,
      STRUCT(NULL),
      STRUCT(STRUCT(NULL))));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_BOOL(s STRUCT<_ STRUCT<INT64>>)AS(s._ IS NULL);
CREATE TEMP FUNCTION
  CONVERT_INT64_TO_ZERO_BYTE_STRUCT(i INT64)AS(ARRAY(
    SELECT
      CONVERT_BOOL_TO_ZERO_BYTE_STRUCT(i&1<<u=0)
    FROM
      UNNEST(GENERATE_ARRAY(0, 63))u
    ORDER BY
      u));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a ARRAY<STRUCT<_ STRUCT<INT64>>>)AS((
    SELECT
      SUM(
      IF
        (CONVERT_ZERO_BYTE_STRUCT_TO_BOOL(a[
          OFFSET
            (u)]),
          0,
          1<<u))
    FROM
      UNNEST(GENERATE_ARRAY(0,ARRAY_LENGTH(a)-1))u));
CREATE TEMP FUNCTION
  CONVERT_BYTES_TO_ZERO_BYTE_STRUCT(b BYTES)AS(ARRAY(
    SELECT
      STRUCT(CONVERT_INT64_TO_ZERO_BYTE_STRUCT(p))
    FROM
      UNNEST(TO_CODE_POINTS(b))p));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_BYTES(a ARRAY<STRUCT<_ ARRAY<STRUCT<STRUCT<INT64>>>>>)AS(CODE_POINTS_TO_BYTES(ARRAY(
      SELECT
        CONVERT_ZERO_BYTE_STRUCT_TO_INT64(_)
      FROM
        UNNEST(a))));
CREATE TEMP FUNCTION
  CONVERT_STRING_TO_ZERO_BYTE_STRUCT(s STRING)AS(CONVERT_BYTES_TO_ZERO_BYTE_STRUCT(CAST(s AS BYTES)));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_STRING(a ARRAY<STRUCT<_ ARRAY<STRUCT<STRUCT<INT64>>>>>)AS(CAST(CONVERT_ZERO_BYTE_STRUCT_TO_BYTES(a)AS STRING));
CREATE TEMP FUNCTION
  _SUPPORT_CONVERT_FLOAT64_TO_ZERO_BYTE_STRUCT(f FLOAT64)
  RETURNS ARRAY<INT64>
  LANGUAGE js AS "return Array.from(new Uint8Array(new Float64Array([f]).buffer))";
CREATE TEMP FUNCTION
  _SUPPORT_CONVERT_ZERO_BYTE_STRUCT_TO_FLOAT64(a ARRAY<INT64>)
  RETURNS FLOAT64
  LANGUAGE js AS "return new Float64Array(new Uint8Array(a).buffer)[0]";
CREATE TEMP FUNCTION
  CONVERT_FLOAT64_TO_ZERO_BYTE_STRUCT(f FLOAT64) AS ( CONVERT_BYTES_TO_ZERO_BYTE_STRUCT(CODE_POINTS_TO_BYTES(_SUPPORT_CONVERT_FLOAT64_TO_ZERO_BYTE_STRUCT(f))) );
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_FLOAT64(a ARRAY<STRUCT<_ ARRAY<STRUCT<STRUCT<INT64>>>>>)AS(_SUPPORT_CONVERT_ZERO_BYTE_STRUCT_TO_FLOAT64(TO_CODE_POINTS(CONVERT_ZERO_BYTE_STRUCT_TO_BYTES(a))));
CREATE TEMP FUNCTION
  CONVERT_DATE_TO_ZERO_BYTE_STRUCT(d DATE) AS (CONVERT_INT64_TO_ZERO_BYTE_STRUCT(UNIX_DATE(d)));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_DATE(a ARRAY<STRUCT<_ STRUCT<INT64>>>) AS (DATE_FROM_UNIX_DATE(CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a)));
CREATE TEMP FUNCTION
  CONVERT_DATETIME_TO_ZERO_BYTE_STRUCT(d DATETIME) AS (CONVERT_INT64_TO_ZERO_BYTE_STRUCT(DATETIME_DIFF(d,
        '1970-01-01',
        MICROSECOND)));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_DATETIME(a ARRAY<STRUCT<_ STRUCT<INT64>>>) AS (DATETIME_ADD('1970-01-01',
      INTERVAL CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a) MICROSECOND));
CREATE TEMP FUNCTION
  CONVERT_TIME_TO_ZERO_BYTE_STRUCT(t TIME) AS (CONVERT_INT64_TO_ZERO_BYTE_STRUCT(TIME_DIFF(t,
        '00:00:00',
        MICROSECOND)));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_TIME(a ARRAY<STRUCT<_ STRUCT<INT64>>>) AS (TIME_ADD('00:00:00',
      INTERVAL CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a)MICROSECOND));
CREATE TEMP FUNCTION
  CONVERT_TIMESTAMP_TO_ZERO_BYTE_STRUCT(t TIMESTAMP) AS (CONVERT_INT64_TO_ZERO_BYTE_STRUCT(UNIX_MICROS(t)));
CREATE TEMP FUNCTION
  CONVERT_ZERO_BYTE_STRUCT_TO_TIMESTAMP(a ARRAY<STRUCT<_ STRUCT<INT64>>>) AS (TIMESTAMP_MICROS(CONVERT_ZERO_BYTE_STRUCT_TO_INT64(a)));

終わりに

特徴量エンジニアリングの際には、変換工程の入出力のデータ量を比較し、中間テーブルの作成を吟味すると、参照コストが抑えられます。特徴量テーブルを作成し、総計 100TB をフルスキャンして結合するのを、中間テーブルを WITH 句に置き換えることで 1 TB 程度の走査量で、100TB の特徴量を結合したテーブルを作成することができました。
これを10個にサンプリングする場合にも、パーティションを切ってあれば、同じデータを走査する必要がなくなります。結果としてクエリ発行容量は 数PB から 数TBまで削減できました。

また、テストデータを UNNEST で用意することで、クエリテストの速度と料金を押し下げ、自動テストを可能にすることができています。
ZERO BYTE STRUCT は、BigQuery の課金の抜け穴のようなものですので、実用は避けるべきでしょう。

BigQuery はコスト効率を高める方法がまだまだあります。特にコストが読み取りデータ量にかかってくる明快さは正しい知識を持って扱えば、ほぼ全ての計算を BigQuery に委譲することができます。
みんな BigQuery 使おう。

（追記） BigQueryを検討している方へ

BigQuery は安心

クエリあたりの上限設定ができるため（一日あたりの制限はできないけど）、不用意な金額のクエリの実行を防ぐことができます。
実際には、BigQuery は非常にコスパよくデータを扱えるため、テラバイト未満のデータに関しては料金の不安なく、毎日使い倒しています！（この記事で値上げされるのは不安）

BigQuery で 1 円も溶かさない人の顔 (ZERO BYTE STRUCT を考案した)