💬

BigQuery初心者を脱出したい３（CloudSQL連携クエリ・スケジュールクエリ）

2021/09/05に公開

GCP

Google Cloud

BigQuery

tech

BigQuery初心者を脱出しようと記事を書いていきます。その３。

今回は、「BigQueryのスケジュール機能を使ってCloudSQLから定期的にデータを取得することが出来る」と聞いたので、どうやってやるんだろう？と調べた結果を記載していきます。

前の記事：
・BigQuery初心者を脱出したい２（パーティション分割テーブル）

・BigQuery初心者を脱出したい１（テーブル作成～検索）

BigQueryからCloudSQLへの接続

以下の「CloudSQL連携クエリ」を利用する。

事前準備

BigQuery 接続サービスを有効にする
CloudSQL側でパブリックIPを有効にする　※接続のホワイトリストは何も追加しなくてOKなのでセキュリティ的にはそれほどリスクは高くなさそう？

CloudSQL側に適当なデータベースとテーブルを作る

use external_test
create table user_master (id int, name varchar(50));

適当にユーザマスタっぽいデータを入れる

insert into user_master values (1, 'Yamada');
insert into user_master values (2, 'Tanaka');
insert into user_master values (3, 'Sato');

CloudSQL側はこんなデータになる

select * from user_master;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Yamada |
|    2 | Tanaka |
|    3 | Sato   |
+------+--------+

BigQueryからCloudSQLへ接続する

BigQuery > エクスプローラ > データを追加 > 外部データソースを選択

CloudSQLの接続情報を入力して「接続を作成」

接続に成功すると「エクスプローラ > 外部接続」にCloudSQLへの接続が追加される

接続へのクエリからデータを取得するSQLを発行してみる。
EXTERNAL_QUERYという関数を使うみたい。

SELECT * FROM 
    EXTERNAL_QUERY(
        "projects/avid-booster-xxxxx/locations/asia-northeast1/connections/external_test",
        "SELECT * FROM user_master;"
    );

先ほど作成したCloudSQLに作成したデータをBigQuery側で取得できた

BigQueryのデータとCloudSQLのデータをJOINしてみる

BigQuery側に以下のようなデータでテーブルを作ってみる。（テーブル名：test_score）

id,user_id,kind,score
1,1,国語,85
2,1,英語,80
3,1,数学,75
4,2,国語,10
5,2,英語,20
6,2,数学,30
7,3,国語,100
8,3,英語,100
9,3,数学,100

CloudSQl側のuser_master（id列）とBigQuery側のtest_score（user_id列）を結合して結果を出力

SELECT user_master.id as user_id,user_master.name,test_score.kind,test_score.score
FROM `avid-booster-xxxxx.dataset_test_01.test_score` as test_score
LEFT JOIN EXTERNAL_QUERY(
        "projects/avid-booster-xxxxx/locations/asia-northeast1/connections/external_test",
        "SELECT * FROM user_master;") as user_master
ON test_score.user_id = user_master.id
ORDER BY user_master.id, test_score.kind;

BigQuery側のトランザクションデータとCloudSQL側のマスタデータを結合して結果を取得が出来た。

CloudSQLのデータを定期取得する

以下のようなクエリを作成する

//BigQueryにテーブルを作成or置き換え、CloudSQLから取得したデータを挿入する
CREATE OR REPLACE TABLE `dataset_test_01.user_master`
    AS
SELECT * FROM
    EXTERNAL_QUERY(
        "projects/avid-booster-xxxxx/locations/asia-northeast1/connections/external_test",
        "SELECT * FROM user_master;")

スケジュール>スケジュールされたクエリを新規作成

希望の間隔でスケジュール設定して完了

スケジュールされたクエリの一覧やクエリの実行結果は以下から確認可能
BigQuery > スケジュールされたクエリ > 閲覧したいクエリの[表示名]を押下

転送実行が無事にされたことを確認して完了。