Cloud SQL federated queries from BigQuery

BigQuery 上から外部データソースとして Cloud SQL の接続情報を登録し、bq コマンドラインから Cloud SQL のデータを参照します。 

フェデレーション構成
====
* BigQuery は以下のサービスを外部データソースとして、BigQueryへ接続情報を設定した構成をサポートしてます。

  * Cloud Spanner
  * Cloud SQL

***上記以外も Bigquery Omni が発表されていますので、データの移行無くAWS、Azure などサービスから参照が出来そうです。*** 

* フェデレーションはデータソース上のデータを移管せず、Bigquery からデータソースの参照を受け付けます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/4742c360-9d62-3bef-0f1e-ca03bd716b2f.png)

* 外部データソースには `EXTERNAL_QUERY` 関数を使用します。
その際、Cloud SQL のデータ（MySQL または PostgreSQL のデータ型）は BigQuery の標準 SQL 型に変換されます。データ型のマッピングが異なる場合があるので注意が必要です。

* ***Cloud SQL MySQL から BigQuery への型マッピング / PostgreSQL と BigQuery の型マッピング***

https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries#postgresql-mapping

* ***Spanner データ型マッピング***

https://cloud.google.com/bigquery/docs/cloud-spanner-federated-queries#data_type_mappings

設定順序
====
* Bigguey 上の設定を行う際は `bigquery.admin` 権限を含むIAMから実施が必要です。
* `BigQuery Connection API` の有効化が必要です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/c819b032-dd21-075a-0ad5-ebc482ba968e.png)

* 今回は Cloud SQL for Postgresql をデータソースとします。
Cloudsqltable テーブルには、以下のデータが格納されています。

```
postgres=> select * from cloudsqltable;
   date   |   name
----------+-----------
 20211019 | cloudsql1
 20211019 | cloudsql1
 20211020 | cloudsql2
(3 rows)
```

* 外部データソースとの接続設定を行います。

```
bq mk --connection \
--display_name='bigquery_cloudsql' \
--connection_type='CLOUD_SQL' \
--properties='{"instanceId":"xxxx","database":"postgres","type":"postgres"}' \
--connection_credential='{"username":"postgres", "password":"xxxx"}'  \
--project_id=xxxxx --location=us  --connection_id
```
| パラメータ | 説明 | 値の例 |
|:-:|:-:|:-:|
| display_name  | 接続名(任意で省略可)  | 'bigquery_cloudsql' |
| connection_type  | 外部データソースのタイプ  | 'cloud_SQL'  |
| properties  | データソースの instanceID、database、type パラメータを指定する必要があります  | '{"instanceId":"projectid:us-central1:postgresqlinst","database":"postgres","type":"postgres"}'  |
| connection_credential | データソースの username と password  | {"username":"postgres", "password":"gtkpkps@ksv"}  |
| project_id  | プロジェクトID | 123456789  |
| location  | Cloud SQL インスタンスが配置されているリージョン  | us |
| connection_id  | 接続ID | fedtest1 |

* 登録が完了すると、Cloud コンソール上から '外部接続' としてカタログが完了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/d72e106b-b21d-af0d-1cf4-350046b940c6.png)

* `bq` コマンドラインから Cloud SQL のテーブルを表示させてみましょう。 `EXTERNAL_QUERY` 関数を含むクエリを実行します。

```
$ bq query --nouse_legacy_sql 'SELECT * FROM EXTERNAL_QUERY("connection_id", "SELECT * FROM cloudsqltable")'
Waiting on bqjob xxx ... (1s) Current status: DONE   
+----------+-----------+
|   date   |   name    |
+----------+-----------+
| 20211019 | cloudsql1 |
| 20211019 | cloudsql1 |
| 20211020 | cloudsql2 |
+----------+-----------+
```
:::note info
EXTERNAL_QUERY 関数は、外部データベースの読み取り専用クエリがありますので読み取りのクエリを受け付けます。
:::

```
EXTERNAL_QUERY(connection_id, external_database_query[, options]);
```
| 名前 | 説明 | 値の例 |
|:-:|:-:|:-:|
| connection_id  | 接続ID  | bigquery-federation-test.us.test-mysql  |
| external_database_query  | 外部データベースの SQL 言語の読み取り専用クエリ。クエリは外部データベースで実行されます | select * from table  |
| option  | オプションの名前と値の Key-Value ペアを含む、JSON 形式マップの文字列。ともに大文字と小文字が区別されます  | {"default_type_for_decimal_columns":"numeric"}  |

* BigQuery のテーブルと JOIN してみます。



***Bigquery test_dataset_s3.test_tableの情報***

```
bq query --nouse_legacy_sql 'SELECT * FROM test_dataset_s3.test_table'
Waiting on bqjob_xxxxxx ... (0s) Current status: DONE   
+----------+-------------+------+------+
|   date   | randomvalue | data | name |
+----------+-------------+------+------+
| 20211004 | gsjog@wgsf  | NULL | NULL |
| 20211004 | b@etewr     | NULL | NULL |
| 20211005 | brdew@fasz  | NULL | NULL |
+----------+-------------+------+------+
```

* ***LEFT JOIN***

```
 bq query --nouse_legacy_sql 'SELECT * FROM test_dataset_s3.test_table LEFT JOIN EXTERNAL_QUERY("connection-id", "SELECT * FROM cloudsqltable")  AS  cloudsql ON test_table.name = cloudsql.name'
Waiting on bqjob_xxxxxx ... (1s) Current status: DONE   
+----------+-------------+------+------+--------+--------+
|   date   | randomvalue | data | name | date_1 | name_1 |
+----------+-------------+------+------+--------+--------+
| 20211004 | gsjog@wgsf  | NULL | NULL |   NULL | NULL   |
| 20211004 | b@etewr     | NULL | NULL |   NULL | NULL   |
| 20211005 | brdew@fasz  | NULL | NULL |   NULL | NULL   |
+----------+-------------+------+------+--------+--------+
```

* ***FULL OUTER JOIN(外部結合)***

```
bq query --nouse_legacy_sql \
'SELECT * FROM test_dataset_s3.test_table FULL OUTER JOIN EXTERNAL_QUERY("connection-id", "SELECT * FROM cloudsqltable")  AS  cloudsql ON test_table.randomvalue = cloudsql.name'

Waiting on bqjob_xxxxxxx... (1s) Current status: DONE   
+----------+-------------+------+------+----------+-----------+
|   date   | randomvalue | data | name |  date_1  |  name_1   |
+----------+-------------+------+------+----------+-----------+
| 20211004 | b@etewr     | NULL | NULL |     NULL | NULL      |
| 20211005 | brdew@fasz  | NULL | NULL |     NULL | NULL      |
| 20211004 | gsjog@wgsf  | NULL | NULL |     NULL | NULL      |
| NULL     | NULL        | NULL | NULL | 20211020 | cloudsql2 |
| NULL     | NULL        | NULL | NULL | 20211019 | cloudsql1 |
| NULL     | NULL        | NULL | NULL | 20211019 | cloudsql1 |
+----------+-------------+------+------+----------+-----------+

```

EXTERNAL_QUERY を含む JOIN 句は少し慣れが必要そうです。