How to replicate a bigquery table into google cloud postgres?

Question

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?

Sunandini Ravada · Accepted Answer · 2021-05-04 06:34:28Z

1

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:

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
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

answered May 4, 2021 at 6:34

Sunandini Ravada

1424 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to replicate a bigquery table into google cloud postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related