【新機能】LOAD DATA文（Cross-cloud transfer）でS3のファイルをBigQueryテーブルへロードできるようになりました

BigQueryのLOAD DATA文（Cross-cloud transfer）がGAされた。 Amazon S3のファイルをBigQuery内のテーブルにロードしてみる。

#Google BigQuery

#Google BigQuery Omni

#Google Cloud (GCP)

#Amazon S3

ikeda

2022.11.10

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
昨日、BigQueryの LOAD DATA 文（Cross-cloud transfer）が GA となりました。

【 BigQuery release notes > November 09, 2022 】

【 Cross-cloud transfer from Amazon S3 】　※執筆時点では日本語のガイドは未更新

もともと BigQuery Omni の外部テーブルでAmazon S3など別クラウドのストレージを参照することはできました。 ↓この辺は以前ブログにしています。

S3からBigQueryへ連携する方法いろいろ（Omni/Transfer Service）

※現在はストレージへのアクセスなどは BigLake の機能として整備されました。

ただし、S3の場合、その外部テーブルは aws-us-east-1 という特別なリージョンで作成する必要があり、既にBigQuery内にある US など他のリージョンのデータと組み合わせて使うことが難しく、また、別のリージョンにテーブルとして持ち出すこともできないようでした。

今回のLOAD DATA文を使うことで、S3にあるデータをBigQuery内の通常のテーブルとして格納することができるようになります。

以下ではS3からのロードを試してみます。

制限事項

GA直後ということもあるせいか、いろいろ制限事項があります。

例えばリージョンは、BigQueryの接続が aws-us-east-1 リージョンである必要があるため S3のリージョンも us-east-1 のみ、宛先のBigQueryデータセットも US ・ US-EAST-4 の2つになるようです。（2022/11/10時点）

また、BigQuery Omniの外部テーブルが定額料金のみ（ ※最近オンデマンド料金で試せるようになった）だったのに対し、 LOAD DATA文では予約スロットが使われないそうです。

Limitations
…
・LOAD DATA jobs don't run on reservations. Jobs utilize on-demand slots that are managed by Google Cloud.

使ってみる

とりあえず使ってみます。

外部テーブルの時と同じく、BigQueryの接続（S3）の作成が必要になります。

↓こんな感じで作成できますが、AWS側の権限付与などは、先述の以前のブログをご覧下さい。

app_name=bqomni-s3-access
aws_account_id=＜AWSのアカウントID＞
aws_iam_role_id="arn:aws:iam::${aws_account_id}:role/${app_name}-role"

bq mk --connection --connection_type='AWS' \
--iam_role_id=$aws_iam_role_id \
--location=aws-us-east-1 \
${app_name}-conn

データ格納先になるデータセットを作成します。

US か US-EAST-4 とのことなので、前者のリージョンを使用。

CREATE SCHEMA bqomni_s3_transfer
OPTIONS(
    location="US"
);

ここからが本題のロードです。
S3上のファイルは圧縮されたJSON（JSON Lines）です。

【 Other statements in Google Standard SQL > LOAD DATA statement 】

LOAD DATA INTO でレコード追記と、 LOAD DATA OVERWRITE でテーブルごと上書きができるようです。

テーブルのスキーマ情報は自動で検出してくれるそうなので、まずは未指定でロードしてみます。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://＜S3バケット名＞/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

オプションなど外部テーブルの時と同じような書き方ですね。

↓無事ロードされました！

これで本題は終わりなのですが、もう少しいろいろ試してみます。

次にスキーマ情報を定義してロードしてみます。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_with_schema (
    station_number INTEGER, year INTEGER, month INTEGER, day INTEGER,
    fog BOOLEAN, rain BOOLEAN, snow BOOLEAN, hail BOOLEAN, thunder BOOLEAN, tornado BOOLEAN
)
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://＜S3バケット名＞/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

↓こちらは指定通りにロードされました。

JSON型でロードしたい！

リリースノートに以下のような記載がありました。

Load semi-structured JSON source data into BigQuery without providing a schema by using JSON columns in the destination table.

スキーマ定義せず JSON型でロードできると、フィールドの追加などに柔軟に対応できそうなので、良さそうです。

いろいろ試したのですが、 ↓のようなもともとのJSONファイル（オブジェクトが入れ子になっていないフラットなもの）ではうまくJSON型として格納することができず…

{"station_number":"110240","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
{"station_number":"670050","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
{"station_number":"949350","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
…

↓のようなデータ（入れ子があるもの）に変更しました。

{data:{"station_number":"110240","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
{data:{"station_number":"670050","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
{data:{"station_number":"949350","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
…

変更後のファイルは、↓のSQLでJSON型としてロードできました。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_json (
    data JSON
)
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://＜S3バケット名＞/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

ちなみに上記SQLの2行目のカラム指定が無いと、 STRUCT型としてスキーマを検出して定義・ロードされました。

（この辺は別のやり方が分かったら追記します。たぶん。）

（2022/11/18追記）
これが正しい方法なのか確証はありませんが、 ↓のようにJSON Linesを1列のCSVとしてみなすことで、前述の前者のような入れ子の無いファイルでもそのままJSON型としてロードできました。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_jsonl_as_csv (
    data JSON
)
FROM FILES (
    format='CSV',
    field_delimiter='\t',
    compression='GZIP',
    uris=['s3://＜S3バケット名＞/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;