投稿者:中根

日本情報通信の中根です。
今回は BigQuery の EXPORT DATA 関数について紹介したいと思います。

EXPORT DATA 関数

BigQuery はクエリ結果を GCS に出力する関数となります。
おもにバッチ的な使い方で利用されることが多いのではないでしょうか。

GCP コンソールから CSV の出力は可能ですのでインタラクティブな操作ではこちらを使うことは少ないかと思いますがこの関数ならではの特徴があります。 それは出力形式に AVRO や Parquet を選択できることです。

データを取り扱う際に CSV は非常にポータビリティに優れていますが、スキーマ情報を持っていないため分析用途でのデータ授受には向かない場面がしばしばあります。 SQL でデータの加工から出力まで完結するのは非常に便利です。

それでは実際にいくつか例を見てみましょう。

以下の SQL は CSV 形式でファイルを出力します。 ポイントは GCS の出力先 URI にワイルドカードが含まれていることです。

EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*.csv',
  format='CSV',
  overwrite=true,
  header=true) AS
SELECT * FROM `bigquery-public-data.ncaa_basketball.mbb_pbp_sr`

これは BigQuery がファイルを出力する場合 1 つのファイルは最大 1GB までという制限があります。 そのため1 つ以上のファイルへのデータのエクスポートにもありますが 1GB を超える場合はこのようにワイルドカードを URI に指定する必要があります。

上記の場合は 3.14GB のテーブルを 36 個のファイルとして出力しています。 出力されるファイルは必ずしも等分にはなっていないのが出力からわかります。

yohei_nakane@cloudshell:$ gsutil ls -l gs://xxxxxxxxxxxxxx-sample-bucket/csv-files*
   1825723  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000000.csv
 293342154  2022-06-01T15:43:57Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000001.csv
 293306386  2022-06-01T15:43:56Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000002.csv
 293161931  2022-06-01T15:43:56Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000003.csv
 293016584  2022-06-01T15:43:56Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000004.csv
 292970152  2022-06-01T15:43:56Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000005.csv
    397459  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000006.csv
    385979  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000007.csv
    415328  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000008.csv
    397028  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000009.csv
    374976  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000010.csv
    431586  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000011.csv
    408330  2022-06-01T15:43:33Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000012.csv
    415081  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000013.csv
    341768  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000014.csv
 383692979  2022-06-01T15:44:07Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000015.csv
 382401502  2022-06-01T15:44:07Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000016.csv
 384716517  2022-06-01T15:44:05Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000017.csv
 384114048  2022-06-01T15:44:04Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000018.csv
 384068649  2022-06-01T15:44:05Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000019.csv
    348331  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000020.csv
    345195  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000021.csv
    402681  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000022.csv
    411855  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000023.csv
    325170  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000024.csv
    346600  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000025.csv
    401867  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000026.csv
    449414  2022-06-01T15:43:33Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000027.csv
    353145  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000028.csv
    376804  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000029.csv
    419121  2022-06-01T15:43:33Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000030.csv
    397097  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000031.csv
    341684  2022-06-01T15:43:33Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000032.csv
    299501  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000033.csv
    366983  2022-06-01T15:43:32Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000034.csv
    291280  2022-06-01T15:43:33Z  gs://xxxxxxxxxxxxxx-sample-bucket/csv-files/000000000035.csv
TOTAL: 36 objects, 3396060888 bytes (3.16 GiB)

次の SQL は結果を Parquet ファイルとして出力しています。

EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*.parquet',
  format='PARQUET',
  overwrite=true) AS
SELECT * FROM `bigquery-public-data.ncaa_basketball.mbb_pbp_sr`

Parquet はエクスポートすると以下のような型変換が行われます。

BigQuery のデータ型 Parquet のプリミティブ型 Parquet の論理型
整数 INT64 NONE
数値 FIXED_LEN_BYTE_ARRAY DECIMAL (precision = 38, scale = 9)
Numeric(P[, S]) FIXED_LEN_BYTE_ARRAY DECIMAL (precision = P, scale = S)
BigNumeric FIXED_LEN_BYTE_ARRAY DECIMAL (precision = 76, scale = 38)
BigNumeric(P[, S]) FIXED_LEN_BYTE_ARRAY DECIMAL (precision = P, scale = S)
浮動小数点数 FLOAT NONE
ブール値 BOOLEAN NONE
文字列 BYTE_ARRAY STRING (UTF8)
バイト BYTE_ARRAY NONE
日付 INT32 DATE
日時 INT64 TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)
時間 INT64 TIME (isAdjustedToUTC = true, unit = MICROS)
タイムスタンプ INT64 TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)

出典: Parquet エクスポートの詳細

この型変換はユーザーにて変更ができないため Parquet ファイルを利用するときには注意が必要です。 とくに DATETIME 型は BigQuery → Parquet → BigQuery とするときは一手間加える必要があることも覚えておくと良いでしょう。

まとめ

SQL 結果を出力する際に CSV 以外を選択する場合に気軽に使用できるのがEXPORT DATA 関数です。 Parquet, AVRO ともに型について意識する部分もありますがうまく使いこなすことで BigQuery での分析がより効率的になればと思います。