BigQueryへのデータロードを数倍高速化する方法

12671 ワード
GoogleCloudPlatform google Embulk BigQuery google テキストリンク

BigQueryへデータロードする時に圧縮をしていると、ロード時間が伸びることがあります。
この記事では圧縮の有無によってどの程度の時間差が出るのかを紹介します。

検証方法

S3に配置したファイルをEC2上のembulkによってBigQueryに転送します。
この時に使用したEC2インスタンスは c5.xlarge で、awsとGCPの間の通信は専用線ではなくインターネット回線を利用しています。

転送対象のファイルの作成

転送対象のファイルをS3に配置します。
以下のYAMLをembulkで実行することによって、gz圧縮されたcsvファイルがS3に配置されます。
ファイルのサイズは1.8GBです。

exec:
  min_output_tasks: 1
  max_threads: 1
in:
  type: random
  rows: 10000000
  schema:
    col1: integer
    col2: integer
    col3: integer
    col4: integer
    col5: integer
    col6: string
    col7: string
    col8: string
    col9: string
    col10: string
filters:
  - type: speedometer
out:
  type: s3
  sequence_format: ''
  bucket: <bucket-name>
  path_prefix: dummy
  file_ext: .csv.gz
  formatter:
    type: csv
    delimiter: ','
    newline: CRLF
    charset: UTF-8
    quote_policy: ALL
  encoders:
  - type: gzip

S3→BigQueryへの転送

S3のファイルをBigQueryに転送するためには以下のYAMLファイルを使用します。
outのcompressionを GZIP 、 NONE にするかで転送時間がどのように変わるかを検証しました。

in:
  type: s3
  bucket: <s3 bucket name>
  path_prefix: dummy.csv.gz
  region: ap-northeast-1
  auth_method: instance
  decoders:
  - {type: gzip}
  parser:
    charset: UTF-8
    newline: CRLF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 1
    null_string: ''
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: col1, type: long}
    - {name: col2, type: long}
    - {name: col3, type: long}
    - {name: col4, type: long}
    - {name: col5, type: long}
    - {name: col6, type: string}
    - {name: col7, type: string}
    - {name: col8, type: string}
    - {name: col9, type: string}
    - {name: col10, type: string}
out:
  type: bigquery
  mode: replace
  auth_method: json_key
  json_keyfile: <path to JSON key file>
  dataset: <dataset name>
  table: dummy
  auto_create_dataset: false
  auto_create_table: false
  gcs_bucket: <gcs bucket name>
  auto_create_gcs_bucket: false
  open_timeout_sec: 300
  send_timeout_sec: 300
  read_timeout_sec: 300
  retries: 5
  compression: GZIP # ここのフラグをGZIPにするかNONEにするかで転送時間の差を比較する
  source_format: NEWLINE_DELIMITED_JSON
  job_status_max_polling_time: 36000
  default_timezone: "Asia/Tokyo"

結果と考察

圧縮の有無による転送時間の変化を下表に示します。

圧縮あり 圧縮なし
24:15 4:14

なんと、圧縮の有無で約6倍もの差が出ました！

なぜこのような差が出たのかを考えたいと思います。
そのために、この転送処理の中でも特に時間のかかっている処理を３つ取り上げて、それぞれの処理時間を比較してみることにします。
特に時間がかかっている処理は以下の3つです。

  1. S3のファイルをEC2インスタンスのローカルストレージに配置する
  2. 上記ファイルをGCSにアップロードする
  3. GCSに配置されたファイルをBigQueryにロードする

これらの処時間が圧縮の有無でどのように変化するのかを下表に示します。
これらのデータはembulkのログに出力されているタイムスタンプから求めました。

圧縮あり 圧縮なし
① 3:15 1:54
② 0:34 0:56
③ 20:26 1:24

①についてはファイルを圧縮する処理の負荷がなくなったために、圧縮なしの方が高速になっています。
②は圧縮の有無によってファイルサイズが変わるために、ネットワーク転送時間が変化したためです。
そして、今回の転送時間高速化に対して最も寄与しているのは③です。
③だけに着目するとなんと15倍もの高速化が行われています。
この理由は以下のドキュメントに書かれています。

For other data formats such as CSV and JSON, BigQuery can load uncompressed files significantly faster than compressed files because uncompressed files can be read in parallel. Because uncompressed files are larger, using them can lead to bandwidth limitations and higher Cloud Storage costs for data staged in Cloud Storage prior to being loaded into BigQuery. You should also note that line ordering is not guaranteed for compressed or uncompressed files. It's important to weigh these tradeoffs depending on your use case.

どうやら、圧縮なしですとファイルの読み取りが並列で行えるようです。
この効果によって圧縮なしの場合は高速にデータ転送ができました。
しかし、ネットワーク帯域に対して与える負荷は圧縮なしの方が大きいため、輻輳が発生しないように注意する必要はありそうです。

まとめ

BigQueryへのデータロードの時の圧縮の有無によって、処理時間に大きな差が出ました。
ネットワーク負荷に与える影響が大きくないのであれば圧縮無しでデータ転送することも十分考慮にあたいするでしょう。