How to Move Data From MariaDB to BigQuery: Batch and CDC Methods

You can move data from MariaDB to BigQuery using two main approaches: export MariaDB tables as files for a batch load, or use change data capture (CDC) to replicate ongoing inserts, updates, and deletes from the MariaDB binary log.

CSV is suitable for small, one-time migrations and infrequent batch loads. CDC is better when MariaDB changes continuously and BigQuery must remain current without repeatedly exporting complete tables. CDC can be implemented through a managed platform such as Estuary or through a self-managed stack using Debezium, Kafka, and Dataflow.

This guide compares these options and shows how to build a managed MariaDB-to-BigQuery CDC pipeline with Estuary.

MariaDB to BigQuery methods compared

Method 1: Load data from MariaDB to BigQuery using CSV

This method exports a MariaDB table to a CSV file and loads that file into BigQuery. It is most appropriate for a one-time migration or an infrequent batch process.

Step 1: Export a MariaDB table as CSV

Run the following statement through a MariaDB client:

sql
SELECT *
FROM database_name.table_name
INTO OUTFILE '/var/lib/mysql-files/target_file.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';

Replace database_name, table_name, and the output path with your values.

INTO OUTFILE writes the CSV file to the MariaDB server rather than your local computer. The database user needs the FILE privilege, the output file must not already exist, and the permitted directory may be restricted by the server’s secure_file_priv setting. You must then securely copy the exported file from the database server to the environment that will run the BigQuery load job.

The example creates a file without a header row. If you add column names as the first row, change skip_leading_rows to 1 in the BigQuery configuration below. See MariaDB’s SELECT INTO OUTFILE documentation for additional options.

Step 2: Load the CSV file into BigQuery

Install the Google Cloud BigQuery client library, configure Application Default Credentials, and run the following Python code:

python
from google.cloud import bigquery

client = bigquery.Client()

file_path = "/path/to/target_file.csv"
table_id = "project_id.dataset_id.table_id"

job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.CSV,
    skip_leading_rows=0,
    autodetect=True,
    write_disposition=bigquery.WriteDisposition.WRITE_EMPTY,
)

with open(file_path, "rb") as source_file:
    load_job = client.load_table_from_file(
        source_file,
        table_id,
        job_config=job_config,
    )

load_job.result()

table = client.get_table(table_id)
print(
    f"Loaded {table.num_rows} rows and "
    f"{len(table.schema)} columns into {table_id}"
)

Replace file_path and table_id with your file path and fully qualified BigQuery table ID.

This example uses schema autodetection for simplicity. BigQuery infers data types from a sample of the input, so define and validate the schema explicitly for production migrations where type accuracy is important. If the CSV contains a header row, set skip_leading_rows=1.

Google explains that schema autodetection examines up to the first 500 rows, so it should not be treated as full-file validation. See the BigQuery schema autodetection documentation.

Limitations of CSV-based migration

• No automatic change synchronization: New inserts, updates, and deletes in MariaDB are not automatically reflected in BigQuery after the file is exported.
• Snapshot consistency: If source tables change during a multi-table export, the files may not represent a consistent point-in-time snapshot unless you use an appropriate transactional export strategy.
• Schema management: CSV does not preserve source data types. BigQuery must infer the schema or use a schema that you define.
• Server access requirements:INTO OUTFILE requires server-side file access and appropriate MariaDB privileges.
• Repeated source load: Recurring full exports can increase load on MariaDB and repeatedly transfer unchanged records.
• Duplicate-load risk: Scheduled scripts need checkpoints, idempotent loading, and reconciliation logic to avoid missing or duplicating data.

Alternative: Self-managed CDC with Debezium, Kafka, and Dataflow

Teams that already operate Kafka can build a self-managed CDC pipeline. The Debezium MariaDB connector reads change events from the MariaDB binary log and publishes them to Kafka topics. A Dataflow Kafka-to-BigQuery pipeline can then process the events and write them to BigQuery.

This approach provides infrastructure-level control, but the engineering team must operate the connector, Kafka cluster, schema and serialization strategy, Dataflow jobs, error handling, monitoring, retries, and recovery procedures. It is generally most suitable when Kafka is already part of the organization’s production architecture.

Method 2: Continuously replicate MariaDB to BigQuery with Estuary

Estuary is a managed real-time data movement platform that combines CDC, streaming, and batch pipelines. Its MariaDB connector reads row-level change events from the MariaDB binary log rather than repeatedly querying source tables.

