BigQueryでGCS上のファイルにクエリを発行する

はじめに

GCSからBigQueryへファイルを取り込む際の型定義が悩ましい。
できれば自動検知で済ませたいけど、検知できない場合（例えばYYYYMMDDの数字を日付と判断できなそう）に困る。
GCS上のファイルにクエリを発行でき、SQLのSELECT句でちょっとした加工ができるなら、自動検知できるように加工すると良さそう。

公式のドキュメント

https://cloud.google.com/bigquery/external-data-cloud-storage
https://cloud.google.com/bigquery/external-data-cloud-storage#temporary-tables

GCSに置いたファイル

test.csv

null,abc,文字列１,12345,1.2345,20180101,2018-01-01,2018/01/01
null,def,文字列２,67890,6.7890,20180201,2018-02-01,2018/02/01

テーブル定義ファイル

bqtabledef.json

{
  "autodetect": true,
  "sourceFormat": "CSV",
  "sourceUris": [
    "gs://XXXXX-bucket/test.csv"
  ]
}

CLIで単にSELECT ＊してみる

$ bq --project=XXXXX --location=US query --external_table_definition=testtable::/tmp/bqtabledef.json 'select * from testtable'
Waiting on bqjob_rbcf80eccec0c503_00000164b09097fc_1 ... (0s) Current status: DONE   
+----------------+----------------+----------------+---------------+----------------+---------------+--------------+--------------+
| string_field_0 | string_field_1 | string_field_2 | int64_field_3 | double_field_4 | int64_field_5 | date_field_6 | date_field_7 |
+----------------+----------------+----------------+---------------+----------------+---------------+--------------+--------------+
| null           | abc            | 文字列１           |         12345 |         1.2345 |      20180101 |   2018-01-01 |   2018-01-01 |
| null           | def            | 文字列２           |         67890 |          6.789 |      20180201 |   2018-02-01 |   2018-02-01 |
+----------------+----------------+----------------+---------------+----------------+---------------+--------------+--------------+

結果

null（という文字列）は文字列型になってる。
20180101は数値型。
2018/02/01は区切り文字がハイフンではなくスラッシュなのに日付型と判断してくれている。

YYYYMMDDを日付型にするには

例えば文字にキャストした上でPARSE_DATEしてみる。

test.csv

col1,col2,col3,col4,col5,col6,col7,col8
null,abc,文字列１,12345,1.2345,20180101,2018-01-01,2018/01/01
null,def,文字列２,67890,6.7890,20180201,2018-02-01,2018/02/01

$ bq --project=XXXXX --location=US query --external_table_definition=testtable::/tmp/bqtabledef.json --use_legacy_sql=false 'select PARSE_DATE("%Y%m%d", CAST(col6 AS STRING)) from testtable'
Waiting on bqjob_r52cefb0cf8a06d7c_00000164b5f1932f_1 ... (0s) Current status: DONE   
+------------+
|    f0_     |
+------------+
| 2018-01-01 |
| 2018-02-01 |
+------------+