めもめも

データエンジニアリング、機械学習について書いてます

Cloud SQL と BigQueryを連携する

はじめに

マスタデータを連携したい場合などのために、Cloud SQLで管理しているデータをBigQueryに連携する方法を紹介します。

方法は、大きく以下の3つあります。

1. Cloud SQL 連携クエリ

BigQueryからCloudSQLのデータに対してクエリを実行できる機能です。
メリットは、使うサービスがCloud SQLとBigQueryのみであること、クエリのスケジューリングによって定期的な連携が容易なことです。

2. DataflowDataFusionなどのETLツールで連携

Google Cloudの各種ETLサービスを使うことによって、Cloud SQLとBigQueryを連携することができます。
しかし、そのサービス自体の学習・運用コストがかかってしまいます。

3. サーバーレスエクスポート

Cloud SQLのデータを、コンソール画面のGUI操作によって、GCSにエクスポートする機能です。
簡単にエクスポートできますが、デメリットとして、GCSを経由してしまう点、定期的なスケジューリングが難しい点が挙げられます。

今回は、実装が容易である・定期的なスケジュール実行が可能であるという点で、1のCloud SQL連携クエリを試します。

手順

Cloud SQLの準備

Cloud SQLに以下のようなシンプルなテーブル「student」を用意しておきます。 スクリーンショット 2022-06-12 18.46.22.png

BigQueryの準備

Cloud SQLのデータをインサートするための、同スキーマのテーブルを作成しておきます。
この時点では、空のテーブルです。
スクリーンショット 2022-06-12 18.25.53.png

※ Cloud SQLインスタンスとBigQueryデータセットのロケーションを揃える必要があります。

1. 外部データソースの設定

BigQueryのコンソール画面の「データを追加」から「外部データソース」を選択します。 スクリーンショット 2022-06-12 18.50.44.png

Cloud SQLとの連携を設定します。
Cloud SQL接続名は、Cloud SQLの画面から確認することができます。 スクリーンショット 2022-06-12 18.58.15.png

「接続を作成」を押すと、BigQueryのコンソール画面にて、外部接続が作成されていることが確認できます。

「クエリ」を押して自動で作成されるクエリを実行することで、Cloud SQLのテーブル一覧が取得できると思います。

スクリーンショット 2022-06-12 18.59.04.png

2. BigQueryへのインサート

Cloud SQLと接続できることが確認できたので、以下のSQLを実行して、データをBigQueryのテーブルにインサートしてみます。

INSERT `プロジェクト名.データセット名.テーブル名` 
SELECT * FROM EXTERNAL_QUERY("projects/プロジェクト名/locations/asia-northeast1/connections/CloudSQL_to_BigQuery", "SELECT * FROM student;");

上手くBigQueryにインサートすることができました。

スクリーンショット 2022-06-12 22.25.42.png

最後に

今回は、BigQueryからCloud SQLに対してクエリを実行し、データを連携してみました。

実行したクエリをスケジューリングすることによって、定期的にBigQueryにデータを反映させることも可能です。

今回は、単純にインサートするだけでしたが、以下のようにクエリを変えることで、テーブルの全書き換え(drop & create)も可能です。

CREATE OR REPLACE TABLE `プロジェクト名.データセット名.テーブル名` AS 
SELECT * FROM EXTERNAL_QUERY("projects/プロジェクト名/locations/asia-northeast1/connections/CloudSQL_to_BigQuery", "SELECT * FROM student;");

参考

https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries?hl=ja