Cloud SQLとBigQueryのデータ同期サービス化

この記事はenechain Advent Calendar 2024の13日目の記事です。 (English version follows)

はじめに

こんにちは、enechainのデータプラットフォームデスクで主にプラットフォーム開発を担当しているデータエンジニアのakizhouです。

このブログでは、Cloud SQL (PostgreSQL) からBigQueryに分析目的でデータを同期するためのパイプライン (DB Pipeline) についてご紹介します。

Cloud SQLからBigQueryへのデータ同期はCloud SQL federated queriesを使っており一般的な方法であるため詳細には深く踏み込みません。代わりにパイプラインを設計するにあたり検討した重要なポイントについてお話ししたいと思います。単純なfederated queriesの利用ではなく、データ同期パイプラインをサービスとして提供しようと考えている方々に役立つ知見を提供できればと思います。

アーキテクチャと設計上の考慮点

DB Pipelineのアーキテクチャは、信頼性とスケーラビリティを確保するために、あえてシンプルにしています。基本的には、Argo Workflows上で連携クエリをCron Workflowsとして実行しています。社内ですでに採用されているワークフローエンジンであるArgo Workflowsを活用することで、既存のインフラストラクチャとシームレスに統合しています。

またDB Pipelineを構築する際の目標は、enechainの各チームがこのパイプラインをサービスとして利用できるようにし、データプラットフォームチームへの依存を最小限に抑えることでした。この目標を達成するために、設計時にデータセキュリティと使いやすさの2つを優先事項としました。

セキュリティ

enechainでは、各プロダクトが独自のGoogle Cloudプロジェクトを持ち、それに専用のCloud SQLインスタンスが紐付いています。この構成により、DB Pipelineは複数のCloud SQLインスタンスにアクセスし、複数のBigQuery connectionを管理する必要があります。

最も簡単なアプローチとしては、すべてのBigQuery connectionにアクセスできる単一のGoogleサービスアカウント（GSA）を使用することでした。しかし、この方法では1つのCloud SQLインスタンスのデータが他からもアクセス可能となり、機密データを扱うプロダクトにとって懸念となります。

この問題を解決するため、各Cloud SQLインスタンスに専用のGSAを割り当てることにしました。これにより、Google Cloudプロジェクト単位でデータへのアクセスが限定されるようになります。また、同様に各GSAに紐づけられたKubernetesサービスアカウント（KSA）も作成することで実行レベルでのインスタンス間アクセスを防ぎ、各Cloud SQLインスタンスの分離とセキュリティを維持するようにしています。

使いやすさ

enechainでは、データベースとしてPostgreSQLの利用を標準化していますが、スキーマやテーブル設計に関する厳密なルールは設けていません。そのため、DB Pipelineにはさまざまなスキーマ設計やテーブル要件に柔軟に対応できる設計が求められました。
加えてPostgreSQLとBigQueryのデータ階層構造の違いも考慮する必要がありました。PostgreSQLはデータベース、スキーマ、テーブルの3層構造を採用していますが、BigQueryではデータセットとテーブルの2層構造しかありません。この違いを埋めるために、DB Pipelineではスキーマごとでデータセットを分けて作成し、スキーマごとに設定を適用できるようにしています。
また基本的にinformation schemaを通じて自動的にスキーマ内のテーブルを検出するようにして、場合によってはinclusion, exclusionを指定できるようにすることによって次のようなことを達成しています。

• 開発者が同期対象のテーブルを手動でリスト化する手間が省ける
• テーブルの増減時、連携処理側が自動で追従できる
• テーブルごとに異なる同期頻度や条件を柔軟に設定できる
• スキーマ単位で統一的な管理が可能になり、設定ファイルの簡潔化が図れる

設定ファイルの一例

## Required
bq_project_id: some_project
bq_connection_id: some_id
bq_dataset_name: dataplatform_db1_schema1
service_name: some_service
db_catalog_name: db1
db_schema_name: schema1

## Optional
## Table configuration
target_tables:
- table1
excluded_tables:
- table2
## Examples:
## - Set only target_tables: Sync only the listed tables
## - Set only excluded_tables: Sync all tables except the excluded ones
## - Set neither: Sync all tables (default and recommended)
## - Set both: Raise an error

振り返りと課題

DB Pipelineを開発する中で、多くの試行錯誤を重ねてきました。当初の目標を達成することには成功しましたが、より良いアプローチを取れた部分もあり、システムの進化に伴って直面している課題も存在します。ここでは、振り返りとして改善の余地があった点と、現在直面している課題について共有します。

振り返り

1. テーブル階層の違いへの対応
   前述の通りPostgreSQLとBigQueryのデータ階層構造には違いがあります。当初、ほとんどのチームがスキーマを分けてテーブルを管理していないことを理由に、BigQueryでは異なるスキーマのデータを同じデータセットに連携していました。しかし、プロジェクトが増えるにつれてスキーマ設計が多様化し、この仮定が成り立たなくなりました。その結果、データ移行や構造調整の必要が発生してしまったため、早い段階で対応していれば回避できたコストが発生しました。

