Cloud SQL federated queries from BigQuery

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

フェデレーション構成

• BigQuery は以下のサービスを外部データソースとして、BigQueryへ接続情報を設定した構成をサポートしてます。

  • Cloud Spanner
  • Cloud SQL

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

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

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

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

• Spanner データ型マッピング

設定順序

• Bigguey 上の設定を行う際は bigquery.admin 権限を含むIAMから実施が必要です。

• BigQuery Connection API の有効化が必要です。
  

• 今回は 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 コンソール上から '外部接続' としてカタログが完了します。
  

• 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 |
+----------+-----------+

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 句は少し慣れが必要そうです。