BigQuery to MySQL: How to Move Your Data

Moving data from BigQuery to MySQL runs against the usual direction of travel, and that is exactly why it is worth doing carefully. You are taking something computed in a warehouse, a customer segment, a risk score, an aggregated usage figure, and putting it somewhere an application can read it in single-digit milliseconds. A warehouse cannot serve that traffic. A relational database can.

The hand-rolled version exports a CSV and loads it nightly. This guide covers the managed path with Airbyte, and one thing that makes this pipeline different from most: BigQuery charges you for reading, so the sync mode you choose is a line on a bill rather than just a performance decision.

BigQuery to MySQL at a glance:

Why move data from BigQuery to MySQL?

Two situations account for most of these pipelines.

The first is serving computed results back to an application. Your warehouse calculates something expensive overnight, a churn score, a recommendation set, a usage total, and the product needs to read it per request. BigQuery is not built for that access pattern and MySQL is, so the warehouse computes and the database serves.

The second is giving a tool something it can actually connect to. Plenty of internal applications, admin panels and legacy reporting systems speak MySQL and nothing else. Rather than rewriting them, you land the specific tables they need. In both cases the important word is specific, which is the theme of everything below.

What do you need before you start?

Two of these are credentials and two are decisions that determine what this costs:

A Google service account with read access. Scoped to the project and dataset you are reading. Give it the minimum it needs, because a service account able to read your entire warehouse is a service account able to run up a bill against your entire warehouse.

A view or table designed for this pipeline. Do not point this at a wide raw table. Build a view in BigQuery containing exactly the columns the consuming application reads, flattened, and sync that. It reduces the bytes scanned on every run, which is what you are billed for, and it removes the nested field problem entirely.

A cursor column, if one exists. Incremental sync needs a column that only increases and is set on every write. Where your modelled table has one, use it. Where it does not, full refresh is the honest answer and the frequency should reflect what it costs.

A MySQL user with write access. A database and a user able to create and write tables in the target schema. Airbyte adds its own metadata columns alongside your data, so decide whether the consuming application tolerates them or reads a view that excludes them.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the MySQL side before you begin.

How do you build a BigQuery to MySQL pipeline in Airbyte?

Step 1: Build the view you are going to sync

In BigQuery, create a view containing only the columns the application needs, with nested and repeated fields already flattened into scalar columns. Keep the resulting column names short enough to stay clear of MySQL's 64 character identifier limit, which flattened struct paths reach faster than you expect. This single step solves the cost problem, the type problem and the naming problem at once.

Step 2: Configure the BigQuery source

Click Sources in the left navigation, then New Source, and select BigQuery, following adding a source. Supply the project ID, the service account key, and optionally a dataset ID to scope what the connector can see. Scoping to a dataset is worth doing even when not required, because it limits the blast radius of a misconfigured connection.

Step 3: Configure the MySQL destination

Click Destinations, then New Destination, and select MySQL, following adding a destination. Supply the host, port, database and credentials. If the consuming application is latency-sensitive, think about which MySQL instance this writes to, because a sync writing a large table is a write load arriving on a database that is also serving requests.

Step 4: Choose sync modes with the bill in mind

Click Connections, then New connection, select your tables, and pick a sync mode per stream. Use incremental wherever a cursor exists, even on tables small enough that it does not seem to matter, because the ten minutes it costs to identify a cursor is repaid on every subsequent sync. Set a frequency that reflects how often the underlying model actually changes: a score recomputed nightly does not need an hourly sync.

Why does full refresh cost money here?

On most pipelines, choosing full refresh for a moderately sized table is a performance decision you can revisit later. Here it is a financial one, because BigQuery bills for the bytes a query scans and every full refresh scans the whole table again.

A daily full refresh of a table a cursor could have read incrementally is a recurring charge for data you already have. Multiply that by a handful of tables and a year, and it is a real number that nobody budgeted for because it never appeared as a decision, only as a default.

Two things reduce it. Sync fewer columns, which is what the view in step 1 achieves, since BigQuery bills on the columns a query touches rather than the whole row. And if the source table is partitioned, keep your cursor aligned with the partitioning column so the scan can prune partitions rather than reading everything.

How do nested and repeated fields land in MySQL?

Badly, if you leave them alone. BigQuery supports nested and repeated fields natively and analytics teams lean on them heavily, because the query language makes them pleasant to work with. MySQL's relational model has no direct equivalent, so anything nested has to be flattened or serialised on the way through.

Handle it deliberately in BigQuery rather than discovering how it resolved itself in the destination. A flattened view gives you a schema that maps cleanly, column names you control, and as a side effect fewer bytes scanned. It is the same fix as the cost fix, which is why step 1 is where it is.

Watch the resulting names. MySQL caps identifiers at 64 characters, and a flattened path through two levels of struct reaches that quickly. Alias the columns in your view to something short and readable rather than letting a generated name collide.

Frequently asked questions

How do I keep BigQuery costs down on this pipeline?

Sync a narrow view rather than a wide table, use incremental sync wherever a cursor exists, keep the cursor aligned with any partitioning column, and reduce frequency on anything that must run full refresh.

Are deletes propagated to MySQL?

Not with incremental sync, which is cursor-based and cannot observe a deletion. A periodic full refresh on the affected tables is the usual fix where deletions matter.

What happens to nested and repeated fields?

MySQL has no direct equivalent, so flatten them in a BigQuery view before they reach the pipeline rather than resolving it at the destination.

Should I sync a raw table or a view?

A view, almost always. It cuts the bytes scanned, removes the nested field problem, and lets you control column names so they stay inside MySQL's 64 character limit.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want to write the BigQuery view in SQL, which is a few lines and pays for itself immediately.

Get your BigQuery data into MySQL

Build the narrow flattened view first and everything else gets easier: the sync is cheaper, the types map cleanly, the column names behave, and the write load on your MySQL instance stays small. Then use incremental sync wherever a cursor exists and set the frequency to match how often the underlying model actually changes.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same BigQuery source can feed other systems alongside this one. For BigQuery into another warehouse, see BigQuery to Amazon Redshift, and for BigQuery feeding a streaming platform, BigQuery to Kafka.

Start syncing now →