💬

BigQuery初心者を脱出したい3(CloudSQL連携クエリ・スケジュールクエリ)

2021/09/05に公開

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

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

前の記事:
・BigQuery初心者を脱出したい2(パーティション分割テーブル)
https://zenn.dev/ykdev/articles/c98bbd8f87a9d7
・BigQuery初心者を脱出したい1(テーブル作成~検索)
https://zenn.dev/ykdev/articles/e66217cd33bc69

BigQueryからCloudSQLへの接続

以下の「CloudSQL連携クエリ」を利用する。
https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries/?hl=ja

事前準備

  • 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 > スケジュールされたクエリ > 閲覧したいクエリの[表示名]を押下

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

Discussion