Hi all,
We have Postgres database ( Amazon hosted) and there is one view ( 10 colums) I need to transfer to Big Query on daily basis. What would be the best solution for this? It must be automatic.
Can I create ODBC connection and somehow link the servers?
Im very confused from GCP, so mamy services and functions and I couldnt find any simple solution for my issue.
Thank you in advance for your answers!
Unfortunately, direct ODBC connection between your PostgreSQL instance (hosted on AWS) and BigQuery isn't currently supported. However, there are several effective approaches:
Datastream: This is ideal for near real-time replication of entire databases, but might be a bit heavy-duty for just a single view.
Custom ETL Pipelines: This gives you complete control using tools like Cloud Composer (Apache Airflow) or Dataflow (Apache Beam). However, it requires more technical setup and maintenance.
Third-Party Tools: Services like Fivetran or Stitch specialize in data integration and could simplify the process, but often come with a cost.
My Recommendation: Dataflow + Cloud Scheduler + Pub/Sub (Google Cloud)
This approach is well-suited for your needs, as it leverages the power and flexibility of Google Cloud Platform:
How It Works
thank you for your answer!