Use `gcloud sql export csv ... ` for multiple calls at the same time

[Harpal Khing]

So I've built my ETL pipeline to move 5 tables from my cloud-sql server to Google Cloud Storage as .csv files at the same time.

However my pipeline fails because the `gcloud sql export csv ... ` command cannot be run while another instance of it is running. Is there a way I can execute these to either run simultaneously?

If not then will I have the same problem with the REST API implementation?

I'd rather not spend time setting up Auth2.0 for the REST API if it's going to have the same problem. See code and error message below.

Thanks in advance!

Harpal

________________

$ gcloud sql export csv my-instance-id gs://bucket-name/sector_cls/automatic/test.csv --database=my-db --query="SELECT * FROM public.my_table"
>>>

ERROR: (gcloud.sql.export.csv) HTTPError 409: Operation failed because another operation was already in progress. Try your request after the current operation is complete.

Moonfire Ventures LLP (FRN: 932750) is authorised and regulated by the Financial Conduct Authority.

[babdelrahman]

You have received a HTTP:409 error because most operations in Cloud SQL are synchronous and you can only run one operation at a time. You can perform only one import or export operation at a time for each instance [1].

Exporting using REST or CLI creates a lock on the sql instance while the exporting process is in progress.

To fix this, you need to list all the operations running on the instance, and then use ‘gcloud sql operations wait’ on all the other operations [2].

[1]https://cloud.google.com/sql/docs/mysql/import-export#reduce_long-running_import_and_export_processes

[2] https://cloud.google.com/sdk/gcloud/reference/sql/operations/wait

[Harpal Khing]

Thanks for the suggestion @babdelrahman.

It seems as if the orchestration software i'm using is programmatically executing each `gcloud sql export csv ... ` command on separate threads. This is causing massive problems with which operation to `wait` at any given moment. Especially since am not sure if I can easily re/name each operation.

Are you aware of any other tools or methods that are better suited for downloading/exporting these tables in a multithreaded setting? Perhaps something similar to the multithreaded upload command `gsutil cp -m ... ` ?

Best wishes

Harpal

[Horace (Cloud Platform Support)]

There is already a feature request [1] for a Cloud SQL export with multi-threaded process, however do note that feature requests do not have ETA's nor guarantee to be implemented. I would invite you to log a comment on the public issue tracker to indicate that you are also requesting for the feature. 

Additionally, kindly take a look at export is taking too long [2, 3] section as it might be applicable on your use case. 

[1] https://issuetracker.google.com/141981674

[2] https://cloud.google.com/sql/docs/mysql/import-export#troubleshooting-export
[3] https://cloud.google.com/sql/docs/mysql/import-export#serverless