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の方に問い合わせて確認したところ、クラウドのリージョンとは異なる「法的な管轄区域」のような意味合いとのことでした。国に近い概念ではあるが、微妙な違いがあるとのこと。

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