Using Mutator to create our BigQuery table and StreamLoader to load, our BQ events table ends up in us-central1. The rest of our BQ data is in US. Is there a way to get Mutator and StreamLoader to work in US instead of us-central1? I’ve tried moving the dataset, but that breaks StreamLoader.
What error do you get when you try this? I would have thought this is the easiest option and the most likely to work if your existing data is in the US multiregion.
Not really. The multi-region concept (US/EU) is somewhat unique to BigQuery - that data can reside in multiple geographic areas at once whereas compute (in this case mutator / streamloader) has to run within a single region.
I get a 404 Table not found error when I tried moving the dataset to US. Is there something I can change within the configs to have the StreamLoader recognize the events table when it is in the US region?
There’s no region for stream loader but I’m not sure if there may be some eventual consistency on the BigQuery API side.
Is it a new dataset / table you are trying in the US region compared to us-central1?
I originally had StreamLoader successfully loading data into a table (created by the Mutator) called
project-id.snowplow.events which was located in us-central1. When I realized it would be impossible to join to our other data located in US, I deleted and recreated the dataset in the US region (copying the schema over, so I again have a table named
project-id.snowplow.events, but it’s been created manually and not by the Mutator). That’s when StreamLoader started throwing the Table not found error.
Could you try (just as a temporary test) with a different dataset name in US? I’m wondering if the BigQuery API might be caching the location of the dataset.
@mike – brilliant! Creating a completely new dataset.table in US and pointing StreamLoader to that solved the problem. Thanks so much for your help!
No worries - I suspect the same dataset and table name will work in US at some stage - there might just be some eventual consistency in the BQ APIs in returning this as I’m not sure what else would do it.