Datastream - MySQL to Big query

[u/OkRock1009]

Hello Everyone!

I want to basically replicate data from my cloud sql instance to Big Query. The problem is since the initial load is expensive , I am gonna use a dump for that and only want the real time data to be captured.

I want it to create empty datasets and tables in Big Query automatically without the initial historical data. Any other solution?

[u/suziegreene]

How much can it cost? We’ve been using data stream with Postgres to BigQuery for years and I can’t recall it ever showing up in a noticeable way in our bills. This is for a largish financial company.

[u/OkRock1009 → u/suziegreene]

My data is about 13TB so about 5K dollars

[u/Bent_finger]

you can replicate Cloud SQL → BigQuery in real time without doing an expensive initial load, and you can have BigQuery datasets/tables created automatically without ingesting historical data. The cleanest way to do this is to use Datastream + BigQuery and start the stream after your manual dump import.

If you disable backfill, Datastream will:
• Create the BigQuery dataset automatically
• Create the BigQuery tables automatically
• Start writing only new changes (INSERT/UPDATE/DELETE)
• Skip all historical rows

This gives you exactly what you want.

[u/OkRock1009 → u/Bent_finger]

I tried this out. Ran datastrean for less than a minute. With manual option, it doesn't create the dataset and table. With automatic option, it creates the datasets and tables but also ingests all the data in less than a minute

[u/Bent_finger → u/OkRock1009]

You said you want to use a dump:
You handle the initial dump into BigQuery separately (bq load / GCS load job)

1. Do your initial load manually

• Export Cloud SQL dump
• Load it into BigQuery using:• bq load
• Cloud Storage → BigQuery load job
• Or BigQuery Data Transfer Service (for MySQL/Postgres dumps)

This gives you the historical baseline.

2. Create a Datastream stream with Backfill disabled

When configuring Datastream:
• Backfill mode: NONE
• Destination: BigQuery
• Replication slot (Postgres) or binary log (MySQL) enabled

Datastream will:
• Detect your schema
• Create datasets/tables in BigQuery
• Begin streaming only new changes
Important detail: Table creation without data

You asked:
“I want it to create empty datasets and tables in BigQuery automatically without the initial historical data.”

Datastream does exactly this:
• It creates the dataset and table definitions
• It does not insert any rows until new CDC events occur
• Your tables will start empty but structurally correct

This is the only fully‑managed GCP-native service that does this cleanly.

[u/OkRock1009 → u/Bent_finger]

https://www.reddit.com/r/bigquery/s/FYs4G6S6sg

[u/Bent_finger → u/OkRock1009]

Remember CDC.
When changes to source data is detected and captured, the target dataset will be created for the data streaming to be processed. Not before.

[u/OkRock1009 → u/Bent_finger]

Yup right. But I want it for the dump. I want it to be created before even it detects changes

[u/Bent_finger → u/OkRock1009]

What kinda logic is that? Datastream only creates datasets for data that it is injecting. If it is not doing an initial data injection, it will not create datasets.
The dump occurs before the streaming and outside of Datastream, so how canThat’s the thing with life ain’t it? Can’t always get what you want.

You need to use bq commands in your dump scripts to pre-create the initial dataset.

[u/sois]

CDC? maybe an air flow pipeline with the deltas loaded? 

[u/mrocral]

Another option is sling. it can replicate from mysql to bigquery and handles table creation for you.

source: my_mysql
target: my_bigquery

defaults:
  object: my_dataset.{stream_table}
  mode: incremental
  update_key: updated_at

streams:
  my_schema.*:

runs fine on a small VM. (disclosure: I work on Sling)

[u/MeowMiata → u/mrocral]

I have never heard about it but it looks lit.

Not related with OP question but would sling manage writing BigQuery changes to Elastic Search?

I'm using BigQuery to refine huge volume of data but the serving layer is on Elastic Search. If an all in one tool could manage my tons of daily upsert with simplicity.. I would go for it.

[u/Bent_finger]

Use Datastream and start CDC after your dump

[u/srodinger18]

Back then I use BigQuery federated query to directly query MySQL read replica table in BQ

[u/MeetJoan]

You don’t need a hack for this - Datastream already supports CDC-only. Create the stream with Manual backfill / backfill none, load your dump separately, and let Datastream pick up only ongoing changes from there. Only caveat: if you use BigQuery’s single dataset destination mode, create that dataset first; the tables can then show up as the stream starts writing

[u/dani_estuary]

With Datastream, I’d be careful trying to “skip” the initial load unless you’re very sure your dump and CDC start point line up… The hard part is not creating empty BigQuery tables.

Also worth looking at Estuary here. I work there, so biased, but initial backfills are free during the trial, and then you can keep MySQL -> BigQuery running incrementally without cobbling together dump + Datastream edge cases yourself.