BigQuery初心者を脱出したい3(CloudSQL連携クエリ・スケジュールクエリ)
BigQuery初心者を脱出しようと記事を書いていきます。その3。
今回は、「BigQueryのスケジュール機能を使ってCloudSQLから定期的にデータを取得することが出来る」と聞いたので、どうやってやるんだろう?と調べた結果を記載していきます。
前の記事:
・BigQuery初心者を脱出したい2(パーティション分割テーブル)
・BigQuery初心者を脱出したい1(テーブル作成~検索)
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 > スケジュールされたクエリ > 閲覧したいクエリの[表示名]を押下
転送実行が無事にされたことを確認して完了。
Discussion