BigQueryでS3上のデータをデータセットに取り込んでみる

はじめに

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

BigQueryではAmazon S3を外部テーブルとしてデータを参照することができます。またBiqQueryではクエリを実行する場合は同一リージョンのデータセット間でのみ実行できます。したがってS3をソースとする外部テーブルと一般公開データセット などBigQueryのデータセット上のデータを結合したい場合はBigQuery Data Transfer Serviceを使ってBigQueryのデータセットにテーブルを作成する必要があります。

だた、すでにS3を外部テーブルとしてデータを参照することができる状態であればBigQuery Data Transfer Serviceを使わなくてもSQLを使ってBigQueryのデータセットにテーブルを作成することができるので、今回このパターンを試してみたのでまとめます。

クロスクラウド オペレーションでデータを読み込む  |  BigQuery  |  Google Cloud

S3上のデータをBigQueryで扱うには

S3上のデータをBigQueryで取り扱うには「S3をソースとして外部テーブルを設定する」か「BigQuery Data Transfer Serviceを使ってデータを直接BigQuery上に作成する」といった方法があります。

今回試すのは前者の方法で、S3上のデータをソースとする外部テーブルを使った方法になります。その中でもBigQueryにテーブルを作成する際には「LOAD DATAステートメントで外部接続からテーブルを作成する」、「CREATE TABLEステートメントで外部テーブルからテーブルを作成する」方法がありますが両パターンを試してみます。

手順としては以下の流れで行います。

1. 外部接続を作成する
   1. LOAD DATAステートメントで外部接続からテーブルを作成する
2. 1で作成した外部接続を使って外部テーブルを作成する
   1. CREATE TABLEステートメントで外部テーブルからテーブルを作成する

なおソースとなるS3上のファイルパスはs3://{バケット名}/population/major_results_2020.csvで、中身は以下になります。

pref_cd,pref_name,pref_alphabet,population,population_male,population_female,population_of_2015,population_variation,population_var_ratio,area,population_density,age_average,age_median,under_15,between_15_64,over_65,under_15_ratio,bet_15_64_ratio,over_65_ratio,under_15_male,bet_15_64_male,over_65_male,under_15_ratio_male,bet_15_64_ratio_male,over_65_ratio_male,under_15_female,bet_15_64_female,over_65_female,under_15_ratio_female,bet_15_64_ratio_female,over_65_ratio_female,sex_ratio,japanese,foreigners,households,households_general,households_institutional,households_2015,households_nuclear,households_couples,households_couples_child,households_male_child,households_female_child,households_single,households_single_over65,households_couples_over_65_60_reposted,households_3gen_reposted
01,北海道,Hokkaido,5224614,2465088,2759526,5381733,-157119,-2.91949,83424.44,66.6,49.78238,51.34284,556526,2988800,1679288,10.652,57.20614,32.14186,284897,1477750,702441,11.55728,59.94715,28.49558,271629,1511050,976847,9.84332,54.75759,35.39909,89.33012,5188441,36173,2476846,2469063,7783,2444810,1324406,584819,511571,29921,198095,999825,361735,345741,59601
02,青森県,Aomori-ken,1237984,583402,654582,1308265,-70281,-5.37208,9645.64,128.3,50.82694,53.35318,130259,689910,417815,10.52186,55.72851,33.74963,66483,345000,171919,11.39574,59.1359,29.46836,63776,344910,245896,9.74301,52.69164,37.56535,89.12588,1232227,5757,511526,509649,1877,510945,268760,100962,109399,7734,50665,168917,71752,60995,42895
03,岩手県,Iwate-ken,1210534,582952,627582,1279594,-69060,-5.39702,15275.01,79.2,50.58634,52.86212,132735,670784,407015,10.965,55.41224,33.62276,67919,342813,172220,11.65087,58.80639,29.54274,64816,327971,234795,10.32789,52.25947,37.41264,92.88858,1203203,7331,492436,490828,1608,493049,252005,94910,105474,7549,44072,163290,62424,57656,46934
04,宮城県,Miyagi-ken,2301996,1122598,1179398,2333899,-31903,-1.36694,7282.29,316.1,47.42673,48.13367,268931,1385425,647640,11.68251,60.18364,28.13385,137916,701792,282890,12.28543,62.51499,25.19958,131015,683633,364750,11.10863,57.96457,30.92679,95.18398,2280203,21793,982523,980549,1974,944720,507063,181038,234787,13068,78170,362255,97239,102638,67505
05,秋田県,Akita-ken,959502,452439,507063,1023119,-63617,-6.21795,11637.52,82.4,52.89928,56.44966,92855,506960,359687,9.67742,52.83574,37.48684,47526,255856,149057,10.5044,56.55039,32.94521,45329,251104,210630,8.93952,49.52126,41.53922,89.22737,955659,3843,385187,383531,1656,388560,203177,81772,81097,5755,34553,117169,55437,52719,39453
....

