ODBC Simba driver for BigQuery returning a Dataset not found error using Output Tool

I'm new to Alteryx and setting up my connection to BigQuery using the ODBC Simba driver.

I'm trying to write a small table of data using the "Delete Data & Append" option of the Output Data Tool and am receiving an error back "Error running TRUNCATE table "projectname.datasetname.tablename":[Simba][BigQuery](31750) Dataset is not found. Not found: Dataset projectname.datasetname.tablename was not found in location US"

I have checked my ODBC config against help docs online and a 3rd party agency and it seems fine. Someone has even tested my workflow and been able to write data to the BQ table on their system, so what is wrong with my set up?

I am in Australia and my dataset is set up on Australian BQ servers, however I don't know how to configure a different location setting? And unsure why this would make a difference.

I'm not even sure what else to try! Any advice would be amazing, thankyou.

Error Message

Output

Connectors

Database Connection

Accepted answers

Trailduck

Hmm... I've been dealing with this issue myself now for some time as well. The BigQuery connector has some room for improvement, and the bulk operations is not utilizing the recommended GCP write APIs, which makes it quite slow. What I ended up doing was switching from the BigQuery output connector to an in-db connector. You can transfer the data in your workflow to a static temp table you create in BQ (or you could have it create one for you like this config). I haven't tested this on large datasets, but it works great with lightning speed on <100K rows datasets from my experience.

All comments

caltang

What version of Alteryx are you running on?

Does this help? https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/DE28086-Intermittent-404-GoogleAPIError-NotFound-thrown-with-the/ta-p/897227

rachaelkertes

Thanks @caltang I'm not using BigQuery Tools as this was deprecated. I'm using the Output Data Tool connected with Google BigQuery ODBC with Simba.

Running Alteryx Designer x64 Version: 2023.1.1.123

Trailduck

Hi,

Predefining a temp table for large datasets in your desired region should do the trick.

https://www.googlecloudcommunity.com/gc/Data-Analytics/Simba-ODBC-driver-error-quot-Dataset-xx-was-not-found-in/m-p/488010

rachaelkertes

Thanks @Trailduck I found this article whilst troubleshooting the issue is there is nowhere to define the temporary table?

Plus I am trying to write 3 columns, 13 rows of basic data, so it shouldn't be classed as a large dataset anyway.

Update: I set up a new dataset for testing and gave it the location of US, and this allowed the workflow to work. Whilst this is a workaround it's not ideal to have all my BigQuery datasets located in the US and therefore not a scalable solution.

Any other suggestions?

apathetichell

@Trailduck 's suggestion to use the datastream-in is solid. If that doesn't work it may have something to do with what's configured in your odbc 64. If there is a conflict (project location/BQ host address) vs where you are trying to write to that could create this kind of error.

Raaghav

HI , im using in-db connection and am able to read gcp tables using the odbc connection . but im not able to create temp table or write into gcp tables in in-DB .