Export data from Google Cloud SQL, and append to BigQuery

Question

We have production databases (postgresql and mysql) on Cloud SQL.

How could I export the data from the production databases, and then append to BigQuery datasets?

I DO NOT want to sync or replicate the data into BigQuery because we purge (after backing up) the production databases on regular basis.

The only method I could think of is:

Export to CSV and then drop into Google Cloud Storage
Python scrip to append into BigQuery.

Are there any other more optimal ways?

Mani Gandham · Accepted Answer · 2022-11-13 09:02:09Z

3

BigQuery supports external data sources, specifically federated queries which allow you to read data directly from a Cloud SQL instance.

You can use this feature to select from all the relevant tables in your Postgres/MySQL instances and copy them into BigQuery without any extra ETL process. You can append the data to your existing tables, create a new table every time, or use some other organization that works for you.

BigQuery also supports scheduled queries so you can automate this.

The actual SQL will depend on your data sources but it's not much more than...

INSERT INTO `your_bq_table`
SELECT * 
FROM `external.postgres123.tablename`

edited Nov 13, 2022 at 9:02

answered Nov 9, 2022 at 10:30

Mani Gandham

8,4132 gold badges54 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Export data from Google Cloud SQL, and append to BigQuery

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