はじめに
ここ最近、実務でBigQuery周りを整備する機会があったため、Google Cloud Nextで発表されてからずっと気になっていたBigQuery周りの新機能・CloudSQL Federationを使ってみました。
使ってみると、Embulk・DigdagのようなETLツールを扱えない非エンジニアでも、大変簡便にBigQueryにCloudSQLのデータを蓄積できるような代物であり、少し感動したのでそちらについて紹介していきます。
対象読者
- データ分析基盤を構築中、または、保守運用中のエンジニアの方々
- 購買データ等をBigQueryにデータを集約して分析したいけど、エンジニアリソースがなくてお困りのマーケター・セールスの方々
CloudSQL Federationの概要
今年発表されたBigQuery周りの新機能であり、これを使うことで、BigQueryからCloudSQL（MySQL・PostgreSQL）のデータにアクセスできるようになります。（2019.11.22現在、β版です。）
用途としては、エンジニア向けというよりは非エンジニアがMySQLのデータなどをサクッと分析したい時に活用しやすいものかなと考えられ、使用するメリットとしては、下記3点が挙げられます。
- CloudSQLに保存されているデータをリアルタイムに分析することが可能
- セキュアな接続が可能なため、購買データなどを扱う上で安全
- 取得したCloudSQLのデータはBigQuery側にコピー可能、且つ、Schedule Queryと組み合わせれば、定期的にCloudSQLデータをBigQueryにエクスポート可能なため、Embulk等を使わずとも、非エンジニアでも簡便にBigQueryにデータを蓄積することができるようになる
（参考：Google Cloud Next '19 ブレイクアウトセッション: データ ウェアハウスのあるべき姿と BigQuery の新機能）
CloudSQL Federationの使い方
GCPのアカウントの用意や、クレジットカードの登録などは既に済ませている前提ですのでご了承下さい。
また、今回は、例として、下図のようにBigQuery中にデータセット（ds_test）の中に、user_testというテーブルを用意した状態から始めます。
1. BigQueryとCloudSQLの接続方法
まず、左側の「リソース」タブのところにある「＋データを追加」という部分をクリックします。
すると、「接続を作成」というタブが出てくるので、そこをクリックします。
初めての操作の時は、「BigQuery Connection APIを有効化する」というポップアップが出てくるので、「APIを有効化する」をクリックします。
先程の「接続を作成」を押すと、以下のようにパラメータを入力する画面が出てくるので、良しなに入力して下さい。
なお、Cloud SQL インスタンスIDについては、Cloud SQLの「インスタンス接続名」を入力すればOKです。
接続が完了すると、リソース一覧に「外部接続」が作成されていることが分かり、中身を見てみると、接続情報を確認できます。
なんと、たったこれだけで、BigQueryからCloudSQLへの接続は完了です！
接続が完了したので、次に、BigQueryからCloudSQLのデータを取得するためのクエリを書いていきます。
2. クエリの書き方
基本文法は以下の通りです。
connection_id の部分は、project_id.location_id.connection_id という形で記述し、external_database_query の部分にSQLを記述する形になります。
SELECT * FROM EXTERNAL_QUERY(connection_id, external_database_query);
より具体的に書くと以下のような形になります。
SELECT * FROM EXTERNAL_QUERY(
"mycurryapp.asia-northeast1.cloudsql-mycurryapp",
"SELECT * FROM users WHERE created_at BETWEEN '2019-11-22 00:00:00' AND '2019-11-22 23:59:59';"
);
その他の例として、MySQLやPostgreSQLに組み込まれている関数は普通に使用できるので、個人情報部分のハッシュ化や、CAST関数によるdatetime型のdate型への変換、DATE関数を用いた日時指定などもできます。
SELECT * FROM EXTERNAL_QUERY(
'mycurryapp.asia-northeast1.cloudsql-mycurryapp',
'''
SELECT id,
MD5(name) AS name,
MD5(email) AS email,
created_at,
CAST(created_at AS date) AS partition_date
FROM users
WHERE created_at BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE();
'''
);
3. Schedule Queryによるバッチ処理
さて、以上のように、BigQueryとCloudSQLを繋ぎ込むことで、
- CloudSQLに保存されているデータをリアルタイムに分析することが可能
- read onlyでセキュアにアクセス可能なため、購買データなどを扱う上で安全
という2つのメリットを享受することができるようになりました。
更に、BigQueryのSchedule Queryという機能を利用すると、決まった時間に決まったクエリを実行するように設定することができます。
しかも、クエリを実行して取得したデータは、BigQuery側のテーブルに保存することができるため、従来EmbulkのようなETLツールで実装していたMySQLからBigQueryへの転送処理など実装する必要はなくなるのです！
非エンジニアでもUI上でポチポチするだけでできちゃうので、CloudSQL Federation × Schedule Queryの組み合わせは本当に便利だと思います。
Schedule Query自体は以前からある機能ですが、この機会に、簡単に使用方法と触っていて気付いたことを下記していきます。
まず、Schedule Queryを使うには、BigQuery画面の左側にある「スケジュールされたクエリ」をクリックします。
Schedule Queryを初めて使用する場合は、「BigQuery Data Transfer API」を有効化する必要があります。
BigQuery Data Transfer APIを有効化すると、このような画面が現れるので、「＋スケジュールされたクエリを作成」をクリックします。
すると、新規クエリ作成画面が開くので、CloudSQL Federationの文法に従ってバッチ処理用のSQLを記述していきます。
その後、「クエリのスケジュール」→「スケジュールされたクエリを新規作成」と進みます。
続いて、Schedule Queryの新規作成画面が現れるので、良しなに入力し、「スケジュール」をクリックします。
ここで1点、触っていて気付いたこととしては、DATE関数を使って、1日前の00:00:00~23:59:59までのデータを取得するようなバッチ処理を記述する場合は、日本時間のAM9:00（＝標準時間のAM0:00）以降でスケジュールを組む必要があるようです。
CloudSQLもBigQueryも東京リージョンで使っていたのですが、実験したところ、CloudSQLのDATE関数が日本時間ではなく、標準時間の方で処理されるようでした。
-- 日本時間の2019-11-22 00:00:00 = 標準時間の2019-11-21 18:00:00にDATE関数を使用した場合
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-> 2019-11-21
-- 日本時間の2019-11-22 08:00:00 = 標準時間の2019-11-21 23:00:00にDATE関数を使用した場合
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-> 2019-11-21
-- 日本時間の2019-11-22 09:00:00 = 標準時間の2019-11-22 00:00:00にDATE関数を使用した場合
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-> 2019-11-22
設定完了後、確認すると、きちんとスケジュールされたクエリが出来上がっていることが分かります。
以上の簡単な操作によって、従来EmbulkのようなETLツールで実装していたバッチ処理をBigQuery上で完結させることができるようになりました！
まとめ・所感
- 今年発表されたBigQueryの新機能・CloudSQL Federationの使用方法についてまとめました。
- 用途としては、エンジニア向けというよりは非エンジニアが分析したい時に活用しやすいものかなと考えられ、使用するメリットとしては、下記3点が挙げられます。
- CloudSQLに保存されているデータをリアルタイムに分析することが可能
- read onlyでセキュアにアクセス可能なため、購買データなどを扱う上で安全
- Schedule Queryと組み合わせることで、Embulk等を使わずとも、非エンジニアでも簡便にBigQueryにデータを蓄積することができるようになる
- 実際に使用してますが、大変便利な機能です。ただデータを貯めれば良いなどの、シンプルな要件に対しては、特に問題なく利用できるのではないかと思います。
- 一方で、UI上での操作が多いため、設定ミスに気付きにくい・やり直しが面倒臭い・複数プロジェクトに横展しにくい、などの辛みもあり、重要な部分や使い回すような部分についてはTerraformなどでコード化して管理する方がベターだと思います。
- 近いうちに、Terraformでコード管理できるか実験してみようと思っているので、上手く行ったらまた記事にしたいと思います。