トップ > BigQuery > 知っておくと運用で役に立つbq loadのTips

知っておくと運用で役に立つbq loadのTips

BigQuery

今日もbq loadが失敗して涙を流していたデータエンジニアのid:syou6162です*1。このエントリではbq loadを使ったデータ取り込みで泣かないで済む、あるいは泣いても致命傷まではいかないようにするための色々なTipsを書きます。

bq loadをベースに書いていますが、SDKを使ってBigQueryにデータを取り込む際もほぼ同様のことを考えれば十分な場合が多いです。

bq loadの基本形

何はともあれ基本形です。ここから色々オプションを適宜追加していくことが多いです。手元のcsv(path/to/data.csv)をBigQuery上のテーブル(my-project:my_dataset.my_table)に取り込みます。スキーマはBigQueryに自動検出(--autodetect=true)させます。BigQueryのスキーマの自動検出はまあまあ賢く、さっとやる場合にはこれで十分な場合も多いです。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  my-project:my_dataset.my_table \
  path/to/data.csv

取り込み先のプロジェクトやデータセットの指定ですが、自分はmy-project:my_dataset.my_tableだったかmy-project.my_dataset.my_tableだったか分からなくなることが多いので、引数のオプション(--project_id / --dataset_id)として与えることが多いです。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  path/to/data.csv

受け付けるファイルの入力は結構色んな種類がサポートされています。具体的にはCSV / NEWLINE_DELIMITED_JSON / DATASTORE_BACKUP / AVRO / PARQUET / ORC / THRIFTがサポートされています。TSVはサポートされていないように見えますが、--field_delimiterでタブを指定すればOKです。

ファイルはローカルだけでなくGCS上のファイルでもBigQueryに取り込めます。パスをGCSに切り替えればいいだけなので、簡単ですね。巨大なファイルをBigQueryに上げるのを何回も試行錯誤したりする場合、ローカルから取り込むより一度GCSに上げた上でbq loadを試すほうが早かったりするので、自分にあった形を選びましょう。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data.csv"

ファイルはgz形式で圧縮されていても問題なく取り込めます。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data.csv.gz"

GCS上のファイルの取り込みは一個一個指定するのではなく、ワイルドカード形式もサポートされています。ファイル毎のスキーマが異なるとうまく動かないので、気を付けましょう(後述の--allow_jagged_rowsオプションなどを活用するとよいでしょう)。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data_*.csv.gz"

あまり使うことはないですが、ワイルドカード形式の読み込みは複数を指定することもできるようです。

bq load \
  --source_format=CSV \
  --autodetect=true \  
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data1_*.csv.gz","gs://my-bucket/data2_*.csv.gz","gs://my-bucket/data2_*.csv.gz"

スキーマを自分で指定する

--autodetectオプションはかなり優秀ですが、以下のような場合はうまく動かない場合があります。

  • サンプリングで推論ができないケース
    • --autodetectオプションはいくつかレコードをサンプリングした上で型を推論します
    • 大半のカラムがNULLで少数のカラムがINTEGER型という場合、STRING型と推論される、などが起こり得ます
  • そもそも型の推論が難しいケース
    • DATE型や DATETIME型などは間違えたり、STRING型になったりすることケースがある印象です

こうした場合は自分でスキーマを指定しましょう。コマンドライン引数で直接スキーマを書くことができます。列数が少ない場合はこれで十分でしょう。スキーマを直接指定したにも関わらずloadが失敗することもたまにありますが、この場合はSTRING型でひとまず読み込むようにして、後段のクエリでparseやSAFE系の関数を使う、などして取り扱うとよいでしょう。loadではあまり複雑なことはできないですし、しないのがオススメです。

また、CSVのヘッダーに列名が書かれていることが多いと思いますが、スキーマを直接指定する場合は--skip_leading_rows=1など先頭をスキップするとよいです。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data.csv" \
  sales:FLOAT,year:STRING

列数が多い場合、コマンドラインに直接全てのスキーマを書くのは苦痛でしょう。スキーマファイルを渡すことができるので、これを利用します。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  my_table \
  "gs://my-bucket/data.csv" \
  schema.json

