CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-sql
user2491463
user2491463

Reputation: 512

How to replicate a bigquery table into google cloud postgres?

I have a table in bigquery that I want to continuously replicate into a google cloud postgres table and be able to query it from the postgres instance. What is the easiest way to do this? I am hoping to create a cronjob to run a query to create the table in postgres

Something like....

CREATE OR REPLACE TABLE postgres_server.table123 as select * from bigquery.table123;

Is this possible?

Upvotes: 0

Views: 695

Answers (1)

Sunandini Ravada
Sunandini Ravada

Reputation: 142

I don't think there is a direct way to do this replicate between Bigquery and Cloud SQL.

To copy data from Big query to Cloud SQL one option I could think of is Export/Import:

  1. First export the data in CSV format in GCS and then import the same in cloud sql. Also because of the export limitation in BQ, for tables larger than 1 GB we need to export to multiple files into GCS bucket
  2. Import Operation into Cloud SQL, we can use psql copy command

Sample Copy Command which we can run from VM(VM instance to have access to GCS bucket Source and Target Post Gres): gsutil cat gs://<bucket_name>/<csv_filename> | psql -h <CLOUDSQL_IP> -d -U -c "\copy from stdin with (format csv)"

[1] https://cloud.google.com/bigquery/docs/exporting-data [2] https://www.postgresql.org/docs/13/sql-copy.html

Upvotes: 1

Related Questions