Simba ODBC driver error "Dataset xx was not found in location US"

[jonnyPS]

Hi,

I am using the Simba Google BigQuery ODBC 64-bit Linux driver (version 2.5 downloaded from https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers) and am able to successfully connect to my project and can view the list of tables in my dataset. However, when I try to query a table I get error:

"Dataset is not found. Not found: Dataset <dataset name> was not found in location US".

My dataset has a location of 'EU' when viewed in the Cloud console. I have configured my odbc.ini configuration file with parameters including Catalog and DefaultDataset but I still get the error. 

Catalog=<project ID>
DefaultDataset=<dataset name>

I've also tried setting DefaultDataset as <project ID>.<dataset name>

Is there any way to define the location as 'EU' in my configuration, assuming that is what I need to do?

Thanks.

[cristianrm · Accepted solution]

To solve this issue, please follow this steps:

1. (If not exist already) Create a temporary dataset for large results in the same region where the table is located.
2. Set the LargeResultsDataSetID specifying the temporary dataset created in step 1.

This limitation is caused when a temporary dataset for large results does not exist, the Simba ODBC Driver connector, will create a dataset in the US region. Due to this limitation, the location for the new temp dataset cannot be specified.

Because the temp dataset is located in the US region, the query will look for the source dataset within the US region, leading to a dataset not found error.

[jonnyPS]

Thank you for your reply. I created a secondary dataset in the same EU region and referenced the name against the LargeResultsDataSetId property in my odbc.ini file, and now I can successfully query from tables in my original dataset.

Thanks again (though please note that your 'temporary dataset' link navigates to Creating a temp table, not creating a dataset).

[tamannapatil]

Hi @cristianrm 

We are also facing the similar issue with Simba ODBC driver for BigQuery and all our datasets are in "australia-southeast1" location. But when we try to write data to BigQ using Alteryx, it gives error " dataset was not found in US location". We created another dataset in same location "australia-southeast1" and set the dataset location in ODBC driver advance settings but it still doesn't work.

 Could you please share where are you setting this value " LargeResultsDataSetID " and assist further on this issue?

[mczopinski]

I have faced the same problem. For me the solution does not work. It is even worse as when I point LargeResultsDataSetID to collocated-dataset,  ODBC returns error that it cannot create table because of "US violates constraint constraints/gcp.resourceLocations". I have the constraint set to UE. Without the LargeResultsDataSetID it creates a job in US location and cannot find the dataset there.

I couldn't solve that issue.

[jonnyPS]

Further update on this - I have since commented out LargeResultsDataSetId  from my odbc.ini  file and I no longer need a secondary dataset. In my case this property is not required as I am not handling large result sets.