Google ColabでBigQueryを使う際によく使うコードスニペット

# 前提: 認証
```python
from google.colab import auth
auth.authenticate_user()
```

ColabからBigQueryを利用するには、最初に認証を行う必要がある。

<img width="340" alt="Screenshot 2020-06-23 at 13.46.39.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14097/15b5e300-fc2b-c2ec-c831-04239f0f867d.png">

短いコードなのでわざわざ覚えるほどでもないが、コードスニペットが用意されているのでそれを使うと素早く追加できる。

# クエリーの結果をDataFrameに格納する
## Magicコマンドを使う
`google.cloud.bigquery`の[Magicコマンド](https://googleapis.dev/python/bigquery/latest/magics.html)を使うと、一発でクエリーの結果をPandasのDataFrameとして取得できる。

```sql
%%bigquery df --project myproject
SELECT * FROM `myproject.foo.logs`
```

Magicコマンドの引数で指定した名前でDataFrameが作られ、Python側から参照できるようになっている。

```python
df.head(5)
```

### ただ内容を確認したい時
ただ内容を確認したい時は、格納先のDataFrameを未指定にする。

```sql
%%bigquery --project myproject
SELECT * FROM `myproject.foo.logs`
```

また、その際に`%load_ext google.colab.data_table`を実行しておくと、出力結果に対してフィルタリング・ページングができるようになって便利。

## パラメータを渡す
[Parameterized Query](https://cloud.google.com/bigquery/docs/parameterized-queries)の機能をつかって、クエリーにパラメータを渡すことができる。

```sql
%%bigquery --project myproject df --params {"user_id": 123}
SELECT * FROM `myproject.foo.logs` WHERE user_id = @user_id
```

パラメータは`--params`引数に辞書型で渡す。渡されたパラメータは@パラメータ名で参照できる。テーブル名の箇所にはパラメータは使えないので注意。また、paramsはコマンドの最後に指定しないとエラーになるため注意

パラメータには配列を渡すこともできる。

```sql
%%bigquery --project myproject df --params {"user_id": [1, 2, 3]}
SELECT * FROM `myproject.foo.logs` WHERE user_id in UNNEST(@user_id)
```

変数を渡したい場合

```Python
params = {"user_id": [1, 2, 3]}
```

```sql
%%bigquery --project myproject df --params $params
SELECT * FROM `myproject.foo.logs` WHERE user_id in UNNEST(@user_id)
```

## Pandasの機能を使う
```python
import pandas as pd

query = "SELECT * FROM ..."
df = pd.io.gbq.read_gbq(query, project_id="myproject") 
```

Magicコマンドを使うやり方では、クエリーの内容を動的に組み合わせたり、ループの中でクエリーを発行するのに限界があるので、そういった場合にはPandasを使ってクエリーを発行し、結果をDataFrameに格納する。

# 結果をCSVとして保存する
```python 
import os
df.to_csv(os.path.join("output.csv"), index=False)
```

取得したDataFrameは`to_csv`でCSVファイルに書き出せる。Google Driveをマウントしておけば、Google Drive上に書き出すことができて便利。

```python
from pathlib import Path
Path(out_dir).mkdir(parents=True, exist_ok=True)
```

出力先のディレクトリを用意したい時は`pathlib`の`Path.mkdir`が便利。`parents=True`をセットすることで、中間ディレクトリもまとめて作ってくれたり、`exist_ok=True`をセットすることで、すでにディレクトリがある場合でもエラーにならず、処理を続行できる。

# クエリーの結果からテーブルを作る
分析の内容によっては、中間テーブルを作ることで計算量を節約できたり、中間結果の可視化/チェックをできて効率を改善できる。

最新の[google-cloud-bigquery](https://googleapis.dev/python/bigquery/latest/index.html)では、`--destination_table`を指定することで、クエリーの実行結果からそのままテーブルを作ることができるが、2020年6月現在Google Colabでデフォルトで入っている`google-cloud-bigquery`のバージョンではこのオプションが利用できないため、Magicコマンドを使わずにジョブを作成するコードを書く。

```sql
from google.cloud import bigquery

client = bigquery.Client(project="myproject")
table_id = "myproject.foo.purchase_logs"

job_config = bigquery.QueryJobConfig(destination=table_id)

sql = r'''
    SELECT transaction_id, user_id, transaction_date, product_id
    FROM `myproject.foo.transactions`
    WHERE action = "purchase"
'''

query_job = client.query(sql, job_config=job_config)
query_job.result()
```

公式ドキュメント: https://cloud.google.com/bigquery/docs/writing-results?hl=ja

なお、クエリー内で`DECLARE`などScriptingを使用している場合は、BigQueryの制約上実行結果をテーブルに保存できないので注意。
https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting
https://stackoverflow.com/questions/58646725/cannot-set-destination-table-with-bigquery-python-api

### 参考: Notebookで使えるgoogle-cloud-bigqueryパッケージのバージョンの確認方法
```
!pip show google-cloud-bigquery
```

バージョン`1.22.0`以降であれば、`--destination_table`を使用できる。


## テーブルが存在する時に上書きする
[QueryJobConfig](https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.job.QueryJobConfig.html#google.cloud.bigquery.job.QueryJobConfig)の[WriteDisposition](https://cloud.google.com/bigquery/docs/reference/auditlogs/rest/Shared.Types/WriteDisposition)で`WRITE_TRUNCATE`を指定すると、すでにテーブルが存在した場合に内容を破棄した上で作り直すことができる。(元のテーブルのデータは破棄されるため注意)

```python
job_config = bigquery.QueryJobConfig(destination=table_id, write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE)
```

`WRITE_APPEND`を指定した場合は既存のテーブルにさらにデータが追加される。


# CSVファイルの中身からテーブルを作る
CSVファイルからテーブルを作りたい場合は、`bq`コマンドの[bg load](https://cloud.google.com/bigquery/docs/reference/bq-cli-reference?hl=ja#bq_load)を使ってアップロードする。

```
!bq load --project_id=myproject --autodetect --source_format=CSV myproject:foo.products sample.csv
```

指定するパラメータの内容は、読み込むCSVの内容によって適宜変更する。

`--replace`を指定すると、すでにテーブルがあった場合にその内容を破棄した上で、作り直すことができる。

https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv?hl=ja

# その他
## クエリー生成用のメソッドをheredocで書く

```python
def build_some_query(user_id, product_id):
  return f'''
SELECT time, user_id, product_id, price FROM some_table WHERE user_id = {user_id} AND product_id = {product_id}
'''
```

# 参考
* https://colab.research.google.com/notebooks/bigquery.ipynb
* https://cloud.google.com/bigquery/docs/writing-results?hl=ja