CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-sql
laplacian
laplacian

Reputation: 11

Speed up BigQuery query job to import from Cloud SQL

I am performing a query to generate a new BigQuery table of of size ~1 Tb (a few billion rows), as part of migrating a Cloud SQL table to BigQuery, using Federated query. I use the BigQuery Python client to submit the query job, in the query I select all from the the Cloud SQL database table and use EXTERNAL_QUERY.

I find that the query can take 6+ hours (and fails with "Operation timed out after 6.0 hour")! Even if it didn't fail, I would like to speed it up as I may need to perform this migration again.

I see that the PostgreSQL egress is 20Mb/sec, consistent with a job that would take half a day. Would it help if I consider something more distributed with Dataflow? Or simpler, extend my Python code using the BigQuery client to generate multiple queries, which can run async by BigQuery?

Or is it possible to still use that single query but increase the egress traffic (database configuration)?

Upvotes: 0

Views: 200

Answers (1)

SeungwooLee
SeungwooLee

Reputation: 969

I think it is more suitable to use the dump export.

Running a query on large table is an inefficient job.

I recommend to export Cloud SQL data to a CSV file.

BigQuery can import CSV format file, So you can use this file to create your new bigQuery table.

enter image description here

I'm not sure of how long this job will takes, But at least will not be failed.

Refer here to get more detailed job about export Cloud SQL to CSV dump.

Upvotes: 1

Related Questions