将 PostgreSQL 表格实时传输到 Google BigQuery

Question

我希望能够自动地将外部 PostgreSQL 数据库中的数据流式传输到我 GCP 帐户中的 Google Cloud Platform BigQuery 数据库。目前，我已经看到可以使用 EXTERNAL_QUERY() 函数查询外部数据库（MySQL 或 PostgreSQL），例如： https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries。

但是为了使其工作，数据库必须在GCP Cloud SQL中。我尝试查看从外部PostgreSQL流式传输到Cloud SQL PostgreSQL数据库的选项，但我只能找到有关一次性复制它的信息，而不是流式传输： https://cloud.google.com/sql/docs/mysql/replication/replication-from-external 我希望将其流式传输到BigQuery的原因是我正在使用Google Data Studio从外部PostgreSQL创建报告，这很好用，但是如果它来自Google BigQuery数据库，GDS只能接受SQL查询参数。例如，如果我们有一个包含100万条目的表，并且我们想要用户添加一个Google Data Studio参数，那么这将变成：

SELECT * from table WHERE id=@parameter;

这意味着查询将更快，并且不会达到Google Data Studio的100K记录限制。

创建一个连接外部PostgreSQL（只读访问）和Google BigQuery之间的最佳方法是什么，以便通过BigQuery查询时可以获得与查询外部PostgreSQL相同的实时结果？

- 719016

---

如果您正在寻找将流数据传输到BigQuery，我认为使用EXTERNAL_QUERY()不是正确的方法。与其计划将数据从PostgreSQL移动到CloudSQL，为什么不直接将数据流式传输到BigQuery呢？这并不是一件简单的事情，但是通过适当的Dataflow JOB，您可以做到。也许为了使它更简单，您可以每隔X个小时批处理一次。 - Alvaro

EXTERNAL_QUERY 的限制是什么？性能？ - 719016

性能将是最重要的，没错。但还有更多 - https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries#limitations。您需要实时数据吗？或者这不重要吗？您可以考虑构建一个管道来提取数据并加载到BQ中吗？ - Alvaro

Answer 1

也许您错过了谷歌云用户指南上提到的选项？

请参考此部分：https://cloud.google.com/sql/docs/mysql/replication/replication-from-external#setup-replication 请注意，该部分中提到：

“当您设置复制设置时，还可以决定 Cloud SQL 副本在完成初始导入后是否应与源数据库服务器保持同步。应保持同步的副本处于联机状态。只更新一次的副本处于脱机状态。”

我猜测您可能需要在线模式。

- Jacky Cheng

---

这并没有引起我的注意：我一定会尝试一下，看看效果如何！谢谢。 - 719016

不幸的是，那个链接是针对Mysql的，我找不到任何适用于PostgreSQL的解决方案。虽然有一种使用Cloudsql的方法，但不能用于Bigquery。 - Mehmet Kurtipek

Answer 2

您所需要的内容需要根据您的需求进行一些架构设计和编码。没有自动将您的PostgreSQL数据库与BigQuery同步的功能（除了具有一些限制的EXTERNAL_QUERY()功能-每个数据库1个连接-性能-总连接数等）。

如果您不需要实时数据，您可以使用Airflow等工具，创建一个DAG，每天连接到所有DB（例如使用KubernetesPodOperator），提取数据（从前一天开始），并将其加载到BQ中。这是一个典型的ETL过程，但在这种情况下更多的是EL(T)。如果您不能等待前一天的数据，则可以更频繁地运行此过程。

另一方面，如果您需要流式传输，那么我可以考虑使用Dataflow作业。我想您可以使用JDBC连接器进行连接。

此外，根据您的管道结构如何，如果在同时写入您的PostgreSQL DB时，也将数据流式传输到BigQuery中，可能会更容易实现（但难以维护）。

- Alvaro

Answer 3

不确定你是否已经尝试过，但是如果你添加一个基于维度的下拉筛选器，而不是添加一个参数，Data Studio将以以下形式将其推送到底层的Postgres db：

SELECT * from table WHERE id=$filter_value;

这样做可以实现你想要的相同结果，而不需要通过BigQuery。

- Minhaz Kazi