やってみた: Cloud SQL for MySQL → Datastream → 別プロジェクトの BigQuery

GCP

pre-amble

構成

  • project A: Cloud SQL, DataStream
  • project B: BigQuery

という設定だよ

DB と BigQuery dataset の対応

ここで紹介するやり方は、複数のデータベースを1つの BigQuery dataset に流し込むやり方で、たとえば

  • MySQL 側の db1 DB に tbl1 tbl2 がある
  • db2 DB に tbl1 がある
  • BigQuery dataset は mysql_data を作成

とした場合、 BigQuery 側には

  • mysql_data.db1_tbl1
  • mysql_data.db1_tbl2
  • mysql_data.db2_tbl1

のように、1つの dataset 以下に schema_table の形式のテーブルが作成される。dataset と DB を 1:1 にするやり方は少なくとも UI からだと難しそう。 terraform だとできるのかも? できないかも?

Cloud SQL の準備

資料

[1] Configure a Cloud SQL for MySQL database  |  Datastream  |  Google Cloud

Cloud SQL インスタンスを立てる

ふつうにインスタンスをつくれば OK なんだけど、 Datastream で使うので[1] binlog を有効化しておく (あとからでもできる)

また、Datastream でデータを転送したいので、たとえばこことかからダミーデータを入れておくとよき

tip: 家の回線が IPv6 の場合 gcloud beta sql connect コマンドだと IPv4 じゃなくても接続できる

Datastream 用にユーザを追加

Datastream が MySQL に接続するための DB user をつくる。資料1にある SQL を叩けば OK ↓

CREATE USER 'datastream'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';
FLUSH PRIVILEGES;

Datastream の設定

全体像はこう↓

  • MySQL につなげる Connection Profile を作成
  • BigQuery につなげる Connection Profile を作成
  • それぞれの profile を source/destination とした Stream を作成

資料

[1] Replicate data to BigQuery in near real-time with Datastream  |  Google Cloud

[2] Create connection profiles  |  Datastream  |  Google Cloud

[3] DatastreamをつかったChange Data Capture環境構築 (MySQL -> Datastream -> Cloud Storage) #GoogleCloud - Qiita

1. MySQL の Connection Profile の作成

tip: ここでは Datastream と Cloud SQL の画面をいったり来たりするよ

tip: 資料1 の QuickStart はわかりやすいけど postgres 用なので、適宜2で保管していくイメージ

まず "Connection settings" のところでは↓のように入力する

  • Hostname or IP: Cloud SQL instance の Public IP Address
  • Username / Password: 前のところで作成した datastream ユーザの情報

"Secure your connection to your source" のところは、 None/Server-only/Server-client の選択肢の順でセキュアになっていく。 Server-client も設定は難しくないのでこれがおすすめ。

Server-client にした場合、

  1. Source CA certificate = Cloud SQL instance の certificate
  2. Source client certificate
  3. Source private key

の3つのファイルが必要になるので、 Cloud SQL のコンソールから証明書たちを発行する

ファイルをローカルに保存したら、 connection profile を作成するページに戻ってアップロードする

最後の Define Connectivity Method のところは、 IP allowlisting でやってみた。この選択肢をクリックすると、 Datastream が使う IP のリストが出てくるので、これらを Cloud SQL 側に追加する。

あとは connection テストが通れば ok

2. BigQuery connection profile の作成

この段階だと「どの BigQuery につなぐ」っていう設定項目はないので、 Quickstart の通りに作成すれば OK

https://cloud.google.com/datastream/docs/quickstart-replication-to-bigquery#destCP4BigQuery

3. Stream の作成

こんな感じの画面で、 step 4 までは資料1を見つつさくさく進めて ok

step 5 になったら、 新しいデータセットを作成する ことで違う project の dataset に接続する

注意点としては、一度選択をはずすと、違う project の dataset は選択肢に出てこない。同じ名前の dataset をもう一度選択したかったら、一回 dataset を削除して改めて同じ手順を踏む必要がある

この段階で Validate stream をしてもエラーになるので、とりあえず CREATE だけする

Datastream service agent に別 project の BigQuery の権限をつける

前提: Datastream が source からデータを読んだり destination にデータを書き込むときは Datastream service agent を使ってて、同じ project 内ならデフォルトで bigquery の権限を持っているけど、違う project だと権限をつけてあげる必要がある。

権限付与には↓の3つのステップがある

  1. service agent の email address の特定
  2. 必要なロールの特定
  3. iam binding の設定

1. service agent の email address の特定

結論 service-PROJECT_NUMBER@gcp-sa-datastream.iam.gserviceaccount.com なんだけど、調べるためには↓を見る

Service agents  |  IAM Documentation  |  Google Cloud

このページにはすべてのサービスの service agent の email が載ってるので、今後も役に立つはず

2. 必要なロールの特定

これも結論

  • BigQuery Data Editor
  • BigQuery Job User

を project level (dataset level ではなく) でつければ OK。dataset を特定してるから Data Editor は dataset だけでいいと思ったんだけど、それだと stream がエラーになったので、project level でつける必要がある (Job User は project でしかつけられないよ)。

なぜ Data Editor かというと、また service agent のページを見ると、 Datastream service agent には "Datastream Service Agent" がついているのがわかる。

で、このロールについてる permission はこう↓なので、これらを持ってる最小権限の pre-defined role の組み合わせは BigQuery Data Editor + BigQuery Job User となる

3. iam binding の設定

principal/email と必要なロールがわかったので、 BigQuery のあるプロジェクト側で 権限を付与する

注意点として、少なくとも自分の環境だと Datastream service agent がコンソールだと BigQuery 側 project の IAM 一覧に出てこなかったという謎がある。下記の gcloud コマンドを叩けば見れるので、なんか UI 側にそういう制限があるのかも?

gcloud projects get-iam-policy <your project> \
        --flatten="bindings[].members" \
        --format='table(bindings.role)' \
        --filter="bindings.members:service-<your project number>@gcp-sa-datastream.iam.gserviceaccount.com"

Stream の実行

最後に Stream をストリームしはじめれば ok。もしエラーになったら Logging を見るといいかも? (解決しないこともある)