BigQuery Location Mismatch Error with Datastream

[bsummy]

I'm getting a location constraint error when trying to create a table that queries data from my Datastream-replicated dataset.

Setup:

• Datastream is replicating data to a BigQuery dataset in us-east1 region 
• I'm trying to create a new table in a schema that's in the US multi-region
• Getting the error about single-region and multi-region locations not being compatible in the same query

Question:  I don't mind re-syncing the data, but it doesn't seem like I can set up a multi-region Datastream Stream. What should I do?


EDIT: 

For best results, keep all resources in one region. Choosing the same region as your source data can optimize cost, performance, and compliance. 

This comes from the datastream connection profiles. Saying "For best results, keep all resources in one region" implies that I should be able to set it to multi-region, but it's not in that menu.

[alisha47]

The reason you are encountering a location constraint error is due to the mixing of regions. Your Datastream target dataset is located in a region (us-east1), which is a single region. But you are trying to create a table in a multi-region (US). BigQuery does not support queries that span single and multi-region resources. 

Follow the given steps to resolve the problem:

If you have not already done so, create a new BigQuery dataset in the us-east1 region.

• Access the BigQuery console.
• Select “Create Dataset.”
• Set the location to the us-east1 region. Do not select US.

Create/update a new Datastream stream.

• Ensure the target dataset is your new dataset in us-east1.
• Datastream multi-region targets are not supported.

Re-sync your data for the new dataset.

• You may need to delete the existing target tables prior to the sync.

Construct your queries to only use datasets located in us-east1.

• Don't use cross references on joins or views that have multi-region datasets, like the US.

While this may seem like a completely unreasonable burden, it is the most effective way forward. Keeping all resources in the same region like the us-east1 region minimizes errors, improves performance, and aligns with Google Cloud's best practices.

If you're looking for a more flexible architecture and want to avoid the strict regional constraints of native BigQuery and Datastream workflows, you might consider a data integration platform like Windsor. It abstracts away regional limitations by default by intelligently syncing data from hundreds of data sources across locations and cloud services.

[caryna]

Hi @bsummy,

Welcome to Google Cloud Community!

You are having location constraints issues since you're querying across different regional locations (us-east-1 and US multi region) which is one of BigQuery's limitations.

Regarding your question, you can specify a multi-region location when configuring information about your stream destination (BigQuery) as shown below:

You may also find this public tracker relevant to your case, this might give you some useful insights. 

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.