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

[rachaelkertes]

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.

[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-Go...

[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

[caltang]

When you said someone tested your workflow, are they on the same Alteryx version as you? Strange... 

[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-n...

[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?

[Trailduck · Accepted solution]

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.

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

[rachaelkertes]

Thanks @Trailduck - I've tried to set this up and am now getting a different error. I haven't set up an In-DB connection before so please bare with me. 

I am now getting the error " Data Stream In (61) Failed to complete write to Google BigQuery: Error while reading data, error message: The Apache Avro library failed to parse the header with the following error: Invalid name: vic-occupancy File: bigstore/str_hotel_occupancy_data/AYX230613a42326c2de8aa5303abfe0e482a5a188 "

When you configure your set up, how do you configure the Bucket location for temporary tables? This is probably the error. The bucket location is australia-southeast2 (Melbourne), same as the dataset. 

Thanks for your help!

[rachaelkertes]

Thanks @Trailduck I got this working.

One thing to note is that the Apache Avro library does not accept "-" in headers, so I changed them all to underscores "_" and it worked fine.

Also interestingly I was getting an error for the Delete Data & Append output, but the Overwrite (Drop) output works perfectly. 

Thanks again :)