2. Terraformモジュール設計の簡素化
   開発者がDB Pipelineを簡単に導入できるようにするため、パイプラインの内部アーキテクチャを抽象化し、各Google Cloudプロジェクトで必要なリソースを作成するTerraformモジュールを定義しました。しかし、このモジュールに多くの機能を詰め込みすぎた結果、一部の機能が既存のTerraformモジュールと重複し、混乱を招きました。より最小限なモジュール設計にしていれば、複雑さを軽減し、モジュールを移行する手間も避けられたと思います。

3. メタデータ改善
   各同期ジョブにかかる時間などの主要なメトリクスをパイプライン自体のヘルスチェック用に取得しています。一部のサービスではデータベースやスキーマ名にサービス名が含まれていないため、同期ジョブの失敗がどのサービスに影響を与えているかを特定するのが困難でした。同期ジョブに所属するサービスのメタデータを最初から含めていれば、トラブルシューティングの時間を大幅に短縮できたと思います。

課題

1. データのサイロ化の管理
   開発チームがデータのownershipを持てるように、Cloud SQLインスタンスが存在するGoogle Cloudプロジェクト内のBigQueryデータセットにデータを同期することにしました。このアプローチにより、各チームが独自にデータセットの読み取り権限を制御できるようになり各々で管理する形になりますが、致し方ないとはいえ結果としてデータがサイロ化されます。また参照頻度が高いテーブルでは個別にアクセス許可を付与する必要があり、データ共有が煩雑になります。この課題については現在プロジェクト間でのデータアクセスを効率化しつつ、チームレベルの所有権を維持する方法を模索しており、今後のブログで共有したいと考えています。

2. 完全なセルフサービス化に向けて
   DB Pipelineを完全にセルフサービスにするには、Terraformリソースの作成とワークフローコード生成を社内のプロジェクト初期化ツールに統合する必要があります。ただし、これを効果的に実現するには、プロジェクト管理をモノレポアプローチに移行する必要があり、多くの既存プロジェクトやワークフローに影響を与える可能性があり、難しい課題として残っています。

3. 開発者による同期ジョブの再実行の許可
   現在Argo WorkflowsのRBACの仕組み上、開発者がUI上から各自の同期ジョブを再実行できないようになっています。これに対処するには、custom roleを使用した解決策を実装する必要がありますが同時に運用の負担が増加します。この領域でのセキュリティと使いやすさのバランスを保つことは引き続き課題となっています。

結論

DB Pipelineは、enechainの各チームがデータを安全かつ効率的に同期できるようにしながら、既存のインフラストラクチャを活用することで、車輪の再発明を回避する役割を果たしています。現状まだ完全なセルフサービス化には至っておらず、一部のプロセスでデータプラットフォームデスクの関与が引き続き必要ですが、このパイプラインの導入によってBigQueryの参照率は大幅に向上しました。

データプラットフォームデスクでは今後もDB Pipelineの改善に注力し、使いやすさを高め、ボトルネックを減らしていきます。このような継続的な取り組みが各チームのデータ活用をさらに促進し、enechainのさらなる成長を支えると確信しています。

tech.enechain.com

herp.careers

---

Introduction

Hi, I’m akizhou, a data engineer on the Data Platform Desk at enechain primarily focused on platform development.

In this blog, I’d like to share some insights about one of our pipelines that synchronizes data from Cloud SQL (PostgreSQL) to BigQuery for analytical purposes.

Since synchronizing data between Cloud SQL and BigQuery is a common task, I won’t dive into the nitty-gritties of how it’s done. Under the hood, we rely on Cloud SQL federated queries. Instead, I’ll focus on the key considerations our team had in mind when designing this pipeline, for those looking to go beyond federated queries and offer a scalable data sync pipeline as a service.

Architecture and Design considerations

The architecture for this pipeline is intentionally kept simple to ensure reliability and scalability. At its core, it executes federated queries on Argo Workflows, specifically using Cron Workflows. By leveraging a workflow engine already adopted company-wide (Argo Workflows), we avoid reinventing the wheel and seamlessly integrate with existing infrastructure.

Our objective up on the creation of this pipeline is that teams at enechain could use it as a service, minimizing dependency on the Data Platform team and avoiding bottlenecks. To achieve this, we focused on two key priorities: data security and ease of use.

Security

At enechain, each product has its own Google Cloud project, along with a dedicated Cloud SQL instance. This setup means our pipeline needs access to multiple Cloud SQL instances and requires managing many BigQuery connections.

The simplest approach would have been to use a single Google Service Account (GSA) with access to all BigQuery connections. However, this would allow data from one Cloud SQL instance to be accessed from others, which is a concern for products handling sensitive data.

