Just import new data from Cloud SQL into BigQuery?

0
votes

My situation is:
I want to schedule regular data updates from Cloud SQL into BigQuery. The data of the table on Cloud SQL is updated regularly and can edit the old data in that table. There are about 20 columns in this table.

When it comes to the update schedule I want to sync between Tables in Cloud SQL and BigQuery.
How can I add new data, update recently edited data and delete data that no longer exist in CLoud SQL in BigQuery?

Currently I use the way to override this table every time I go to the update schedule.
I still do not have a really good solution to save data when querying.

1
mysqlgoogle-bigquerygoogle-cloud-sql

1 Answers

1
votes

For this what you can do is to set Cloud SQL as an external Data Source on Big Query.

This way the data will keep updated automatically in Big Query as it resides in cloud SQL. This will actually lower your billing amounts as data won't be duplicated however the queries done to an external data source are a bit slower than when data resides in BigQuery.

To do this you can follow the guide here and I'm coping the general steps on this post.

  • Enable the BigQuery Connection API here
  • Make sure your Cloud SQL instance has a public IP as BigQuery external Sources only support public IP connectivity.
  • Go to: https://console.cloud.google.com/bigquery
  • Click on +ADD DATA and select External Data Source (this will pop-up a menu on the right side of the window)
  • Select the CloudSQL type (MySQL or PostgreSQL)
  • Create a connection ID for this connection
  • Select Connection Location, Friendly Name and description
  • On Cloud SQL instance ID copy the full name of your cloud SQL instance the one with the form project-id:location-id:instance-id
  • On Database Name, username and password ingress the login data of your cloudSQL database
  • Click on create connection

This will link the data in your cloud SQL instance to Big Query so you can use the BigQuery motor to analyze data in your cloud SQL instance without needing to copy data to BigQuery and making sure it is always updayted.