How to import a SQL Dump File from Google Cloud Storage into Cloud SQL as a Daily Job?

1
votes

I'm trying to import a SQL dump file from Google Cloud Storage into Cloud SQL (Postgres database) as a daily job.

I saw on Google Documentation for the CloudAPI that there was a way to programmatically import a SQL dump file (URL: https://cloud.google.com/sql/docs/postgres/admin-api/v1beta4/instances/import#examples), but quite honestly, I'm a bit lost here. I haven't programmed using APIs before, and I think this is a major factor here.

In the documentation, I see that there's an area for a HTTP POST request, as well as code, but I'm not sure where this would go. Ideally, I'd like to use other Cloud products to make this daily job happen. Any help would be much appreciated.

(Side note: I was looking into creating a cron job in Compute Engine for this, but I'm worried about ease of maintenance, especially since I have other jobs I want to build that are dependent on this one.

I'd read that Dataflow could help with this, but I haven't seen anything (tutorials) that suggests it can yet. I'm also fairly new to Dataflow, so that could be a factor as well. )

1
google-cloud-platformgoogle-cloud-sql
I would suggest using google-cloud-composer which is essentially airflow for this. There are a lot of Operators to move files between various locations. You can find more information here: cloud.google.com/composer/docs/quickstart – Gaurav Taneja
@GauravTaneja Thank you for your suggestion! I'd been spending the past few hours reading and learning more about Cloud Composer & Airflow. Would you suggest using the BashOperator to execute the command to load a SQL dump file from GCS into CloudSQL, or did you have something else in mind? – truckbot
Ideal way would be to create an operator, there is a irflow.contrib.operators.mysql_to_gcs.MySqlToGoogleCloudStorageOperator but am not sure if that would help. If MySqlHook works then I would use that and GoogleCloudStorageHook and create a custom operator or use them with PythonOperator. That being said BashOperator should also work but I have not tried it. – Gaurav Taneja
@GauravTaneja Got it. Thank you very much - I will look into this. – truckbot
@GauravTaneja can you post the comment as an answer? Thank you. – komarkovich

1 Answers

2
votes

I would suggest using google-cloud-composer which is essentially airflow for this. There are a lot of Operators to move files between various locations. You can find more information here I must warn though, that it is still in Beta and unlike google's expected beta this one is rather flaky (at least in my experience)