見出し画像

BigQuery Scriptingを利用してCloudSQLのテーブルを転送する仕組みを作った話

PharmaX Blog

※「株式会社YOJO Technologies」から「PharmaX株式会社」へ社名変更いたしました。この記事は社名変更前にリリースしたものになります。

こんにちは。YOJOのBIチームでデータエンジニアをしている古家（@enzerubank）です。

先日5/31に社内の開発チームで第一回LT大会でBigQuery Scriptingを活用したデータパイプラインについて話しましたので紹介します！

他の発表内容については別途、LT大会についての記事を書ければと思っています。

またYOJOのデータ基盤の全体像に関しては以下の記事で紹介しておりますので、合わせてお読みいただけると嬉しいです。

どんな仕組みか

BigQuery ScriptingはSQLでPythonのようにスクリプトを書くことができる機能です。

今回はCloudSQL for PostgreSQLのアプリケーションのテーブルをINFORMATION_SCHEMAから全件取得してBigQueryに毎日保存するバッチ処理を実装してみました。

なぜ作ろうと思ったのか

今まではOSS版のAirbyteをGCE上にインストールしてHerokuから転送するために使っていましたが、以下の理由から移行を検討していました。

  • 毎月2-3万円のサーバーコストがかかっていたので、CloudSQLに移行することで削減したい

  • CloudSQLをプライベートネットワーク内に置くことでセキュアにしたい

  • CloudSQLのリードレプリカをBigQueryは参照することで、本番DBへの負荷を減らしたい

  • Airbyteは元テーブルのカラムが変更されると、都度スキーマ更新をGUIで行ってから再取得が必要で、アプリケーションエンジニアとの連携が面倒

BigQuery Scriptingとは

2019年にリリースされたBigQueryで複数のステートメントを書けるようになった機能です。

今のドキュメントでは手続き型言語と記載されています。特に機能の有効化は不要で、BigQueryのエディタで直接書き始めることができます。

CloudSQLとは

GCPが提供するフルマネージドなリレーショナルデータベースで、MySQL・PostgreSQL・SQL Serverを提供しています。

プライベートネットワークで構築することができ、セキュリティ担保やスケールしやすいメリットがあります。

今回は本番DBの負荷を減らすために、BigQuery用のリードレプリカを用意して使うようにしました。

Cloud SQL federated queries

BigQueryからCloud SQLへ直接EXTERNAL_QUERY内に記述したクエリを実行できる機能で、利用には接続設定の作成が必要です。

この接続設定はCloudSQLと同じリージョンに作成する必要があります。

BigQueryはデフォルトでUSのマルチリージョンになりますが、リージョンを跨ぐクエリを書くことができないので、CloudSQLはUSのどこかに置くなどしてリージョンを合わせることが必要です。

BigQueryをasia-northeastに統一することも検討しましたが、漏れているテーブルが合った場合に参照できなくなるリスクは取りたくなかったので、CloudSQLのリードレプリカのみus-west1に置くことで対応しました。

For文

For…in 文を利用しました。SELECTのサブクエリから取得した一覧をDOの処理で一件ずつ実行するような形になっています。

CREATE OR REPLACE TABLE と EXECUTE IMMEDIATE

「CREATE OR REPLACE TABLE」は、テーブルが存在しない場合は作成、存在する場合は置き換えができる関数です。

変数を動的に埋め込みたい場合は、文字列と変数を結合させて、EXECUTE IMMEDIATEを使います。

スケジュールクエリで設定

書いたクエリをスケジュールクエリに毎日実行するように設定します。

GUIだと個人のGoogleアカウントに紐付いた設定になってしまい、その人がいなくなった時にパイプラインが動かなくなるので、注意が必要です。

Pythonなどを使ってサービスアカウントでスケジュールクエリを設定する方法を取るのが良いでしょう。

振り返り：メリット

CloudSQLとBigQueryを連携するには、他にCloud Storageへデータを出力してBigQueryにロードするバッチを実装したり、Dataflowでパイプラインを実装するような方法があります。

今回はデータ抽出はCloud SQL federated queriesを利用し、実行環境はスケジュールクエリにしました。結果、以下のようなメリットがありました。

  • 実行環境がBigQueryだけで完結するのでコストが削減できる（他の方法だとCloud Composerなどが必要）

  • 実装がSQLだけで済むので工数も少ない

振り返り：デメリット

  • SQLがGit管理できず、チームメンバーが増えた時に品質管理できない

スケジュールクエリ上に直接SQLを書くことになるので、Git管理することができません。問題のあるSQLを書いていたとしても、誰も気づくことができないので、チームメンバーが増えた時に品質面を管理することができなくなります。

さいごに

今回はBigQuery Scripting + Cloud SQL federated queries + スケジュールクエリを使ってデータタイプラインを組んでみました。

デメリットとして挙げたGit管理できない点に関しては、対応した方法があるので、また別途記事にてご紹介したいと思います。

弊社では患者の健康にコミットすることで、どんな些細なことでも真っ先に相談されるかかりつけオンライン薬局を目指しています。
患者に合ったパーソナルなケアを提供可能な体制の整備をデータの側面から支えてくれるメンバーを募集しています。
少しでも興味のある方、ぜひお話ししましょう！


この記事が気に入ったらサポートをしてみませんか？