To address this, we decided to use separate GSAs for each Cloud SQL instance. This ensures that access is restricted to the specific data associated with each instance. Similarly, we created a dedicated Kubernetes Service Account (KSA) for each GSA. This prevents cross-instance access from the execution side and maintains isolation and security for each Cloud SQL instance.

Ease of use

At enechain, PostgreSQL is the standard choice for databases, but there are no strict rules regarding schema or table design. As a result, the pipeline needed to be designed to flexibly accommodate various schema designs and table requirements.
Additionally, we had to address the differences in data hierarchy between PostgreSQL and BigQuery. PostgreSQL uses a three-layer hierarchy consisting of databases, schemas, and tables, whereas BigQuery has only a two-layer structure with datasets and tables. To bridge this gap, the DB Pipeline creates separate datasets for each schema and allows configurations to be applied at the schema level.
Tables within a schema are automatically detected from the information schema and inclusion or exclusion can be specified when necessary, so the followings are achieved.

• Developers don't need to manually list the tables to be synced
• Pipeline automatically adapts to table additions or removals within the schema
• Allows flexible synchronization conditions and frequencies for individual tables
• Enables unified schema-level management, simplifying configuration files

An example configuration file:

## Required
bq_project_id: some_project
bq_connection_id: some_id
bq_dataset_name: dataplatform_db1_schema1
service_name: some_service
db_catalog_name: db1
db_schema_name: schema1

## Optional
## Table configuration
target_tables:
- table1
excluded_tables:
- table2
## Examples:
## - Set only target_tables: Sync only the listed tables
## - Set only excluded_tables: Sync all tables except the excluded ones
## - Set neither: Sync all tables (default and recommended)
## - Set both: Raise an error

Reflection and Challenges

Developing this pipeline took many iteration and while it has been successful in meeting our objective, there are areas where we could have taken a better approach and challenges we continue to face as we evolve the system. Here, I’d like to reflect on what we wish we had done differently and the ongoing challenges we are addressing.

Reflection

1. Handling Differences in Table Hierarchies
   As mentioned previously, PostgreSQL and BigQuery has different data hierarchy. Initially, we overlooked this discrepancy, based on the observation that most teams were not extensively using schemas to separate tables and concluded that a two-layer hierarchy would suffice. However, as more projects were introduced and schema designs became more diverse, this assumption no longer held true. To combat this we had to do some data migration and structural adjustments which was time consuming. Addressing this early on would have simplified future modifications and reduced the complexity of scaling.

2. Simplifying Terraform Module Design
   To make the pipeline easier to integrate for developers, we defined a Terraform module that abstracts the pipeline’s internal architecture and handles the creation of necessary resources in each Google Cloud project. While this approach made it easier for teams to get started, we tried to pack too many features into the module. Some of these features overlapped with functionalities provided by dedicated Terraform modules, which caused confusion. A more focused and modular design would have reduced this complexity and avoided redundancy and need for module version migration.

3. Enhancing Meta data
   We obtain metrics such as time taken for each sync job for pipeline health checks.　However, some services names are not recognizable from its database or schema names, making it difficult to trace sync job failures back to the affected service. It would have saved significant time during troubleshooting if the meta data was given from the beginning.

Challenges

1. Managing Data Silos
   To give development teams ownership over their data, we decided to sync data to BigQuery datasets within the same Google Cloud project as their corresponding Cloud SQL instance.. This approach allows teams to define read access for their datasets independently, but it also results in siloed data. Popularly referenced tables require read permissions to be granted to individual personnel across projects, making data sharing more tedious. We are exploring ways to streamline data access across projects while maintaining team-level ownership and hopefully talk about it more in a future blog.

2. Towards a More Self-Service Model
   Making the pipeline fully self-service would require further integrations, such as combining Terraform resource creation and workflow code generation into our internal project initialization scripts. However, effectively achieving this would require moving to a monorepo approach for managing projects. This shift could impact many existing projects and workflows, making it a complex decision to implement.

3. Allowing Developers to Rerun Sync Jobs
   Currently, developers cannot rerun their own sync jobs due to limitations in Argo Workflows’ RBAC. Implementing a solution with custom roles is necessary to address this limitation, but it adds operational overhead. Finding a balance between security and usability in this context remains an ongoing challenge.

Conclusion

This pipeline has been instrumental in enabling teams at enechain to synchronize data securely and efficiently, while reusing existing infrastructure to avoid reinventing the wheel. Although it is not yet fully self-service, and some processes still require the Data Platform team’s involvement, it has significantly increased BigQuery adoption across the organization.

As we continue refining the pipeline, our focus remains on improving usability and reducing bottlenecks. This is an ongoing journey, but we’re confident that these efforts will further empower teams to harness the full potential of their data and drive enechain forward.

tech.enechain.com

herp.careers