スキーマを指定するjsonファイルは以下のような形式になっています。後からきちんと使う予定のテーブルの場合、descriptionなども含め、丁寧に書くようにしましょう。固く作りたい場合は"mode": "REQUIRED"を指定することもありますが、bq loadする際にはNULLABLEにしておくことが多いです。外部からの入力はNULLにならないと思ってもNULLが入ってくることもありますし、その度に後段のパイプラインを止めてしまうのもまずいことが多いからです(外部のSaaSなどを使っている場合、問い合わせをしても返答があるのに数日かかるケースなどもあるので)。後段のdbtのnot_nullテストやDWHを作るときに固くするのがよいケースが多いかなと思います。

[
  {
    "name": "qtr",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "quarter"
  },
  {
    "name": "rep",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "sales representative"
  },
  {
    "name": "sales",
    "type": "FLOAT",
    "mode": "NULLABLE",
    "defaultValueExpression": "2.55"
  }
]

スキーマファイルを渡す方法は簡単ではあるものの、カラム数が50個など大量にあると、スキーマファイルを埋めるのは面倒です。この場合、--autodetectオプションで楽をすることができます。

まず、手元のファイルを--autodetect付きで取り込みましょう。この際、スキーマが間違っていても構いません。次にbq showを使って、--autodetectで取り込んだテーブルのスキーマを出力させます。出力したファイルのうち、スキーマが間違っている箇所のみ手動で修正してbq loadしなおせば、最小限の手間で自前でスキーマを指定することができます。

# ひとまずBigQueryに取り込む
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --autodetect=true \  
  my_table \
  "gs://my-bucket/data.csv"
 
# 自動推論された型をjsonとして取り出す
bq show \
  --schema --format=prettyjson \
  my-project:my_dataset.my_table > schema.json

# 推論が間違っているスキーマを手で直す

# 手で直したスキーマを使ってloadしなおす
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --replace \
  my_table \
  "gs://my-bucket/data.csv" \
  schema.json

また、データの取り込みはまだしたくないが、テーブルのスキーマだけ先に作っておきたい場合はbq mk(参考)を使うといいでしょう。

取り込み失敗時の対処方法

癖の強いデータや巨大なデータの取り込みを行なう際、取り込みが失敗することはよくあります。この際、どのレコードが想定外のものだったのか調べたくなるでしょう。公式ページでも紹介されていますが、エラーメッセージを元に簡単に調べる方法があります。例えば、以下のような取り込みエラーのメッセージが出たとしましょう。