By default, the connector first backfills the current contents of selected tables. After the backfill completes, it continuously captures inserts, updates, and deletes and stores them in reusable Estuary collections. A BigQuery materialization then writes those collections into tables in a selected BigQuery dataset through a Google Cloud Storage staging bucket.

The BigQuery connector supports standard updates, which maintain reduced tables based on collection keys, and delta updates for append-oriented or high-volume workloads. Standard updates are the default. Source deletes are represented as soft deletes by default, while hard-delete propagation can be enabled when required.

Prerequisites for MariaDB CDC

Before configuring the pipeline, prepare the MariaDB source and BigQuery destination using the following requirements.

• MariaDB 10.3 or later.
• binlog_format must be set to ROW.
• Binary logs should be retained for at least seven days; Estuary recommends 30 days where possible.
• The capture user needs REPLICATION CLIENT, REPLICATION SLAVE, and SELECT privileges.
• Automatic table discovery also requires access to information_schema.
• A time zone must be configured when capturing DATETIME columns.
• Read-replica capture is supported when binary logging is enabled on the replica.

Prerequisites for BigQuery

• Create a Google Cloud Storage bucket in the same region as the destination BigQuery dataset.
• Create a Google Cloud service account or configure supported Google Cloud IAM authentication.
• Grant roles/bigquery.dataEditor on the destination dataset.
• Grant roles/bigquery.jobUser and roles/bigquery.readSessionUser on the associated project.
• Grant roles/storage.objectAdmin on the staging bucket.

Step 1: Prepare MariaDB and BigQuery

Configure MariaDB with row-based binary logging, sufficient binlog retention, and a capture user with the required replication and table-level read permissions.

In Google Cloud, prepare the destination BigQuery dataset, a GCS staging bucket in the same region, and the required service account or IAM authentication.

Step 2: Create the MariaDB capture

• Sign in to the Estuary dashboard.
• Open the Sources tab and select + New Capture.

• Search for the MariaDB connector and select Capture.

• Give the capture a unique name.
• Enter the MariaDB server address, username, password, and time zone when required.

• Click Next and review the discovered tables and target collections.
• Select the tables you want to capture, then click Save and Publish.

By default, Estuary backfills the current contents of the selected tables before switching to ongoing change capture from the MariaDB binary log.

Step 3: Create the BigQuery materialization

• After publishing the capture, click Destinations and select +New Materialization.

• Search for Google BigQuery and select Materialization.

• Give the materialization a unique name.
• Enter the project ID, region, dataset, GCS bucket, authentication details, and optional bucket path and billing project ID.

• Click Next and review the source collections and their BigQuery table mappings.
• Choose standard or delta updates for each binding when required.
• Configure hard-delete behavior and the materialization schedule.
• Click Save and Publish.

Estuary then captures ongoing MariaDB changes into collections and delivers them to BigQuery according to the configured materialization schedule.

After the pipeline is published, Estuary continuously captures changes from the MariaDB binary log. The BigQuery materialization applies those changes according to its configured sync schedule. For standard updates, Estuary recommends a schedule of five minutes or longer for most workloads to avoid BigQuery’s per-table load-job quota. Delta updates can avoid this quota for suitable append-oriented workloads, but they do not produce a fully reduced current-state table.

Production example: Soli & Company used Estuary to connect six MariaDB instances to BigQuery through a single pipeline in under two weeks. The company reported a 3x reduction in total CDC vendor costs and an implementation that was 6x faster than the evaluated alternative. Read the Soli & Company success story.

Want to build this pipeline yourself? Start streaming data from MariaDB to BigQuery with Estuary or explore the MariaDB-to-BigQuery integration

Conclusion

CSV exports are suitable when you need to move a limited dataset once or can tolerate infrequent batch updates. However, this approach requires manual schema management, does not automatically propagate inserts, updates, or deletes, and becomes harder to operate as data volume and freshness requirements grow.

For continuous replication, log-based CDC captures MariaDB changes from the binary log without repeatedly exporting complete tables. Teams that already operate Kafka can build this pipeline with Debezium and Dataflow, but they must manage the streaming infrastructure, monitoring, retries, and recovery processes.

Estuary provides a managed alternative that backfills existing MariaDB tables, captures ongoing changes, and materializes them into BigQuery on a configurable schedule. This makes it better suited to teams that need continuously refreshed BigQuery data without operating a separate CDC and streaming stack.

Ready to keep BigQuery synchronized with MariaDB without managing separate CDC infrastructure? Start building with Estuary for free or talk to an Estuary expert.