BigQueryからのCloudSQL参照

※本記事は、2022年5月30日に公開済みの記事を移行して再掲載したものです。

はじめに

データ基盤チームでチームリーダーをしている福田です。
データ基盤の構築・運用や、ビジネス側との接続を担当しています。
今回はフェズの中核となるビッグデータを扱うBigQueryの活用事例についてご紹介します。

何をしたいか

フェズでは、データ基盤の中心にBigQueryを利用しています。
理由としてはBigQueryのハイパフォーマンスな性能を利用するためなのですが、
そのため各種データはBigQueryに集まることとなります。

膨大なデータ（いわゆるビッグデータ）を扱うときは、BigQueryは非常に優秀なサービスですが、万能なわけではなく苦手とする処理もあります。
いくつか代表的なものをあげると

• 従量課金なため、やみくもに利用すると高額になってしまう
• 更新や削除などが得意ではなく、コスト高となる
• インデックスが貼れない
  • ※一部リージョンでインデックス機能のプレビューが始まっているため、近いうちに日本でも利用できるようになるかもしれません。

などがあります。
今回マスタデータを更新・削除を行いやすいCloud SQLにて集中管理し、BigQueryと連動して利用できる構成をとったので、事例紹介となります。

BigQueryの外部データソースクエリ

BigQueryの外部データソースクエリという機能を使って実現しています。
（公式のドキュメントはこちら ）

今回ご紹介するCloud SQL以外にも、Cloud SpannerやCloud Storage, Googleドライブからも同様に外部データソースとして参照することができます。

BigQueryとCloud SQLでの設定方法

BigQueryとCloud SQLの設定は、かなり簡単でいくつかのステップで設定することができます。
（公式の手順はこちら）

1. BigQuery Connection APIを有効化

2. BigQUeryの外部データソースにて接続リソースの作成

   

3. クエリやビューにて接続リソースを参照

   1. 呼び出しの参考クエリ

   SELECT *
   FROM EXTERNAL_QUERY(
       "projects/xxxxxx/locations/asia-northeast1/connections/yyyyy",
       "SELECT * FROM hogehoge.table;" );
   

※実際にはterraformにてコード管理しています。（以下は部分的に抜粋）

resource "google_bigquery_connection" "xxxx" {
  provider = google-beta

  connection_id = "cloudsql_xxxx"
  description   = "connect to Cloud SQL (${google_sql_database_instance.xxxx.name})"
  friendly_name = "Cloud SQL (${google_sql_database_instance.xxxx.name})"
  location      = "asia-northeast1"
  project       = local.project

  cloud_sql {
    database    = google_sql_database.xxxx.name
    instance_id = google_sql_database_instance.xxxx.connection_name
    type        = "MYSQL"
    credential {
      password = google_sql_user.xxxx_bq_reader.password
      username = google_sql_user.xxxx_bq_reader.name
    }
  }
}

注意事項

いくつか注意事項もあるので記載しておきます。

• 権限について
  • 通常のBigQueryデータ閲覧ロールなどとは別にBigQuery Connection Userというロールを持っておく必要があります。
  • 編集者など広いロールの場合は内包している可能性があります。
• カラム型について
  • 異なるデータソースを参照するため型の変換が入ります。
  • 詳しくは公式ドキュメントを参照してください。
• パフォーマンスについて
  • 公式ドキュメントでも触れられてますが、Cloud SQLを含む外部データソース参照は、通常のBigQuery（正確にはBigQueryストレージ）よりも高速ではない可能性があります。
  • 今回のようなマスタなどでデータ件数がそこまで多くない場合気になるほどではないようです。

ビューを介してのBigQueryでの参照例

今回の構成のもととなったデータでは、定常的にBigQueryからも参照されます。
そのため毎回FROM句でEXTERNAL_QUERYを書くのは扱いづらいため、Viewを貼っておくことで
通常のテーブルと同様の使い勝手を担保しています。
利用時は以下のようになります。

SELECT *
FROM [dataset].table1
  JOIN [dataset .[external_query_view] AS ex_view
    ON table1.key = ex_view.key

実態は

WITH ex_view AS (
  SELECT *
  FROM EXTERNAL_QUERY(
    "projects/xxxxxx/locations/asia-northeast1/connections/yyyyy",
    "SELECT * FROM hogehoge.table;" );
)
SELECT *
FROM [dataset].table1
  JOIN ex_view
    ON table1.key = ex_view.key

のような形になるのですが、クエリは上のケースのほうがシンプルに扱えることがわかると思います。

まとめ

BigQueryを中心により柔軟なデータ利用を行うための取り組みでした。
今後はGCP内で構築されたプロダクトとCloud SQLで直接参照しつつ、BigQuery側と連動して集計などができる構造に発展させていく見込みです。
これまで以上に様々なデータが集まってきておりデータ基盤のスケールを進めています。
採用も活発化していくので興味のある方はぜひお声がけください。