Waiting on bqjob_r5268069f5f49c9bf_0000018632e903d7_1 ... (0s)
Current status: DONE
BigQuery error in load operation: Error processing job
'myproject:bqjob_r5268069f5f49c9bf_0000018632e903d7_1': Error while reading
data, error message: Error detected while parsing row starting at position: 1405.
Error: Data between close quote character (") and field separator.
File: gs://my-bucket/mytable.csv
Failure details:
- gs://my-bucket/mytable.csv: Error while reading data,
error message: Error detected while parsing row starting at
position: 1405. Error: Data between close quote character (") and
field separator. File: gs://my-bucket/mytable.csv
- Error while reading data, error message: CSV processing encountered
too many errors, giving up. Rows: 22; errors: 1; max bad: 0; error
percent: 0

取り込みエラーが出た場所を教えてくれているので、その周辺を出力させるとエラーが出ている内容を知ることができます。

gcloud storage cat 1405-1505 gs://my-bucket/mytable.csv --recursive

例えば以下のようにダブルクオートの位置がずれている、ということが分かります。

16,Abraham Lincoln,"March 4, 1861","April 15, "1865,Republican

圧縮済みファイルの場合はstreamingで回答しながら、行を絞り込む形でエラーの該当行の内容を調べることができます。

gcloud storage cat gs://my-bucket/mytable.csv.gz | gunzip - | tail -c +1406 | head -n 1

パーティショニング列やクラスタ列を指定する

取り込むデータが巨大で生成されるテーブルも巨大になる場合、パーティション列やクラスタ列を指定してデータを取り込むのがおすすめです。クエリ時に読み込まれるデータ量の削減ができ、コスト削減や実行時間の短縮に繋がります。

パーティションを効かせるには--time_partitioning_fieldオプションと--time_partitioning_typeを指定します。パーティション列の指定を強制したい場合、--require_partition_filterオプションを指定するとよいでしょう。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --time_partitioning_field=my_partitioning_field \
  --time_partitioning_type=DAY \
  my_table \
  "gs://my-bucket/data.csv" \
  schema.json

データが巨大でストレージ料金がバカにならない場合、あまり利用されることがない古いパーティションを--time_partitioning_expirationオプションで削除するようにしておくとよいでしょう。とはいえ、古いデータも何だかんだで見る必要があることは多いので、実際はあまり使う場面は多くないかもしれません。

必要であればbq queryと組み合わせて使う

bq loadのオプションとして--time_partitioning_fieldオプションと--time_partitioning_typeを紹介しましたが、実際のところbq loadでこのオプションを指定できる場面はそれほど多くなかったりします。というのも、入力となるCSVではUnixTimeとしてINTEGER型として入ってくることもあれば、bq loadがDATE型やDATETIME型として認識できない形で入ってくる場合も多いからです*2。また、CSVファイルには存在しない「バッチの取り込み日時(imported_at)」や「外部SaaSからGCSに取り込まれた日時(exported_date)」などをパーティション列にしたい場合などもありえるでしょう。

こうした場合、bq loadは一時テーブルの作成に留め、その一時テーブルの結果をbq queryとしてappendしていく形がよいでしょう。例えば以下のようなクエリを用意します。@EXPORTED_DATEのような変数は後からsedで置換します。

SELECT
  ...,
  -- GCS ファイル名の YYYY-MM-DD の部分に相当
  , date('@EXPORTED_DATE') as exported_date
  -- このバッチが実行されたタイムスタンプ
  , parse_timestamp('%Y%m%d%H%M%S', '@IMPORTED_AT') AS imported_at
from
  `@BQ_TMP_TABLE`

gcs_bucket="my_bucket"
bq_table="my_table"
target_date="2024-08-31"

target_date_str="${target_date//-/}" # YYYYMMDD に変換
bq_target_table="${bq_table}\$${target_date_str}"
now=$(TZ=UTC date "+%Y%m%d%H%M%S")
bq_tmp_table="${bq_table}_tmp_${target_date_str}_${now}"
gcs_path="gs://${gcs_bucket}/my_logs_${target_date}*.csv.gz"

# GCSから一時テーブルとしてBigQueryにテーブルを読み込む
bq load \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --autodetect=false \
  --source_format=CSV \
  --skip_leading_rows 1 \
  --quiet \
  "${bq_tmp_table}" \
  "${gcs_path}" \
  schema.json

# 一時テーブル + GCSにexportされた時間 + 取り込みバッチの実行時間を列として追加できるようにSQLファイルを置換
query=$(sed "s/@EXPORTED_DATE/${target_date}/" import.sql \
  | sed "s/@IMPORTED_AT/${now}/" \
  | sed "s/@BQ_TMP_TABLE/${bq_tmp_table}/")

# GCSのファイル名にあった日時(exported_date)をベースにパーティションを指定
bq query \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --time_partitioning_field=my_partitioning_field \
  --time_partitioning_type=DAY \
  --destination_table="${bq_target_table}" \  
  --max_rows=0 \
  --use_legacy_sql=false \
  --replace \
  --quiet \
  --time_partitioning_field="exported_date" \
  --time_partitioning_type="DAY" \
  --schema_update_option="ALLOW_FIELD_ADDITION" \
  "${query}"

なお、bq loadで作成した一時テーブルは処理が終わった後にbq rmで削除してもよいですが、途中でバッチが止まった場合にテーブルが残り続けてしまいます。ゴミテーブルが溜まっていかないよう、テーブルの有効期限を設定するやり方がオススメです。

# 有効期限を5日(432,000秒)に設定する例
bq update --expiration 432000 my-project:my_dataset.my_table

洗い替えしたい場合

特に最初の試行錯誤をやっている最中はデータの洗い替えをしながら試したいことが多いと思います。そういった場合には、--replaceオプションを付けて実行するとよいでしょう。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --replace \
  my_table \
  "gs://my-bucket/data.csv" \
  schema.json

--replaceを付けない場合のディフォルトの動作はappendになりますし、試行錯誤のときにすでに存在しているテーブルをスキーマが違う場合にはloadが失敗するので、初手では--replaceを付けて実行することが多いです。

パーティション指定で洗い替え

データを追記(append)する場合には使わないように思える--replaceですが、追記したい時にも--replaceを付ける場合があります。それはパーティションを利用する場合です。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --time_partitioning_field=my_partitioning_field \
  --time_partitioning_type=DAY \
  --quiet \
  --replace \
  "my_table$20160501" \
  "gs://my-bucket/data.csv" \
  schema.json

パーティションを指定した上で--replaceも指定すると、そのパーティションを洗い替え(一旦削除した上で新しいデータに詰め替える)してくれるため、特定の日に不正なデータが入った場合などで役に立つことが多いです。パーティションが洗い替えになるため、データの取り込みバッチを冪等に保ちやすくなることにも繋がります。

パーティションを指定せずに--replaceを指定すると、意図せぬ事故(パーティションだけ洗い替えするつもりが、テーブル丸ごと洗い替えされてしまう)に繋がることがあるので、長期運用している場合は気を付けましょう。

一癖あるデータと戦う

Shift-JISやEUC-JPのファイルを読み込む

残念でした、諦めましょう!!

bq loadには--encodingオプションはありますが、Shift-JISやEUC-JPには対応していません。nkfコマンドやスクリプト言語などを使ってUTF-8にしてからbq loadするようにしましょう。

取り込むデータがあまりにも大量である場合、Dataflowの利用を検討してもよいかもしれません。

レコード内に改行を含むCSVを読み込む

世の中には色々なCSVが存在していて、CSVだけど途中で改行が入っているようなケースがあります。

1,2,A,B,"こういう形で

改行された

レコードに

なっていることがあります",X,Y,Z

この場合は--allow_quoted_newlinesオプションを指定すると改行の情報も考慮した上で取り込みができます。quotedされずに改行されている場合、スクリプトで前処理をしたり、諦めたりする必要があるかもしれません。

--max_bad_recordsオプションは最小限に

データを取り込む際、不正なレコードの存在によりbq loadが失敗することがあります。「N件までは不正なレコードは見逃して取り込めるようにする」という--max_bad_recordsというオプションがあります。取り込みが失敗する場合、このオプションは気軽に使えるため、ついつい使ってしまいたくなります。しかし、安易に使わないようにするのがオススメです。理由としては

  • --max_bad_recordsで許容する数が段々増えていき、運用がグダグダになってしまうことが多いため
  • bq loadで除外すると、GCSなどに置かれた件数とBigQueryに取り込まれた件数が徐々に乖離していってしまう
    • GCSで件数を確認するのは割と面倒であることが多いし、bq loadはなるべくあるがままをBigQueryに反映させるのが役割
    • 例: 型が合わないにしてもSTRING型でひとまずレコードとしては存在する状態にしておく
  • --max_bad_recordsだと、どういった条件で除外されているかが明示されないため、件数が一致しないときの分析が困難になる

などがあります。そのため、今回のエントリで紹介しているようなTipsなどを使い、--max_bad_recordsオプションはなるべく使わないように立ち振る舞うのがオススメです。

使ってもよいかなと妥協できる状況としては、ワンショットでのデータ取り込みがあります(多少件数が違っていてもいいので、大雑把な傾向が分かればよいというケース)。ただ、この際も--max_bad_recordsになっているデータの件数がN件で、その内訳はケースAがN_1件、ケースBがN_2件というのを目視で確認した上で「こういったレコードは除外しています」というのを後段の分析者に共有 / 許容してもらった上で使ってもらうようにするのがよいでしょう。

運用中のスキーマ変更に立ち向かう

bq loadでdailyなど定期的にデータを取り込む場合、数ヶ月や一年に一度くらいの頻度で取り込む対象のデータのスキーマが変わるということが起こり得ます。例えば以下のような場合です。

  • 外部SaaSなどを利用している場合、「取得できる情報が多くなったから、CSVに出力するカラムを増やしておくね!」という場合
    • データ活用者側もそういったカラムは活用したいことが多いので、データエンジニアも対応すべき場合がほとんどだと思います
  • 運用途中でカラムの型が変わる場合
  • DEPRECATEDになっていたカラムがCSVの出力先からなくなるケースなど

こういったケースが起きた場合、bq loadで指定しているスキーマとデータのスキーマが一致しないため、取り込みバッチが落ちてしまいます。中長期の運用が必要なのであれば、こういったケースへの対応方法は考慮しておくべきでしょう。

LoadするCSVの一部に新規にカラムが追加される場合: allow_jagged_rowsオプション

GCSでgs://my-bucket/data_*.csv.gzのようにワイルドカードでCSVファイルを指定している場合、ワイルドカードのどこかから新規のカラムが末尾に追加されたとしましょう(新規のカラムは行の末尾に追加されたとします)。具体例としては、dailyでCSVでデータの取り込みを行なっていて、ある日の途中(例: 14時台)から新規のカラムがCSVに追加された場合などですね。この場合「まだ新規のカラムが追加されていないCSV」と「新規のカラムが追加されたCSV」の両方に対応する必要があります。bq loadで指定するスキーマを変更しなければ後者の取り込みが失敗し、スキーマを変更すれば前者の取り込みが失敗するので、悩ましい状況です。

こういった状況には--allow_jagged_rowsオプションを使うのがよいでしょう。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --allow_jagged_rows=true \
  my_table \
  "gs://my-bucket/data_*.csv.gz" \
  schema.json

--allow_jagged_rowsは公式ページでは以下のように紹介されています。

指定すると、CSV ファイルで末尾のオプションの列が欠落している行を受け入れます。欠損値は null として扱われます。オフの場合、末尾の列が欠落しているレコードは不良レコードとして処理され、不良レコードが多すぎる場合はジョブの結果内で無効なエラーが返されます。デフォルト値は false です。

jaggedの意味がよく分からなかったので調べてみたところ、ぎざぎざの(ある), (でこぼこと)とがった, のこぎり状のという意味でした。ワイルドカードで指定しているCSVファイル群の一部では新しいカラムがあったり、一部ではなかったりという状況がカラム数がぎざぎざしている状況、ということで--allow_jagged_rowsという名前になっていそうです。

このオプションを指定した上で新規のスキーマでデータを取り込むようにすれば、スキーマの変更前後の両方のCSVの取り込みが問題なくできます。なお、変更前のCSVに対しては、新規に追加されたカラムはNULLとして取り込まれます。

パーティショニングテーブルで新規のカラムを追加する場合: schema_update_optionオプション

--allow_jagged_rowsとも関連しますが、パーティショニングテーブルを使っている場合で新規のカラムが追加される場合、--schema_update_option="ALLOW_FIELD_ADDITION"を指定する必要があります。指定しないと、既存のパーティショニングテーブルの型と新規に追加したいパーティションの型が合わないということで怒られて取り込みが失敗します。

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --time_partitioning_field=my_partitioning_field \
  --time_partitioning_type=DAY \
  --allow_jagged_rows=true \
  --schema_update_option="ALLOW_FIELD_ADDITION" \
  my_table \
  "gs://my-bucket/data_*.csv.gz"
  schema.json

--schema_update_optionオプションは他にもALLOW_FIELD_RELAXATIONが指定可能で、これは既存のREQUIREDに指定したカラムをNULLABLEに変更(緩和)したい場合に指定します(あまり出番がないことが多いです)。

パーティショニングテーブルのカラムの型を変更したい場合: 手動で頑張る

パーティションを指定してテーブルにデータを積み重ねていく場合、あるカラムの型をINTEGERで指定していたが途中からSTRING型に変える必要が出てきた、というケースがありえます。入力となるCSV側が途中からSTRINGになってしまえば追従するしかありません。この場合、スキーマを指定する際にSTRINGに変えるだけだと、bq load時に(既存のテーブルと)「型が合わない」と怒られて失敗します。

新規のカラム追加の場合、--schema_update_option="ALLOW_FIELD_ADDITION"で乗り越えられましたが、型が変わる場合には残念ながら少しオペレーションが必要になります。BigQueryのカラムの型変更はALTER COLUMN SET DATA TYPEのDDLがありますが、これは

  • INT64 to NUMERIC, BIGNUMERIC, FLOAT64
  • NUMERIC to BIGNUMERIC,FLOAT64

のみがサポートされており、INTEGERからSTRINGのようなケースは現在サポートされていません。大半の場合、bq queryを使ったオペレーションが必要になります。以下のようなコマンドを用意しましょう。

bq query \
  --project_id=my-project \
  --dataset_id=my_dataset \
  --destination_table=my_table \
  --time_partitioning_field=my_partitioning_field \
  --time_partitioning_type=DAY \
  --max_rows=0 \
  --use_legacy_sql=false \
  --replace \
  --quiet \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  `my-project.my_dataset.my_table`

基本的にはbq queryで既存のパーティショニングテーブルを型変更した上で上書きする形になります。気を付ける点としては

  • bq queryは基本的にはクエリ結果にパーティションなどを考慮してくれないので、--time_partitioning_fieldと--time_partitioning_typeを明示的に指定する
  • いきなり試すと失敗したときに痛い目に合う(FOR SYSTEM_TIME AS OFを使って履歴データへのアクセスをする形になる)ので、--destination_tableは最初は検証用のものを指定する
  • クエリ中のSELECT句で指定するカラムが多い場合、bq show --schema --format=prettyjson my-project:my_dataset.my_table | jq -r '.[].name | " " + . + ","'のようにすると既存のカラムをがっと取ってこれるので、楽
    • 機械的に列挙されたほうがレビューする側にとっても助かる

などでしょうか。

その他

テーブルのdescriptionの付与

bq loadは基本的にデータを取り込むことが責務のため、取り込んだメタデータについては後段で付与する必要があります。descriptionやlabelなどのメタデータはbq updateコマンドで行ないましょう。

bq update \
  --description "Description of mytable" \
  my-project:my_dataset.my_table \ 
  --set_label hoge1:fuga1 \
  --set_label hoge2:fuga2

JSONデータの読み込みの場合

このエントリでは主にCSVでの取り込みを中心に説明しましたが、JSONデータでも基本的にはやり方や考え方は同じです。JSONデータの場合、CSVと異なりSTRUCTやARRAYが登場するので、読み込みの型の設定が面倒なことが多いかもしれません(サンプリングの影響でauto-detectが外れることが多いので、試行錯誤が必要になることが多い)。

型の指定が事前には難しい(例: ログのような後からスキーマが修正される場合など)、もしくは型を指定するにはあまりにも量が多かったりネストが深い場合、データの取り込み時に型を決めるのではなく(Schema on Write)、クエリでデータを読み込む際に型を決める方式(Schema on Read)を取るのもありでしょう。BigQueryではしばらく前にJSON型がサポートされたので、JSON型でデータを取り込むとSchema on Readが実現できます。

公式ページでも案内されていますが、以下のようなjsonlファイルがあったとすると

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

以下のようにしてJSON型としてデータを取り込めます。

% bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

% bq show mydataset.table1
Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

上記の例もそうですが、JSONのルートオブジェクトをそのままJSON型として取り込むのではなく、ネストが深い位置以降をJSON型として取り込むようにするのがよいと思います。というのも、パーティション用の列やクラスタ列を設定する際、JSON型でないところに列があったほうがよいからです。

Schema On Readはデータを取り込む際にスキーマの設定が省けるため、楽をしようと思うと選択してしまいそうになることもありますが、クエリを書く際にスキーマを手探りで探さなければならないことは忘れるべきではないでしょう。一応、最近だとJSON型の中のキーがどうなっているかが簡単に調べられるようになったとはいえ、Schema On Writeよりは利用者に一手間取らせているのは忘れないようにしましょう。

データサイズの制限

GCSなどからBigQueryにデータを取り込む際、各ファイルのサイズが適切なサイズになるように注意しましょう。CSVの場合、gzip ファイルの最大サイズは4GB ですし、ファイルサイズが小さ過ぎても取り込みのパフォーマンスが下がってしまいます。

エンジニアでない職種の人にもbq loadを使ってもらう

毎回データエンジニアがBigQueryにデータ取り込みを行なうのも大変なので、他職種の人にbq loadを使ってもらいたい場合もあるでしょう。手元にbq loadが動く環境を用意してもらうのは大変なので、難しくないやり方をまとめてみています。

場合によってはSaaSを検討する

bq loadでのデータ取り込みは便利ですが、転送したいデータ内容によってはSaaSやマネージドサービスの利用を検討しましょう。Data Transfer ServiceによりSalesforceの転送がサポートされていたり、Fivetranを利用してJIRAの転送をするなど最近は取れる選択肢も多いです。

データエンジニアの工数は安くないことも多いので、SaaSを利用したほうが安い、他のタスクのほうが大きくバリューを出せる、などの場合には積極的にSaaSの利用を考慮してもよいでしょう。

まとめ

データを取り込めてしまえば快適なことも多いBigQueryですが*3、取り込むのが大変なことも多いです。とはいえ、はまりポイントは今回まとめたようなケースに集約されることも多いため、本エントリを役立ててもらえればと思います。

Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]

Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]

Amazon

*1:ちなみに、泣いていた原因はbq loadの難しさではなく、データの難しさや運用の難しさに起因するものがほとんどです

*2:微妙にフォーマットが違ってloadできないのはデータエンジニア泣かせであるあるです...

*3:嘘です、取り込めてもちゃんとしたデータを作るにはまだまだ大変なことはあります