またテーブルはテーブルはロケーションがUSのdata_set_sampleデータセットに作成するので予めデータセットを作成しておきます。

create_dataset.sql

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

では早速試してみたいと思います。

外部接続を作成する

S3の外部接続を作成する方法はすでに別エントリがあるのでこちらを参考にしてください。

上記エントリのBigQuery Omni章の「Google Cloud側準備」「AWS側準備」をそのまま行えば外部接続は作成できます。

LOAD DATAステートメントでテーブルを作成する

外部接続が作成できたので早速1つ目の「LOAD DATAステートメントで外部接続からテーブル作成」を試してみます。

接続までできていればLOAD DATAステートメントを使うだけです。

load_data.sql

LOAD DATA INTO data_set_sample.s3_test
  FROM FILES (
    uris = ['s3://{バケット名}/population/major_results_2020.csv'],
    format = 'CSV'
  )
  WITH CONNECTION `aws-us-east-1.ssd_s3_biglake`

これでS3上のデータがBigQueryのデータセット上にs3_testのテーブルとして作成されます。

外部テーブルを作成する

次に外部テーブルを作成した上でデータ取り込みを行ってみます。 外部テーブルを作成するには先に外部テーブル用のデータセットを作成しておく必要があります。

create_schema.sql

CREATE SCHEMA s3_biglake_test
OPTIONS(
    location="aws-us-east-1"
);

次に外部テーブルを作成します。マネコンからs3_biglake_testを選択してテーブルを作成を押下してテーブルを作成します。

• テーブルの作成元: Amazon S3を選択
• S3パスを選択: S3データソースパスs3://{バケット名}/population/major_results_2020.csvを入力
• データセット: s3_biglake_testを選択
• テーブル: 作成したいテーブル名major_results_2020を入力
• スキーマ: 自動検出をチェック

これで外部テーブルが作成されます。

CREATE TABLEステートメントでテーブルを作成する

外部テーブルが作成できたので２つ目の「CREATE TABLEステートメントで外部テーブルからテーブルを作成する」を試してみます。

CREATE TABLEを使ってpref_cdが10未満のデータのテーブルを作成します。

create_table.sql

CREATE OR REPLACE TABLE
  data_set_sample.s3_test_2 AS
SELECT
  *
FROM
  s3_biglake_test.major_results_2020
WHERE
  pref_cd < 10;

この様に外部テーブルを使うとS3上のデータをフィルタしつつBigQueryにテーブルを作成することができます。

まとめ

BigQueryの外部接続を使ってAmazon S3上のデータをBigQuery上にテーブルとして作成してみました。スケジューリングで大規模なデータをBigQueryに転送したい場合はBigQuery Data Transfer Serviceを使うのがベストプラクティスですが、S3上のデータをクエリしてデータを加工しつつテーブルに取り込むには今回の方法は最適では無いかと思います。

最後まで読んで頂いてありがとうございました。