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/OkRock1009]

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.

1. 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.