goodbyegangsterのブログ

備忘録的な

BigQueryでExternal Tableを作成する

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

bq mkdef

作成されたjsonは下記。

table_definition.json

{
  "autodetect": true,
  "csvOptions": {
    "encoding": "UTF-8",
    "quote": "\""
  },
  "sourceFormat": "CSV",
  "sourceUris": [
    "gs://[bucket-name]/external_table/train.csv"
  ]
}

スキーマ定義情報がないと不安になりますが、BigQueryでテーブル作成時にauto detectされるので大丈夫です。下記が各パラメーターに関するリファレンスです。

ExternalDataConfiguration

auto detectを利用すると、csvファイル1行目がヘッダー行であるか否か、判断してスキーマ定義を作ってくれるようです。

External Tableを作成する

テーブルを作成してみます。

外部の永続テーブルを作成してクエリを実行する

bq mkコマンドにて作成。

$ bq mk --external_table_definition=table_definition.json sample.external_test_table

bq mk

作成されたテーブルのスキーマ情報を確認してみます。ヘッダー行を列名として読み取って、データ型を設定してくれています。

$ 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 |
+-------------+-----------------------------------------------------+------+

インタラクティブ クエリの実行