BigQueryのエクスポート機能で、Cloud Storageのバケットにテーブルのデータをエクスポートしてみた

2022.06.22

データアナリティクス事業本部の鈴木です。

BigQueryのエクスポート機能で、Cloud Storageのバケットにテーブルのデータをエクスポートできるので試してみました。

テーブルデータのエクスポートについて

コンソールやbqコマンド、SQLなどさまざまなインターフェースからBigQueryのテーブルに格納されているデータをCloud Storageにエクスポートすることができます。

今回ご紹介するテーブルデータのエクスポート機能については、以下のガイドを参考にしています。

記事執筆時点だと、以下のフォーマットでのエクスポートに対応しています。

  • CSV
  • JSON
  • Avro
  • Parquet

各フォーマットは対応した圧縮タイプで圧縮して出力することも可能です。

今回は、一番簡単なコンソール上からの操作と、SQLを使った方法を試してみました。

準備

Cloud Storageのバケットの作成

コンソールから、asia-northeast1にバケットを作成しました。

今回は、ロケーションが重要なので、名前とリージョンだけ入力し、ほかの項目はデフォルトのままです。

バケットの作成

ロケーションに関する留意事項に記載がありますが、データをエクスポートするには、データセットがUSマルチリージョンにある場合を除き、Cloud Storage バケットをデータセットと同じリージョンに配置する必要があります。

バケットの作成については、以下のガイドも参照ください。

データセット・テーブルの用意

以下のガイドに従って、asia-northeast1に、sample_datasetというデータセットを作成しました。

ロケーションはバケットと同じ場所にしています。

作成したデータセット

以下のように、sample_tableというテーブルを作り、適当なデータを入れておきました。

CREATE TABLE sample_dataset.sample_table
(
    id INTEGER,
    name STRING,
    price INTEGER
);

INSERT sample_dataset.sample_table (id, name, price)
VALUES(1, 'Apple', 150),
      (2, 'Orange', 120),
      (3, 'Grape', 390);

このようにデータセットとテーブルができました。

作成したテーブル

やってみる

1. CSVの例

まずはCSVファイルでエクスポートしてみます。

最終的に、以下のようにエクスポートされます。

CSVファイルのエクスポート結果

コンソールから

エクスポートしたいテーブルを選び、右上のエクスポートボタンをクリックします。選択肢が表示されるので、GCSにエクスポートをクリックします。

エクスポート1

Google Cloud Storage へのテーブルのエクスポートに、必要な情報を入力します。

今回は以下のようにしました。保存を押すと早速エクスポートが実行されます。

エクスポート2

ファイルの名前は、console_*.csv.gzとしています。*を入れることにより、サイズの大きいファイルだと複数に分割されて出力されるようにしています。

冒頭のガイドによると、

BigQuery は最大 1 GB のデータを 1 つのファイルにエクスポートできます。1 GB を超えるデータをエクスポートする場合は、データを複数のファイルにエクスポートする必要があります。データを複数のファイルにエクスポートすると、さまざまなサイズのファイルになります。

の記載があり、確実に1GBを下回ることが分かっている場合以外は、*を入れて複数に分かれるようにするのがよさそうです。

なお、エクスポート形式はガイドにも記載があるように、以下のように複数の形式から選択できます。

エクスポートの形式

圧縮エクスポート形式に対応したものを選択できますが、GCSのロケーションでGZIPを選ぶと自動的に拡張子が付くわけではなく、*.csv.gzのように対応した名前を付けてあげる必要がありました。

SQLから

EXPORT DATAステートメントを使ってエクスポート時の詳細を指定します。

EXPORT DATAステートメントのオプションなどは、以下のページにまとまっていました。

まず、コンソールから行なったのと同じGZIP圧縮されたCSV形式でエクスポートしてみます。

SQLは以下のようなものを作成しました。

  EXPORT DATA
  OPTIONS(
    uri='gs://エクスポート先のバケット名/csv/sql_*.csv.gz',
    format='CSV',
    overwrite=true,
    header=true,
    field_delimiter=',', -- default,
    compression='GZIP'
  )
  AS WITH tmp AS (
    SELECT 
      * 
    FROM sample_dataset.sample_table 
    ORDER BY id
  )
  SELECT 
    *
  FROM tmp

EXPORT DATAASの後ろは、WITH句から初めても問題ありませんでした。コンソールからの場合と異なり、ある程度複雑な処理をしてから、結果をエクスポートすることも可能ですね。

BigQueryのSQLエディタから実行すると、バケットにデータがエクスポートされます。

SQLからのエクスポート実行結果

エクスポート結果の確認

csv_console_000000000000.csv.gzをダウンロードしてみて、結果が期待通りに出力されているか確認しました。3行とも出力されていました。

$ gunzip csv_console_000000000000.csv.gz
$ cat csv_console_000000000000.csv
# id,name,price
# 1,Apple,150
# 3,Grape,390
# 2,Orange,120

2. Parquetの例

ほとんど同じですが、Parquetの例もご紹介します。

最終的に、以下のようにエクスポートされます。

parquetのエクスポート結果

コンソールから

コンソールからは、CSVのときと全く同じです。エクスポート形式でParquetを選び、必要な場合は圧縮SNAPPYなど形式を指定します。

コンソールからのparquetエクスポート

SQLから

SQLからもほぼ同じです。EXPORT DATA ステートメントで指定するオプションをParquetのものに変えて実行します。

例えば、field_delimiterheaderformatCSVのときに適用されるので、PARQUETのときには指定しません。

  EXPORT DATA
  OPTIONS(
    uri='gs://エクスポート先のバケット名/parquet/sql_*.parquet.snappy',
    format='PARQUET',
    overwrite=true,
    compression='SNAPPY'
  )
  AS WITH tmp AS (
    SELECT 
      * 
    FROM sample_dataset.sample_table 
    ORDER BY id
  )
  SELECT 
    *
  FROM tmp

エクスポート結果の確認

SQLからのエクスポート結果をダウンロードして、以下のようにして確認しました。

# pyarrowをインストールしておく。
# pip install pyarrow

import pandas as pd

df = pd.read_parquet("parquet_sql_000000000000.parquet.snappy")
df.head(5)

parquetの出力結果

最後に

今回はBigQueryのテーブルデータのエクスポート機能を使って、テーブルのデータをCSVとParquet形式で出力する例をご紹介しました。

特にSQLを使った方法は、スケジュールしたクエリなどと組み合わせると、非常に簡単にエクスポートの定期実行が実現できるのでとても便利ですね。

参考