Looked into this topic for several days now and in my opinion BigQuery Notebooks (Python) with the Cloud SQL connector library is a good way doing so: How to Connect to Cloud SQL using Python

Loading the data into a DataFrame is two lines:

%%bigquery results --project xxx
SELECT * FROM yyy

Afterwards, just follow the description in the link to establish the connection and write the entire DataFrame with:

results.to_sql(zzz)

This is still not super fast, however, if you get the concept, you may consider parallelizing the process and identify the bottleneck: Colab, network, Cloud SQL...