BigQueryでGCS上のCSVファイルを外部テーブルとして使用する

Cloud Storage に取り込んだ CSV（TSV）ファイルを、SQL でちょっと加工してから BigQuery に投入する手順について、公式ドキュメントではあちこちのページを見て回らないといけなかったので覚書きとして。

やりたいこと

• BigQueryからGCSに保存したCSVファイルをSQLで検索する
• CSVデータのスキーマは独自で定義（自動検出しない）
• 検索結果をBigQueryの実テーブルに追加する

前提

• 外部データソースには 永続テーブル と 一時テーブル の２種類がある
• 永続テーブルは、データセットで作成され、外部データソースにリンクされるテーブル
• 一時テーブルは、外部データソースに対するクエリ実行時に一時的に外部データソースにリンクしたテーブル
• BigQuery データセットと外部データソースを同じロケーションに配置

準備

1.スキーマ定義を作成

構文

[
  {
    "description": "[description]",
    "name": "[name]",
    "type": "[type]",
    "mode": "[mode]"
  },
     ...
]

• [description] : （省略可）列の説明

• [field_name] : 列の名前（英字(a-z,A-Z)、数字(0-9)、アンダースコア(_)で最大 128 文字）
  参考）https://cloud.google.com/bigquery/docs/schemas?hl=ja#column_names

• [field_type] : 列のデータ型
  参考）https://cloud.google.com/bigquery/docs/schemas?hl=ja#standard_sql_data_types

• [mode] : （省略可）列のモード（デフォルトは NULLABLE）
  参考）https://cloud.google.com/bigquery/docs/schemas?hl=ja#modes

例

schema.json

[
   {
     "name": "string_field_1",
     "type": "STRING"
   },
   {
     "name": "string_field_2",
     "type": "STRING"
   },
   {
     "name": "string_field_3",
     "type": "STRING"
   }
]

2.データ定義ファイルを作成

構文

$ bq mkdef \
  --noautodetect \
  --source_format=[source_format] \
  "[bucket_uri]" \
  [path_to_schema_file] \
  > [definition_file]

• --noautodetect : スキーマの自動検出しない（自動検出する場合は --autodetect ）
• [source_format] : ファイル形式（NEWLINE_DELIMITED_JSON , CSV , GOOGLE_SHEETS から選択）
• [bucket_uri] : 外部データソースが存在する CloudStorage の URI
• [path_to_schema_file] : ローカルマシン上の JSON スキーマファイルのパス
• [definition_file] : テーブル定義ファイル名（出力）

例

$ bq mkdef \
  --noautodetect \
  --source_format=CSV \
  "gs://mybucket/externalData.tsv" \
  ./schema.json \
  > define.json

3.データ定義ファイルを適宜修正

例

define.json

{
 "csvOptions": {
   "allowJaggedRows": false,
   "allowQuotedNewlines": false,
   "encoding": "UTF-8",
   "fieldDelimiter": ",",
   "quote": "\"",
   "skipLeadingRows": 0
 },
 "schema": {
   "fields": [
　　　　:

• fieldDelimiter : フィールド区切り文字（カンマ区切りをタブ区切りにする場合は ”\t” に変更）
  参考）https://cloud.google.com/bigquery/docs/reference/rest/v2/tables?hl=ja#externaldataconfiguration

永続テーブルを使用する

1.BigQuery データセットに外部データソースにリンクするテーブルを作成

参考）https://cloud.google.com/bigquery/external-data-cloud-storage?hl=ja#permanent-tables

構文

$ bq mk \
  --external_table_definition=[path_to_definition_file] \
  [dataset].[table]

• [path_to_definition_file] : ローカルマシン上のテーブル定義ファイルのパス
• [dataset] : テーブルを作成する対象のデータセットの名前（BigQuery に既存）
• [table] : 作成するテーブルの名前

例

$ bq mk \
  --external_table_definition=./define.json \
  mydataset.external_table

2.外部データソースにリンクする永続テーブルに対してクエリを実行する

例

$ bq query \
  ‘SELECT string_field_2 FROM mydataset.external_table WHERE string_field_1=”V"’

（BigQueryの通常のテーブルと同様に扱える）

3.クエリ結果をテーブルに保存する

構文

$ bq --location=[location] query \
  --destination_table [project_id]:[dataset].[table] \
  --use_legacy_sql=false '[query]'

• [location] : （省略可）クエリの処理に使用するロケーション名
• [project_id] : プロジェクト ID
• [dataset] : クエリ結果を書き込むテーブルが含まれるデータセット名
• [table] : クエリ結果を書き込むテーブル名
• [query] : 標準 SQL 構文のクエリ

例）新規作成（既に同名のテーブルがあるとエラーとなる）

$ bq query \
  --destination_table mydataset.mytable \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM `myproject`.mydataset.external_table
   WHERE string_field_1="V"'

例）上書き登録

$ bq query \
  --destination_table mydataset.mytable \
  --replace \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM `myproject`.mydataset.external_table
   WHERE string_field_1="V"'

例）追加登録

$ bq query \
  --destination_table mydataset.mytable \
  --append \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM `myproject`.mydataset.external_table
   WHERE string_field_1="V"'

一時テーブルを使用する

1.外部データソースにリンクする一時テーブルに対してクエリを実行する

参考）https://cloud.google.com/bigquery/external-data-cloud-storage?hl=ja#temporary-tables

構文

$ bq --location=[location] query \
  --external_table_definition=[table]::[path_to_definition_file] \
  '[query]'

• [location] : （省略可）使用するロケーション名
• [table] : 作成する一時テーブルの名前
• [path_to_definition_file] : ローカルマシン上のテーブル定義ファイルのパス
• [query] : 一時テーブルに送信するクエリ（ダブルクォーテーションかシングルクォーテーションで囲む）

例

$ bq query \
  --external_table_definition=external_data::./define.json \
  'SELECT string_field_2 FROM external_data where string_field_1=”V"'

2.クエリ結果をテーブルに保存する

構文

$ bq --location=[location] query \
  --destination_table [project_id]:[dataset].[table] \
  --use_legacy_sql=false '[query]'

• [location] : （省略可）クエリの処理に使用するロケーション名
• [project_id] : プロジェクト ID
• [dataset] : クエリ結果を書き込むテーブルが含まれるデータセット名
• [table] : クエリ結果を書き込むテーブル名
• [query] : 標準 SQL 構文のクエリ

例）新規作成（既に同名のテーブルがあるとエラーとなる）

$ bq query \
  --destination_table mydataset.mytable \
  --external_table_definition=external_data::./define.json \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM external_data
   WHERE string_field_1="V"'

例）上書き登録

$ bq query \
  --destination_table mydataset.mytable \
  --replace \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM external_data
   WHERE string_field_1="V"'

例）追加登録

$ bq query \
  --destination_table mydataset.mytable \
  --append \
  --use_legacy_sql=false \
  'SELECT string_field_2, string_field_3
   FROM external_data
   WHERE string_field_1="V"'