How to Migrate from BigQuery to MariaDB in PostgreSQL

Why migrate from BigQuery to MariaDB?

Teams switch to MariaDB for lower cost, on-prem control, or OLTP workloads that need row-level updates BigQuery lacks.

What are the high-level steps?

1) Export BigQuery data to Cloud Storage. 2) Download or stream files to the MariaDB host. 3) Create equivalent tables in MariaDB. 4) Load data with LOAD DATA INFILE or mysqlimport.5) Verify row counts and checksums.

How do I export BigQuery tables?

Use EXPORT DATA to write compressed CSV or AVRO files to GCS. Partition large tables to keep file sizes <2 GB for MariaDB import.

Export Orders table

EXPORT DATA OPTIONS( uri='gs://ecom_dump/orders_*.csv', format='CSV', overwrite=true, header=true ) AS SELECT * FROM `shop.sales.Orders`;

How do I create matching MariaDB tables?

Translate BigQuery types: STRING→VARCHAR, INT64→BIGINT, NUMERIC→DECIMAL, TIMESTAMP→DATETIME.Keep column order identical to the CSV.

Orders table definition

CREATE TABLE Orders ( id BIGINT PRIMARY KEY, customer_id BIGINT, order_date DATETIME, total_amount DECIMAL(12,2) );

How do I load data efficiently?

Place CSV files in secure-file-priv directory.Disable foreign keys and indexes during load, then rebuild.

Fast bulk load

SET foreign_key_checks=0; LOAD DATA INFILE '/var/lib/mysql-files/orders_1.csv' INTO TABLE Orders FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES; SET foreign_key_checks=1;

How can I verify migrated data?

Compare row counts: SELECT COUNT(*) FROM Orders; vs BigQuery.For numeric columns, run SUM checksums on both sides.

Best practices for a smooth migration

Automate type mapping, export in parallel, compress files, use transactions for critical loads, and schedule downtime or CDC for cut-over.

Common mistakes and fixes

Wrong delimiter: BigQuery default is comma; mismatched FIELDS TERMINATED BY causes shifts.Align delimiters.
Type overflow: BigQuery NUMERIC supports 38 digits; pick compatible DECIMAL scale in MariaDB.

Example migration: Orders & OrderItems

Export both tables, create targets, load, then run a join test: SELECT COUNT(*) FROM Orders o JOIN OrderItems i ON o.id=i.order_id; to confirm referential integrity.

FAQs

Can I use Parquet instead of CSV?

MariaDB 10.6+ supports CONNECT engine for Parquet, but CSV remains simpler and faster for bulk loads.

How do I handle incremental changes?

Export only new partitions or use Datastream/MaxScale CDC to replicate changes until the final switch.

.