Cloud Storage上にアップロードしたCSVファイルに対して、External Tableの機能を利用し、BigQueryのクエリを実行してみます。BigQuery External Tableとは、AWSで言うところの、Redshift Spectrumとなります。
利用したファイル
利用したファイルは、kaggleのサイトにて公開されているtitanic passenger listのトレーニング用データとなります。下記よりダウンロード。
https://www.kaggle.com/c/titanic/data
データはこんな感じになります。
PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked 1,0,3,"Braund, Mr. Owen Harris",male,22,1,0,A/5 21171,7.25,,S 2,1,1,"Cumings, Mrs. John Bradley (Florence Briggs Thayer)",female,38,1,0,PC 17599,71.2833,C85,C 3,1,3,"Heikkinen, Miss. Laina",female,26,0,0,STON/O2. 3101282,7.925,,S
適当なCloud StorageのBucketにアップロードしておきます。
External Tableの制限事項
制限事項です。
テーブル定義ファイルの作成
まず、テーブル定義ファイルを作成します。テーブル定義ファイルとは、下記とのこと。
テーブル定義ファイルには、外部テーブルのスキーマ定義とメタデータ（テーブルのデータ フォーマットや関連プロパティなど）が記述されています。テーブル定義ファイルを作成するときにスキーマの自動検出を有効にして、外部データソースのスキーマを定義できます。コマンドラインを使用する場合は、スキーマをインラインで指定できます。また、スキーマ定義を含む JSON ファイルを使用することもできます。
今回は、BigQueryのスキーマ定義自動検出機能を利用して、テーブル定義ファイルを作成してみます。
以下コマンドにてテーブル定義ファイルを作成してくれます。
$ bq mkdef --autodetect=true --source_format=CSV \ gs://[bucket-name]/external_table/train.csv > table_definition.json
作成されたjsonは下記。
table_definition.json
{ "autodetect": true, "csvOptions": { "encoding": "UTF-8", "quote": "\"" }, "sourceFormat": "CSV", "sourceUris": [ "gs://[bucket-name]/external_table/train.csv" ] }
スキーマ定義情報がないと不安になりますが、BigQueryでテーブル作成時にauto detectされるので大丈夫です。下記が各パラメーターに関するリファレンスです。
auto detectを利用すると、csvファイル1行目がヘッダー行であるか否か、判断してスキーマ定義を作ってくれるようです。
External Tableを作成する
テーブルを作成してみます。
bq mkコマンドにて作成。
$ bq mk --external_table_definition=table_definition.json sample.external_test_table
作成されたテーブルのスキーマ情報を確認してみます。ヘッダー行を列名として読み取って、データ型を設定してくれています。
$ bq show --schema=true --format=prettyjson sample.external_test_table [ { "mode": "NULLABLE", "name": "PassengerId", "type": "INTEGER" }, { "mode": "NULLABLE", "name": "Survived", "type": "INTEGER" }, (略) { "mode": "NULLABLE", "name": "Embarked", "type": "STRING" } ]
検索してみます。"の引用符をちゃんと判断して、Name列を表示してくれています。
$ bq --location=asia-northeast1 query --use_legacy_sql=false \ 'SELECT PassengerId, Name, Age FROM sample.external_test_table limit 3' +-------------+-----------------------------------------------------+------+ | PassengerId | Name | Age | +-------------+-----------------------------------------------------+------+ | 1 | Braund, Mr. Owen Harris | 22.0 | | 2 | Cumings, Mrs. John Bradley (Florence Briggs Thayer) | 38.0 | | 3 | Heikkinen, Miss. Laina | 26.0 | +-------------+-----------------------------------------------------+------+
