エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

BigQueryの連携クエリ導入時にハマった制約と回避策

エムスリーエンジニアリンググループの遠藤 (@en_ken) です。

エムスリーではクラウドプロバイダとしてAWSが主要に利用されていますが、 分析基盤はGoogle CloudのBigQueryを利用しており、各チームが分析に必要なデータを集約しています。 歴史的な経緯もあり、各チームのデータ (国外に置いても問題ないもの) は米国のリージョンのデータセットに配置しています。 連携クエリを利用しようとした際、この環境にまつわる問題に引っかかったので紹介します。

連携クエリ (Federated queries)

連携クエリとはなんぞやを簡単に紹介すると、BigQueryの外部にあるDBのデータをBigQuery上のデータであるかのように扱える機能です。ドキュメントはこちら。つまり、アプリケーションのDB上にあるデータをBigQueryに同期しなくともJOINできる (!) ということですね。めっちゃ便利。

2022/09時点では、データソースとしてCloud Spanner/Cloud SQLをサポートしています。 私が所属するBIRというチームはエムスリー全体からみると技術スタックが少し特殊なチームで、特に近年立ち上げたサービスはGoogle Cloud上で構築されていることが多く、データベースとしてCloudSQLやCloud Spannerを利用しています。 こりゃ使うっきゃないね!ということで導入を試みました。

前提条件とハマった制約

Cloud SQLを利用しているアプリケーションに対して導入する場合、以下の図のような構成になります。 BigQuery上にCloud SQLに対する外部接続を定義し、その外部接続を参照するビューを作る必要があります。

実現したい構成
アプリケーションのDBは東京リージョン (asia-northeast-1) のプロジェクト (プロジェクトAとする) に配置されています。 一方で、前述したとおり、利用するBigQueryのデータセット群は別のプロジェクト (プロジェクトBとする) の米国のリージョン (US) に存在しており、ビューはそちらに作成する必要がありました。 その結果、構築時には様々な制約を踏み抜くことになりました。

制約1. 外部接続はデータソースと同一プロジェクトに存在する必要がある

外部接続もBigQueryの定義であるため、当初は上図のようにプロジェクトB側に作成しようとしましたが、これはできません。実際に作成しようとすると以下のようなエラーが出ます。

異なるプロジェクトで作成しようとした際のエラー

外部接続の定義はCloud SQLのDBがあるプロジェクトA内で作成する必要があります。

制約2. ビューは参照先が同一リージョンに存在する必要がある

外部接続をプロジェクトA側に作る必要があるので、自然な流れで上記のようにDBと同じ東京リージョンに外部接続を定義しました。 ところが、これだとビューの作成に問題が発生します。

ビューの制限に記載がある通り、ビューの参照先は同一リージョンである必要があります。 したがって、外部接続を東京リージョンに構築すると、米国のリージョンにあるデータセットには外部接続を参照するビューが作成できません。

制約3. 外部接続はデータソースと同一リージョンに存在する必要がある

先のビューの制限を回避するためには、上記のように外部接続を米国のリージョンに作成する必要があるわけですが、これは実現できません (泣) 。 米国のリージョンに東京リージョンのDBへの外部接続を作成しようとすると以下のようなエラーが表示されます。

米国のリージョンに作成しようとした際のエラー

この「管轄 (Jurisdiction) 」という概念はGoogle Cloudのドキュメントを検索しても定義が見つからなかったのでGoogle Cloudの方に問い合わせて確認したところ、クラウドのリージョンとは異なる「法的な管轄区域」のような意味合いとのことでした。国に近い概念ではあるが、微妙な違いがあるとのこと。

じゃあ、大阪リージョンになら構築できるのかなと思うかもしれませんが、やってみると以下のエラーが出ます。

大阪リージョンに作成しようとした際のエラー

ということで、東京リージョンのDBへの外部接続は東京リージョンに構築する必要があります。

回避策

これまでの図で紹介された3つの構成要素しかない場合、ある制約の回避が別の制約にひっかかってしまい、全てを満たすことはできません。 そこで、これらの制約をすべて回避するにはどうするかというと、以下のようにCloudSQLのリードレプリカを追加します。

リードレプリカをUSのリージョンに配置してやり、レプリカを外部データソースとして外部接続を作ると、外部データソース・外部接続・ビューは同一リージョンに構成されることになるため制約を回避でき、無事構成できました!

まとめ

リージョンをまたいでBigQueryの連携クエリを利用する際の制約とその回避策を紹介しました。 考えてみるととても単純な話なのですが、結構ハマってしまって時間を溶かしました。連携クエリに関わらず、リージョンまたぎの構成は制約を受けやすいので避けられるなら避けたいですね。

ただし、今回紹介した外部接続をレプリカに対して構築する構成自体は、「分析用のクエリをオフロードしてアプリケーションの本番DBに影響を与えない」という効果も期待できるので、本問題とは関係なく一考に値すると思います。

We are hiring!!

弊社ではAWSもGoogle Cloudも使いこなしたいというエンジニアを募集してます! 以下のURLからカジュアル面談にぜひご応募ください!

jobs.m3.com