BigQuery の EXPORT DATA 関数でクエリ結果を様々なタイプで GCS に出力
投稿者：中根 洋平
日本情報通信の中根です。
今回は 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 ファイルを利用するときには注意が必要です。
とくに DATETIME 型は BigQuery → Parquet → BigQuery とするときは一手間加える必要があることも覚えておくと良いでしょう。
まとめ
SQL 結果を出力する際に CSV 以外を選択する場合に気軽に使用できるのがEXPORT DATA 関数です。
Parquet, AVRO ともに型について意識する部分もありますがうまく使いこなすことで BigQuery での分析がより効率的になればと思います。
