BigQueryのエクスポート機能で、Cloud Storageのバケットにテーブルのデータをエクスポートしてみた
データアナリティクス事業本部の鈴木です。
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ファイルでエクスポートしてみます。
最終的に、以下のようにエクスポートされます。
コンソールから
エクスポートしたいテーブルを選び、右上のエクスポートボタンをクリックします。選択肢が表示されるので、GCSにエクスポートをクリックします。
Google Cloud Storage へのテーブルのエクスポートに、必要な情報を入力します。
今回は以下のようにしました。保存を押すと早速エクスポートが実行されます。
ファイルの名前は、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 DATAのASの後ろは、WITH句から初めても問題ありませんでした。コンソールからの場合と異なり、ある程度複雑な処理をしてから、結果をエクスポートすることも可能ですね。
BigQueryの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の例もご紹介します。
最終的に、以下のようにエクスポートされます。
コンソールから
コンソールからは、CSVのときと全く同じです。エクスポート形式でParquetを選び、必要な場合は圧縮でSNAPPYなど形式を指定します。
SQLから
SQLからもほぼ同じです。EXPORT DATA ステートメントで指定するオプションをParquetのものに変えて実行します。
例えば、field_delimiterとheaderはformatがCSVのときに適用されるので、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)
最後に
今回はBigQueryのテーブルデータのエクスポート機能を使って、テーブルのデータをCSVとParquet形式で出力する例をご紹介しました。
特にSQLを使った方法は、スケジュールしたクエリなどと組み合わせると、非常に簡単にエクスポートの定期実行が実現できるのでとても便利ですね。
