BigQuery→Cloud SQL Data Transfer Methods
There is no single built-in “BigQuery→Cloud SQL” transfer service, so data engineers typically use one of
several pipeline patterns. Common approaches include:
- Cloud Dataflow (Apache Beam): Custom or template pipelines reading from BigQuery and writing to
Cloud SQL.
- Cloud Functions or Cloud Run: Serverless code triggered (e.g. daily via Cloud Scheduler) that exports or
queries BigQuery and inserts into Cloud SQL.
- BigQuery → Cloud Storage → Cloud SQL: BigQuery EXPORT DATA to GCS (CSV/Avro/Parquet), then
import via Cloud SQL Admin API (e.g. through Cloud Workflows).
- Third-party ETL/ELT tools: Open-source or managed connectors (e.g. Airbyte, Fivetran) syncing BigQuery
tables to MySQL.
- (No direct service): BigQuery Data Transfer Service and Federated Queries do not push data into Cloud
SQL; federated queries only allow BigQuery to read Cloud SQL, not write to it.
Each method can handle large volumes (GBs to 1 TB/day) but varies in cost, complexity, and reliability. The
tables below summarize pros & cons; detailed notes and examples follow.
Google Cloud Dataflow (Apache Beam)
Use a Dataflow (Apache Beam) batch job to read from BigQuery and write into Cloud SQL (MySQL) via JDBC
or a DoFn. For example, a Python pipeline can read with ReadFromBigQuery and use a custom DoFn
(SQLAlchemy or Cloud SQL proxy) to insert into MySQL 1 2 . In Java, Beam’s [Link]() batches
up to 1,000 rows per batch by default 3 .
Pros Cons
Highly scalable: Dataflow can autoscale Complex to implement: No built-in “BigQuery→Cloud
workers to handle very large datasets SQL” connector. Requires custom pipeline code (Java or
(parallel BigQuery reads, JDBCIO batched Python) and careful use of Cloud SQL proxy or network
writes) 3 4 . setup 1 2 .
Cloud SQL bottleneck: Writing to MySQL can be slow;
Flexible transforms: Supports filtering or performance depends on instance size and network. Bulk
light transformations in-flight if needed. inserts are batched (jdbc default ~1000 rows), but very
large loads may still be lengthy 3 .
Automation: Can be scheduled via Cloud
Cost: Dataflow compute cost can be high for TB-scale
Scheduler or triggered in Cloud
loads. Also incurs BigQuery read costs (if querying) and
Composer/Airflow by launching a
Cloud SQL runtime costs.
Dataflow job.
1
Pros Cons
Reliability: Managed service with retry/ Maintenance: Must update/maintain Beam pipeline code
monitoring, logs in Stackdriver. and handle failures (e.g. transient DB errors) in code.
Implementation notes: You may write a Beam pipeline like:
with [Link](options=options) as p:
(p
| 'ReadBQ' >> ReadFromBigQuery(query="SELECT * FROM [Link]")
| 'WriteSQL' >> [Link](WriteToCloudSql()))
# custom DoFn inserting via SQLAlchemy/PyMySQL
(requires packaging connectors and possibly using the Cloud SQL Proxy for secure DB access 1 2 ).
Scalability: Dataflow can handle TB-scale reads with enough worker VMs. JDBC writes can be parallelized
(e.g. partitioning queries) but Cloud SQL instance size may limit throughput. Use a high-tier instance and
fine-tune batch size.
Automation: Launch the Dataflow job daily via Cloud Scheduler/Composer; no native schedule inside
Dataflow.
Cost: Pay-per-GB for Dataflow and BQ reads, plus MySQL compute hours. Potentially high for large loads.
Reliability: Dataflow is reliable and managed, but database inserts must handle retries. See JDBC batching
3 and secure connections (e.g. Cloud SQL proxy recommended 2 ).
Cloud Functions / Cloud Run with Batch Exports
A serverless approach is to use Cloud Functions (Python/Node) or Cloud Run (container) to orchestrate the
transfer. For example, a Cloud Function triggered by Cloud Scheduler can: 1) run a BigQuery query (or
EXPORT), 2) write results to Cloud SQL via a language connector, or 3) initiate an export-and-import. Cloud
Run (longer max runtime and more memory) can similarly run a Python/Go/etc. script.
Pros Cons
Limited resources: Cloud Functions have time/memory
Simplicity: Write a short function that
limits (Gen1 ~9 min, Gen2 up to 1 hr) which may be
calls the BigQuery API and MySQL
insufficient for >GB loads. Cloud Run can run longer, but
connector. No need for Dataflow SDK.
must be managed.
Managed compute: Auto scales (Cloud
Manual chunking: For very large transfers (~1 TB), you
Run) or autoscaling (Function) with pay-
must batch or page the data manually to avoid timeouts.
per-use.
2
Pros Cons
Complexity: Need to manage DB connections (likely via
Easy scheduling: Integrate with Cloud
Cloud SQL Proxy or IAM-auth libraries) and possibly track
Scheduler to call the function daily.
progress.
Low operational overhead: No VM Cost: Generally cheaper for small jobs; but continuous
management. heavy usage (many instances) can add up.
Example: A Python Cloud Function could use the BigQuery client and Cloud SQL Python library. A pseudo-
code:
def transfer_daily(request):
rows = bigquery_client.query("SELECT * FROM [Link]").result()
conn = connect_to_cloud_sql()
for row in rows:
[Link]("INSERT INTO target_table ...", [Link]())
But for large tables, you'd break into chunks (e.g. filter by date or ID ranges).
Alternatively, the function could trigger a BigQuery EXPORT DATA to GCS, then call the Cloud SQL Admin
API (via REST/gcloud) to import the CSV files.
Scalability: Cloud Functions are better for smaller loads. Cloud Run can handle more (with concurrency) but
very large loads (100s of GB) may need multiple invocations or sharding.
Automation: Use Cloud Scheduler (pub/sub trigger) to invoke the function/container every day.
Cost: You pay for execution time. If the function runs many minutes repeatedly, costs rise. No idle cost.
Reliability: Simpler code is easier to test, but functions may hit timeouts or concurrency limits. Cloud Run is
more robust. Both require retries/monitoring. According to Google’s guidance, this is best for smaller or less
frequent transfers 5 .
BigQuery → Cloud Storage → Cloud SQL Import
This batch pattern uses BigQuery’s EXPORT DATA SQL statement to write query results to Cloud Storage,
then loads that file into Cloud SQL using the Cloud SQL Admin import API. A typical implementation uses
Cloud Workflows or Cloud Scheduler + Functions to orchestrate the steps 6 7 . In short:
1. Export: Run a BigQuery EXPORT DATA query: e.g.,
EXPORT DATA OPTIONS(
uri='gs://bucket/prefix-*.csv',
3
format='CSV', overwrite=true, header=false
) AS SELECT * FROM `[Link]`;
This writes one or more CSV (or Avro/Parquet) files to GCS 6 . (No query cost if exporting an entire
table; otherwise you pay for the SELECT.)
2. Import: Call the Cloud SQL Admin API (or gcloud sql import csv ) to load each CSV into
MySQL. For large exports (multiple shard files), you loop over files and import sequentially 8 9 .
Each import is asynchronous, so you poll until status=“DONE” 10 .
3. Orchestration: Use Cloud Workflows or a scheduled script to sequence the export and import steps.
For example, Google’s community blog shows a Workflow that queries BigQuery, writes to GCS, then
calls the [Link] API 6 7 . The Workflow can be triggered daily via
Cloud Scheduler (or via a Cloud Function) 11 .
Pros Cons
Handles bulk loads well: BigQuery Multi-step complexity: Requires managing two services
export is designed for TB-scale exports (BigQuery and Cloud SQL Admin) and orchestrating them.
and can split output into shards 6 . Each import only handles one file at a time 8 9 .
Official Google tools: Uses native
EXPORT DATA and Cloud SQL API; no Latency: The process is inherently batch and can take time
custom code for the data movement (BigQuery export + sequential imports).
itself.
Idempotency issues: You must handle partial failures (e.g.
Scalable for large volumes: By using
re-running export, re-import). Workflows or scripts must
BigQuery export, you leverage
be robust (the example code includes loops and checks 10
BigQuery’s scalability for 1 TB+ datasets.
8 ).
Cost of storage: Storing the exported data in GCS (even
Automation: Cloud Scheduler can
temporarily) incurs minimal storage cost. Also, Cloud SQL
trigger the entire workflow daily 11 .
must import large data (long write operations).
Scalability: BigQuery export can produce many files and handle TB of data efficiently. The bottleneck is
then the MySQL import speed and DB instance capacity. You should use a powerful Cloud SQL instance and
potentially increase its import file size limits.
Automation: This can be fully automated. For example, a Cloud Workflow can be triggered daily via
Scheduler. The workflow can call the BigQuery [Link] API to EXPORT DATA , then iterate over files and
call the Cloud SQL API for each import 6 8 .
Cost: Minimal for BigQuery side (no extra charges beyond query cost). GCS storage cost for temporary CSV
is low. Cloud SQL import itself is free, but the Cloud SQL instance must run during load. Workflows and
Scheduler have nominal charges.
4
Reliability: This is a robust, repeatable process. Cloud Workflows handles retries/polling as needed 10 . You
must give the workflow service account appropriate IAM roles ( [Link] ,
[Link] , [Link] as shown 12 13 ). In practice, many Google architects
recommend this method for bulk transfers of data 14 6 .
Third-Party ETL/ELT Tools (Airbyte, Fivetran, etc.)
Many data-integration platforms support BigQuery and MySQL connectors. For example, Airbyte (open-
source) provides a BigQuery source and MySQL destination connector, enabling point-&-click syncs 15 .
Fivetran offers a fully-managed BigQuery connector (with a paid SaaS) that can replicate tables into target
databases 16 (though its MySQL connector is typically used as a source). Other tools include Stitch, Hevo
Data, and cloud ETL services.
Pros Cons
Turnkey connectors: Minimal coding – you Cost: Managed tools can be expensive (often pay-
configure source, target, and schedule via a UI per-row or flat subscription). Open-source (Airbyte)
or simple configuration. requires hosting infrastructure.
Incremental/CDC support: Many tools handle
Flexibility: May have limitations on very large tables
incremental syncs or CDC natively, reducing
or custom transformations; tuning may be required.
data volumes transferred.
Monitoring & tooling: Built-in dashboards for Data latency: Typically run at intervals (e.g. minutes
job status, retries, schema drift handling. to hours), not truly real-time.
Custom connectors: Airbyte’s open-source
Vendor lock-in: Using a service means dependency
connectors can be customized (CDK) if needed
on that vendor/tool. Exiting can require effort.
17 .
Scalability: Modern tools can sync multi-GB tables, but 1 TB/day is very large. Airbyte is multi-threaded but
depends on your deployment size. Fivetran’s SaaS can scale but pricing goes up. For very high throughput,
you must ensure the tool supports parallel load to MySQL (Airbyte streams tables in chunks).
Automation: These platforms have built-in schedulers. You set a sync frequency (e.g. daily), and they will
run jobs automatically.
Cost: Airbyte Open Source has no software license fee, but you pay for the VM/cluster running it. Fivetran/
Stitch/Hevo charge by rows or number of connectors (often quite high for TB-scale).
Maintenance: Low effort once configured, but you must still monitor runs. Open-source Airbyte requires
you to maintain the Airbyte deployment (Docker, Kubernetes, etc.). Managed tools handle updates for you.
Example: Airbyte’s documentation shows setting up BigQuery as a source, MySQL (Cloud SQL) as
destination, and syncing 15 . Fivetran similarly advertises “real-time, efficient replication from
BigQuery” 16 . These tools handle data type mapping and can retry on transient errors.
5
Direct/Other Integration Methods
• BigQuery Data Transfer Service: Not applicable – it only moves data into BigQuery (from Google
Ads, Storage, S3, etc.), not out to Cloud SQL.
• Federated Queries / External Tables: BigQuery can run queries on external Cloud SQL tables
(EXTERNAL_QUERY), but that only reads Cloud SQL data into BQ. There is no corresponding feature
to write BQ results into Cloud SQL without an intermediate step.
• Datastream: Only supports on-premises/Cloud SQL → BigQuery (CDC), not BQ→SQL.
In short, no single direct Google-managed connector exists for BigQuery→Cloud SQL; you must use
one of the above pipeline patterns.
Recommendations
For daily ETL of GB–TB-scale data, we recommend using the BigQuery→GCS→Cloud SQL workflow, as it
leverages scalable built-in operations and can be fully automated. In practice, many teams use BigQuery’s
EXPORT DATA and Cloud SQL import in a Cloud Workflow or Cloud Composer job 6 11 . This approach
can reliably move 1 TB+ per day (BigQuery export handles scale, and imports run under control).
If you already use Apache Beam or need streaming/transform logic, Cloud Dataflow is a strong option. It
can handle large volumes, but requires more engineering effort (writing and maintaining the pipeline code)
4 3 . For smaller or simpler cases, a Cloud Run/Functions script may suffice, but watch for function
time limits and ensure parallelism for big loads.
Third-party ETL tools are easiest to set up: e.g. Airbyte (self-hosted or Cloud) can sync BigQuery to MySQL
with minimal coding 15 , and Fivetran provides a managed connector 16 . These are worth considering if
operational simplicity is a priority and budget allows.
In summary, use a fully-Google pipeline (BigQuery EXPORT + Cloud SQL import) for maximum scalability
and reliability on large daily transfers 6 11 . For lighter-weight needs or if adopting a modern ELT tool,
third-party connectors can greatly simplify the work 15 16 .
Sources: Google Cloud documentation and expert community examples 4 3 6 15 16 .
1 2 4 5 Push Data from Big Query to Cloud SQL - Data Analytics - Google Developer forums
[Link]
3 Google Dataflow (Apache beam) JdbcIO bulk insert into mysql database - Stack Overflow
[Link]
6 7 8 9 10 11 Replicate data from BigQuery to Cloud SQL with Cloud Workflow | by
12 13
guillaume blaquiere | Google Cloud - Community | Medium
[Link]
14 Data Transfer from BQ To CLoud SQL - Stack Overflow
[Link]
6
15 17 ETL BigQuery data to MySQL Destination fast | Airbyte
[Link]
16 Google BigQuery ETL | Data Integration
[Link]