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

Cloud Storage に取り込んだ CSV（TSV）ファイルを、SQL でちょっと加工してから BigQuery に投入する手順について、公式ドキュメントではあちこちのページを見て回らないといけなかったので覚書きとして。
# やりたいこと
- BigQueryからGCSに保存したCSVファイルをSQLで検索する
- CSVデータのスキーマは独自で定義（自動検出しない）
- 検索結果をBigQueryの実テーブルに追加する

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

# 準備
### 1.スキーマ定義を作成

**構文**

```json
[
  {
    "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.データ定義ファイルを作成
**構文**

```console
$ 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]` : テーブル定義ファイル名（出力）

**例**

```console
$ 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]` : 作成するテーブルの名前

**例**

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

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

**例**

```console
$ bq query \
  ‘SELECT string_field_2 FROM mydataset.external_table WHERE string_field_1=”V"’
```
（BigQueryの通常のテーブルと同様に扱える）

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

**構文**

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

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

```console
$ 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"'
```

**例）上書き登録**

```console
$ 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"'
```

**例）追加登録**

```console
$ 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

**構文**

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

**例**

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

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

**構文**

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

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

```console
$ 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"'
```

**例）上書き登録**

```console
$ 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"'
```

**例）追加登録**

```console
$ 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"'
```