この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部、池田です。
昨日、BigQueryの LOAD DATA 文（Cross-cloud transfer）が
GA となりました。
【 BigQuery release notes > November 09, 2022 】
【 Cross-cloud transfer from Amazon S3 】 ※執筆時点では日本語のガイドは未更新
もともと BigQuery Omni の 外部テーブル でAmazon S3など別クラウドのストレージを参照することはできました。 ↓この辺は以前ブログにしています。
※現在はストレージへのアクセスなどは 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`;おわりに
S3（やAzure Blob Storage）との連携の幅が広がるのではないでしょーか。
3
